How to change max_allowed_packet size

Question

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?

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 09 '11 at 09:12
Ok thanks, had hoped a compress feature build into mysql existed — Muleskinner, Nov 09 '11 at 09:26
possible duplicate of [MySQL Error 1153 - Got a packet bigger than 'max\_allowed\_packet' bytes](http://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes) — cnst, Jan 17 '14 at 04:04
https://dba.stackexchange.com/questions/45087/max-allowed-packet-in-mysql — Channa, Jun 26 '21 at 08:10

score 501 · Accepted Answer · edited Jan 16 '19 at 10:02

501

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

max_allowed_packet=500M

then restart the MySQL service and you are done.

See the documentation for further information.

edited Jan 16 '19 at 10:02

Destroyica

4,147
3
33
50

answered Nov 09 '11 at 09:04

Manuel

10,153
5
41
60

1

Thanks seems to be working even though I had hoped this would be possible without having to modify ini files manually. – Muleskinner Nov 09 '11 at 09: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 09 '11 at 09:30
83

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

@Konerak, Who was the taunter? – Pacerier Apr 01 '15 at 05:21
@Pacerier, probably someone whose comment was subsequently deleted. – octern Nov 07 '15 at 03:57
@octern, People should be manually capturing it with web.archive.org or archive.is so that future readers can see it. Relying on the auto-capture will often be too late: http://web.archive.org/web/20130228004545/http://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size – Pacerier Nov 08 '15 at 02:51
1

@Pacerier the taunter was the person who posted the above answer -@Manual. His taunts were removed by -@Konerak. You can check the history of this answer by editing it (if you have enough credits I guess). – Abhishek Madhani Jan 07 '16 at 13:13
19

It seems rather pointless to give someone an answer and then tell them they should go check Google for an answer. Especially since, SO is invariably occupies several of the top results for any decent programming related question. Talk about programming an infinite loop!!! Google > SO > Google > SO > Google > SO and so on. – Phill Healey Feb 19 '16 at 11:06
8

Make sure it is under `[mysqld]` and not `[mysql]` (very similar). Cost me some minutes of headscratching. – Halvor Holsten Strand Dec 15 '16 at 08:26
`/etc/mysql/mysql.conf.d/mysqld.cnf` on Debian 8 / MySQL 5.7 – Martin Schneider Mar 01 '17 at 21:56
On Xubuntu 16.04 with XAMPP (lampp) file is here: `/opt/lampp/etc/my.cnf` – Harkály Gergő Feb 01 '18 at 15:01

score 296 · Answer 2 · edited Mar 21 '21 at 17:10

296

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 max allowed packet for everyone to 1GB until the server restarts:

SET GLOBAL max_allowed_packet=1073741824;

edited Mar 21 '21 at 17:10

KARASZI István

30,900
8
101
128

answered Nov 09 '11 at 09:41

TehShrike

9,855
2
33
28

7

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

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

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

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 Wonlaw Jul 29 '15 at 20:51
2

Matt Crinklaw-Vogt is right. It needs exit and connect again – makriria Jan 28 '16 at 09:16
life saver for those who can't dare a restart – kommradHomer Jun 22 '16 at 11:45
after run set cmd, then do SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; – kyrre Mar 20 '18 at 02:52
Here's an idea that might help: `mysql -h${DB_HOST} -u${DB_USERNAME} -p${DB_PASSWORD} -D${DB_DATABASE} -e"SET GLOBAL max_allowed_packet=16777216; SHOW VARIABLES LIKE 'max_allowed_packet';"` – jpswade Nov 12 '20 at 15:18

score 113 · Answer 3 · edited Jan 13 '22 at 06:02

113

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:

open terminal
ssh root@YOURIP
enter root password
nano /etc/mysql/my.cnf (if command is not recognized do this first or try vi then repeat: yum install nano )
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.
Control + O (save) then Enter (confirm) then Control + X (exit file)
service mysqld restart
You can check the change in the variables section on phpmyadmin

edited Jan 13 '22 at 06:02

Giacomo1968

25,759
11
71
103

answered Jan 20 '14 at 14:32

naw103

1,843
1
15
14

this was actually done on CentosOS6 , I of course agree about not using root ssh access though – naw103 Jun 16 '15 at 00:38
@tamasd A few GNU/Linux distros that I've used (like Debian 8 and CentOS 6) produce `sudo: command not found` or `this incident will be reported` because either `sudo` has not been installed and configured. Would it be better to litter this answer with instructions on setting up `sudo` for the first time? – Damian Yerrick Sep 15 '16 at 16:38
not root ssh is the problem, but password login is. A user account with sudo permission is almost as unsecure as root is. Just use SSH key based authentication. – Martin Schneider Mar 01 '17 at 21:43
the matter of how to log into the machine is OT, he rightly reported in his recipe that you have to log in, in a way or another. – devsmt Feb 01 '18 at 08:15

score 51 · Answer 4 · answered Nov 22 '13 at 03:30

51

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

answered Nov 22 '13 at 03:30

fstang

5,607
4
25
26

2

This is important on Windows. Had MySQL as a service and edited my.ini in Workbench and this answer made me realize that MySQL as a service uses an other my.ini then the one i edited in Workbench. – Robert Niestroj Jan 25 '19 at 09:04
1

still works on mysql 8. nice one. – dewd Apr 21 '22 at 22:35

score 33 · Answer 5 · answered Feb 03 '16 at 18:15

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.

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.

if \*\*SET GLOBAL max_allowed_packet=1073741824\*\*; generates error, you can try after removing ** symbol. i.e. SET GLOBAL max_allowed_packet=1073741824; — Rahul Vansh, Apr 01 '23 at 07:45

score 13 · Answer 6 · edited Feb 02 '16 at 11:01

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.

score 12 · Answer 7 · answered Sep 29 '15 at 09:38

For those running wamp mysql server

Wamp tray Icon -> 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

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

Check whether it worked with this query

Select @@global.max_allowed_packet;

score 6 · Answer 8 · edited Nov 10 '11 at 04:03

6

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.

Now just restart the MySQL.

edited Nov 10 '11 at 04:03

Brock Adams

90,639
22
233
295

answered Nov 09 '11 at 09:23

Suresh

1,494
3
13
14

2

to ```/etc/my.cnf``` for MySQL instance, or ```/etc/my.cnf.d/server.cnf``` for MariaDB – Eugene Lebedev May 20 '16 at 20:24

score 5 · Answer 9 · answered Mar 01 '18 at 13:18

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).

You should search for the max_allowed_packet parameter, change its value, and then hit 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.

score 4 · Answer 10 · answered Dec 09 '16 at 14:16

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)

You just connect to your instance and go on menu:

Server -> Options File -> Networking -> max_allowed_packed

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

score 3 · Answer 11 · answered Jan 23 '21 at 17:16

3

Set the max allowed packet size using MySql Workbench and restart the server

answered Jan 23 '21 at 17:16

An Illusion

769
1
10
24

score 1 · Answer 12 · answered Feb 02 '21 at 08:40

1

in MYSQL 5.7, max_allowed_packet is at most 1G. if you want to set it to 4G, it would failed without error and warning.

answered Feb 02 '21 at 08:40

Siwei

19,858
7
75
95

score 0 · Answer 13 · answered Aug 28 '15 at 06:48

0

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

answered Aug 28 '15 at 06:48

Vishal J

344
3
10

score -1 · Answer 14 · answered Mar 25 '20 at 17:30

-1

set global max_allowed_packet=10000000000;

answered Mar 25 '20 at 17:30

Vishal Sheth

163
1
7

Maximum Value of max_allowed_packet is 1073741824 – Muhammad Azeem Nov 08 '21 at 13:18

How to change max_allowed_packet size

14 Answers14

Linked

Related