Is there a maximum limit for max_allowed_packet?

Question

I run the drupal site. I received an error on my site warning the user: received a package larger than the request "max_allowed_packet". I set the value above 128M. Even after the same error is reported.

What is the problem? Why is this not working?

Is there a maximum limit for max_allowed_packet?

Answer 1

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

Although it is probably not recommended to set it so high in your case.

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

Answer 2

Often this can be caused by actually invalid variables - you change the configuration, but in the wrong my.cnf, or you forget to refuse the application, etc.

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

mysqladmin variables -u root -p

and enter the root password. This resets all current variables (including max_allowed_packet) and allows you to check what it is set to. If it is set to 128M and you are still suffocating, then you need to increase it, but this is rather unlikely.

Answer 3

Modify your /etc/my.cnf by adding the variable max_allowed_packet .

It should look like this:

 [mysqld] max_allowed_packet=1000000000 

Then reboot the server.

Answer 4

First you need to set max_allowed_packet to 128M in my.cnf file.

to find it, use the command "locate my.cnf" on the 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 string max_allowed_packet = 128M)

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

which should do the trick .: D

Answer 5

Try setting max_allowed_packet = 128M as the last parameter in the [mysqld] my.cnf category.

When I had it as the first option, it didn’t work, but when I had it as the last option, it worked! I think this is due to the fact that some other variables were overloaded by max_allowed_packet .

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

Answer 6

You need to set the parameter in all sections that apply to the action you are performing, and always in the [MySQLd] section. This parameter applies to the buffers of the elements you are using. Thus, in [MySQLd] for your MySQL server, deamon is processed on linux / service on windows. And if you want to dump with MySQLDump, add it as a parameter on the command line or create a [MySQLDump] section in my.ini, as well as for this tool with the same parameter, to make it permanent. If you want to import the dump from MySQL again, use the parameter on the command line again or create a [MySQL] section with the same parameter in my.ini to make the selection permanent for these tools.

I kept talking about my.ini because I am in windows, but on linux, which is my.cnf, of course.

I decided to explain it here because it took me years to figure it out, because it is not explained anywhere. In the examples, however, I noticed that some ppl have customization under several sections, so I started google more and found a correlation between the sections and the actions that they do. Now I no longer have this problem, and in most cases settings are not required, since the mentioned 128M are not needed here. However, since this is the maximum that the server will use for this buffer, if you have memory, just set it high enough to never get into trouble with your actions. The size you really need is slightly larger than the largest record in your database.