How to change max_allowed_packet size

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:

  1. Win+R(shortcut for 'run'), type services.msc, Enter
  2. You could find an entry like 'MySQL56', right click on it, select properties
  3. 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


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.


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/mysql/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


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;

Tags:

Mysql

Recent Posts