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:
[mysqld]
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.
