MySQL: Got Packet Bigger Than max_allowed_packet bytes

We might face error like "ERROR 1153 (08S01) at line 25247: Got a packet bigger than 'max_allowed_packet' bytes" while importing database in MySQL/MariaDB. Sometimes error message may be like "ERROR 2006 (HY000) at line 25247: MySQL server has gone away". It happens when the database dump file is having SQL INSERT statements with long lines of data.

The solution is to increase the allowed packet size on MySQL/MariaDB server as well as specifying it on 'mysql' command used to import the database.

Login to mysql server as root user. Then, run these commands in MySQL/MariaDB command prompt.

SET global net_buffer_length=1*1024*1024;
SET global max_allowed_packet=100*1024*1024;

Also need to specify max_allowed_packet size in 'mysql' client command.

mysql --max_allowed_packet=100M -u root -p mydatabase < db-dump.sql

Please note that these limits setting on MySQL/MariaDB command prompt will not be there after mysql server is restarted. It will not be an issue in most cases as usually it happenns on large database imports that do rarely. However, if you want to make these settings permanent, then you need to put this on 'my.cnf' under mysqld group and restart MySQL/MariaDB server.

[mysqld]
max_allowed_packet=100M