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

Navigating Database Compatibility: Magento 2 and Modern MariaDB Versions

As an e-commerce expert at Shopping Mover, we frequently guide merchants through complex platform upgrades and migrations. A robust, compatible database backend is the bedrock of any high-performing Magento 2 store, whether you're running Adobe Commerce or the Open Source edition. However, the landscape of database technology is constantly evolving, and sometimes, these advancements can introduce unexpected challenges. A recent, critical compatibility issue has emerged for Magento 2 users upgrading to newer MariaDB versions, specifically 11.x and 12.x, leading to frustrating transaction failures and potential site instability under peak loads.

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

The core of this challenge is meticulously documented in Magento GitHub issue #41047. Magento 2 users who have upgraded their MariaDB instances to versions like 11.8 (officially supported since Adobe Commerce 2.4.8-p5) or 12.3 (supported since 2.4.9) are encountering frequent SQLSTATE[HY000]: General error: 1020 Record has changed since last read; try restarting transaction errors. This error isn't a minor glitch; it typically manifests during critical, concurrent catalog writes, such as a product price reindex, inventory updates, or any other long-running transactions executed 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 are more than just log entries; they trigger full transaction rollbacks. For a busy e-commerce store, this can disrupt essential background processes, lead to inconsistent data, and create significant performance bottlenecks, directly impacting merchant operations and customer experience.

Unpacking the Root Cause: MariaDB's innodb_snapshot_isolation

To understand why this error occurs, we need to delve into a specific change within MariaDB's InnoDB storage engine, related to the innodb_snapshot_isolation setting. This feature, designed to enhance transaction isolation and consistency, underwent a significant default change:

• Initially, innodb_snapshot_isolation was introduced as OFF in earlier MariaDB versions (e.g., 10.6.18, 10.11.8, 11.4.2).
• Crucially, it became ON by default starting with MariaDB 11.6.2. This change is documented in MDEV-35124.

Under the REPEATABLE READ isolation level, which Magento commonly uses for its transactions, when innodb_snapshot_isolation is ON, a locking read on a row that has been modified by a *committed* concurrent transaction will now fail with ER_CHECKREAD (error 1020). Previously, in older MariaDB versions or with the setting OFF, such a read would silently succeed, potentially returning an older version of the data or waiting for locks. MariaDB's explicit guidance for error 1020 is to restart the transaction, ensuring data consistency.

The Magento-MariaDB Disconnect

Here lies the critical compatibility gap: Magento's database adapter, specifically Magento\Framework\DB\Adapter\Pdo\Mysql, is designed to retry certain transient database errors. It includes a list of retriable errors such as 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 1020 is conspicuously absent from this list.

While 1020 is not a deadlock (1213), its semantic implication for the application is similar: the transaction cannot proceed as intended due to a concurrent modification, and the recommended action is to restart it. Magento's failure to recognize and retry 1020 means that instead of seamlessly retrying the operation, the entire transaction rolls back, leading to the errors observed by merchants.

Impact on Your Magento Store and Operations

For merchants, this isn't just a technical detail; it translates directly into operational headaches:

• Disrupted Background Processes: Price reindexing, inventory updates, and other crucial cron jobs may fail or take significantly longer, leading to outdated product information or stock discrepancies.
• Performance Bottlenecks: Frequent transaction rollbacks consume database resources and CPU cycles, degrading overall store performance, especially during peak traffic.
• Data Inconsistencies: While transactions are rolled back to prevent corruption, the repeated failures can make it difficult to complete critical data updates reliably.
• Developer Frustration: Debugging intermittent database errors under high load is challenging and time-consuming for development teams.

Immediate Workaround: A Temporary Fix

Until a permanent solution is implemented in Magento core, the most effective workaround is to revert MariaDB's behavior by setting innodb_snapshot_isolation=OFF. This can typically be done in your MariaDB configuration file (e.g., my.cnf or mariadb.cnf) under the [mysqld] section:

[mysqld]
innodb_snapshot_isolation=OFF

After making this change, you'll need to restart your MariaDB server for the setting to take effect. While this resolves the immediate issue by making locking reads succeed silently again, it's crucial to understand that this is a workaround. It alters MariaDB's default behavior and might have subtle implications for other applications or specific transaction patterns, though for Magento's typical usage, it effectively mitigates the 1020 error.

The Path Forward: Core Solution and Documentation

The proposed long-term solutions, as outlined in the GitHub issue, are clear:

1. Magento Core Update: The ideal solution is for Adobe Commerce to update the Pdo\Mysql adapter to include error 1020 in its list of retriable errors. This would align Magento's behavior with MariaDB's guidance and ensure robust transaction handling.
2. Official Documentation: At a minimum, Adobe Commerce documentation should explicitly address this compatibility nuance for supported MariaDB versions (11.6.2 and above), providing clear guidance for configuration or expected behavior.

As e-commerce migration experts, we at Shopping Mover emphasize that database compatibility is paramount for successful Magento 2 upgrades and migrations. Proactive identification and mitigation of such issues are critical to maintaining a stable and performant platform. When planning your next Magento upgrade or migration, thorough testing in a staging environment and expert consultation can prevent these types of unexpected challenges from impacting your live store.

Conclusion: Stay Vigilant, Stay Stable

The 'Record Has Changed' error 1020 serves as a powerful reminder that even officially supported database versions can introduce subtle behavioral changes that impact complex applications like Magento 2. Understanding the underlying cause – MariaDB's innodb_snapshot_isolation – and implementing the appropriate workaround is key to maintaining stability. For long-term resilience, we look forward to a core Magento update that natively handles this error, ensuring seamless operations for all merchants leveraging modern MariaDB versions. Until then, thorough testing, careful configuration, and expert guidance are your best allies in keeping your Magento store running smoothly.