Symptoms
Unable to migrate large MySQL database to Plesk server with the following error message:
PLESK_ERROR: Failed to copy content of database ‘exampledb’
Migration tools tried to perform operation in 3 attempts: Command execution failed on the source server ‘source’ (203.0.113.2) with non-zero exit code.
command: MYSQL_PWD=”$(cat)” mysqldump –no-defaults -h localhost -P 3306 -uadmin –quick –quote-names –add-drop-table –default-character-set=utf8 –set-charset –routines –events exampledb > /home/tmp_for_migration/plesk_migrator-ys5t5s9jg70cylmmnek7feuchdgw6ejy/db-dumps/exampledb.sql
exit code: 3
stdout:
stderr: mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table wp_postmeta at row: 780798
Cause
Plesk Migrator bug with ID #PMT-4581 which will be fixed in future product updates.
Database with large record could not be migrated because of default max_allowed_packet for mysqldump.
Resolution
Migrate the database manually
-
Backup the file my.cnf:
# cp -a /etc/my.cnf /root/
-
Edit the file
my.cnf
with any text editor (for example: vi) and add the following lines to[mysqld]
and[mysqldump]
sections:wait_timeout = 12000
max_allowed_packet = 1024M -
Restart MySQL service:
# service mysql restart
-
Create a dump on the source server using the following command:
# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` -h localhost -P 3306 –quick –quote-names –add-drop-table –default-character-set=utf8 –set-charset –routines –events –skip-extended-insert exampledb > /home/exampledb.sql
-
Transfer the dump to destination server.
-
Upload information from the dump to the required database:
# cat /home/exampledb.sql | mysql -uadmin -p`cat /etc/psa/.psa.shadow` -h localhost -P 3306 exampledb
Apply the patch for Plesk migrator
Warning: This solution has not been tested by Plesk developers and could be overwritten during Plesk Migrator updates.
-
Backup the file
/usr/local/psa/admin/plib/modules/panel-migrator/backend/lib/python/parallels/core/utils/database_utils.py
:# cp -a /usr/local/psa/admin/plib/modules/panel-migrator/backend/lib/python/parallels/core/utils/database_utils.py /usr/local/psa/admin/plib/modules/panel-migrator/backend/lib/python/parallels/core/utils/database_utils.py.backup
-
Replace the following string in
/usr/local/psa/admin/plib/modules/panel-migrator/backend/lib/python/parallels/core/utils/database_utils.py
file with any text editor (for example: vi):u"mysqldump --no-defaults -h {src_host} -P {src_port} -u{src_admin} --quick --quote-names "
Should be replaced with
u"mysqldump --max_allowed_packet=1073741824 -h {src_host} -P {src_port} -u{src_admin} --quick --quote-names "