MySQL 如何更改 max_allowed_packet 大小

Question

提问by Muleskinner

I am having a problem with BLOB fields in my MySQL database - when uploading files larger than approx 1MB I get an error Packets larger than max_allowed_packet are not allowed.

我的 MySQL 数据库中的 BLOB 字段有问题 - 上传大于约 1MB 的文件时出现错误 Packets larger than max_allowed_packet are not allowed.

Here is what i've tried:

这是我尝试过的：

In MySQL Query Browser I ran a show variables like 'max_allowed_packet'which gave me 1048576.

在 MySQL 查询浏览器中，我运行了一个show variables like 'max_allowed_packet'，它给了我 1048576。

Then I execute the query set global max_allowed_packet=33554432followed by show variables like 'max_allowed_packet'- it gives me 33554432 as expected.

然后我执行查询，set global max_allowed_packet=33554432然后show variables like 'max_allowed_packet'- 它按预期给了我 33554432。

But when I restart the MySQL server it magically goes back to 1048576. What am I doing wrong here?

但是当我重新启动 MySQL 服务器时，它神奇地回到了 1048576。我在这里做错了什么？

Bonus question, is it possible to compress a BLOB field?

额外的问题，是否可以压缩 BLOB 字段？

Answer 1

回答by Manuel

Change in the my.inior ~/.my.cnffile by including the single line under [mysqld]or [client]section in your file:

通过在文件中包含或部分下的单行来更改my.ini或文件：~/.my.cnf[mysqld][client]

max_allowed_packet=500M

then restart the MySQL service and you are done.

然后重新启动 MySQL 服务，你就完成了。

See the documentationfor further information.

有关更多信息，请参阅文档。

Answer 2

回答by TehShrike

The max_allowed_packetvariable can be set globally by running a query.

该max_allowed_packet的变量可以在全球范围通过运行查询进行设置。

However, if you do not change it in the my.inifile (as dragon112 suggested), the value will reset when the server restarts, even if you set it globally.

但是，如果您没有在my.ini文件中更改它（如dragon112 建议的那样），则即使您全局设置，该值也会在服务器重新启动时重置。

To change the max allowed packet for everyone to 1GB until the server restarts:

要将每个人的最大允许数据包更改为 1GB，直到服务器重新启动：

SET GLOBAL max_allowed_packet=1073741824;

Answer 3

回答by naw103

One of my junior developers was having a problem modifying this for me so I thought I would expand this in greater detail for linux users:

我的一位初级开发人员在为我修改它时遇到了问题，所以我想我会为 linux 用户更详细地扩展它：

1) open terminal

1) 打开终端

2) ssh root@YOURIP

2) ssh 根@YOURIP

3) enter root password

3）输入root密码

4) nano /etc/mysql/my.cnf (if command is not recognized do this first or try vi then repeat: yum install nano )

4) nano /etc/mysql/my.cnf (如果命令不被识别，请先执行此操作或尝试 vi 然后重复： yum install nano )

5) add the line: max_allowed_packet=256M (obviously adjust size for whatever you need) under the [MYSQLD] section. He made a mistake of putting it at the bottom of the file first so it did not work.

5) 在 [MYSQLD] 部分下添加以下行：max_allowed_packet=256M（显然可以根据需要调整大小）。他犯了一个错误，先把它放在文件的底部，所以它不起作用。

enter image description here

在此处输入图片说明

6) Control + O (save) then ENTER (confirm) then Control + X (exit file)

6) Control + O (save) 然后 ENTER (confirm) 然后 Control + X (exit file)

7) service mysqld restart

7）服务mysqld重启

8) You can check the change in the variables section on phpmyadmin

8) 您可以在 phpmyadmin 的变量部分检查更改

Answer 4

回答by fstang

I think some would also want to know how to find the my.ini file on your PC. For windows users, I think the best way is as follows:

我想有些人还想知道如何在您的 PC 上找到 my.ini 文件。对于windows用户，我认为最好的方法如下：

Win+R(shortcut for 'run'), type services.msc, Enter
You could find an entry like 'MySQL56', right click on it, select properties
You could see sth like "D:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld" --defaults-file="D:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

Win+R（“运行”的快捷键），输入services.msc，回车
您可以找到像“MySQL56”这样的条目，右键单击它，选择属性
你可以看到类似“D:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld”--defaults-file="D:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

I got this answer from http://bugs.mysql.com/bug.php?id=68516

我从http://bugs.mysql.com/bug.php?id=68516得到了这个答案

Answer 5

回答by zwitterion

Following all instructions, this is what I did and worked:

按照所有说明，这就是我所做的和工作的：

mysql> SELECT CONNECTION_ID();//This is my ID for this session.
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              20 |
+-----------------+
1 row in set (0.00 sec)

mysql> select @max_allowed_packet //Mysql do not found @max_allowed_packet
+---------------------+
| @max_allowed_packet |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

mysql> Select @@global.max_allowed_packet; //That is better... I have max_allowed_packet=32M inside my.ini
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                    33554432 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> **SET GLOBAL max_allowed_packet=1073741824**; //Now I'm changing the value.
Query OK, 0 rows affected (0.00 sec)

mysql> select @max_allowed_packet; //Mysql not found @max_allowed_packet
+---------------------+
| @max_allowed_packet |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

mysql> Select @@global.max_allowed_packet;//The new value. And I still have max_allowed_packet=32M inisde my.ini
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                  1073741824 |
+-----------------------------+
1 row in set (0.00 sec)

So, as we can see, the max_allowed_packet has been changed outside from my.ini.

因此，正如我们所看到的，max_allowed_packet 已从 my.ini 更改为外部。

Lets leave the session and check again:

让我们离开会话并再次检查：

mysql> exit
Bye

C:\Windows\System32>mysql -uroot -pPassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CONNECTION_ID();//This is my ID for this session.
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              21 |
+-----------------+
1 row in set (0.00 sec)

mysql> Select @@global.max_allowed_packet;//The new value still here and And I still have max_allowed_packet=32M inisde my.ini
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                  1073741824 |
+-----------------------------+
1 row in set (0.00 sec)

Now I will stop the server
2016-02-03 10:28:30 - Server is stopped

mysql> SELECT CONNECTION_ID();
ERROR 2013 (HY000): Lost connection to MySQL server during query


Now I will start the server
2016-02-03 10:31:54 - Server is running


C:\Windows\System32>mysql -uroot -pPassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.26-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|               9 |
+-----------------+
1 row in set (0.00 sec)

mysql> Select @@global.max_allowed_packet;//The previous new value has gone. Now I see what I have inside my.ini again.
+-----------------------------+
| @@global.max_allowed_packet |
+-----------------------------+
|                    33554432 |
+-----------------------------+
1 row in set (0.00 sec)

Conclusion, after SET GLOBAL max_allowed_packet=1073741824, the server will have the new max_allowed_packet until it is restarted, as someone stated previously.

结论，在 SET GLOBAL max_allowed_packet=1073741824 之后，服务器将拥有新的 max_allowed_packet 直到它重新启动，正如之前有人所说。

Answer 6

回答by xpros

If getting this error while performing a backup, max_allowed_packetcan be set in the my.cnfparticularly for mysqldump.

如果在执行备份时出现此错误，max_allowed_packet可以在my.cnf特别为mysqldump.

[mysqldump]
max_allowed_packet=512M

I kept getting this error while performing a mysqldumpand I did not understand because I had this set in my.cnfunder the [mysqld]section. Once I figured out I could set it for [mysqldump]and I set the value, my backups completed without issue.

我在执行 a 时不断收到此错误mysqldump，我不明白，因为我my.cnf在该[mysqld]部分下设置了此错误。一旦我发现我可以设置它[mysqldump]并设置值，我的备份就顺利完成了。

Answer 7

回答by Sayka

For those running wamp mysql server

对于那些运行 wamp mysql 服务器的人

Wamp tray Icon -> MySql -> my.ini

Wamp 托盘图标 -> MySql -> my.ini

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 16M        // --> changing this wont solve
sort_buffer_size = 512K

Scroll down to the end until u find

向下滚动到最后，直到找到

[mysqld]
port=3306
explicit_defaults_for_timestamp = TRUE

Add the line of packet_sizein between

在其间添加packet_size行

[mysqld]
port=3306
max_allowed_packet = 16M
explicit_defaults_for_timestamp = TRUE

Check whether it worked with this query

检查它是否适用于此查询

Select @@global.max_allowed_packet;

Answer 8

回答by Suresh

This error come because of your data contain larger then set value.

出现此错误是因为您的数据包含大于设置的值。

Just write down the max_allowed_packed=500Mor you can calculate that 500*1024k and use that instead of 500M if you want.

只需记下max_allowed_packed=500M或您可以计算出 500*1024k 并根据需要使用它而不是 500M。

Now just restart the MySQL.

现在只需重新启动MySQL。

Answer 9

回答by cnom

Many of the answerers spotted the issue and already gave the solution.

很多回答者发现了这个问题并已经给出了解决方案。

I just want to suggest another solution, which is changing the Glogal variable value from within the tool Mysql Workbench. That is ofcourse IF you use Workbench running locally on server (or via SSH connection)

我只想提出另一种解决方案，即从工具 Mysql Workbench 中更改 Glogal 变量值。当然，如果您使用在服务器上本地运行的 Workbench（或通过 SSH 连接）

You just connect to your instance and go on menu:

您只需连接到您的实例并进入菜单：

Server -> Options File -> Networking -> max_allowed_packed

服务器 -> 选项文件 -> 网络 -> max_allowed_packed

You set the desired value and then you need to restart MySql Service.

您设置了所需的值，然后您需要重新启动 MySql Service。

Answer 10

回答by SebaGra

For anyone running MySQL on Amazon RDS service, this change is done via parameter groups. You need to create a new PG or use an existing one (other than the default, which is read-only).

对于在 Amazon RDS 服务上运行 MySQL 的任何人，此更改是通过参数组完成的。您需要创建一个新的 PG 或使用现有的 PG（默认值除外，它是只读的）。

You should search for the max_allowed_packetparameter, change its value, and then hit save.

您应该搜索max_allowed_packet参数，更改其值，然后点击保存。

Back in your MySQL instance, if you created a new PG, you should attach the PG to your instance (you may need a reboot). If you changed a PG that was already attached to your instance, changes will be applied without reboot, to all your instances that have that PG attached.

回到您的 MySQL 实例，如果您创建了一个新的 PG，您应该将 PG 附加到您的实例（您可能需要重新启动）。如果您更改了已附加到您的实例的 PG，则更改将应用到所有附加了该 PG 的实例，而无需重启。

MySQL 如何更改 max_allowed_packet 大小

提问by Muleskinner

回答by Manuel

回答by TehShrike

回答by naw103

回答by fstang

回答by zwitterion

回答by xpros

回答by Sayka

回答by Suresh

回答by cnom

回答by SebaGra

相关推荐

最近更新

标签

MySQL 如何更改 max_allowed_pa​​cket 大小

提问by Muleskinner

回答by Manuel

回答by TehShrike

回答by naw103

回答by fstang

回答by zwitterion

回答by xpros

回答by Sayka

回答by Suresh

回答by cnom

回答by SebaGra

相关推荐

如何更新 MySQL 5.7.10 中的 JSON 数据类型列？

MySQL 加入多个条件

使用分隔符提取 MySQL 子串

如何将 SQL Server 数据库迁移到 MySQL？

相关推荐

最近更新

标签

MySQL 如何更改 max_allowed_packet 大小