如何更改 max_allowed_packet 大小（修改max_allowed_packet）

25-03-22 7

对于想了解如何更改max_allowed_packet大小的读者，本文将提供新的信息，我们将详细介绍修改max_allowed_packet，并且为您提供关于Gotapacketbiggerthan'

对于想了解如何更改 max_allowed_packet 大小的读者，本文将提供新的信息，我们将详细介绍修改max_allowed_packet，并且为您提供关于Got a packet bigger than ''max_allowed_packet'' byte、Got a packet bigger than ''max_allowed_packet'' bytes、How to change max_allowed_packet size、max_allowed_packet的有价值信息。

本文目录一览：

如何更改 max_allowed_packet 大小（修改max_allowed_packet）
Got a packet bigger than ''max_allowed_packet'' byte
Got a packet bigger than ''max_allowed_packet'' bytes
How to change max_allowed_packet size
max_allowed_packet

如何更改 max_allowed_packet 大小（修改max_allowed_packet）

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

这是我尝试过的：

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

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

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

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

Got a packet bigger than ''max_allowed_packet'' byte

Got a packet bigger than ''max_allowed_packet'' bytes With statement错误：

今天导入数据到数据库的时候出现了这个错误。

因为图片储存在数据库里面，

受packet接受包最大值所限，

听有说改my.ini的

太麻烦了，

然后自己继续找资料，

发现：打开mysql（我用的mysql）数据control,

输入下面参数即可：

set global max_allowed_packet=1000000000; //一般只要这个就可了。
set global net_buffer_length=1000000; //加入上面还不能解决加入此。

Got a packet bigger than ''max_allowed_packet'' bytes

错误代码： 1153
Got a packet bigger than ''max_allowed_packet'' bytes

执行耗时 : 0 sec
传送时间 : 0 sec
总耗时 : 0.018 sec

How to change max_allowed_packet size

up vote 116 down vote favorite

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.

Here is what i''ve tried:

In MySQL Query Browser I ran a show variables like ''max_allowed_packet'' which gave me 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.

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

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

mysql

shareimprove this question

asked Nov 9 ''11 at 9:01

Muleskinner

3,891123868

A BLOB field is a Binary Large OBject. It''s just bits. So yes, you can compress the contents, and it gives other (and hopefully, less) bits you store in the BLOB-field instead. It just changes which data you put in it. You''ll have to decompress the BLOB-contents when you need it again, too. – Konerak Nov 9 ''11 at 9:12

Ok thanks, had hoped a compress feature build into mysql existed – Muleskinner Nov 9 ''11 at 9:26

possible duplicate of MySQL Error 1153 - Got a packet bigger than ''max_allowed_packet'' bytes – cnst Jan 17 ''14 at 4:04

add a comment

8 Answers

active oldest votes

up vote 140 down vote accepted

Change in the my.ini file. Include the single line under [mysqld] in your file

max_allowed_packet=500M

now restart the MySQL service and you are done.

See the documentation for further information.

shareimprove this answer

edited Jan 28 ''15 at 14:23

answered Nov 9 ''11 at 9:04

Manuel

5,31242651

Most stuff is easy to find through Google, once you know how and where to find it. Taunting is not needed, but if you could provide a link to the correct location in the MySQL documentation, the user gets to know the official sites and resources and can find it for himself easier the next time. Teach a man to fish... – Konerak Nov 9 ''11 at 9:13

Thanks seems to be working even though I had hoped this would be possible without having to modify ini files manually. – Muleskinner Nov 9 ''11 at 9:24

Changing the my.ini file is basically the same as chaning the settings in other programs. Here it''s just a file from which the program reads the info. – Manuel Nov 9 ''11 at 9:30

FYI readers, this is also the solution to "MySQL has gone away" error – djb Jul 10 ''13 at 14:59

@Konerak, Who was the taunter? – Pacerier Apr 1 ''15 at 5:21

show 3 more comments

up vote 86 down vote

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

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.

To change the setting for everyone until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

shareimprove this answer

edited May 5 ''13 at 21:40

answered Nov 9 ''11 at 9:41

TehShrike

5,4312025

Not helps :(. It displays "Query OK, 0 rows affected (0.00 sec)" – artnikpro Jul 3 ''14 at 16:06

@artnikpro It does work, "Query OK, 0 rows affected (0.00 sec)" might feel misleading but it is right. – AnnTea Aug 4 ''14 at 12:09

doesn''t work for me. SHOW VARIABLES WHERE variable_name = ''max_allowed_packet'' still shows old value – Poma May 22 ''15 at 22:47

It shows the old value because max_allowed_packet doesn''t change for existing connections. If you disconnect and reconnect you''ll see the updated value. – Matt Crinklaw-Vogt Jul 29 ''15 at 20:51

!!! This solution does not work.!!! – Pat R Ellery Aug 17 ''15 at 22:00

show 1 more comment

up vote 37 down vote

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:

1) open terminal

2) ssh root@YOURIP

3) enter root password

4) nano /etc/my.cnf (if command is not recognized do this first or try vi then repeat: 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.

enter image description here

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

7) service mysqld restart

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

shareimprove this answer

answered Jan 20 ''14 at 14:32

naw103

866610

-1 for recommending to ssh as root. Root ssh access must be disabled on a safe server. Use sudo instead. – tamasd Jun 4 ''14 at 8:55

this was actually done on CentosOS6 , I of course agree about not using root ssh access though – naw103 Jun 16 ''15 at 0:38

add a comment

up vote 14 down vote

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:

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

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

shareimprove this answer

answered Nov 22 ''13 at 3:30

fstang

586510

works pefectly thanks – Robocide Jul 1 ''15 at 7:43

Excellent!!! Been searching all over for the file... – Ayodeji Jan 22 at 16:34

add a comment

up vote 7 down vote

If getting this error while performing a backup, max_allowed_packet can be set in the my.cnf particularly for 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.

shareimprove this answer

edited May 19 ''14 at 17:52

answered Feb 23 ''14 at 21:14

xpros

74979

add a comment

up vote 4 down vote

For those running wamp mysql server

Wamp tray Icon -> MySql -> my.ini

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

Scroll down to the end until u find

[mysqld]port=3306explicit_defaults_for_timestamp = TRUE

Add the line of packet_size in between

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

Check whether it worked with this query

Select @@global.max_allowed_packet;

shareimprove this answer

answered Sep 29 ''15 at 9:38

Sayka

1,9781919

plus 1 for mentioning how to check that it is set – Abimbola Esuruoso Dec 27 ''15 at 18:18

add a comment

up vote 3 down vote

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.

Now just restart the MySQL.

shareimprove this answer

edited Nov 10 ''11 at 4:03

Brock Adams

47.3k985138

answered Nov 9 ''11 at 9:23

Suresh

7372512

Where should it be added ???????? – Pratik C Joshi Oct 17 ''15 at 4:29

add a comment

up vote 0 down vote

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

max_allowed_packet

今天安装好mycat后，数据库及navicat都连接测试成功，但是运行程序一直报错：
Packet for query is too large (44 > -1). You can change this value on the server by setting the max_allowed_packet'' variable.
网上查了一大圈都没能解决，max_allowed_packet为何我提示为负数，我mysql设置的值还挺大，求大神解答

关于如何更改 max_allowed_packet 大小和修改max_allowed_packet的问题就给大家分享到这里，感谢你花时间阅读本站内容，更多关于Got a packet bigger than ''max_allowed_packet'' byte、Got a packet bigger than ''max_allowed_packet'' bytes、How to change max_allowed_packet size、max_allowed_packet等相关知识的信息别忘了在本站进行查找喔。

本文标签：