Knowledge Base

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

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

Connect to the server using SSH.
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.

Connect to the server using SSH.
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 "
```