Migrating a WordPress site is something I do a lot on a weekly basis. Many migrations happen between local environments or production and staging. The source and target systems don’t necessarily have the same settings. From time to time you have to migrate a site with a large database. In these cases, you might run into this error:

ERROR 1153 (08S01) at line 56: Got a packet bigger than 'max_allowed_packet' bytes

This can happen when a single INSERT statement on the import is too large. In this blog post, I want to give you tips on how to import the database.

Create a new backup with smaller imports

When you export your databases, you probably use either the mysqldump command, the wp db export or a database management tool such as Adminer or phpMyAdmin. In the default settings, they will create INSERT statements that would not only import a single line into a table, but multiple of them. This is usually a good strategy, as it speeds up the import. But if one of those statements become too long, you will run into the error.

To solve it, you can try to create an export of the database, that only has one single row in any INSERT statement. This will make the import run longer, but it’s more likely it succeeds.

To create an export like this, you can use the WP-CLI with some additional mysqldump arguments:

wp db export --extended-insert=FALSE --complete-insert=TRUE

This should create such an export file. Now you should hopefully be able to import that database dump.

Increase the “max_allowed_packet” value

If you are still unable to import the database dump file, you probably have to increase the value of “max_allowed_packet”. The default value can usually only be changed by the server administrator. But you can often increase the value temporarily. For this, you connect to the MySQL server, increase the value and import the file. I will use the wp db cli command from the WP-CLI. This will connect me to the correct database of my WordPress installation. Now I can run the commands:

SET GLOBAL max_allowed_packet=1*1024*1024*1024;
SOURCE db_dump_filename.sql;

The first command will set the variable globally to 1 GB. Unlike in the MySQL configuration file, you can not use a value of “1G” in this statement, but you have to use a number in bytes. But as you can also use a calculation while setting the value, in the above notation it’s easier to see, what the value in bytes will be.

Conclusion

Importing large databases can be an issue. But with these tips you can hopefully import it yourself. If not, ask a server administrator to help you out. They probably have tools to do the import for you. And if you yourself write data into the database, please make sure rows don’t get too large.