setup:upgrade SQL error after update from 2.2.8 to 2.3.1

Question

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

Answer 1

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

Answer 2

Do you may be use MariaDB and not MySQL?

Quite recently we encountered a strange constraint problem when updating magento to 2.3.2 on a server.

SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP FOREIGN KEY SHARED_CATALOG_CREATED_BY_ADMIN_USER_USER_ID; check that it exists, query was: ALTER TABLE shared_catalog DROP FOREIGN KEY SHARED_CATALOG_CREATED_BY_ADMIN_USER_USER_ID

I couldn't reproduce the issue locally. That is, until I switched to from mysql 5.7 to MariaDB 10.2.

I suspect magento needing to add/remove constraints twice during update but probably doing that in the wrong order. Might be somehow related to the fact that both modules contain a db_schema.xml and db_schema_whitelist.json but I did not investigate any further.

Anyway, to me it seems that MySQL just not throws an error in these cases and to me seems to be the better working choice for M2 at this point of time.