Is there a maximum limit for the value max_allowed_packet?

Question

I am running a drupal site. I got an error in my site user warning: Got a packet 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 ?

Dressmaker

Possible shared cause: #93628 – Burgonet

Are you 100% sure you've set max_allowed_packet to 128 megs? Usually, if a program complains about something not being set - it usually indicates the user screwed up somehow. How did you set max_allowed_packts? – Moreau

It seems the change is not getting reflected. I have added max_allowed_packet = 128M in /etc/my.cnf file. But when I run the command "show variables like 'max_allowed_packet%' ;" output is obtained as max_allowed_packet 1048576 I tried to set it from mysql console mysql> set max_allowed_packet = 134216704; But when I restart it and check it again set variable is back to 1048576. How do I set max_allowed_packet permanently ?? – Dressmaker

Answer 1

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

Lexicography

Answer 2

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.

Demetra

Changes that I make in /etc/my.cnf are not getting reflected. When I run mysqladmin variables -u root -p max_allowed_packet is obtained as 1048576. Changes made from MySQL console are taking effect. But max_allowed_packet goes back to 1048576 when MySQL is restarted – Dressmaker

We are facing same issue, any solutions have you got – Vuong

Answer 3

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

Rhizo

Answer 4

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.

Carbuncle

Answer 5

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';

Stripling

Answer 6

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.

Rhizogenic

Answer 7

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

Stercoricolous