Recently I needed to modify a MySQL database from a latin1 character set to a utf8 character set which I may write an article on later however when importing the MySQL dumped data back into a new database I received an error complaining about the packet size. This can easily be worked around using the information below.
Importing Data Into MySQL Error:
[root@cent1 ~]# mysql -u root -p --default-character-set=utf8 database < db-data_utf8.sql Enter password: ERROR 1153 (08S01) at line 1355: Got a packet bigger than 'max_allowed_packet' bytes
As you can see above MySQL is complaining about the max_allowed_packet setting which by default is 1MB. The MySQL server can handle packets up to 1GB in size and is set low initially to conserve memory. This setting can be set higher without to much concern since MySQL will only allocate memory when it is necessary. To change the max_allowed_packet size to something larger you need to modify the my.cnf file typically located in the /etc/ directory of Linux servers.
Below we are going to modify the max_allowed_packet size to 32MB using the below my.cnf configuration line.
my.cnf max_allowed_packet Configuration:
Example my.cnf With max_allowed_packet Configured:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_allowed_packet=32M # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Restart MySQL after the changes are made using “/etc/init.d/mysql restart” or whatever method you typically use to restart MySQL. You should now be able to import the data into a MySQL database without issue.