Articles in this section

See more

Unable to migrate large MySQL database to Plesk server: : Got packet bigger than 'max_allowed_packet' bytes

Avatar
Vladimir Chernikov
Updated
Follow

Applicable to:

  • Plesk Onyx 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

Migrate the database manually

  1. Connect to the server using SSH.

  2. Backup the file my.cnf:

    # cp -a /etc/my.cnf /root/

  3. Edit the file my.cnf with 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

  4. Restart MySQL service:

    # service mysql restart

  5. 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

  6. Transfer the dump to destination server.

  7. 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.

  1. Connect to the server using SSH.

  2. 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

  3. 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):

    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

Sort by Date Votes

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles