Shopware 6.5 - The function or expression 'variant_listing_config' cannot be used in the CHECK clause

Error Message

When updating Shopware from version 6.4 to 6.5, the following error message appears:

SQLSTATE[HY000]: 
General error: 1901 
Function or expression 'variant_listing_config' cannot be used in the CHECK clause of `variant_listing_config`

The error occurs regardless of the MariaDB version used. Therefore, all MariaDB versions are affected: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11, 11.1, 11.2, 11.3

Solution

Shopware provides an update migration in version 6.5.6.0 to resolve the issue. Unfortunately, the issue is not resolved until version 6.5.6.0, which is why the update to 6.5 continues to fail.

Before executing the following MySQL commands, create a server snapshot or a server backup so you can revert to the previous state in case of an error.

Execute the following database commands via the MySQL CLI or phpMyAdmin before updating to 6.5 to resolve the issue:

# Create a temporary database table

CREATE TABLE IF NOT EXISTS `product_tmp` (
 `id` BINARY(16) NOT NULL,
 `version_id` BINARY(16) NOT NULL,
 `variant_listing_config` JSON NULL DEFAULT NULL,
 PRIMARY KEY (`id`, `version_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

# Add products to the temporary table that contain a `variant_listing_config`

INSERT INTO `product_tmp` (SELECT `id`, `version_id`, `variant_listing_config` FROM `product` WHERE variant_listing_config IS NOT NULL)

# Remove the `variant_listing_config` column from the `product` table

ALTER TABLE `product` DROP COLUMN `variant_listing_config`

# Re-adding the `variant_listing_config` column (without the CHECK clause)

ALTER TABLE `product` ADD COLUMN `variant_listing_config` JSON NULL DEFAULT NULL

# Add the moved products

UPDATE `product`
SET product.variant_listing_config = (SELECT variant_listing_config FROM product_tmp WHERE product.id = product_tmp.id AND product.version_id = product_tmp.version_id)
WHERE product.variant_listing_config IS NULL AND EXISTS (SELECT variant_listing_config FROM product_tmp WHERE product.id = product_tmp.id AND product.version_id = product_tmp.version_id)

# Remove the temporary database table

DROP TABLE `product_tmp`

Next, perform the Shopware update to 6.5.

Shopware 6.5 Update with Composer

bin/console system:update:prepare

composer update

bin/console system:update:finish

# Force an update of all configuration files
composer recipes:update

Once the Shopware update is complete, you will see the following output:

Shopware has been successfully updated to 6.5.

Common Error Messages

/usr/bin/env: “php”: No such file or directory

If the PHP environment configuration cannot be found, the path to the PHP binary is missing from the Bash profile. Run the following command to set PHP 8.2 as the default CLI version:

echo "export PATH=/opt/plesk/php/8.2/bin:$PATH;" >> ~/.bash_profile

Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 315392 bytes)

If the defined memory limit is exceeded, you can use the Shopware CLI with unlimited memory.

php -d memory_limit=-1 bin/console system:update:finish

Sources

Shopware 6.5.6.0 Migration:

https://github.com/shopware/shopware/blob/v6.5.6.0/src/Core/Migration/V6_5/Migration1678197291ConvertVariantListingConfig.php