7

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        |
+------------------+----------------+--------------+
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.