How to Check and Set Max_Allowed_Packet MySQL Variable

how to check and set max_allowed_packet mysql variable

max_allowed_packet
is set in mysql config, not on php side

[mysqld]
max_allowed_packet=16M 

You can see it's curent value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet';

You can try to change it like this, but it's unlikely this will work on shared hosting:

SET GLOBAL max_allowed_packet=16777216;

You can read about it here http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

EDIT

The [mysqld] is necessary to make the max_allowed_packet working since at least mysql version 5.5.

Recently setup an instance on AWS EC2 with Drupal and Solr Search Engine, which required 32M max_allowed_packet. It you set the value under [mysqld_safe] (which is default settings came with the mysql installation) mode in /etc/my.cnf, it did no work. I did not dig into the problem. But after I change it to [mysqld] and restarted the mysqld, it worked.

How to store permanent set max_allowed_packet mysql variable

Change in the my.ini file. Include the single line under [mysqld] in your file

max_allowed_packet=500M
now restart the MySQL service and you are done.

See the documentation for the further information.....https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html

How to change max_allowed_packet size

Change in the my.ini or ~/.my.cnf file by including the single line under [mysqld] or [client] section in your file:

max_allowed_packet=500M

then restart the MySQL service and you are done.

See the documentation for further information.

SET GLOBAL max_allowed_packet doesn't work

Hmmmm.. You have hit this NOT-A-BUG it seems. :)

If you change a global system variable, the value is remembered and used for new
connections until the server restarts. (To make a global system variable setting
permanent, you should set it in an option file.) The change is visible to any client that
accesses that global variable. However, the change affects the corresponding session
variable only for clients that connect after the change. The global variable change does
not affect the session variable for any client that is currently connected (not even that
of the client that issues the SET GLOBAL statement).

Refer this too. Read Shane Bester explanation.

You should change from the my.ini/my.cnf file and restart the server for the max_allowed_packet setting to take effect.

MySQL: determine the value of max_allowed_packet for the client at runtime

You cannot directly determine max_allowed_packet for client at run time. When you execute show variables like "max_allowed_packet", it shows max_allowed_packet for server side only and that too whatever it read at start time. And there seems no other way to find this value.

Further, to solve your problem, by default, From MySQL Docs

On the client side, max_allowed_packet has a default of 1GB.

Moreover, as you have specified that you are using MySQL C API. You can set the value of max_allowed_packet, using mysql_options() API as follows:

First create your option file with content

[client]
max_allowed_packet=10M

Lets say this file is saved as "c:/mysql.cnf" which sets the value for max_allowed_packet for client as 10MB. Now what you need is to include following line of code to read this file before your connect statement.

mysql_options (conn, MYSQL_READ_DEFAULT_FILE, "C:/mysql.cnf");

if you wish to change name of group from client to myClient in file then, make your "c:/mysql.cnf" as

[myClient]
max_allowed_packet=10M

and use following line of codes before your connection statement:

mysql_options (conn, MYSQL_READ_DEFAULT_FILE, "c:/mysql.cnf");
mysql_options (conn, MYSQL_READ_DEFAULT_GROUP, "myClient");

So, finally your code will look something like this:

MYSQL mysql;

mysql_init(&mysql);
mysql_options (conn, MYSQL_READ_DEFAULT_FILE, "c:/mysql.cnf");
mysql_options (conn, MYSQL_READ_DEFAULT_GROUP, "myClient");
if (!mysql_real_connect(&mysql,"host","user","passwd","database",0,NULL,0))
{
    fprintf(stderr, "Failed to connect to database: Error: %s\n",
          mysql_error(&mysql));
}

Above line of code, will set your max_allowed_packet to 10M, now you can read server side max_allowed_packet using query "show variables like "max_allowed_packet"" and you know your client side max_allowed_packet as 10MB.

For further references 1

For MySQL Commandline client, MySQL docs says

If you are using the mysql client program, its default
max_allowed_packet variable is 16MB

Hope it helps, and serves your purpose....

How to set max_allowed_packet in phpmyadmin?

You cannot.

To change it dynamically, as with the SET you tried, you need the SUPER privilege, there is no way around it. And this is a good thing, because 1. the setting is global, which means it affects all connections, and 2. it might jeopardize the server (it makes it easier to DoS a server, for example).

To set it permanently, you need access to the MySQL configuration file and be able to restart the service, as Zak advises.

The real question is, however, why do you need such a high limit. Unless you are trying to import a large dump, having a need for such a limit almost always suggests something was wrongly designed in the first place. If you are importing a dump, try to import smaller bits at a time.

the variable max_allowed_packet not able to be edited on MysQL 5.5

Possible duplicate, please see if this can help to resolve your problem.

Also, Ivan is correct - the my.ini file should be in bin.

If the my.ini file cannot be found, any of the config files can be renamed to my.cnf to change MySQL's max_allowed_packet to the value of the max_allowed_packet variable in that config file.

---

Related Topics