This article provides a solution for database connection errors in the
var/log/exception.log that may occur when importing a large number of products or performing another task that forces the server to handle bigger packets than set in
max_allowed_packet that is larger than the default, 16MB.
Affected products and versions
When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error (which can be seen in the
exception.log) and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.
Steps to reproduce
A variety of tasks can produce this issue. This can include trying to import a large number of products into Magento or transactional queries sending back too much data. The result is database connection errors in
var/log/exception.log and other problems, like products not being successfully imported.
The default value of 16MB for the MySQL
max_allowed_packets setting (applies to both Magento Commerce and Magento Commerce Cloud) is not large enough for your needs.
- Identify queries where the individual rows exceed the current
max_allowed_packetlimit. Such queries need to be rewritten to reduce the amount of data being returned. This can be done by having a smaller number of columns in the
SELECTstatement or choosing a smaller data type for various columns as part of the table design. If you have a New Relic account, use the New Relic APM Errors page and the New Relic APM Databases page, and New Relic Logs to search for the relevant queries.
- For quick remediation, you can temporarily request the
max_allowed_packetsize to be increased when you submit a ticket, but this is at the discretion of the Customer Engineering team, as too large of a value can cause replication failures by causing network congestion.
- As a best practice, you should run the following command in your CLI for some of your large database tables:
Evaluate the queries running on these tables to determine if you are exceeding the recommended
show table status like [table name to match]
max_allowed_packetsize of 16MB. Follow the same process in step one to reduce the data being returned by such queries.