Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-70480

Some assignment submissions are invisible on the grading form on primary/replica database setup

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.9 regressions
    • Fix Version/s: None
    • Component/s: Assignment
    • Labels:
      None

      Description

      When the number of users started rising sharply, we implemented a master/slave MariaDB 10.3.17 configuration for our Moodle 3.9.2 installation to alleviate some of the stress on the database, as we have also started receiving DDOS attacks.
      Some of our users have started to notice some assignment submissions missing from the grading form in the assignment module. The form lists the users entry as not submitted, but the "Edit submission" or "Grade" option shows all the submitted files correctly.
      After some database querying, we have found that the column "latest" in the table "mdl_assign_submission" is set to 0 on the users only submission entry for that assignment (status="submitted"), which was the reason for it not showing up on the grading forum.

      As we have also had some problems with assignment notification mails for some users - the column "mailed" in the table "mdl_assign_user_flags" could not be set to 1 during the cron task, possibly due to the database sync time, which resulted in some users getting the same notification on every time the cron task ran.
      We have since rolled back to a single database setup. We manually set the "mailed" flag to 1 for the users, affected by that previous problem, to stop receiving spam every minute.

      We found all of the missing assignment submissions with the sql query:

      SELECT s.id,s.assignment,s.userid,s.status,s.latest FROM mdl_assign_submission AS s 
        JOIN mdl_course_modules AS m ON s.assignment=m.instance 
        WHERE s.status="submitted" 
        AND s.latest=0 
        AND (SELECT count(*) FROM mdl_assign_submission WHERE assignment = s.assignment AND userid = s.userid ) = 1 
        AND m.module=1;
      

      and fixed them manually by running

      UPDATE mdl_assign_submission AS s
        SET s.latest=1
        WHERE s.status="submitted"
        AND s.latest=0
        AND (SELECT count(*) FROM mdl_assign_submission WHERE assignment = s.assignment AND userid = s.userid ) = 1;
      

      We have confirmed that the problems started when we switched to master/slave DB configuration and that no newly created submissions after the rollback to single database instance have experienced the same problem, so it would make sense to blame the database replica synchronization time interval. The problem does, however, persist on submissions that have been created during that time period and have since been updated.

      Unfortunately, we have not been able to reproduce the problem, only its symptoms (setting the column "latest" to 0).

      Any ideas as to what may have happened? Any help would be greatly appreciated.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            tjazbec Timotej Jazbec
            Participants:
            Component watchers:
            Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

              Dates

              Created:
              Updated: