Is there a maximum limit for the value max_allowed_packet?

I am running a drupal site. I got an error in my site user warning: Got a p开发者_JAVA技巧acket bigger than 'max_allowed_packet' bytes query. I have set the value as high as 128M . Even after that same error is reported.

What is the issue here?? Why is it not working ??

Is there a maximum limit for the value max_allowed_packet ?

This is the bleeding edge: set global max_allowed_packet=1073741824;

Although, it is probably not a good idea to set it that high in your case.

As a side note, I experienced this error with mysqldump, and setting this maximum didn't help. This did the trick: $ mysqldump --max_allowed_packet=999M -u root -p table_name > table_name.sql

Often this can be caused by the variables not actually taking effect- you make the configuration change, but in the wrong my.cnf, or you forget to bounce the app, etc.

An easy way to check a running mysql instance is to do something like this in a shell:

mysqladmin variables -u root -p

and enter in your root password. This will dump all of the current variables (including max_allowed_packet), and will let you verify what it's set to. If it's set to 128M and you're still choking on it, then you'll need to increase it- but it's pretty unlikely.

first is you need to set your max_allowed_packet to 128M in your my.cnf file.

to find it, use "locate my.cnf" command in your command line.

the file should look like this:

#
!includedir /etc/my.cnf.d
#max_allowed_packet = 1024M
[mysqld]
port            = 3306
            key_buffer_size = 256M
#           max_allowed_packet = 100M
            table_open_cache = 256
            sort_buffer_size = 1M
            read_buffer_size = 1M
            read_rnd_buffer_size = 4M
            myisam_sort_buffer_size = 64M
            thread_cache_size = 8
            query_cache_size= 16M
            thread_concurrency = 8
            bind-address    = 202.90.158.47
            # skip-networking
log = /var/log/mysql.access.log
log-error = /var/log/mysql.error.log
wait_timeout = 1

[mysqldump]
#max_allowed_packet     = 101M

be sure to uncomment(remove the # sign before the max_allowed_packet = 128M line)

and finally, restart your sql using command "/etc/init.d/mysqld restart"

that should do the trick. :D

Edit your /etc/my.cnf, adding the max_allowed_packet variable.

It should look like this:

[mysqld]
max_allowed_packet=1000000000

Then restart your server.

Try setting max_allowed_packet = 128M as the very last option under the [mysqld] category of my.cnf.

When I had it as the first option, it would not work, but when I had it as the last option, it worked! I think this is because some other variables were over-riding max_allowed_packet.

After changing my.cnf, restart MySQL using sudo service mysql restart, and check the setting using SHOW VARIABLES LIKE 'max_allowed_packet';

You need to set the setting in all sections that apply to the action you are doing, and always in the [MySQLd] section. The setting applies to the buffer of the elements you are using. So under [MySQLd] is for your MySQL server proces deamon on linux / service on windows. And if you want to make a dump with MySQLDump add it as parameter on the command line or make a section [MySQLDump] in your my.ini as well for this tool with the same parameter to make it permanent. If you want to import the dump again with MySQL again use the parameter on the command line or make a section [MySQL] with again the same parameter in your my.ini to make the choice permanent for this tools also.

I kept on talking about my.ini because i am on windows but on linux that is my.cnf of course.

I decided to explain it here because it took me ages to figure this out because it is not explained anywhere. In examples however i noticed some ppl having the setting under multiple sections so i started to google more and found correlation between the sections and the actions they where doing. Now i never have this problem anymore and settings as high as the mentioned 128M here are not needed in most cases. However because it's the maximum the server will use for this buffer if you have the memory just set it high enough to never get into trouble with your actions. The size you actually need is a little bit larger than the largest record in your database.

I experienced this error with mysqldump with LONGBLOB fields, and setting this maximum didn't help. This did the trick:

$ mysqldump --max_allowed_packet=999M -u root -p table_name > table_name.sql