General help

 
 
Picture of Chris Barone
Error writing to database
 

I keep on getting an Error writing to database on certain Moodle courses when I try to use the backup feature. The odd thing about it is I updated my.cnf file 'max_allowed_packet' to 10000M so I cant imagine these courses are bigger than that. I feel that I am missing something with this one. 

 

Any help would be greatly appreciated. 

 

See attached file (Moodle 2.4.3+)

 

Thanks,

Chris

 

 
Average of ratings: -
Picture of Howard Miller
Re: Error writing to database
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Could just be me... but I can't open your attachment. It's much preferred if you simply copy/paste the error messages into the forum please.

 
Average of ratings: -
Picture of Christian Herman
Re: Error writing to database
Group Particularly helpful Moodlers

You don't want him to paste the entire error into the forums.  It's 47 pages.  Abbreviated:

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 => 'c1fe550e1e23e34f65a85a273cd20e6b',
1 => 'backup',
2 => 'course',
3 => 215,
4 => 'moodle2',
5 => 1,
6 => 10,
7 => '1227',
8 => 500,
9 => 1,
10 => 0,
11 => '4139ffcd737bdd7af49ea27e4d752da1',
12 => '117,000+ continuous characters of junk test',
13 => 1366294435,
14 => 0,
)]
Error code: dmlwriteexception

Stack trace:

  • line 429 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 1107 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1149 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
  • line 87 of /backup/util/dbops/backup_controller_dbops.class.php: call to mysqli_native_moodle_database->insert_record()
  • line 333 of /backup/controller/backup_controller.class.php: call to backup_controller_dbops::save_controller()
  • line 137 of /backup/util/ui/base_ui.class.php: call to backup_controller->save_controller()
  • line 91 of /backup/backup.php: call to base_ui->save_controller()
 
Average of ratings: -
Picture of Chris Barone
Re: Error writing to database
 

Christian,

Thanks for that! So more information: I just recently updated to 2.4.3 about two weeks ago, I didn't have any reports of this issue before the update. I would like to keep the version were its at but if it becomes more of a widespread issue I can restore my old moodle from backup. 

 

 

Chris

 
Average of ratings: -
Picture of Rob Johnson
Re: Error writing to database
Group Particularly helpful Moodlers

You can adjust the max_allowed packets in the my.cnf file of your server.

 
Average of ratings: -
Picture of Chris Barone
Re: Error writing to database
 

Rob

I did that already and I am still getting this. I reboot the server a few times to make sure I wasn't losing it either. Any other suggestions? Is it possible I made it to big? I tried 100m and that didn't help either. Could it possible be just these couple of courses?

 

Thanks,

Chris

 

 
Average of ratings: -
Picture of Christian Herman
Re: Error writing to database
Group Particularly helpful Moodlers

It's an old thread but...

https://moodle.org/mod/forum/discuss.php?d=71608

They suggest moving sessions out of the db to the file level to lighten the load on SQL.

 
Average of ratings: -
Picture of Chris Barone
Re: Error writing to database
 

I tired this still with no sucess. I will keep an eye on it though I am nervous about performance issues. 

 

Any other suggestions? Thanks!

 
Average of ratings: -
Picture of Rob Johnson
Re: Error writing to database
Group Particularly helpful Moodlers

It is very likely just those courses.  They are sending a packet larger than the database will allow with the settings you have.

 
Average of ratings: -
Picture of Christian Herman
Re: Error writing to database
Group Particularly helpful Moodlers

Well, in that case, just how big is this course you're backing up, Chris?

 
Average of ratings: -
Picture of Chris Barone
Re: Error writing to database
 

I would like to know that myself. Sorry for the noob question but how can I find out?

 
Average of ratings: -
Picture of Ken Task
Re: Error writing to database
Group Particularly helpful Moodlers

Please see:

https://moodle.org/mod/forum/discuss.php?d=212532&parent=926137

https://github.com/peterbulmer/moodle-report_coursesize

Have installed on a 2.3.6+ version of Moodle.  It works. 

No real directions to install, however.  Unzipping in /moodle23/report/ will create a directory with a long name:

moodle-report_coursesize-master

change that directory name to 'coursesize' and change ownerships/permissions appropriate to system.  Then remove the zip and hit notifications (Site Admin Menu).

It does work as advertised and will give you info about course sizes ... and even a list of your users usage.

Have also tried it in a 2.4.3+.  Works there was well.

Uhhhh, make a backup of your site(s) first (just in case).

'spirit of sharing', Ken

 
Average of ratings: -
Picture of Chris Barone
Re: Error writing to database
 

Ken,

Thank you so much for this! Works great! As for the course size its only 110 MB so I am still confused on why I am getting this error. I fear that my database has some issues. 

Would anyone recommend a reorg. on the database? 

 
Average of ratings: -
Picture of Ken Task
Re: Error writing to database
Group Particularly helpful Moodlers

Not sure that the tool is all that accurate - consider it to be an 'approximation'.

Finally opened (against my 'better judgement') the docx - got to curious - and see that 'field' (12) contains mucho data.  Think that's supposed to be either 'checksum' or 'timecreated' ... so there is something gone wrong there - but I couldn't begin to tell you what!!! :\

First, check your moodledata/temp/backup/ folder for any folders that might still be residing there.  Is there a 'c1fe550e1e23e34f65a85a273cd20e6b' folder?  There really shouldn't be any when backups complete successfully.  Rather than explore what's in there - a rather complicated process - suggest that it is safe to remove them manually.

Then, using whatever tool you have to explore tables in your DB find the records that this failed backup refers to:

select * from `mdl_backup_controllers where backupid="c1fe550e1e23e34f65a85a273cd20e6b"`

Remove that record.

Then, take stock of that course (course id) ... what's in it?  Anything particularly large?   Anything that's embeded?

Might have to resort to un-checking suspect items in the course during the backup process (excluding them) and seeing which/what causes an error.

'spirit of sharing', Ken

 
Average of ratings: -
Picture of Christian Herman
Re: Error writing to database
Group Particularly helpful Moodlers

Field 12 is 'controller'.  I did a little poking around in the backup docs but couldn't make sense of exactly what that was or what a typical result in that field should look like.

 
Average of ratings: -
Picture of Ken Task
Re: Error writing to database
Group Particularly helpful Moodlers

Gee ... can't count! sad  Anyway ... in looking at that table for a 2.4.3+ and a site that hasn't had the same issue, that field is 'blank' ... ie, contains no data.

Whlle the setting for max_packets looks large enough, wonder if it really is?  Think I'd bump it up a little ... restart mysqld ... and TIA (try it again!).

'spirit of sharing', Ken

 
Average of ratings: -