Error King

[Futureworks]

Hello people,

I keep on receiving this error whenever entering several of my schedules.

Any ideas?


There was an error executing your query:
DB Error: unknown error SELECT res.*, res_users.*, login.fname, login.lname, participant.memberid as participantid, participant.owner FROM reservations as res INNER JOIN reservation_users as res_users ON res.resid = res_users.resid INNER JOIN login as login ON res_users.memberid = login.memberid LEFT JOIN reservation_users as participant ON res.resid = participant.resid AND participant.memberid = 'sc14a9eaa7c0bef2' AND participant.invited = 0 WHERE ( ( (start_date >= 1329091200 AND start_date <= 1329609600) OR (end_date >= 1329091200 AND end_date <= 1329609600) ) OR (start_date <= 1329091200 AND end_date >= 1329609600) ) AND res_users.owner=1 AND res.machid IN ('sc14cac96fd1e7b0','sc14cac9724c4a3e','sc14cac962b842d2','sc14cac9654d2b0a','sc14cac9553426ca','sc14cac95d7aa318','sc14cac975cc96d7','sc14cac97808d91a','sc14cac4466a01c9','sc14cac44d1ae21c','sc14cac44e93c010','sc14d80d4821e437','sc14e68e193e57c9','sc14e68e1a988ff5','sc14e79fbe9d59a3','sc14f0cabcc689b7') ORDER BY res.start_date, res.starttime, res.end_date, res.endtime [nativecode=1104 ** The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay]
Back

phpScheduleIt v1.2.12

[Nick]

Hmm, I'm not sure. What page is triggering this error?

[Futureworks]

It's a couple of the schedules. Within the 'View Schedule' tab.
When you delete old bookings it appears much less regularly.

[Nick]

It sounds like its caused by the size of the data, but that seems silly. Do you have a ballpark estimate of how many reservations you have?

[Futureworks]

It sounds like its caused by the size of the data, but that seems silly. Do you have a ballpark estimate of how many reservations you have?

I'm afraid not. But that seems quite possible as we have many reservations dating back into last year.
I've just recently started booking the 'unchanging blackout reservations' a year in the future so I don't have to redo them every few months. I'm guessing this will contribute to the massive amount of data?
I presume a simple fix would be to delete all the old bookings but is there another way around this?

[Nick]

According to mysql, the default for this value is 4294967295, meaning

1) you have more than 4 billion reservation and user rows
or 2) the default value for this setting was changed

One thing we can try is reorganizing the query to limit the results more efficiently. Lines 56-70 of lib/db/scheduledb.class.php should look like this:

Code:

$sql = 'SELECT res.*, res_users.*, login.fname, login.lname, participant.memberid as participantid, participant.owner'
. ' FROM ' . $this->get_table(TBL_RESERVATIONS) . ' as res'
. ' INNER JOIN ' . $this->get_table(TBL_RESERVATION_USERS) . ' as res_users ON res.resid = res_users.resid'
. ' INNER JOIN ' . $this->get_table(TBL_LOGIN) . ' as login ON res_users.memberid = login.memberid'
. ' LEFT JOIN ' . $this->get_table(TBL_RESERVATION_USERS) . ' as participant ON res.resid = participant.resid AND participant.memberid = ? AND participant.invited = 0'
. ' WHERE ( '
. '( '
. '(start_date >= ? AND start_date <= ?)'
. ' OR '
. '(end_date >= ? AND end_date <= ?)'
. ' )'
. ' OR '
. '(start_date <= ?  AND end_date >= ?)'
.      ' )'
. ' AND res_users.owner=1';

Try changing it to this:

Code:

$sql = 'SELECT res.*, res_users.*, login.fname, login.lname, participant.memberid as participantid, participant.owner'
. ' FROM ' . $this->get_table(TBL_RESERVATIONS) . ' as res'
. ' INNER JOIN ' . $this->get_table(TBL_RESERVATION_USERS) . ' as res_users ON res.resid = res_users.resid AND res_users.owner=1'
. ' INNER JOIN ' . $this->get_table(TBL_LOGIN) . ' as login ON res_users.memberid = login.memberid'
. ' LEFT JOIN ' . $this->get_table(TBL_RESERVATION_USERS) . ' as participant ON res.resid = participant.resid AND participant.memberid = ? AND participant.invited = 0'
. ' WHERE ( '
. '( '
. '(start_date >= ? AND start_date <= ?)'
. ' OR '
. '(end_date >= ? AND end_date <= ?)'
. ' )'
. ' OR '
. '(start_date <= ?  AND end_date >= ?)'
.      ' )';

[Futureworks]

Forgive my n00bness.
Where do I change that? Is that script or code?

[axar]

its code in page

 Lines 56-70 of lib/db/scheduledb.class.php

he wrote before the piece of code.