Linux SET GLOBAL max_allowed_packet 不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9922894/
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
SET GLOBAL max_allowed_packet doesn't work
提问by Rika
I found out how to change the default value of max_allowed_packetin MySQL using SET GLOBAL. However, each time I used this command, the default value stayed untouched!
I used these commands:
我发现了如何max_allowed_packet使用SET GLOBAL. 然而，每次我使用这个命令时，默认值都保持不变！我使用了这些命令：
mysql --user=root --password=mypass
mysql> SET GLOBAL max_allowed_packet=32*1024*1024;
Query OK, 0 rows affected (0.00 secs)
mysql> SHOW VARIABLES max_allowed_packet;
And then the result is max_allowed_packet = 1048576. What am I missing?
然后结果是 max_allowed_packet = 1048576。我错过了什么？
采纳答案by Ashwin A
Hmmmm.. You have hit this NOT-A-BUGit seems. :)
嗯.. 您似乎已经遇到了这个NOT-A-BUG。:)
If you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).
如果更改全局系统变量，则该值将被记住并用于新连接，直到服务器重新启动。（要使全局系统变量设置永久化，您应该在选项文件中设置它。）任何访问该全局变量的客户端都可以看到更改。但是，更改仅影响更改后连接的客户端的相应会话变量。全局变量更改不会影响当前连接的任何客户端的会话变量（甚至不影响发出 SET GLOBAL 语句的客户端的会话变量）。
Refer thistoo. Read Shane Bester explanation.
也参考这个。阅读 Shane Bester 的解释。
You should change from the my.ini/my.cnf file and restart the server for the max_allowed_packet setting to take effect.
您应该更改 my.ini/my.cnf 文件并重新启动服务器以使 max_allowed_packet 设置生效。
回答by BeardedGeek
Just a quick way to see the variable for anybody who comes across this. To get the value back you need to run
对于遇到此问题的任何人，这只是一种快速查看变量的方法。要取回价值，您需要运行
SHOW VARIABLES LIKE 'max_allowed_packet'
回答by John Cogan
After running
运行后
set global max_allowed_packet=1000000000;
you have to restart mysql before
你必须先重启mysql
SHOW VARIABLES LIKE 'max_allowed_packet'
will show the new value.
将显示新值。
I have this issue when restarting mysql through the MAC OSX system preferences and the value hadn't changed. So by logging into mysql via console
通过 MAC OSX 系统首选项重新启动 mysql 并且值没有改变时，我遇到了这个问题。所以通过控制台登录mysql
mysql -u root -p
changing it and then restarting mySql seemed to work. Might have been a OS X quirk though.
更改它然后重新启动 mySql 似乎有效。不过可能是 OS X 的怪癖。
回答by Cuarcuiu
For those with a MariaDb configuration the problem could be that the max_allowed_packetvariable is overwritten by a configuration file called later.
对于那些使用 MariaDb 配置的人，问题可能是max_allowed_packet变量被稍后调用的配置文件覆盖。
In my case I tried to import a database and the server answered me: ERROR 2006 (HY000) at line 736: MySQL server has gone away
在我的例子中，我尝试导入一个数据库，服务器回答我： ERROR 2006 (HY000) at line 736: MySQL server has away
I discovered that the file:
我发现该文件：
/etc/mysql/mariadb.conf.d/50-server.cnf
is called later
稍后调用
/etc/mysql/conf.d/mysql.cnf
I tried continuously changing in the "mysql.cnf" file but the value was overwritten in "50-server.cnf".
我尝试在“mysql.cnf”文件中不断更改，但该值在“50-server.cnf”中被覆盖。
So the solution is to enter the file
所以解决办法是输入文件
/etc/mysql/mariadb.conf.d/50-server.cnf
and instead of "max_allowed_packet = 16M"put the desired value as an example "max_allowed_packet = 64M"
而不是“max_allowed_packet = 16M”，而是 将所需的值作为示例 “max_allowed_packet = 64M”
回答by hipsandy
I came across this problem as well and in my case I have multiple versions of MySql installed.
Adding this note for anyone who might have setup MySql using homebrew on macand are having trouble setting max_allowed_packetvalue in your my.cnffile.
我也遇到了这个问题，就我而言，我安装了多个版本的 MySql。为可能在mac上使用自制软件设置 MySql并且max_allowed_packet在您的my.cnf文件中设置值时遇到问题的任何人添加此注释。
The most key information that helped was that the my.cnffile can be present in different locations (excerpt from https://github.com/rajivkanaujia/alphaworks/wiki/Install-MySQL-using-Homebrew) -
最重要的帮助信息是该my.cnf文件可以存在于不同的位置（摘自https://github.com/rajivkanaujia/alphaworks/wiki/Install-MySQL-using-Homebrew） -
/usr/local/etc/my.cnf --> Global Access
/usr/local/etc/my.cnf --> Global Access
/usr/local/Cellar/mysql/5.7.18/my.cnf --> Sever Level Access
~/.my.cnf --> User Level Access
Since I installed MySql 5.6 via Home brew I found it at -
由于我通过 Home brew 安装了 MySql 5.6，我在 -
/usr/local/Cellar/mysql\@5.6/5.6.43/my.cnf
Steps followed -
步骤如下——
Update the
/usr/local/Cellar/mysql\@5.6/5.6.43/my.cnffile under[mysqld]group with the necessarymax_allowed_packet value-[mysqld] max_allowed_packet=5GRestart mysql using brew services -
brew services restart [email protected]Connect/Reconnect to the mysql shell and verify that the configuration has taken effect using -
show variables like 'max_allowed_packet';
使用必要的更新组
/usr/local/Cellar/mysql\@5.6/5.6.43/my.cnf下的文件-[mysqld]max_allowed_packet value[mysqld] max_allowed_packet=5G使用 brew 服务重启 mysql -
brew services restart [email protected]连接/重新连接到 mysql shell 并使用 - 验证配置是否已生效
show variables like 'max_allowed_packet';
