Add questions to quiz from a course restore - getting a duplicate entry database error

Add questions to quiz from a course restore - getting a duplicate entry database error

by Michael McCoy -
Number of replies: 5

Moodle version 3.0.5 - coming from 2.9.1

Attempting to add questions to a quiz in a course that has been restored from a previous course. All of the questions are in a question bank in the course and they would have come with the restored course.  When attempting to add a question, a database error will be thrown and then this shows up in the apache log file. In looking at the data, I see how the error is getting thrown but I'm not sure why. I'm not very familiar with the quiz sections table or the index that's throwing the error.

The behavior is sporadic but could indicate it has something to do with the backup and restore process as a result of going to version 3.0.5. Any help or suggestions would be greatly appreciated.

Error writing to database Debug: Duplicate entry '36932-6' for key 'mdl_quizsect_quifir_uix'

                UPDATE mdl_quiz_sections
                   SET firstslot = firstslot + 1
                 WHERE quizid = ?
                   AND firstslot > ?

                   [array (
  0 => '36932',
  1 => '4',
  )]
Error code: dmlwriteexception
* line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 974 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 2045 of /mod/quiz/locallib.php: call to mysqli_native_moodle_database->execute()
* line 129 of /mod/quiz/edit.php: call to quiz_add_quiz_question()
, referer: http://<<urlofsite>>/mod/quiz/edit.php?cmid=329118&cat=111894%2C401510&qpage=0&recurse=0&showhidden=0
Average of ratings: -
In reply to Michael McCoy

Re: Add questions to quiz from a course restore - getting a duplicate entry database error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

Does your quiz have section headings? The query that is failing is one that updates the position of each section heading when you add a new question.

E.g. if your quiz has two sections, Section 1 contains 4 questions and Section 2 contains 4 question, so Question 5 is the first question in section 2.

If you add a new question after question 4, then you have to update to say that now Section 2 stars at question 6. This should not cause an error. I wonder why it is failing for you?

Average of ratings: -
In reply to Tim Hunt

Re: Add questions to quiz from a course restore - getting a duplicate entry database error

by Michael McCoy -

Thanks for the reply Tim.

Yes, there are section headings. I've attached two screenshots, a shot of the quiz as it looks in editing mode and a shot of the table rows in mdl_quiz_sections for this quiz id. I've also included the exact error (similar to the one in the original post). The error occurs when attempting to add a question from the question bank in the section that is circled in the image.


[Wed Nov 30 16:18:57 2016] [error] [client 163.11.254.50] Default exception handler: Error writing to database Debug: Duplicate entry '37059-21' for key 'mdl_quizsect_quifir_uix'
                UPDATE mdl_quiz_sections
                   SET firstslot = firstslot + 1
                 WHERE quizid = ?
                   AND firstslot > ?
                
                   [array (
  0 => '37059',
  1 => '12',
  )]
Error code: dmlwriteexception
* line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 974 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 2045 of /mod/quiz/locallib.php: call to mysqli_native_moodle_database->execute()
* line 113 of /mod/quiz/edit.php: call to quiz_add_quiz_question()
, referer: http://<<siteurl>>/mod/quiz/edit.php?cmid=330399&cat=111894%2C401510&qpage=0&recurse=0&showhidden=0
Attachment quiz_data.PNG
Attachment quiz_layout.jpg
Average of ratings: -
In reply to Michael McCoy

Re: Add questions to quiz from a course restore - getting a duplicate entry database error

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

So, the query being executed is

UPDATE mdl_quiz_sections
                   SET firstslot = firstslot + 1
                 WHERE quizid = 37059
                   AND firstslot > 12

So, looking at the data, the firstslot updates that need to be made are 13 -> 14, 20 -> 21, 21 -> 22 and 28 -> 29. A real database should be able to do this (even though, if you do the updates one at a time in order, you get a problem). Unforunately, we are talking about MySQL.

Oh, and here is a blog post from 2006 which exactly describes the problem https://www.xaprb.com/blog/2006/06/16/how-to-avoid-unique-index-violations-on-updates-in-mysql/, and here is the bug report it refers to, still not fixed: https://bugs.mysql.com/bug.php?id=18913

So, the suggested work-around is to add an ORDER BY clause to the query, but that is non-standard SQL, and so only works in MySQL. Looks like we need an ugly DB-specific hack.

I created MDL-57228 for this.

So: the thing that triggers this is having 2 section headings in the quiz, with only one question in between, after where you are trying to add the new question.

I work-around would be to add the new question between those 2 section headings, then drag it into the right place.


Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Add questions to quiz from a course restore - getting a duplicate entry database error

by Michael McCoy -

Thanks for the detective work Tim!

Average of ratings: -
In reply to Tim Hunt

Re: Add questions to quiz from a course restore - getting a duplicate entry database error

by Susan Mangan -

Re: So, the suggested work-around is to add an ORDER BY clause to the query,

Has anyone attempted this workaround?  Adding ORDER BY in the query eliminated the original error but didn't quite do the trick, still getting a dmlwriteexception occassionally.  Doesn't seem to be consistent.


Error code: dmlwriteexception
* line 482 of /lib/dml/moodle_database.php: dml_write_exception thrown
* line 1069 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 2071 of /mod/quiz/locallib.php: call to mysqli_native_moodle_database->execute()
* line 129 of /mod/quiz/edit.php: call to quiz_add_quiz_question()

Of course it's entirely possible I've not done it correctly...

Average of ratings: -