Magento 2.3.1 – Fix setup:upgrade SQL Error After Update

declarative-schemamagento2.3magento2.3.1upgradewishlist

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 TABLE wishlist ADD
CONSTRAINT WISHLIST_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.

alter table wishlist rename index WISHLIST_CUSTOMER_ID to WISHLIST_CUSTOMER_ID_DELETE_ME;

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

Related Topic