how to check and set max_allowed_packet mysql variable [duplicate]

Solution 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.

Solution 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.

Solution 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!!!