如何更改max_allowed_packet大小

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的文件时，我得到一个大于max_allowed_packet的错误数据包是不允许的。

Here is what i've tried:

以下是我尝试过的:

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

在MySQL查询浏览器中，我运行了一个显示变量，比如'max_allowed_packet'，结果是1048576。

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

然后执行查询集全局max_allowed_packet=33554432，后面是显示变量，如'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字段?

11 个解决方案

#1

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

我的变化。ini或~/.my.cnf文件，在文件中包括[mysqld]部分中的一行:

max_allowed_packet=500M

then restart the MySQL service and you are done.

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

See the documentation for further information.

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

#2

The max_allowed_packet variable can be set globally by running a query.

可以通过运行查询来全局设置max_allowed_packet变量。

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

但是，如果你不改变我的。ini文件(正如dragon112所建议的)，当服务器重新启动时，该值将被重置，即使您将它设置为全局的。

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

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

SET GLOBAL max_allowed_packet=1073741824;

#3

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 root@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安装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)添加一行:max_allowed_packet= 2.56 m(在MYSQLD)部分下，可以调整大小。他犯了一个错误，把它放在文件的底部，所以它不起作用。

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

6)控件+ O(保存)然后输入(确认)再控制+ X(退出文件)

7) service mysqld restart

7)服务mysqld重启

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

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

#4

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:

我想有些人也想知道如何找到我的。你电脑上的ini文件。对于windows用户，我认为最好的办法是:

1. Win+R(shortcut for 'run'), type services.msc, Enter
2. Win+R(运行快捷键)，输入服务。msc,输入
3. You could find an entry like 'MySQL56', right click on it, select properties
4. 你可以找到一个像MySQL56这样的条目，右键点击它，选择properties
5. 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
6. 你可以看到“D:/程序文件/MySQL/MySQL Server 5.6/bin\mysqld”——default -file=“D:\ProgramData\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

#5

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.

结论:在设置全局max_allowed_packet=1073741824之后，服务器将拥有新的max_allowed_packet，直到重新启动，正如前面有人所说的。

#6

If getting this error while performing a backup, max_allowed_packet can be set in the my.cnf particularly for mysqldump.

如果在执行备份时得到这个错误，可以在my.cnf中设置max_allowed_packet，特别是mysqldump。

[mysqldump]
max_allowed_packet=512M

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

我在执行mysqldump时一直得到这个错误，我不理解，因为我在[mysqld]部分的my.cnf中有这个集合。一旦我确定我可以为[mysqldump]设置它，并设置了值，我的备份就完成了，没有问题。

#7

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_size in 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;

#8

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变量值。当然，如果您使用的是在服务器上运行的工作台(或者通过SSH连接)

You just connect to your instance and go on menu:

你只需连接到你的实例，然后进入菜单:

Server -> Options File -> Networking -> max_allowed_packed

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

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

设置所需的值，然后需要重新启动MySql服务。

#9

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

这个错误是由于您的数据包含较大的然后设置值。

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

只要写下max_allowed_pack =500M，或者您可以计算出500*1024k，如果需要，可以使用它而不是500M。

Now just restart the MySQL.

现在重新启动MySQL。

#10

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_packet parameter, change its value, and then hit save.

您应该搜索max_allowed_packet参数，更改它的值，然后单击save。

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的所有实例。

#11

If you want upload big size image or data in database. Just change the data type to 'BIG BLOB'.

如果你想上传大尺寸的图片或数据到数据库中。只需将数据类型更改为“BIG BLOB”即可。