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.


Before executing the following MySQL commands, create a server snapshot or a server backup in order to be able to return to the previous status in the event of an error.


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)
LIMIT 1000


# Remove 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