[php] how to check and set max_allowed_packet mysql variable

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?

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.

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