Applicable to:
- Plesk for Linux
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
-
Backup the file my.cnf:
# cp -a /etc/my.cnf /root/
-
Edit the file
my.cnfwith any text editor (for example: vi) and add the following lines to[mysqld]and[mysqldump]sections:CONFIG_TEXT: 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
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.pyfile with any text editor (for example: vi):CONFIG_TEXT: u"mysqldump --no-defaults -h {src_host} -P {src_port} -u{src_admin} --quick --quote-names "
Should be replaced with
CONFIG_TEXT: u"mysqldump --max_allowed_packet=1073741824 -h {src_host} -P {src_port} -u{src_admin} --quick --quote-names "
Comments
3 comments
Let me know when the problem is fixed on the next update.
@Saokimthanglong,
I'd recommend clicking Follow button under the title of the article.
By following the article you'll receive notifications on article update.
Be sure that the article will be updated once bug is fixed.
@Anzhelika Khapaknysh
Thank you.
Please sign in to leave a comment.