MySQL max_allowed_packet

2022年2月21日
技術情報

max_allowed_packet is the maximum size of a MySQL network protocol packet that the server can create or read.

The maximum packet that can be sent and received from the MySQL8.0 server or client is 1 GB.

When the MySQL client or mysqld server receives a packet larger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. For some clients, if the communication packet is too large, the connection to the MySQL server may be lost during a query error.

Both the client and the server have their own max_allowed_packet variable, so if you want to handle large packets, you need to increase this variable.

Updating max_allowed_packet

You can change in the my.ini or ~/.my.cnf file depending on your OS and installation. For example

max_allowed_packet=500M

You can also update the value by running a query.

Firstly, you can check what’s the default value you have using this.

SHOW VARIABLES WHERE variable_name = 'max_allowed_packet'

You can update the value using this.

SET GLOBAL max_allowed_packet=1073741824;

These are the ways to update your max_allowed_packet config. But there is always a thing which is your code. You have to make sure your code is not doing anything wrong to exceed this value.

For example, if you are using session driver to mysql and you are adding large data to session once, there might have an exception , fatal error regarding with max_allowed_packet. Same concepts applied for the query cases.

To solve above kind of session issues, you can change your session driver or you can manage something else (Eg. tmp storage) to store your data.

I hope you get something from this article.

Yuuma