"Error writing to database" during Course Restore

"Error writing to database" during Course Restore

- Chris Walsh の投稿
返信数: 17

Hello Moodlers,

I am currently migrating some courses from Moodle 1.9.18+ to Moodle 2.5.1 and some are failing with a strange error message: "Error writing to database / Error reading from database".  I have attached a screenshot.  The error occurs after pressing "Continue" on the "2. Destination" step (where you select the Course Category).

I'm aware that User Data doesn't restore when making this jump between these versions but we are okay with that.  I exported the courses from 1.9.18+ with User Data = None.

I've set the Debug Messages (debug) setting to both NORMAL and ALL but no debug information is appearing on screen.  I have also confirmed that the "Display debug messages" (debugdisplay) setting is set to Yes.

The courses are around the 55Mb mark but I've allowed up to 128Mb upload size and the actual upload step works without a problem.  I can also uploaded the zip to the "User private backup area" first but restoring from there didn't help either.

I have also cleared out the moodledata/temp/backup folder and this didn't help.

Can anyone point me on how I can identify the course of the error message.

Thanks,
Chris

 

添付 Error importing course -.png
添付 previous-step.png
評点平均:Useful (1)
Chris Walsh への返信

Re: "Error writing to database" during Course Restore

- Emma Richardson の投稿
画像 Documentation writers 画像 Particularly helpful Moodlers 画像 Plugin developers

You say some?  So some are restoring just fine?  The smaller ones maybe?  Have you checked timeout settings in php.ini?

 

評点平均: -
Emma Richardson への返信

Re: "Error writing to database" during Course Restore

- Chris Walsh の投稿

Yes, there is a corrolation between successful upload and zip size but it isn't the usual max_upload type settings that are the issue as I can upload the zip into a private user files area without a problem.  

Here are my zip sizes and success rate:

Size Worked
0.01 Y
0.9 Y
1.8 Y
2.6 Y
3.1 Y
3.7 Y
3.9 Y
4.2 Y
5.1 Y
5.9 Y
6.8 Y
7.5 N
7.6 Y
18.3 Y
18.9 Y
23.2 N
26.4 Y
38.2 Y
45.3 N
50.2 N
54.4 N
65.2 N
80.4 N
102.7 N

The Red ones failed for the reason given in my OP. 

I have checked the php file and max_input_time = 60.  I will try changing this to 600 temporarily, restart Apache and try again.  I'll feedback on my findings.

Thanks

評点平均: -
Chris Walsh への返信

Re: "Error writing to database" during Course Restore

- Chris Walsh の投稿

OK, so it is definitely not an issue with max_input_time.  I set it to 600 (10 minutes), restarted Apache, double checked via phpinfo() and then proceeded to try a restore of the 54.4MB course.  Step 3 returned the same error in the OP after only 20 seconds.

 

評点平均: -
Chris Walsh への返信

Re: "Error writing to database" during Course Restore

- Ken Task の投稿
画像 Particularly helpful Moodlers

Hope you can set parameters for MySQL.  Try setting

max_allowed_packet = 100M (default is 1M if not changed in my.cnf.

The 100M is a guess.  If you continue to get the same error, increase gradually upwards until no more error.

'spirit of sharing', Ken

評点平均:Useful (3)
Ken Task への返信

Re: "Error writing to database" during Course Restore

- Chris Walsh の投稿

Hi Ken,

I don't have a my.cnf in my MySQL, PHP or Apache folder trees.

I do have a my.ini in my MySQL folder which I believe is the one being picked up my MySQL.  However this doesn't have a max_allowed_packet setting in it.

I am using MySQL 5.5 on a Windows environment.

I have added the setting under the [mysqld] section of my.ini and restarted MySQL server.

... and this has resolved the problem!  Thanks Ken.

I am going to open the zip file and see what the largest binary file is that it most likely tried to insert into the database.  I am guessing 100M might be overkill so bringing it down to a smaller value might be a good idea.

Thanks again.

Chris

評点平均: -
Chris Walsh への返信

Re: "Error writing to database" during Course Restore

- Ken Task の投稿
画像 Particularly helpful Moodlers

Welcome.  Might add that opening the backup zip and looking for large files may not be the only issue.  If you do temporarily remove a large file from course_files don't forget to edit the related .xml files before re-zipping.

The backups could contain references to mods/blocks that were add-ons to the 1.9 ... no longer compat with 2.

As a fall back, consider creating 2 file system repos in /moodledata/repository/ One called 19data.  In there, copy all the 1.9 course ID folders and clean them up ... ie, remove moddata, backupdata.  Any time a 1.9 restored course has issues with files, at least one can set that course to use /moodledata/repository/19data/[oldcourseid] to re-acquire files.  Can also use file system repo for all your 1.9 backups in a 19backups folder.  Only use them as repo's in courses when you need to.  I sometimes use that 19backups folder as a 'work/looksee' area before restoring or linking via file system repo.

Yes, that does eat up some serious HD space but once one gets all the courses 'settled into the 2', they can be removed manually - with one cravat ... users, if having to re-link to files in their 2, should NOT use 'alias' when selecting a file from a file system repo.

'spirit of sharing', Ken

 

評点平均:Useful (1)
Ken Task への返信

Re: "Error writing to database" during Course Restore

- David Dunn の投稿

(Moodle 2.5.1+ upgraded from 2.2.1)

Hi Ken,

I have the same issue after step 2 of restoring an 86MB course.

_____________________________________________________________________

Error writing to database

More information about this error

Debug info: Got a packet bigger than 'max_allowed_packet' bytes
INSERT INTO mdl_backup_controllers (backupid,operation,type,itemid,format,interactive,purpose,userid,status,execution,executiontime,checksum,controller,timecreated,timemodified) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => 'ee1343e6c0f94e5e3176bb690f42844d',
1 => 'restore',
2 => 'course',
3 => 84,
4 => 'moodle2',
5 => 1,
6 => 10,
7 => '2',
8 => 500,
9 => 1,
10 => 0,
11 => 'af2d5f4fd65a736f98449a0cf68d84ad',
12 => VERY LONG STRING!

13 => 1376643127,
14 => 0,
)]
Error code: dmlwriteexception

Stack trace:
  • line 426 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1089 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1131 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 87 of /backup/util/dbops/restore_controller_dbops.class.php: call to mysqli_native_moodle_database->insert_record()
  • line 384 of /backup/controller/restore_controller.class.php: call to restore_controller_dbops::save_controller()
  • line 137 of /backup/util/ui/base_ui.class.php: call to restore_controller->save_controller()
  • line 52 of /backup/restore.php: call to base_ui->save_controller()

_________________________________________________________________________

I have successfully restored a 7MB course, so I guess the issue is related to the discussion above.

My question is: Where do I edit the following line?

max_allowed_packet = 100M

In my moodle folder I have a php.ini, which contains only

upload_max_filesize = 100M
post_max_size = 100M

I do not have a my.ini or myconf file.

I looked in the moodle database but didn't know if I could/should make any changes directly there.

 

- David

評点平均: -
David Dunn への返信

Re: "Error writing to database" during Course Restore

- David Dunn の投稿

I just successfully restored a 100MB, without having changed anything.

Yet the error message with the failed restored course specifically states "Got a packet bigger than 'max_allowed_packet' bytes".

思慮深い

評点平均: -
David Dunn への返信

Re: "Error writing to database" during Course Restore

- Ken Task の投稿
画像 Particularly helpful Moodlers

@David

max_allowed_packet is not PHP.  It's a MySQL setting.  On Winders, the my.ini file (as a poster in here pointed out).  On Linux, my.cnf.  If you are remotely hosted, you might have to contact hosting provider and inquire.  Not sure they give access to settings for DB server such as that.

It's not really the size of the zip/mbz that's an indicator, it's the data contained therein ... especially those DB records/fields that contain a BIG chunk of data like a wiki.

For a technical explanation, please see:

http://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

'spirit of sharing', Ken

評点平均:Useful (2)
Ken Task への返信

Re: "Error writing to database" during Course Restore

- David Dunn の投稿

Hi Ken,

I have full access to our moodle server, so I can access the DB. In the course in question, I cannot see a single item that could be too big. There was a 32MB audio file that I deleted, but this did not prevent the same error message when trying to restore. Is there any way (sorting in the DB?) to find out which file is too big? Or can I create a my.cnf file with the necessary max_allowed_packet line?

I appreciate all your time in helping out on these forums, Ken.

評点平均: -
David Dunn への返信

Re: "Error writing to database" during Course Restore

- Ken Task の投稿
画像 Particularly helpful Moodlers

@David

Even if you delete files contained in a course and linked to in that course in the Moodle UI, it may not help.   Moodle 2 doesn't tokenize and create records in the database that represent the files.  It does, however, have metadata that pertain to them (humanly recognizable file name, size of file, mime type, etc.).  The actual physical file's name is hashed and placed in filesdir of the data folder.

The table Moodle is trying to write to: mdl_backup_controllers.  That line that you edited out: 12 => VERY LONG STRING!  adds to the issue by may not be the culprit by itself ... depending upon how Moodle attempts to write to mdl_backup_controllers.  The database will not accept the total chunk of data Moodle is attempting to send to it.

If you are remotely hosted on a shared server, you may NOT be able to add any my.cnf file to control the database server ... which is also shared by multiple customers.  Most setups like that *are* restricted ... that's why they are so cheap!  ** You must contact your provider and inquire about increasing max_packets for your setup. **

'spirit of sharing', Ken

 

評点平均: -
Ken Task への返信

Re: "Error writing to database" during Course Restore

- David Dunn の投稿

Hi Ken,

Fortunately this installation is not on a shared host but on our own university servers. A kind sysadmin bumped the max_packets up to 100M and I have been able to restore the final troublesome course.

All's well that ends well 満面の笑顔

評点平均: -
David Dunn への返信

Re: "Error writing to database" during Course Restore

- Rui Pimenta の投稿

Hi David and Ken,

I have the same problema and i'am using MSSQL 2008, where can i change that variable?

Thanks in advance.

Best Regards

評点平均: -
Rui Pimenta への返信

Re: "Error writing to database" during Course Restore

- Ken Task の投稿
画像 Particularly helpful Moodlers

Don't use MSSQL, but can Google!

http://bit.ly/19cp51j

Hopefully, that search will point you to a MSSQL page/posting/blog, etc. that tells how.

'spirit of sharing', Ken

評点平均: -
Ken Task への返信

Re: "Error writing to database" during Course Restore

- Rui Pimenta の投稿

Hi ken

Thanks for your reply, i already googleit, but the result is different, mssql only talks about network packet size, and has a maximum value, so i'am not sure if that works, so i ask David,because he already solve the problem using mssql.

Best regards

評点平均: -
Rui Pimenta への返信

Re: "Error writing to database" during Course Restore

- neha pal の投稿

Hello,


M also gtng same problem while adding a quiz in the course when i click to svae and continue button it show me this error and in the database  phpmyadmin mood_course table has the modinfo and sectioncache ....

and when i adding a new course this both the field showing NULL value bt actually this field should have  data  like  this: a:1:{i:31;O:8:"stdClass":12:{s:2:"id";s:2:"17";s:2....... idnt knw this all is related to my error or not bt i notice this  kindlly plz rply


評点平均: -
Ken Task への返信

Re: "Error writing to database" during Course Restore

- Ryan Hazen の投稿

I ran into the same issue with a locally hosted Moodle 2.7.8. Started having issues with course restores from publishers and from an earlier instance of Moodle 2.6, also hosted locally. Basically, I would try to restore and after selecting "merge contents into this course" I would get an error message reading "error writing to database." For a few courses I stripped out bigger items (like folders and powerpoint files), and had mixed success restoring elements of the courses this way.

In any case, I had success restoring everything after changing Max_packet_size to 100 MB, as suggested by Ken. For those that encounter this issue in the future, I would start here. 

Again, thanks a lot for your contributions, Ken. You're the man.

評点平均: -