Cannot open Quizzes – error/moodle/dmlwriteexception

Cannot open Quizzes – error/moodle/dmlwriteexception

by Bernd Albers -
Number of replies: 6

Lately when I try to open any quizz on my moodle 3.3.5 installation I get the following debug info:

 

Debug info: Duplicate entry '48954-0' for key 'mdl_quesattestep_queseq_uix'
INSERT INTO mdl_question_attempt_steps (questionattemptid,sequencenumber,state,fraction,timecreated,userid) VALUES(?,?,?,?,?,?)
[array (
0 => 48954,
1 => 0,
2 => 'todo',
3 => NULL,
4 => 1533734132,
5 => '2',
)]
Error code: dmlwriteexception

×Stack trace:

·         line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown

·         line 1300 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()

·         line 1346 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()

·         line 227 of /question/engine/datalib.php: call to mysqli_native_moodle_database->insert_record()

·         line 148 of /question/engine/datalib.php: call to question_engine_data_mapper->insert_question_attempt_step()

·         line 100 of /question/engine/datalib.php: call to question_engine_data_mapper->insert_question_attempt()

·         line 100 of /question/engine/lib.php: call to question_engine_data_mapper->insert_questions_usage_by_activity()

·         line 349 of /mod/quiz/locallib.php: call to question_engine::save_questions_usage_by_activity()

·         line 2366 of /mod/quiz/locallib.php: call to quiz_attempt_save_started()

·         line 111 of /mod/quiz/startattempt.php: call to quiz_prepare_and_start_new_attempt()


Seems that Moodle cannot write into the Database table „mdl_question_attempt_steps“ anymore.

 

I have copied the database to a test-moodle 3.4.4 on another server with barracuda enabled.

I have run the cli script to change the database to barracuda which converted selcted tables, but did not include the table „mdl_question_attempt_steps“ – the problem is still the same.

In a radical step I deleted all the content in that table via phpmyadmin – now everything seems to work again.

Is there a better solution to my problem? Might emptying that table have any consequences in other places?

I have checked the grades for quizzes in  different courses and everything seems to be  fine.

Has anybody run into the same problem?


Average of ratings: -
In reply to Bernd Albers

Re: Cannot open Quizzes – error/moodle/dmlwriteexception

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

This is almost certaily a problem in your database. If I had to guess, the autoincrement value for one of these tables is wrong, which is what is leading to the duplicate keys.

I really would not recommend truncating random Moodle tables. (Although that does reset the autoincrement counter according to the docs.)

Can you reproduce the error on qa.moodle.net?

Average of ratings: -
In reply to Tim Hunt

Re: Cannot open Quizzes – error/moodle/dmlwriteexception

by Bernd Albers -

Hi Tim

Thanks for answering here and in the tracker: https://tracker.moodle.org/browse/MDL-63195

I’m pretty sure it is a problem in my database.

Somehow the autoincrement value has gone down by many numbers.

I’m not a database specialist at all. Just trying to understand some basics.

My first approach was to delete the entries in the database with the ID-number given in the debug-info. But then always the next number would be the problem.  And there were much higher ID-numbers in the table. That’s why I decided to truncate the whole table.

The question is why the autoincrement value went down so far. And one explanation would maybe be, that when importing the quizzes to another course the question_attempt_steps were somehow also copied, so when trying to start the test again in the new course that would lead to those duplicate entries.

And that would probably be a bug.

I have no idea how to reproduce the error on qa.moodle.net.  What I have done, was uploading  the course into a fresh Moodle installation. And there everything works fine of course.

So a.t.m. for me the only solution for my production Moodle would be to also truncate the tables

·       mdl_question_attempts                                                          

·       mdl_question_attempt_steps                                                                                  

·       mdl_question_attempt_step_data

·       mdl_quiz_attempts

to get the quizzes working again.

The question is how that could affect this Moodle installation in other places. It’s working well in my copied Moodle, but things may be different when hundreds of participants are working in the system.

So what do I really risk?


Average of ratings: -
In reply to Bernd Albers

Re: Cannot open Quizzes – error/moodle/dmlwriteexception

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

Ah! I have another theory: Suppose the backup file contains bad data, so that when the backup is restored, it puts data into those tables with unique key values that should not have been used yet.

Then, later, when the autoincrement sequence gets to those numbers you get these errors.

To test that theory, I would need to see the backup file.

Average of ratings: -
In reply to Tim Hunt

Re: Cannot open Quizzes – error/moodle/dmlwriteexception

by Bernd Albers -

Hi Tim

That was kind of what I wanted to say – of course your explanation is much more precise.

I have created a backup of that course only containing these quizzes without userdata.

Then I imported it to a clean new Moodle and nothing was added to the database.

So far so good.

But my original import was not from a backup but an import of activities directly from a running course to a freshly created course in the same Moodle.

I have done the same thing again and checked the database before and after - no entries were added to the database by this process.

So there seems to be no proof for our theory.

You will probably understand that I cannot send you a copy of the dastabase of our production-Moodle.

So I guess the only thing I can do is truncate these tables?


Average of ratings: -
In reply to Bernd Albers

Re: Cannot open Quizzes – error/moodle/dmlwriteexception

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

I think that instead of truncating these tables, a safer option would be to change the autoincrement sequence value, so that all newly generated ids are 'safe' and don't cause conflicts.

That would involve which id is causing the conflicts (which table), then finding the sequence value that is big enough to avoid future problems, then executing SQL like

ALTER TABLE tablename AUTO_INCREMENT = 123456


Average of ratings: -