Thursday, January 28, 2010

MySQL: How to fix ERROR 1153 (08S01)

How to fix ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

Mysql Server Version: MySQL-server-community-5.1.41
OS: Open Suse 11.0 (server)

I tried to dump my database to mysql and got the error as stated in entry's title above.
The default value for max_allowed_packet is 1048576 (1M). The available range for it is between 1024 to 1 073 741 824.

Firstly, I think can edit the value by using command line when I signed in to mysql.
myserver:/etc/init.d # mysql -u root -p

mysql> SET @max_allowed_packet = '16M';
(Let say I want to increase the value to 16M).

But I got nothing, 0 row affected.
Ok, just exit from mysql.

Then open file mysql under directory /etc/init.d
vim /etc/init.d/mysql
I just pasted the excerpt from mysql file.
# If you want to affect other MySQL variables, you should make your changes

# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.

Then just create a file and name it as my.cnf inside /etc.
To make my life easy, I just copied[1] mysql file and then renamed[2] it as my.cnf
Then edit[3] my.cnf file.

[1]myserver:/etc/init.d # cp mysql /etc
[2] myserver:/etc/init.d # mv mysql my.cnf
[3] myserver:/etc/init.d #gedit my.cnf

Just add these two lines:
max_allowed_packet = 16M

Then save it.
Then restart[4] msql service
[4] myserver:/etc/init.d # ./mysql restart

Again, sign in to mysql, and type command
mysql> show global variables like 'm%'; (Refer to Figure 1)

Figure 1: Show global variable in mysqld.

The new value will appear in the first row. Other way to check it is by typing this command
myserver:/etc/init.d # mysql service status (Refer to Figure 2)

Figure 2: An alternative method to look at variables set in mysqld.