Magento 2.4.x & MariaDB 12.1+ Alert: Unmanaged Reserved Keywords Break Core Functionality

Magento 2.4.x & MariaDB 12.1+ Alert: Unmanaged Reserved Keywords Break Core Functionality

At Shopping Mover, we constantly monitor the Magento ecosystem for critical issues that could impact your e-commerce operations, especially during or after platform migrations. A recent GitHub issue (magento/magento2#41049) has brought to light a significant compatibility problem between Magento 2.4.x (specifically 2.4.9, but potentially other 2.4.x versions) and MariaDB 12.1+.

The Problem: SQL Errors Due to New MariaDB Reserved Keywords

Users running Magento 2.4.x on MariaDB versions 12.1 and above are encountering critical SQL syntax errors. This issue manifests in two key areas, severely impacting core Magento functionality:

1. Admin Grid Sorting Failure: When attempting to sort columns like 'End' (which maps to to_date) in admin grids, such as Marketing → Cart Price Rules or Catalog Price Rules, the operation fails with a SQL error.
2. Cart Price Rule Validation Failure: During the checkout process, or when applying coupons, if active cart price rules are in place, the validation process (specifically DateApplier::applyDate()) throws a similar SQL error, preventing rules from being applied correctly.

Both scenarios result in an error message similar to this:

SQLSTATE[42000]: Syntax error or access violation: 1064
You have an error in your SQL syntax; ... near 'to_date ...'

Deep Dive into the Root Cause

The core of the problem lies in MariaDB 12.1 introducing new reserved keywords, notably TO_DATE and CONVERSION. Magento's core framework, specifically the Magento\Framework\Data\Collection\AbstractDb and Magento\SalesRule\Model\ResourceModel\Rule\DateApplier classes, does not account for these new reserved words when constructing SQL queries.

In AbstractDb::_renderOrders(), the internal $sqlReservedWords map, used to backtick-quote identifiers in ORDER BY clauses, does not include TO_DATE. Consequently, the column name is emitted unquoted, causing a syntax error:

// _renderOrders()
if (isset($this->sqlReservedWords[strtoupper($field)])) {
    $field = "`$field`";
}

Similarly, in DateApplier::applyDate(), the WHERE clause is built with a bare to_date column name, which MariaDB 12.1+ interprets as a reserved keyword, leading to the syntax violation:

->where(
    'to_date is null or to_date >= ?',   // <-- unquoted, fails on MariaDB 12.1+
    $now
);

Impact and Proposed Solution

This issue has been triaged with a Severity S0, indicating it affects critical data or functionality and leaves users without a workaround. For merchants, this means broken cart price rules, potentially leading to incorrect pricing, failed checkouts, and an inability to manage crucial aspects of their store through the admin panel.

The author of the GitHub issue, Nuranto, has not only meticulously identified the problem and its root cause but also stated their intention to submit a Pull Request (PR) to address it. The fundamental solution involves updating Magento's internal reserved word lists and ensuring that column names that might conflict with new database reserved keywords are properly quoted in SQL queries.

Shopping Mover's Take

This incident underscores the critical importance of thorough compatibility testing, especially when upgrading underlying infrastructure components like your database. For businesses considering or undergoing a Magento migration, ensuring database compatibility with your chosen Magento version is paramount. Issues like this can cause significant downtime and operational headaches if not addressed proactively. Staying informed about such community-reported bugs and their fixes is crucial for maintaining a stable and performant e-commerce platform.