max_allowed_packet in mySQL

You have two values of max_allowed_packet in MySQL :

  • one on the client side : [mysql] section, [mysqldump], [client] and more.
  • one on the server side : [mysqld] section.

The value of max_allowed_packet you see with the command show variables like 'max_allowed_packet'; is the one on the server side.

In order to increase this value, you must increase both sides : in your server configuration file ([mysqld] section in your my.ini file) and in your client configuration file (whether your [client] or [mysql] section in your my.ini file).

This setting can be changed on the server side without restarting the server if you have the SUPER privilege with this command : mysql> SET GLOBAL max_allowed_packet = numeric;.

Don't forget to change the 'numeric' value by a numeric value. Don't forget to change your configuration file too otherwise this value will be reset at reboot.


What worked for me was

  1. Start MySQL Workbench 5.6 (tested up to version 6.3)
  2. In the navigator go to Instance options file. If the file cannot be found, then create it yourself at /etc/my.cnf:
# /etc/my.cnf
[mysqld]
  1. Click on the Networking tab
  2. Enter 16M (or any size you wish to have) in the max_allowed_packet edit box, click apply and restart mysql
  3. SHOW VARIABLES LIKE 'max_allowed_packet'; will now give 16777216.

Alternative

You can set the max_allowed_packet directly on the my.cnf file as follow:

# /etc/my.cnf
[mysqld]
max_allowed_packet=16M

MAX ALLOWED PACKET DOCUMENTATION

This value indicates maximum size of one packet, The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets. You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use.

This server variable can be set globally by running a query.

However, if you do not change it in the my.ini/my.cnf file, 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;

Tags:

Mysql

Recent Posts