Database errors related to max_allowed_packet on Adobe Commerce
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
- Adobe Commerce on-premises, all supported versions
Issue
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.
Cause
The default value of 16MB for the MySQL max_allowed_packets setting is not large enough for your needs.
Solution
-
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 theSELECTstatement 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:
code language-none 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_packetsize of 16MB. Follow the same process in step one to reduce the data being returned by such queries.
Related reading
- On-premises installation overview in our developer documentation.
- Database upload loses connection to MySQL in our support knowledge base.
- Database best practices for Adobe Commerce on cloud infrastructure in our support knowledge base.
- Best practices to resolve database performance issues in our support knowledge base.
- Best practices for modifying database tables in the Commerce Implementation Playbook