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
Plesk for Linux kb: bug ext: migrator ABT: Group B

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

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
  • Avatar
    Che Guevara

    Let me know when the problem is fixed on the next update.

    0
    Comment actions Permalink
  • Avatar
    Anzhelika Khapaknysh (Edited )

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

    0
    Comment actions Permalink
  • Avatar
    Che Guevara

    @Anzhelika Khapaknysh

    Thank you.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Related articles