SQL error in attendance after exceeding max queries

[cSanders]

I recently exceeded a maxqueries limit on my server (it’s a cheap server) and it caused the gibbons to escape. When the server was again able to receive queries, I’m now getting this error in the attendance screen. I’m certain that what caused the crash was somebody updating attendance. Unfortunately my backup is a bit old, so I can’t just do a hard reset.

Any suggestions would be appreciated!

SQLSTATE[42000]: Syntax error or access violation: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

[sandra]

Hi @cSanders Hmmm, this is interesting. The gibbonAttendanceLogPerson table sure can get big, in some schools I’ve seen it get to over 2 million records, so this could certainly be problematic long term. Long term, I’m thinking about how we could introduce an attendance archive system, to keep the amount of records in the everyday-use table in check.

In the short term, can you check how many rows are in your gibbonAttendanceLogPerson table? One option is to duplicate table, then choose a sufficiently past date, such as a few years ago, and delete all the entries older than that in the original table. That will speed up the queries and reduce the size, while retaining a backup of the old records should you need to query them for some reason. Generally, attendance logs are only relevant for the current year, so clearing out older years shouldn’t cause much of an issue.

[cSanders]

Thanks for the quick reply!

136,406 rows at present.

I hadn’t seen this error until just after the server crash. We take attendance for every class period, but have only been live on Gibbon since February of this year. It’s not a huge quantity of data, but perhaps something went sideways when 30 teachers all logged in at the same time frantically trying to update their overdue attendance records.

I also tried to see if the last few entries before the crash stood out as having odd data, but nothing in particular popped up.

[sandra]

That is very odd, as that is not very many rows at all in the SQL scale of things. Perhaps something happened to the tables when they crashed, can you try running the following commands on the table:

ANALYZE TABLE `gibbonAttendanceLogPerson`; 

CHECK TABLE `gibbonAttendanceLogPerson`; 

[cSanders]

@sandra

CHECK TABLE gibbonAttendanceLogPerson;

Nothing interesting happened when checking analyzing and checking the table.

[sandra]

Hmm, quite unusual. I suppose you likely don’t have access to your MySQL config file to make the recommended change to the SQL_BIG_SELECTS or MAX_JOIN_SIZE values. It’d be interesting to see if you have any way to know what the MAX_JOIN_SIZE value is, at the very least.

A couple things to try:

• In attendance.php, before the offending query (around line 359), you could add the following code: $pdo->statement("SET SQL_BIG_SELECTS=1"); Not an ideal solution, but might work.
• Adding an index to the table theoretically could reduce the cartesian estimate of the join size. Try running the following on your system:

  ALTER TABLE `gibbonAttendanceLogPerson` ADD INDEX(`context`, `gibbonCourseClassID`); 
  

Hope this helps!

[cSanders]

SQL_BIG_SELECTS is set to 0 on the server, MAX_JOIN_SIZE is 7000000. The cloud host I use was unable to adjust these values, so I tried your suggestion.

Added the index and now the error is gone!

[sandra]

Great to hear that worked! 7 million should be a pretty reasonable MAX_JOIN_SIZE so lets hope this solves the issue long-term.