DocumentationCommerceCommerce KB

Database errors related to max_allowed_packet on Adobe Commerce

Last update: Thu Jul 27 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Developer

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

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

  1. 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.

  2. 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.

  3. 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_packet size of 16MB. Follow the same process in step one to reduce the data being returned by such queries.

recommendation-more-help
8bd06ef0-b3d5-4137-b74e-d7b00485808a