30

I found out how to change the default value of max_allowed_packet in MySQL using SET GLOBAL. However, each time I used this command, the default value stayed untouched! I used these commands:

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
Hossein
  • 24,202
  • 35
  • 119
  • 224
  • 1
    Breeze, at this point in time, 7 years later, SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet'; should show you a 32MB number - depending on version. Many details have changed in 7 years. Also, SET GLOBAL xxx=xx applies to NEW CONNECTIONS to the instance. If your configuration is not successfully changed, and you stop/start your instance, you will have the same value you had on past days. – Wilson Hauck Sep 22 '19 at 18:57

5 Answers5

46

Hmmmm.. You have hit this NOT-A-BUG it seems. :)

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

Refer this too. Read Shane Bester explanation.

You should change from the my.ini/my.cnf file and restart the server for the max_allowed_packet setting to take effect.

Ashwin A
  • 3,779
  • 23
  • 28
  • except the "bug" is closed as "not a bug". – J-16 SDiZ Mar 29 '12 at 10:04
  • 1
    thank you sir for your propmt reply . but i tried restarting mysql after that , still im getting the 1 Mb limit ! – Hossein Mar 29 '12 at 10:11
  • @J-16 SDiZ, Yup they have marked the issue as Not-a-bug(Issue, Which is still confusing for us). – Ashwin A Mar 29 '12 at 10:11
  • @Hossein, Did you change it from my.ini/my.cnf and restarted the server? – Ashwin A Mar 29 '12 at 10:13
  • @Ashwin A : no sir , after issuing the command "SET GLOBAL ..." i exited the mysql and restarted it . i thought that would enough . i try to edit the my.ini/my.cnf and report back . by the way thank you again – Hossein Mar 29 '12 at 10:18
  • @AshwinA : Thank you sir , your solution worked flawlessly :) by the way do i need to enter anything else to avoid any conflict ? such as changing the value of net_buffer_length ? – Hossein Mar 29 '12 at 10:28
  • A note to RedHat users to add here: reloading mysqld after changing the variable in the config file doesn't do anything, a full mysqld restart is required – machineaddict Oct 19 '16 at 12:35
8

After running 

set global max_allowed_packet=1000000000;

you have to restart mysql before 

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

mysql -u root -p

changing it and then restarting mySql seemed to work. Might have been a OS X quirk though.

John Cogan
  • 1,034
  • 4
  • 16
  • 39
4

For those with a MariaDb configuration the problem could be that the max_allowed_packet variable is overwritten by a configuration file called later.

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

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

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"

Cuarcuiu
  • 467
  • 1
  • 8
  • 20
  • did the trick for me, then I ran `service mariadb restart`, and `service apache2 restart`. – gray Apr 12 '20 at 20:46
2

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 mac and are having trouble setting max_allowed_packet value in your my.cnf file.

The most key information that helped was that the my.cnf file can be present in different locations (excerpt from 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 - 

/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.cnf file under [mysqld] group with the necessary max_allowed_packet value -

    [mysqld] max_allowed_packet=5G

  • Restart mysql using brew services -

    brew services restart mysql@5.6

  • Connect/Reconnect to the mysql shell and verify that the configuration has taken effect using -

    show variables like 'max_allowed_packet';

Hossein
  • 24,202
  • 35
  • 119
  • 224
hipsandy
  • 982
  • 8
  • 7
  • 1
    Requesting 5G for max_allowed_packet is likely to be limited to the maximum found in this URL - https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html – Wilson Hauck Sep 22 '19 at 21:25
0

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'
eh1160
  • 674
  • 1
  • 6
  • 14
BeardedGeek
  • 154
  • 1
  • 2
  • 10