"MySQL server has gone away" error when making backup

"MySQL server has gone away" error when making backup

per Miha Osojnik,
Number of replies: 8

Hello moodlers.

 

I have a problem with creating backup of one of our courses. Proces of selecting what to backup goes OK, but proces stops at 96.38% and gives me this error info:

Debug info: MySQL server has gone away
SELECT count(r.repositoryid)
FROM mdl_files f
LEFT JOIN mdl_files_reference r
ON r.id = f.referencefileid
JOIN mdl_backup_ids_temp bi
ON f.id = bi.itemid
WHERE bi.backupid = ?
AND bi.itemname = 'filefinal'
[array (
0 => 'f61a2222557c5724f642bd63c961fdca',
)]
Error code: dmlreadexception
Stack trace:
  • line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 996 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1428 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
  • line 1501 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
  • line 1672 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
  • line 524 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
  • line 1772 of /backup/moodle2/backup_stepslib.php: call to backup_controller_dbops::backup_includes_file_references()
  • line 34 of /backup/util/plan/backup_execution_step.class.php: call to backup_store_backup_file->define_execution()
  • line 181 of /backup/util/plan/base_task.class.php: call to backup_execution_step->execute()
  • line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
  • line 120 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
  • line 320 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
  • line 111 of /backup/util/ui/backup_ui.class.php: call to backup_controller->execute_plan()
  • line 131 of /backup/backup.php: call to backup_ui->execute()

 

Can you help me please.

 

Average of ratings: -
In reply to Miha Osojnik

Re: "MySQL server has gone away" error when making backup

per Kevin Wiliarty,

In some cases when I have encountered this error increasing the `max_allowed_packet` size in `my.cnf` has made the difference. There's information about this -- although in reference to a dmlwriteexception -- as well as a suggestion to check the file format for InnoDB tables at:

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

Average of ratings:Useful (1)
In reply to Kevin Wiliarty

Odg: Re: "MySQL server has gone away" error when making backup

per Miha Osojnik,

Thanks for your fast and sorry for my late response (too much to do and too little time).

Your sugestion and this one https://moodle.org/mod/forum/discuss.php?d=273400 helped us to find a solution.

Average of ratings: -
In reply to Miha Osojnik

Re: Odg: Re: "MySQL server has gone away" error when making backup

per Ben Laor,
Can you share the specific solution you used? i'm having the same problem and can't seem to fix it
Average of ratings: -
In reply to Ben Laor

Re: Odg: Re: "MySQL server has gone away" error when making backup

per Ben Laor,

After trying all the offered solutions (increasing max_allowed_packet, adding $CFG->dbsessions='false', and updating all tables to Barracuda) The solution was found by going through the course's units and deleting them one by one until we could perform a backup, and we found the problematic content (probably was corrupted).
Not the fastest solution but the best in lack of a better one.

Average of ratings: -
In reply to Kevin Wiliarty

Re: "MySQL server has gone away" error when making backup

per Brett Baker,

Hi Kevin I Increased both max_allowed_packet and the execution in php ini but seem to still have the problem any furthur ideas as this is not allowing me to backup a specific  course other courses backup fine but are smaller?

Debug info: MySQL server has gone away
SELECT COUNT(*)
FROM mdl_backup_ids_temp b
JOIN mdl_user u ON u.id = b.itemid
WHERE b.backupid = ?
AND b.itemname = 'userfinal'
AND u.mnethostid != ?
[array (
0 => 'a80f890aab0733d54ebcaac460f2643f',
1 => '1',
)]
Error code: dmlreadexception
Stack trace:
  • line 474 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 1088 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1527 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
  • line 1600 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
  • line 1810 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
  • line 471 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
  • line 1771 of /backup/moodle2/backup_stepslib.php: call to backup_controller_dbops::backup_includes_mnet_remote_users()
  • line 88 of /backup/util/plan/backup_structure_step.class.php: call to backup_main_structure_step->define_structure()
  • line 181 of /backup/util/plan/base_task.class.php: call to backup_structure_step->execute()
  • line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
  • line 120 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
  • line 322 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
  • line 136 of /backup/util/ui/backup_ui.class.php: call to backup_controller->execute_plan()
  • line 131 of /backup/backup.php: call to backup_ui->execute()

Average of ratings: -
In reply to Miha Osojnik

Re: "MySQL server has gone away" error when making backup

per Robert Clements,

I am having the same issue and my system admin at the hosting company has changed the max_allowed_packet to max_allowed_packet=500M and still I get this error.

3.62 secs

Error reading from database

More information about this error
Debug info: MySQL server has gone away
SELECT count(r.repositoryid)
FROM mdlua_files f
LEFT JOIN mdlua_files_reference r
ON r.id = f.referencefileid
JOIN mdlua_backup_ids_temp bi
ON f.id = bi.itemid
WHERE bi.backupid = ?
AND bi.itemname = 'filefinal'
[array (
0 => '57d19b6e4a02d510ffffbb3f125ab53b',
)]
Error code: dmlreadexception
Stack trace:

    line 443 of /lib/dml/moodle_database.php: dml_read_exception thrown
    line 1080 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 1480 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
    line 1553 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
    line 1763 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
    line 516 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
    line 1847 of /backup/moodle2/backup_stepslib.php: call to backup_controller_dbops::backup_includes_file_references()
    line 34 of /backup/util/plan/backup_execution_step.class.php: call to backup_store_backup_file->define_execution()
    line 181 of /backup/util/plan/base_task.class.php: call to backup_execution_step->execute()
    line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
    line 120 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
    line 320 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
    line 136 of /backup/util/ui/backup_ui.class.php: call to backup_controller->execute_plan()
    line 131 of /backup/backup.php: call to backup_ui->execute()

I have also attached a copy of the my.cnf file in a txt file format so you can see if there is anything else we are missing.


Average of ratings: -