Easily solve the problem of "too large information package" in Mysql

The communication information package is a single SQL statement sent to the MySQL server or a single row sent to the client.

The maximum possible information package that can be sent between the MySQL 5.1 server and the client is 1 GB.

When the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is sent and the connection is closed. For some clients, if the communication information package is too large, the "lost connection to MySQL Server" error can be returned during query.

Both the client and server have their own max_allowed_packet variable. Therefore, if you want to process a large information package, you must add the variable on the client and server.

If you are using a mysql client, the default value of the max_allowed_packet variable is 16 MB. To set a large value, you can start mysql in the following ways:

Mysql> mysql -- max_allowed_packet = 32 M


It sets the size of the information package to 32 MB.

The default max_allowed_packet value of the server is 1 MB. If the server needs to process a large query, you can add this value (for example, if you want to process a large BLOB column ). For example, to set this parameter to 16 MB, start the server in the following ways:

Mysql> mysqld -- max_allowed_packet = 16 M


You can also use the option file to set max_allowed_packet. To set this variable of the server to 16 MB, add the following content to the option file:

[Mysqld]
Max_allowed_packet = 16 M


It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or mysqld must return a large result, mysqld will allocate more memory. The small default value of this variable is a preventive measure to capture error packets between the client and the server, and ensure that memory overflow is not caused by accidental use of large information packets.

If you use a large BLOB value and do not grant mysqld the permission to access enough memory for query processing, you may also encounter a strange problem related to the big information package. If this is suspected, add ulimit-d 256000 in the mysqld_safe script and restart mysqld.