how to check and set max_allowed_packet mysql variable [duplicate]

Question

Possible Duplicate:
MySQL Error 1153 - Got a packet bigger than ‘max_allowed_packet’ bytes

Hi I am getting the error :

[1153] Got a packet bigger than 'max_allowed_packet'bytes

but I made no changes in my source code and the hosting states that they did not made any change in server settings.

I don't know what happened. But I am trying to find the reason.

so, how to check max_allowed_packet mysql variable by php script?

and is that possible to set it in source code?

Knowlton

Sometimes type setting: max_allowed_packet = 16M in my.ini is not working. Try to determine the my.ini as follows: set-variable = max_allowed_packet = 32M or set-variable = max_allowed_packet = 1000000000 Then restart the server: /etc/init.d/mysql restart – Schilling

Here, the same error, "Packet for query is to large (5526600 > 1048576).", was caused by a wrong password entry and got resolved when rectified (using the right password corresponding to the MySQL database user). – Yonkers

Answer 1

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.

Latrell

thank you I was going to say that it is in my.ini and not in php.ini – Knowlton

Should be SET GLOBAL max_allowed_packet=16777216; or SET SESSION max_allowed_packet=16777216; – Palindrome

Note that the MySQL doc says, "As of MySQL 5.0.84, the session value of this variable is read only. Before 5.0.84, setting the session value is permitted but has no effect." dev.mysql.com/doc/refman/5.0/en/… – Macilroy

@geofflee, "permitted but has no effect", Wow no warnings even? – Bacteriology

You need configure the client and server – Wicks

max_allowed_packet refers to two entirely disconnected variables, one of which is a value in the client library that is entirely overlooked in this answer. – Harneen

SHOW VARIABLES LIKE 'max_allowed_packet'; returns 1 for me before and after I try to set it... – Fabian

Answer 2

The following PHP worked for me (using mysqli extension but queries should be the same for other extensions):

$db = new mysqli( 'localhost', 'user', 'pass', 'dbname' );
// to get the max_allowed_packet
$maxp = $db->query( 'SELECT @@global.max_allowed_packet' )->fetch_array();
echo $maxp[ 0 ];
// to set the max_allowed_packet to 500MB
$db->query( 'SET @@global.max_allowed_packet = ' . 500 * 1024 * 1024 );

So if you've got a query you expect to be pretty long, you can make sure that mysql will accept it with something like:

$sql = "some really long sql query...";
$db->query( 'SET @@global.max_allowed_packet = ' . strlen( $sql ) + 1024 );
$db->query( $sql );

Notice that I added on an extra 1024 bytes to the length of the string because according to the manual,

The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

That should hopefully set the max_allowed_packet size large enough to handle your query. I haven't tried this on a shared host, so the same caveat as @Glebushka applies.

Sills

Hi, is it possible to edit it on the aws RDS? – Banian

@YusufIbrahim Short answer: I don't know. But a quick scan of this article suggests that it should be possible. – Sills

Answer 3

goto cpanel and login as Main Admin or Super Administrator

1. find SSH/Shell Access ( you will find under the security tab of cpanel )

2. now give the username and password of Super Administrator as root or whatyougave

   note: do not give any username, cos, it needs permissions
   

3. once your into console type

   type ' mysql ' and press enter now you find youself in

   mysql> /* and type here like */

   mysql> set global net_buffer_length=1000000;

   Query OK, 0 rows affected (0.00 sec)

   mysql> set global max_allowed_packet=1000000000;

   Query OK, 0 rows affected (0.00 sec)

Now upload and enjoy!!!

Wretch