MariaDB 11.x/12.x & Magento 2: Unpacking the 'Record Has Changed' Error 1020

MariaDB 11.x/12.x & Magento 2: Unpacking the 'Record Has Changed' Error 1020

As an e-commerce platform, Magento 2 relies heavily on a robust and compatible database backend. For many Adobe Commerce and Open Source users, MariaDB is the database of choice. However, recent upgrades to newer MariaDB versions, specifically 11.8 and 12.3, have unveiled a critical compatibility challenge that can lead to frequent transaction failures and impact site stability under high load.

The Problem: Frequent SQLSTATE[HY000]: General error: 1020

A GitHub issue (#41047) highlights a significant problem faced by Magento 2 users who upgrade their MariaDB instances to versions 11.8 (officially supported since Adobe Commerce 2.4.8-p5) or 12.3 (supported since 2.4.9). These users are encountering frequent SQLSTATE[HY000]: General error: 1020 Record has changed since last read; try restarting transaction errors. This error typically manifests during concurrent catalog writes, such as a price reindex operation, or any other long-running transactions under high system load.

SQLSTATE[HY000]: General error: 1020 Record has changed since last read in table 'catalog_product_entity'; try restarting transaction, query was: INSERT INTO `catalog_product_index_price_temp` (...) SELECT ...

Such errors can lead to full transaction rollbacks, disrupting critical background processes and potentially causing data inconsistencies or performance bottlenecks for merchants.

The Root Cause: MariaDB's innodb_snapshot_isolation

The core of this issue lies within a change in MariaDB's InnoDB behavior related to the innodb_snapshot_isolation setting. This feature, introduced as OFF in earlier versions (10.6.18/10.11.8/11.4.2), became ON by default starting with MariaDB 11.6.2 (documented in MDEV-35124). Under the REPEATABLE READ transaction isolation level, which Magento often utilizes, a locking read on a row that has been modified by a concurrently committed transaction will now explicitly fail with ER_CHECKREAD (error code 1020). Previously, this operation would have silently succeeded.

MariaDB's guidance for this specific error is to restart the transaction. This is where Magento's current implementation falls short.

Magento's Missing Retry Mechanism

Magento's database adapter, specifically Magento\Framework\DB\Adapter\Pdo\Mysql, is designed to handle and retry certain transient database errors. It includes error codes like 2006 (MySQL server has gone away), 2013 (Lost connection to MySQL server), 1205 (Lock wait timeout exceeded), 1213 (Deadlock found), 1062 (Duplicate entry), and 1146 (Table doesn't exist). However, error code 1020 is conspicuously absent from this list.

Despite being semantically equivalent to a deadlock (1213) in terms of requiring a transaction restart, Magento does not automatically retry transactions failing with error 1020. This oversight means that instead of a graceful retry, the transaction simply fails, leading to the issues described above.

Immediate Workaround and Proposed Solutions

For Magento 2 users currently experiencing this problem, an immediate workaround is available: set innodb_snapshot_isolation=OFF in your MariaDB configuration. This reverts the behavior to the previous state, preventing the 1020 error from occurring due to this specific mechanism.

Looking ahead, the GitHub issue proposes two main solutions for the Magento core team:

• Core Code Update: Add error code 1020 to the list of retriable errors within Magento\Framework\DB\Adapter\Pdo\Mysql. This would allow Magento to gracefully handle these transient errors without requiring manual database configuration changes.
• Official Documentation: Document the necessity of setting innodb_snapshot_isolation=OFF for supported MariaDB versions (11.6.2 and above) to ensure compatibility and stable operation.

While the issue is currently categorized as S3 (affecting non-critical functionality without forcing a workaround), the frequency and impact on critical operations like price reindexing under high load suggest that addressing this is crucial for maintaining a stable and performant Magento store, especially for those considering or undergoing Magento migrations or platform upgrades.

Staying informed about such database compatibility nuances is vital for developers, system administrators, and merchants alike to ensure their Magento 2 deployments run smoothly and efficiently.