php 如何检查和设置 max_allowed_packet mysql 变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5688403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to check and set max_allowed_packet mysql variable
提问by John
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
[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_packetmysql variable by php script?
那么，如何max_allowed_packet通过php脚本检查mysql变量？
and is that possible to set it in source code?
可以在源代码中设置它吗？
回答by glebtv
max_allowed_packetis 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 中看到它的当前值，如下所示：
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_packetworking since at least mysql version 5.5.
[mysqld] 是必需的，max_allowed_packet因为至少从 mysql 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。如果您[mysqld_safe]在 /etc/my.cnf 中设置（这是 mysql 安装附带的默认设置）模式下的值，它没有工作。我没有深入研究这个问题。但是在我将其更改为[mysqld]并重新启动 mysqld 后，它起作用了。
回答by morphatic
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 相同的警告也适用。
回答by Rafee
goto cpanel and login as Main Admin or Super Administrator
转到 cpanel 并以主管理员或超级管理员身份登录
find SSH/Shell Access ( you will find under the security tab of cpanel )
now give the username and password of Super Administrator as
rootorwhatyougavenote: do not give any username, cos, it needs permissionsonce your into console type
type '
mysql' and press enter now you find youself inmysql>/* 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)
找到 SSH/Shell Access（您会在 cpanel 的安全选项卡下找到）
现在给超级管理员的用户名和密码作为
root或whatyougavenote: do not give any username, cos, it needs permissions一旦你进入控制台类型
输入 '
mysql' 然后按 Enter 现在你会发现自己在mysql>/* 并在此处键入 */mysql> set global net_buffer_length=1000000;查询正常，0 行受影响（0.00 秒）
mysql> set global max_allowed_packet=1000000000;查询正常，0 行受影响（0.00 秒）
Now upload and enjoy!!!
现在上传并享受！
