Grade queries crashing moodle site

Picture of Henriette Stewart
Grade queries crashing moodle site

Hello, in the last few days our moodle site (2.2.1.+ Build: 20120202) has experienced crashes because there were queries clogging up the system. These are the queries our "server people" reported back as the ones they had to kill:

| Query | 491 | Copying to tmp table | SELECT, COUNT(DISTINCT AS count FROM mdl_grade_items gi


| Query | 14 | Copying to tmp table | SELECT DISTINCT go.userid FROM mdl_grade_grades go JOIN m |

This last one seemed to happen when I clicked the Grade link shown in the screenshot.

Is someone able explain to me what might be going on here and what I need to do to prevent our site from crashing like this?

Picture of Patric Lougheed
Re: Grade queries crashing moodle site

Hello Henriette,

I saw your Google+ post and thought I might pipe in. Sounds like the act of loading the gradebook is sucking up all the database resources. Is this a very big gradebook with tons of students?

I found a good article on the perils of using "copy to tmp table":

It might be the SET tmp_table_size in the MySQL config needs to be increased.

Picture of Debbie Kimball
Re: Grade queries crashing moodle site

Hi Patrick,

We had the same problem occur in 37 random courses after we upgraded from 1.9.13 to 2.3.3.  We found that the reset button in settings was our answer.

  1. Backup the course with user first (just in case!)
  2. Click the reset button
  3. Check ONLY the box under the Gradebook setting: "Delete all items and categories" leave all others unchecked!
  4. Scroll down and click the "Reset course" button
  5. Click the "Continue" button on the next page
  6. (It will not reset questionnairres)

I hope this helps!