Restore Failed - Error Writing to the Database

Restore Failed - Error Writing to the Database

by Angel Vander Steeg -
Number of replies: 12

We have Moodle running Windows Server 2008 Standard and we are using the Uniform Server for our stack.  It has worked great, we have about 100 some courses in it already that have been running for 6 months without issue.  I went to add a course in recently and now I am getting an error.  When I click on the More information about this error - it takes me to the page about upping the max_allowed_packet size.  I have done this, it has been set high enough, I am not sure what the issue is.  I can figure out most things, but Moodle is a new thing to me, so any help would be appreciated.  I turned on the debugging and below is what I get:

-------------------------------------------------------------------------------------------------------------------------------------

Debug info: Duplicate entry '58792' for key 'mdl_qtypmultopti_que_uix'

INSERT INTO mdl_qtype_multichoice_options (layout,single,shuffleanswers,correctfeedback,partiallycorrectfeedback,incorrectfeedback,answernumbering,correctfeedbackformat,partiallycorrectfeedbackformat,incorrectfeedbackformat,questionid) VALUES(?,?,?,?,?,?,?,?,?,?,?)

[array (

0 => '0',

1 => '1',

2 => '0',

3 => '',

4 => '',

5 => '',

6 => 'abc',

7 => '1',

8 => '1',

9 => '1',

10 => 58792,

)] 

Error code: dmlwriteexception

Stack trace:

line 446 of \lib\dml\moodle_database.php: dml_write_exception thrown

line 1098 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()

line 1144 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()

line 76 of \question\type\multichoice\backup\moodle2\restore_qtype_multichoice_plugin.class.php: call to mysqli_native_moodle_database->insert_record()

line 137 of \backup\util\plan\restore_structure_step.class.php: call to restore_qtype_multichoice_plugin->process_multichoice()

line 103 of \backup\util\helper\restore_structure_parser_processor.class.php: call to restore_structure_step->process()

line 151 of \backup\util\xml\parser\processors\grouped_parser_processor.class.php: call to restore_structure_parser_processor->dispatch_chunk()

line 91 of \backup\util\helper\restore_structure_parser_processor.class.php: call to grouped_parser_processor->postprocess_chunk()

line 148 of \backup\util\xml\parser\processors\simplified_parser_processor.class.php: call to restore_structure_parser_processor->postprocess_chunk()

line 92 of \backup\util\xml\parser\processors\progressive_parser_processor.class.php: call to simplified_parser_processor->process_chunk()

line 190 of \backup\util\xml\parser\progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()

line 278 of \backup\util\xml\parser\progressive_parser.class.php: call to progressive_parser->publish()

line ? of unknownfile: call to progressive_parser->end_tag()

line 179 of \backup\util\xml\parser\progressive_parser.class.php: call to xml_parse()

line 158 of \backup\util\xml\parser\progressive_parser.class.php: call to progressive_parser->parse()

line 110 of \backup\util\plan\restore_structure_step.class.php: call to progressive_parser->process()

line 181 of \backup\util\plan\base_task.class.php: call to restore_structure_step->execute()

line 177 of \backup\util\plan\base_plan.class.php: call to base_task->execute()

line 167 of \backup\util\plan\restore_plan.class.php: call to base_plan->execute()

line 333 of \backup\controller\restore_controller.class.php: call to restore_plan->execute()

line 184 of \backup\util\ui\restore_ui.class.php: call to restore_controller->execute_plan()

line 107 of \backup\restore.php: call to restore_ui->execute()

Thank you for any information in advance!!!!!!!!!!!!!!!!!!!!!!!! - Angel

Average of ratings: -
In reply to Angel Vander Steeg

Re: Restore Failed - Error Writing to the Database

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

The 'More information about his error' link is just relating to the type of error 'dmlwriteexception' which must means that something went wrong when writing to the database - and there could be many, many reasons for that.

Thank you very much for giving the full debug output. That makes it easy to help. They key bit is "Duplicate entry '58792' for key 'mdl_qtypmultopti_que_uix'" which tells us why the data could not be written to the database - it violated a unique constraint.

For each multiple choice question, there should be just one set of options. However rule was only enforced properly in Moodle 2.5. It seems that your backup file was created in an older version of Moodle, and contains bad data. Now you are trying to restore it into Moodle 2.7.2 and the constraint is checked and is failing.

I note you say "I went to add a course in recently". What exactly where you doing when the error occurred?

Average of ratings: -
In reply to Tim Hunt

Re: Restore Failed - Error Writing to the Database

by Angel Vander Steeg -

We went from 1.9 to 2.7 for Moodle, we had about just over 100 courses to import back into the new version.  Originally as a fresh copy, I was able to restore all of those courses and they went in pretty well and we have been using them for 6 months.  Some of the things were kind of wacky but all of the information was there and it was easy to fix.  Well I have seen that I forgot one of the courses, so I went into the 2.7 version to restore the course and that is when I get the error.  It goes through the motions, right up to the preparing the data part, then during the actual restore it will stop  just under 1% and give the error.  Any suggestions to getting around this?  And thank you Tim for taking the time to look and respond to my post!! - Thanks, Angel

Average of ratings: -
In reply to Tim Hunt

Re: Restore Failed - Error Writing to the Database

by Gedeon J Lidório, Jr -

Hi Tim

I have the same in my Moodle. 

My version: Moodle 2.6.6+ (Build: 20141121)

Error writing to database

Other information about this error

Debug info: Duplicate entry '1495' for key 'mdl_qtypmultopti_que_uix'

INSERT INTO mdl_qtype_multichoice_options (layout,single,shuffleanswers,correctfeedback,correctfeedbackformat,partiallycorrectfeedback,partiallycorrectfeedbackformat,incorrectfeedback,incorrectfeedbackformat,answernumbering,shownumcorrect,questionid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)

[array (

0 => '0',

1 => '0',

2 => '1',

3 => '',

4 => '1',

5 => '',

6 => '1',

7 => '',

8 => '1',

9 => 'abc',

10 => '0',

11 => 1495,

)]

Error code: dmlwriteexception

Stack trace:

line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown

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

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

line 76 of /question/type/multichoice/backup/moodle2/restore_qtype_multichoice_plugin.class.php: call to mysqli_native_moodle_database->insert_record()

line 137 of /backup/util/plan/restore_structure_step.class.php: call to restore_qtype_multichoice_plugin->process_multichoice()

line 103 of /backup/util/helper/restore_structure_parser_processor.class.php: call to restore_structure_step->process()

line 151 of /backup/util/xml/parser/processors/grouped_parser_processor.class.php: call to restore_structure_parser_processor->dispatch_chunk()

line 91 of /backup/util/helper/restore_structure_parser_processor.class.php: call to grouped_parser_processor->postprocess_chunk()

line 148 of /backup/util/xml/parser/processors/simplified_parser_processor.class.php: call to restore_structure_parser_processor->postprocess_chunk()

line 92 of /backup/util/xml/parser/processors/progressive_parser_processor.class.php: call to simplified_parser_processor->process_chunk()

line 186 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()

line 274 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->publish()

line ? of unknownfile: call to progressive_parser->end_tag()

line 175 of /backup/util/xml/parser/progressive_parser.class.php: call to xml_parse()

line 154 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->parse()

line 110 of /backup/util/plan/restore_structure_step.class.php: call to progressive_parser->process()

line 181 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()

line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()

line 167 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()

line 333 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()

line 184 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()

line 99 of /backup/restore.php: call to restore_ui->execute()

Average of ratings: -
In reply to Tim Hunt

Re: Restore Failed - Error Writing to the Database

by Andrew Zhong -
I have the same issue. We recently upgraded from 2.4 to 2.6.8 and now this happened (full debug report below). The odd thing is we have 8 instances and only 1 is having this issue... Any insight would be greatly appreciated!

Error writing to database

More information about this error

Debug info: Duplicate entry '32147' for key 'mdl_dat_qtypmultopti_que_uix'
INSERT INTO mdl_dat_qtype_multichoice_options (layout,single,shuffleanswers,correctfeedback,correctfeedbackformat,partiallycorrectfeedback,partiallycorrectfeedbackformat,incorrectfeedback,incorrectfeedbackformat,answernumbering,shownumcorrect,questionid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => '0',
1 => '1',
2 => '1',
3 => '',
4 => '1',
5 => '',
6 => '1',
7 => '',
8 => '1',
9 => 'ABCD',
10 => '0',
11 => 32147,
)]
Error code: dmlwriteexception
Stack trace:
line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 1146 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1188 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
line 76 of /question/type/multichoice/backup/moodle2/restore_qtype_multichoice_plugin.class.php: call to mysqli_native_moodle_database->insert_record()
line 137 of /backup/util/plan/restore_structure_step.class.php: call to restore_qtype_multichoice_plugin->process_multichoice()
line 103 of /backup/util/helper/restore_structure_parser_processor.class.php: call to restore_structure_step->process()
line 151 of /backup/util/xml/parser/processors/grouped_parser_processor.class.php: call to restore_structure_parser_processor->dispatch_chunk()
line 91 of /backup/util/helper/restore_structure_parser_processor.class.php: call to grouped_parser_processor->postprocess_chunk()
line 148 of /backup/util/xml/parser/processors/simplified_parser_processor.class.php: call to restore_structure_parser_processor->postprocess_chunk()
line 92 of /backup/util/xml/parser/processors/progressive_parser_processor.class.php: call to simplified_parser_processor->process_chunk()
line 186 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()
line 274 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->publish()
line ? of unknownfile: call to progressive_parser->end_tag()
line 175 of /backup/util/xml/parser/progressive_parser.class.php: call to xml_parse()
line 154 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->parse()
line 110 of /backup/util/plan/restore_structure_step.class.php: call to progressive_parser->process()
line 181 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()
line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
line 167 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
line 333 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
line 184 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()
line 99 of /backup/restore.php: call to restore_ui->execute()
Average of ratings: -
In reply to Angel Vander Steeg

Re: Restore Failed - Error Writing to the Database

by Angel Vander Steeg -

I am not sure if I can shed light on this or not, I am also sure that this is just a workaround and not the fix for the underlying issue?  When we first started, all of our courses went from 1.9 to 2.7 without any issues.  Then after running for a few months, we tried to restore a couple of courses again, and we received the error above.  After testing some courses, we found that if we restored the courses without the quizzes they went in just fine.  So what we have been doing is exporting all the quizzes from a course in one instance of Moodle and then importing them into our current running version.  For now, this is how we are getting our courses back in.  I am not sure why it worked for so long and then decided that it didn't?  So if anyone finds a way to fix the error, I would love to know.

Thanks, Angel 

Average of ratings: -
In reply to Angel Vander Steeg

Re: Restore Failed - Error Writing to the Database

by Dwight Hawley -

Just started having this issue this week. Course likely 2.5 and running 2.7 now. Tried restoring without quizzes but no change. This is a small course (9MB) so can't be max_Packets


Linux.Centos

10.0.19 MariaDB

Moodle 2.7.4

Debug below but I can't make sense of it beyond something about a duplicate entry:


Error writing to database

More information about this error

Debug info: Duplicate entry '6467' for key 'mdl_qtypmultopti_que_uix'
INSERT INTO mdl_qtype_multichoice_options (layout,single,shuffleanswers,correctfeedback,correctfeedbackformat,partiallycorrectfeedback,partiallycorrectfeedbackformat,incorrectfeedback,incorrectfeedbackformat,answernumbering,shownumcorrect,questionid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => '0',
1 => '1',
2 => '1',
3 => '',
4 => '1',
5 => '',
6 => '1',
7 => '',
8 => '1',
9 => 'abc',
10 => '0',
11 => 6467,
)]
Error code: dmlwriteexception

Stack trace:

  • line 446 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1164 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1210 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 76 of /question/type/multichoice/backup/moodle2/restore_qtype_multichoice_plugin.class.php: call to mysqli_native_moodle_database->insert_record()
  • line 137 of /backup/util/plan/restore_structure_step.class.php: call to restore_qtype_multichoice_plugin->process_multichoice()
  • line 103 of /backup/util/helper/restore_structure_parser_processor.class.php: call to restore_structure_step->process()
  • line 151 of /backup/util/xml/parser/processors/grouped_parser_processor.class.php: call to restore_structure_parser_processor->dispatch_chunk()
  • line 91 of /backup/util/helper/restore_structure_parser_processor.class.php: call to grouped_parser_processor->postprocess_chunk()
  • line 148 of /backup/util/xml/parser/processors/simplified_parser_processor.class.php: call to restore_structure_parser_processor->postprocess_chunk()
  • line 92 of /backup/util/xml/parser/processors/progressive_parser_processor.class.php: call to simplified_parser_processor->process_chunk()
  • line 190 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser_processor->receive_chunk()
  • line 278 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->publish()
  • line ? of unknownfile: call to progressive_parser->end_tag()
  • line 179 of /backup/util/xml/parser/progressive_parser.class.php: call to xml_parse()
  • line 158 of /backup/util/xml/parser/progressive_parser.class.php: call to progressive_parser->parse()
  • line 110 of /backup/util/plan/restore_structure_step.class.php: call to progressive_parser->process()
  • line 181 of /backup/util/plan/base_task.class.php: call to restore_structure_step->execute()
  • line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
  • line 167 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()
  • line 333 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()
  • line 184 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()
  • line 111 of /backup/restore.php: call to restore_ui->execute()

Any guidance?

Average of ratings: -
In reply to Dwight Hawley

Re: Restore Failed - Error Writing to the Database

by Ken Task -
Picture of Particularly helpful Moodlers

Did you click that link that has more information about this error?

It points to:

https://docs.moodle.org/27/en/error/moodle/dmlwriteexception

and on that page it says:

"If you're using a MySQL database for your Moodle installation, this error can be caused by the server's max_allowed_packet size being configured incorrectly."

Tracker Item says 'fixed':

https://tracker.moodle.org/browse/MDL-46651

Did see a suggestion to run cron.

And you might want to run the health tool

http://yoursite/admin/tool/health/ to see what it says about quiz banks.

'spirit of sharing', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: Restore Failed - Error Writing to the Database

by Dwight Hawley -

Hi Ken,

Just wanted to say thanks!

We started with the simplest solution and increased max packets (again, had just done this a month ago so I just KNEW it couldn't be Max Packets)  to 1,024MB and that did it!


I did run the /health report and will tidy up some things there once the dust settles.


Thanks again!


--Dwight

Average of ratings: -
In reply to Dwight Hawley

Re: Restore Failed - Error Writing to the Database

by Ken Task -
Picture of Particularly helpful Moodlers

Welcome!   Yep ... that's Moodle for ya ... always onwards and upwards.

Feel free to rate the posting that helped as 'useful'. ;)

'spirit of sharing', Ken

Average of ratings: -
In reply to Dwight Hawley

Re: Restore Failed - Error Writing to the Database

by Dwight Hawley -

Unfortunately I got the one restore and then it started throwing the error again. We upped Max_Packets to 2,048MB and for another but now throwing errors again. My SysAdmin says thats the max recommended size due to stability issues.  Anyone else run into this?


The Health check keeps pointing to quiz so I'l try over on that forum as well.


--Dwight

Average of ratings: -
In reply to Dwight Hawley

Re: Restore Failed - Error Writing to the Database

by Ken Task -
Picture of Particularly helpful Moodlers

For ref:

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet

Am certain others have had similar issues, but there are variables that might prevent anyone for giving any more advice that has already been given. :\ (not that I'm that much of a guru at this!).

So this latest restore failure ... how large is the backup file itself?

Does the restore process get to any restore screen?

If it does, the debug output of for this course report the same thing?

When the backups were created, I wonder if they were using the same question bank?

Have you tried to restore the course minus the quizzes?

You might have to resort to a manual 'fix' ... or 'work-around' ...  It's messy but ...

Un-archive the backup file on a local system - change .mbz to .zip and unzip it in a test folder.

Then one has to 'guess' as to which/what will reduce the issue ... suspect quiz here.

Inside the test directory 'activities' directory of the un-archived backup, you will find other directories named thusly:

quiz_10777

quiz_10756

quiz_28369

Move those folders outside of the test directory.   Jot down the numbers you see. (the 10777, etc. above).   You will need those to edit moodle_backup.xml file.

Edit the questions.xml file and remove all references leaving only the following:

<?xml version="1.0" encoding="UTF-8"?>
<question_categories>
</question_categories>

If the questions.xml file is too large to edit, move the questions.xml file out of the directory then create one with NotePad or other text editor that contains only the lines above.   File you create is in the same location as the moodle_backup.xml file.

Edit the moodle_backup.xml file.

You'll see sections of it like this (following the example of a quiz that had a number of 10777):

        <activity>
          <moduleid>10777</moduleid>
          <sectionid>82</sectionid>
          <modulename>quiz</modulename>
          <title>Shakespeare Intro. Notes Quiz</title>
          <directory>activities/quiz_10777</directory>
        </activity>

Remove those references.

Now re-package the backup.

From the location of the 'test' directory, where you can see moodle_backup.xml

zip -r backup.mbz ./*

The backup.mbz file should be smaller than the backup file you began with but it not be much.

Now attempt to restore the course.

Note: should say I have been successful in restoring a course this way, but, of course, no questions in the restored course.   Also, would not normally do this nor recommend it, but we do what we must!

'spirit of sharing', Ken

Average of ratings: -
In reply to Ken Task

Re: Restore Failed - Error Writing to the Database

by Dwight Hawley -

Hi Ken,

Thanks for the followup.

A bit of context.  These are backups created in order to reuse that course in the next term. So the backups are created, then immediately restored as new courses. I am not at all certain this is the best way of going about this, but this is what they do and it had been working without fail. The only difference I can point to is we upgraded from 2.5 to 2.7 and then moved the server from a hosted environment to a private server at the beginning of the term.

Backups range from 8 to 12 MB in size and I haven't, nor will I assume those on campus have, any clue as to question bank origins.

Each comes back with the error below, the only differences appear to be in the number of seconds it ran - 25-80 or so


Error writing to database

More information about this error

Debug info: Duplicate entry '6467' for key 'mdl_qtypmultopti_que_uix'
INSERT INTO mdl_qtype_multichoice_options (layout,single,shuffleanswers,correctfeedback,correctfeedbackformat,partiallycorrectfeedback,partiallycorrectfeedbackformat,incorrectfeedback,incorrectfeedbackformat,answernumbering,shownumcorrect,questionid) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => '0',
1 => '1',
2 => '1',
3 => '',
4 => '1',
5 => '',
6 => '1',
7 => '',
8 => '1',
9 => 'abc',
10 => '0',
11 => 6467,
)]
Error code: dmlwriteexception



 Stack trace is same as well.


I'll try restore minus quizzes and then the other steps, which do indeed appear daunting but will work with the sysadmin to try and complete them.

One other question.  I ran across a single mention of using course import somewhere in issues along this vein and after reading up on documentation and postings I am just not seeing clear sign of when to use import and when to use restore. When re purposing a course, is one a better route than the other? If not, what purpose does import serve?


Thanks again for your lifeline tossed into the murky depths!


--Dwight

Average of ratings: -