Shopware 6.7 Update Failure: Resolving Error 500 with MySQL 8.4.x `restrict_fk_on_non_standard_key`

Shopware 6.7 Update Failure: Resolving Error 500 with MySQL 8.4.x `restrict_fk_on_non_standard_key`

A critical issue emerged during Shopware 6.7.x updates, specifically from version 6.7.6.2 to 6.7.8.2, leading to an Error 500 and an inaccessible shop. This problem, reported by multiple users, points to a specific database migration conflict when running Shopware on MySQL 8.4.x.

The Problem: Migration Failure and SQL Error 1553

Users attempting the update encountered a failure during the "Run Post Update" phase, specifically when migrating the `Migration1763125891AddProductTypeColumn`. The core error message was:

SQLSTATE[HY000]: General error: 1553 Cannot drop index '': needed in a foreign key constraint

This error occurred while the migration was attempting to add a 'type' column to the 'product' table. Following the failed update, the Shopware frontend and backend became unreachable, displaying a `Column not found: 1054 Unknown column 'sales_channel_analytics.track_offcanvas_cart' in 'field list'` error in the logs, indicating an incomplete or corrupted database state.

The affected migration step aimed to add a `type` column to the `product` table and create an index:

class Migration1763125891AddProductTypeColumn extends MigrationStep
{
    public function getCreationTimestamp(): int
    {
        return 1763125891;
    }

    public function update(Connection $connection): void
    {
        if (!TableHelper::columnExists($connection, 'product', 'type')) {
            $this->addColumn(
                $connection,
                'product',
                'type',
                'VARCHAR(32)',
                false,
                '\'physical\''
            );

            $connection->executeStatement('CREATE INDEX `idx.product.type` ON `product` (`type`)');
        }

        $batchSize = 5000;

        do {
            $affected = $connection->executeStatement(
                "UPDATE `product`
                 SET `product`.`type` = 'digital'
                 WHERE JSON_CONTAINS(states, '"is-download"')
                 LIMIT {$batchSize};"
            );
        } while ($affected > 0);
    }
}

The Root Cause: MySQL 8.4.x `restrict_fk_on_non_standard_key`

Shopware support and community experts quickly identified the root cause: MySQL 8.4.x's `restrict_fk_on_non_standard_key` setting. This setting, when `ON` (which is its default or a common configuration in newer MySQL 8.4.x versions), acts as an additional security measure, preventing certain foreign key configurations that are deemed non-standard. The Shopware migration, in this specific instance, conflicted with this restriction, leading to the `Cannot drop index` error.

This issue was also linked to a known MySQL bug (bug #118151) and tracked in a GitHub issue (ALTER TABLE product Migrations fail on MySQL 8.4 · Issue #16240 · shopware/shopware · GitHub).

The Solution: Adjusting MySQL Configuration

The confirmed workaround, provided by Shopware support and successfully implemented by affected users, involves globally setting `restrict_fk_on_non_standard_key` to `OFF` in the MySQL configuration. This allows the Shopware migration to proceed without encountering the foreign key restriction.

Steps to Resolve:

1. Backup your database: Always perform a full database backup before making any direct changes to your MySQL configuration.
2. Edit `my.cnf`: Locate your MySQL configuration file, typically `my.cnf` or `my.ini`.
3. Add or modify the setting: Under the `[mysqld]` section, add or change the following line:

[mysqld]
restrict_fk_>

4. Restart MySQL: After saving the changes, restart your MySQL server for the new configuration to take effect.
5. Retry Shopware Update: Once MySQL is restarted, attempt the Shopware update again. It should now complete successfully.

This solution provides a clear path for merchants and developers facing update issues on Shopware 6.7.x with MySQL 8.4.x, ensuring their e-commerce platforms can stay up-to-date.