6

I've got this error when run "bin/magento setup:upgrade".

Module 'Magento_Catalog':
Running schema recurring...

  [PDOException]                                                                                        
  SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'mydb/CAT_CTGR_PRD_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID'

What can cause it?

6 Answers 6

14

Run command:

php bin/magento setup:db-declaration:generate-whitelist
2
  • Error: Magento was installed with a table prefix. Please re-install without prefix. Sep 7, 2022 at 8:49
  • Thanks @Nguyen it saves a lot of my times. Thumbs up
    – Anees
    Mar 9, 2023 at 14:57
7

just run below command if your magento version Magento 2.3 or Magento 2.4

php bin/magento setup:db-declaration:generate-whitelist --module-name=Vendor_Module

above is for generate whitelist for perticular module or run below for all modules

run command: php bin/magento setup:db-declaration:generate-whitelist

hope this helps you :)

4
  • Error: Magento was installed with a table prefix. Please re-install without prefix. Sep 7, 2022 at 8:49
  • According to Magento docs, Whitelists cannot be generated on Magento instances that use a database prefix. The presence of a prefix impacts the names of some DB entities, such as the names of keys, and these names cannot be used in generated whitelists. The whitelist should be generated by the extension developer on a development environment with no prefixes. Sep 8, 2022 at 11:34
  • I thought that after running this and then setup:upgrade, it was going to fail again, as it was taking such a long time to complete, and is usually much quicker. But nope, just give it time, be patient, and it will eventually complete.
    – Smithee
    Nov 10, 2023 at 11:30
  • Check in logs..seems its something different issue Nov 14, 2023 at 7:29
2

I had this same problem on a custom table. After creation I needed to add new columns to this table.

To make such a change to the table in declarative mode I needed to generate the db_schema_whitelist.json.

Run the following command:

bin/magento setup:db-declaration:generate-whitelist --module-name=Your_Module

You can learn more about declarative schema at: Configure declarative schema

1

Same issue there, it seems that my columns declaration don't match (between the reference table and the table owning the foreign key), for example: one is unsigned and not the other, or one is nullable or not the other.

I didn't find a way to solve it yet, except by making these columns match (which is not what I want, for example to have a nullable foreign key).

What I don't understand is that Magento's core succeed in doing this, cf. tables store and customer_entity, where store_id column is nullable.

[Edit] It seems that generating a db_schema_whitelist.json with command bin/magento setup:db-declaration:generate-whitelist --module-name=My_Module solved it, didn't exactly understood why, if anyone has an explanation I would be grateful!

1
  • Too bad I cannot confirm if this worked for me or not. Feb 25, 2019 at 16:21
0

In Magento 2.3 :

The <module_vendor>/<module_name>/etc/db_schema_whitelist.json file provides a history of all tables, columns, and keys added with declarative schema. It is required to allow drop operations. It can be generated manually or created automatically with the following command:

bin/magento setup:db-declaration:generate-whitelist [options] [options] can be:

--module-name[=MODULE-NAME] specifies which module to generate a whitelist for. If no module name is specified, then the default behavior is to generate a whitelist for all modules. You can also explicitly set --module-name=all.

When creating a new table, remember to generate the db_schema_whitelist.json file.

As a best practice, you should generate a new whitelist file for each release. You must generate the whitelist in any release that contains changes in the db_schema.xml file.

For more detail refer below link: https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/migration-commands.html#create-whitelist

For specific Module execute below command:

sudo php bin/magento setup:db-declaration:generate-whitelist --module-name=PackageName_ModuleName
0

I had faced with this issue in php bin/magento setup:upgrade

SQLSTATE[HY000]: General error: 1826 Duplicate foreign key constraint name 'FK_8DFA69E89F93F6B2F7D11FEB4972429A', query was: ALTER TABLE amasty_mostviewed_pack_productMODIFY COLUMNentity_idint(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Entity ID", MODIFY COLUMNpack_idint(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT "Pack id", MODIFY COLUMNproduct_idint(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT "product id", ADD CONSTRAINTAMASTY_MOSTVIEWED_PACK_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID FOREIGN KEY (product_id) REFERENCES catalog_product_entity (entity_id) ON DELETE CASCADE, ADD CONSTRAINT FK_8DFA69E89F93F6B2F7D11FEB4972429A FOREIGN KEY (pack_id) REFERENCES amasty_mostviewed_pack (pack_id) ON DELETE CASCADE

So I run

php bin/magento setup:db-declaration:generate-whitelist --module-name=Amasty_Mostviewed

this issue has been fixed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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