Backup via moosh failed

Backup via moosh failed

Francis Lorenzo Guingao Doydora - මගින්
Number of replies: 8

Good day everyone,

The moodle installation uses Ubuntu 20.04 LTS, Php 7.4, MySQL 8.0, and moodle version is 3.9.6

My problem is that backup via moosh never finishes, same happens when going through the UI.

Attached is the error after performing backup.

Default exception handler: Error reading from database Debug: 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 => '00bca615721834e0e48834155e4274a1',
)]
Error code: dmlreadexception
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1624 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
* line 1697 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
* line 1907 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
* line 522 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
* line 2088 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 178 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
* line 145 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
* line 410 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
* line 95 of /home/elearnadmin/moosh/Moosh/Command/Moodle23/Course/CourseBackup.php: call to backup_controller->execute_plan()
* line 354 of /home/elearnadmin/moosh/moosh.php: call to Moosh\Command\Moodle23\Course\CourseBackup->execute()

!!! Error reading from database !!!

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 => '00bca615721834e0e48834155e4274a1',
)]
Error code: dmlreadexception
Stack trace: * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1624 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
* line 1697 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
* line 1907 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
* line 522 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
* line 2088 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 178 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
* line 145 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
* line 410 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
* line 95 of /home/elearnadmin/moosh/Moosh/Command/Moodle23/Course/CourseBackup.php: call to backup_controller->execute_plan()
* line 354 of /home/elearnadmin/moosh/moosh.php: call to Moosh\Command\Moodle23\Course\CourseBackup->execute()
Exception ignored in shutdown function tool_log\log\manager::dispose: Error reading from database (MySQL server has gone away
SHOW VARIABLES LIKE 'max_allowed_packet'
[NULL])
Exception ignored in shutdown function tool_monitor\eventobservers::process_buffer: Error reading from database (MySQL server has gone away
SHOW VARIABLES LIKE 'max_allowed_packet'
[NULL])
Potential coding error - existing temptables found when disposing database. Must be dropped!
PHP Fatal error:  Uncaught ddl_change_structure_exception: DDL sql execution error (MySQL server has gone away
DROP TEMPORARY TABLE mdl_backup_ids_temp) in /var/www/html/moodle/lib/dml/moodle_database.php:492
Stack trace:
#0 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(1098): moodle_database->query_end()
#1 /var/www/html/moodle/lib/ddl/database_manager.php(77): mysqli_native_moodle_database->change_database_structure()
#2 /var/www/html/moodle/lib/ddl/database_manager.php(332): database_manager->execute_sql_arr()
#3 /var/www/html/moodle/lib/dml/moodle_temptables.php(140): database_manager->drop_table()
#4 /var/www/html/moodle/lib/dml/moodle_database.php(405): moodle_temptables->dispose()
#5 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(622): moodle_database->dispose()
#6 /var/www/html/moodle/lib/dml/moodle_database.php(160): mysqli_native_moodle_database->dispose()
#7 [internal function]: moodle_database->__destruct()
#8 {main}
  thrown in /var/www/html/moodle/lib/dml/moodle_database.php on line 492

Fatal error: Uncaught ddl_change_structure_exception: DDL sql execution error (MySQL server has gone away
DROP TEMPORARY TABLE mdl_backup_ids_temp) in /var/www/html/moodle/lib/dml/moodle_database.php:492
Stack trace:
#0 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(1098): moodle_database->query_end()
#1 /var/www/html/moodle/lib/ddl/database_manager.php(77): mysqli_native_moodle_database->change_database_structure()
#2 /var/www/html/moodle/lib/ddl/database_manager.php(332): database_manager->execute_sql_arr()
#3 /var/www/html/moodle/lib/dml/moodle_temptables.php(140): database_manager->drop_table()
#4 /var/www/html/moodle/lib/dml/moodle_database.php(405): moodle_temptables->dispose()
#5 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(622): moodle_database->dispose()
#6 /var/www/html/moodle/lib/dml/moodle_database.php(160): mysqli_native_moodle_database->dispose()
#7 [internal function]: moodle_database->__destruct()
#8 {main}
  thrown in /var/www/html/moodle/lib/dml/moodle_database.php on line 492


And these are the steps I've done to try and fix it.

Error running backup, decided to change max allowed packet in mysql.cnf -> https://docs.moodle.org/27/en/error/moodle/dmlwriteexception

changed max_allowed_packet to 1GB in mysql

Error still persists, next step change max_execution_time in php.ini -> https://moodle.org/mod/forum/discuss.php?d=320964

changed max_execution_time in php.ini to 1200 from 600
    required apache2 service restart

Error still persists, next step change max_heap_table_size and tmp_table_size -> https://moodle.org/mod/forum/discuss.php?d=273400

SELECT @@global.max_heap_table_size;
SET PERSIST max_heap_table_size = 1024 * 1024 * 1024;

SELECT @@global.tmp_table_size;
SET PERSIST tmp_table_size = 1024 * 1024 * 1024;

Error still persists.

Your help is greatly appreciated, thanks and stay safe and well.


ශ්‍රේණිගත කිරීම්වල සාමාන්යය: Useful (1)
In reply to Francis Lorenzo Guingao Doydora

Re: Backup via moosh failed

Ken Task - මගින්
Particularly helpful Moodlers ගේ පින්තුරය

2 things:

1st gone away does indicate max_allowed_packet increase and you did that, but did you restart the mysql service for that change to take affect?

mysql> show variables like 'max_allowed_packet';

2nd:
/home/elearnadmin/moosh/moosh.php: call to Moosh\Command\Moodle23\Course\CourseBackup->execute()

moodle version is 3.9.6 but moosh version is version 23?

what does moosh -v return?

or moosh -n --list-commands |grep course

That version of moosh didn't have a course-backup command me thinks.

So me thinks you need to upgrade moosh.

'SoS', Ken

ශ්‍රේණිගත කිරීම්වල සාමාන්යය: Useful (1)
In reply to Ken Task

Re: Backup via moosh failed

Francis Lorenzo Guingao Doydora - මගින්
Hi Ken,

Thanks for replying.

mysql> show variables like 'max_allowed_packet';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.00 sec)
and

elearnadmin@elearn2:/var/www/html/moodle$ moosh -n --list-commands | grep course
cache-course-rebuild
category-resortcourses
course-backup
course-cleanup
course-config-set
course-create
course-delete
course-enableselfenrol
course-enrol
course-enrolbyname
course-info
course-list
course-reset
course-restore
course-unenrol
ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -
In reply to Francis Lorenzo Guingao Doydora

Re: Backup via moosh failed

Ken Task - මගින්
Particularly helpful Moodlers ගේ පින්තුරය

So max packets is there.

What did moosh -v show?

or are you reporting sucessfully using the command now?

There is a backup.php script in code/admin/cli/ that has an add destination.

'SoS', Ken

ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -
In reply to Ken Task

Re: Backup via moosh failed

Francis Lorenzo Guingao Doydora - මගින්

Hi Ken,

I've updated moosh and it now shows version 1.3 but the error still exists. Please find attached the error.

elearnadmin@elearn2:/var/www/html/moodle$ moosh -n course-backup --fullbackup -f /home/elearnadmin/Backups/mybackup.mbz 4672
Default exception handler: Error reading from database Debug: 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 => '440e40b4398f39af51195d9101f720fb',
)]
Error code: dmlreadexception
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1624 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
* line 1697 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
* line 1907 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
* line 522 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
* line 2088 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 178 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
* line 145 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
* line 410 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
* line 95 of /home/elearnadmin/moosh/Moosh/Command/Moodle39/Course/CourseBackup.php: call to backup_controller->execute_plan()
* line 361 of /home/elearnadmin/moosh/moosh.php: call to Moosh\Command\Moodle39\Course\CourseBackup->execute()

!!! Error reading from database !!!

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 => '440e40b4398f39af51195d9101f720fb',
)]
Error code: dmlreadexception
Stack trace: * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
* line 1624 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
* line 1697 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
* line 1907 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
* line 522 of /backup/util/dbops/backup_controller_dbops.class.php: call to moodle_database->count_records_sql()
* line 2088 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 178 of /backup/util/plan/base_plan.class.php: call to base_task->execute()
* line 145 of /backup/util/plan/backup_plan.class.php: call to base_plan->execute()
* line 410 of /backup/controller/backup_controller.class.php: call to backup_plan->execute()
* line 95 of /home/elearnadmin/moosh/Moosh/Command/Moodle39/Course/CourseBackup.php: call to backup_controller->execute_plan()
* line 361 of /home/elearnadmin/moosh/moosh.php: call to Moosh\Command\Moodle39\Course\CourseBackup->execute()
Exception ignored in shutdown function tool_log\log\manager::dispose: Error reading from database (MySQL server has gone away
SHOW VARIABLES LIKE 'max_allowed_packet'
[NULL])
Exception ignored in shutdown function tool_monitor\eventobservers::process_buffer: Error reading from database (MySQL server has gone away
SHOW VARIABLES LIKE 'max_allowed_packet'
[NULL])
Potential coding error - existing temptables found when disposing database. Must be dropped!
PHP Fatal error:  Uncaught ddl_change_structure_exception: DDL sql execution error (MySQL server has gone away
DROP TEMPORARY TABLE mdl_backup_ids_temp) in /var/www/html/moodle/lib/dml/moodle_database.php:492
Stack trace:
#0 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(1098): moodle_database->query_end()
#1 /var/www/html/moodle/lib/ddl/database_manager.php(77): mysqli_native_moodle_database->change_database_structure()
#2 /var/www/html/moodle/lib/ddl/database_manager.php(332): database_manager->execute_sql_arr()
#3 /var/www/html/moodle/lib/dml/moodle_temptables.php(140): database_manager->drop_table()
#4 /var/www/html/moodle/lib/dml/moodle_database.php(405): moodle_temptables->dispose()
#5 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(622): moodle_database->dispose()
#6 /var/www/html/moodle/lib/dml/moodle_database.php(160): mysqli_native_moodle_database->dispose()
#7 [internal function]: moodle_database->__destruct()
#8 {main}
  thrown in /var/www/html/moodle/lib/dml/moodle_database.php on line 492

Fatal error: Uncaught ddl_change_structure_exception: DDL sql execution error (MySQL server has gone away
DROP TEMPORARY TABLE mdl_backup_ids_temp) in /var/www/html/moodle/lib/dml/moodle_database.php:492
Stack trace:
#0 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(1098): moodle_database->query_end()
#1 /var/www/html/moodle/lib/ddl/database_manager.php(77): mysqli_native_moodle_database->change_database_structure()
#2 /var/www/html/moodle/lib/ddl/database_manager.php(332): database_manager->execute_sql_arr()
#3 /var/www/html/moodle/lib/dml/moodle_temptables.php(140): database_manager->drop_table()
#4 /var/www/html/moodle/lib/dml/moodle_database.php(405): moodle_temptables->dispose()
#5 /var/www/html/moodle/lib/dml/mysqli_native_moodle_database.php(622): moodle_database->dispose()
#6 /var/www/html/moodle/lib/dml/moodle_database.php(160): mysqli_native_moodle_database->dispose()
#7 [internal function]: moodle_database->__destruct()
#8 {main}
  thrown in /var/www/html/moodle/lib/dml/moodle_database.php on line 492

I've tried the moodle built-in backup cli script and attached is the error.

elearnadmin@elearn2:/var/www/html/moodle$ sudo -u www-data /usr/bin/php admin/cli/backup.php --courseid=4672 --destination=/home/elearnadmin/Backups/
[sudo] password for elearnadmin:
== Performing backup... ==
!!! Error reading from database !!!
Potential coding error - existing temptables found when disposing database. Must be dropped!
ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -
In reply to Francis Lorenzo Guingao Doydora

Re: Backup via moosh failed

Ken Task - මගින්
Particularly helpful Moodlers ගේ පින්තුරය

Is DB host localhost? Is your instance on shared hosting?

Both moosh and the admin/cli/backup.php script use config.php info - DB user.  Using mysql client, logged on as user for DB seen in config.php,

show grants;

If you can, change DB user/password in config.php to superuser credentials then execute commands.

After success, you can change back DB user/password. 

'SoS', Ken


ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -
In reply to Francis Lorenzo Guingao Doydora

Re: Backup via moosh failed

Ken Task - මගින්
Particularly helpful Moodlers ගේ පින්තුරය

Check moodledata/temp/backup/ directory for directories that are named with a bunch of letters/numbers.  Inspect one of those.

The last thing a backup routine does is to copy the built .mbz file to a location as set in backup preferences/automated backup settings.   If in one of those directories you see a 'backup.mbz' file, that is a valid backup of that course, but your system could not 'copy' the .mbz to final destination.

There is a cron job that is supposed to clean up that area of moodledata.  If there are directories present, might want to manually delete them .... to assure you don't run out of free space on partition where moodledata resides.

Me thinks this is an issue of DB user in config not having priv's to drop the temp table moodle creates when doing a backup.

mdl_backup_ids_temp ... is there a table by that name in DB?

Overtime database could need tweaking.   Suggest intalling MySQL Tuner and run 
the .pl script as superuser of DB server.  Look at recommendations at bottom.
Note: you don't have to do all that is recommended ... so read up on recommendatons
and make decisions.

'SoS', Ken


ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -
In reply to Ken Task

Re: Backup via moosh failed

Francis Lorenzo Guingao Doydora - මගින්

Hi Ken,

I finally solved the issue.

I found this post https://moodle.org/mod/forum/discuss.php?d=400018#p1634159 and his recommendations are what fixed it.

I changed the wait_timeout, net_read_timeout,  and net_write_timeout to 120.

Thanks again.

ශ්‍රේණිගත කිරීම්වල සාමාන්යය: Useful (1)
In reply to Francis Lorenzo Guingao Doydora

Re: Backup via moosh failed

Séverin Terrier - මගින්
Documentation writers ගේ පින්තුරය Particularly helpful Moodlers ගේ පින්තුරය Testers ගේ පින්තුරය Translators ගේ පින්තුරය
Hi,

Thanks for pointing to this discussion. I had problems with automatic backups, and "errors reading database".

I've changed timeout and max_allowed_packet values for MariaDB, and problems have gone සිනහව

Séverin
ශ්‍රේණිගත කිරීම්වල සාමාන්යය: -