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:

max_allowed_packet=32M

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.

MySQL High Availability: Tools for Building Robust Data Centers (Paperback)

By (author): Charles Bell, Mats Kindahl, Lars Thalmann


List Price: $49.99 USD
New From: $22.95 USD In Stock
Used from: $13.95 USD In Stock

High Performance MySQL: Optimization, Backups, Replication, and More (Paperback)

By (author): Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz, Derek J. Balling


List Price: $49.99 USD
New From: $21.92 USD In Stock
Used from: $1.66 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , , , , , , , ,
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.

*