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

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

  • Can you tell me how I can enable module. Thank you – Robert Oct 10 '19 at 12:08
  • Depends. How did you disable it? – Fabian Schmengler Oct 11 '19 at 7:42
  • no, I use the migration tool and I have the same error, I try to use the alter table command that you recommend and is not work too. – Robert Oct 11 '19 at 8:29
  • so I want to migrate from magento 1.9.4.1 to magento 2.3.1 version, if I use the mysql alter command, I still have the same error: " SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' f or key 'WISHLIST_CUSTOMER_ID_DELETE_ME' " . Can you tell us how we can remove the foreign key that depends on it, or how we can fix this error? Thank you in advance – Robert Oct 12 '19 at 9:05
  • We're receiving this same error, we're using Open Source, not Enterprise. Anyone figure why this is happening? – Nick Piro 2 days ago
0

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.

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.