Shopware 6.5 - 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.

Problem solution

Shopware provides an update migration in version 6.5.6.0 to solve the problem. Unfortunately, the problem is only solved in version 6.5.6.0, which is why the update to 6.5 still fails.

Execute the following database commands via the MySQL CLI or PhpMyAdmin before updating to 6.5 to solve the problem:

# Create 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`

# Add the variant_listing_config again (without the check clause)

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

# Add the outsourced 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)

# Removal of the temporary database table

DROP TABLE `product_tmp`

Then carry out 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 update of all configuration files
composer recipes:update

After completing the Shopware update, you will receive the following output:

Shopware has been successfully updated to 6.5.

Frequent error messages

/usr/bin/env: "php": File or directory not found

If the PHP environment configuration cannot be found, the PHP binary is missing in the bash profile. Execute 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