how to check and set max_allowed_packet mysql variable

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?

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 — Grzegorz Brzęczyszczykiewicz, Oct 16 '15 at 07:14
[Here](https://stackoverflow.com/a/49465122/6381711), 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). — nyedidikeke, Mar 24 '18 at 13:29

score 266 · Accepted Answer · edited Jun 03 '16 at 05:47

266

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.

edited Jun 03 '16 at 05:47

lhrec_106

630
5
18

answered Apr 16 '11 at 18:08

glebtv

3,739
1
22
10

1

thank you I was going to say that it is in my.ini and not in php.ini – John Apr 16 '11 at 18:13
9

Should be `SET GLOBAL max_allowed_packet=16777216;` or `SET SESSION max_allowed_packet=16777216;` – mikeytown2 Feb 08 '12 at 02:04
10

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." http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet – geofflee Jan 31 '13 at 03:25
@geofflee, "permitted but has no effect", Wow no warnings even? – Pacerier Apr 01 '15 at 05:20
1

You need configure the client and server – Janderson Silva Nov 28 '16 at 13:22
`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. – Michael - sqlbot May 29 '17 at 12:27
`SHOW VARIABLES LIKE 'max_allowed_packet';` returns `1` for me before and after I try to set it... – Eagle_ Jan 02 '23 at 10:10

score 35 · Answer 2 · answered Oct 30 '11 at 01:02

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.

@YusufIbrahim Short answer: I don't know. But a quick scan of [this article](https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/) suggests that it should be possible. — morphatic, Nov 29 '19 at 18:54

score 4 · Answer 3 · edited Feb 22 '13 at 13:07

goto cpanel and login as Main Admin or Super Administrator

find SSH/Shell Access ( you will find under the security tab of cpanel )
now give the username and password of Super Administrator as root or whatyougave
```
note: do not give any username, cos, it needs permissions
```
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!!!

how to check and set max_allowed_packet mysql variable

3 Answers3

Linked

Related