What could be the reason for this error on bin/magento setup:upgrade
after updating from 2.2.8 to 2.3.1?
SQLSTATE[42000]: Syntax error or access violation: 1061 Duplicate key
name 'WISHLIST_CUSTOMER_ID', query was: ALTER TABLEwishlist
ADD
CONSTRAINTWISHLIST_CUSTOMER_ID
UNIQUE KEY (customer_id
)
The db_schema.xml
of the wishlist module contains this definition:
<constraint xsi:type="unique" referenceId="WISHLIST_CUSTOMER_ID">
<column name="customer_id"/>
</constraint>
But it should not try to create the constraint if it is already there?
SHOW CREATE TABLE whishlist
shows me:
CREATE TABLE `wishlist` (
`wishlist_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Wishlist ID',
`customer_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Customer ID',
`shared` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Sharing flag (0 or 1)',
`sharing_code` varchar(32) DEFAULT NULL COMMENT 'Sharing encrypted code',
`updated_at` timestamp NULL DEFAULT NULL COMMENT 'Last updated date',
`name` varchar(255) DEFAULT NULL COMMENT 'Wish List Name',
`visibility` smallint(6) DEFAULT '0' COMMENT 'Wish List visibility type',
PRIMARY KEY (`wishlist_id`),
KEY `WISHLIST_SHARED` (`shared`),
KEY `WISHLIST_CUSTOMER_ID` (`customer_id`),
CONSTRAINT `WISHLIST_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=34412 DEFAULT CHARSET=utf8 COMMENT='Wishlist main Table'
The entry in the setup_module
table:
+------------------+----------------+--------------+
| module | schema_version | data_version |
+------------------+----------------+--------------+
| Magento_Wishlist | 2.0.2 | 2.0.2 |
+------------------+----------------+--------------+
Best Answer
Apparently it is a Magento bug. When comparing current schema to declared schema, constraints and other indexes are handled separately, but the existing index is not dropped before the new
UNIQUE
index is created.A workaround in this case is to
drop or¹ rename the existing index in a separate script before performing the update.Not quite satisfying but does the job, as long as there's no solution for the core bug.
Update: what actually triggered the bug was that the
magento/module-multiple-wishlist
module from Commerce/Enterprise Edition had been disabled. Re-enabling it also "solved" the issue.¹) dropping it was not possible without also removing the foreign key that depends on it