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.
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 Adobe Commerce 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 is not large enough for your needs.
Identify queries where the individual rows exceed the current
max_allowed_packet limit. 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
SELECT statement 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_packet size 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:
show table status like [table name to match]
Evaluate the queries running on these tables to determine if you are exceeding the recommended
max_allowed_packet size of 16MB. Follow the same process in step one to reduce the data being returned by such queries.