如何检查和设置max_allowed_packet mysql变量[复制]

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

可能的重复:MySQL错误1153 -包大于' max_allowed_packet '字节

Hi I am getting the error :

你好，我收到错误信息:

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

[1153]有一个大于'max_allowed_packet'字节的数据包

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?

那么，如何用php脚本检查max_allowed_packet mysql变量呢?

and is that possible to set it in source code?

有可能在源代码中设置它吗?

3 个解决方案

#1

162

max_allowed_packet is set in mysql config, not on php side

max_allowed_packet是在mysql配置中设置的，而不是在php端

[mysqld]
max_allowed_packet=16M

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

你可以在mysql中看到它的curent值:

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

您可以在这里阅读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.

要使max_allowed_packet至少在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.

最近，使用Drupal和Solr搜索引擎在AWS EC2上设置了一个实例，这需要32M max_allowed_packet。如果您在/etc/my.cnf中设置[mysqld_safe](这是mysql安装的默认设置)模式下的值，那么它就没有工作。我没有深入研究这个问题。但在我将它更改为[mysqld]并重新启动mysqld之后，它成功了。

#2

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

下面的PHP对我来说是有用的(使用mysqli扩展，但是其他扩展的查询应该是一样的):

$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:

如果你有一个很长的查询，你可以确保mysql会接受它:

$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,

注意，我在字符串的长度上增加了1024字节因为根据手册，

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

值应该是1024的倍数;非倍数被四舍五入到最近的倍数。

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.

这样应该可以将max_allowed_packet的大小设置到足以处理查询的大小。我还没有在共享主机上尝试过，所以@Glebushka的警告同样适用。

#3

goto cpanel and login as Main Admin or Super Administrator

后到cpanel，作为主管理员或超级管理员登录

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

查找SSH/Shell访问(在cpanel的security选项卡下)
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”，然后按回车键，现在你找到自己了

mysql> /* and type here like */

mysql> /*，在这里输入*/

mysql> set global net_buffer_length=1000000;

mysql >集全球net_buffer_length = 1000000;

Query OK, 0 rows affected (0.00 sec)

查询OK，影响0行(0.00秒)

mysql> set global max_allowed_packet=1000000000;

mysql >集全球max_allowed_packet = 1000000000;

Query OK, 0 rows affected (0.00 sec)

查询OK，影响0行(0.00秒)

Now upload and enjoy!!!

现在上传和享受! ! !

#1

162