Setup upgrade with “Duplicate FOREIGN KEY constraint error” from the second execution onwards

[SamueleMartini]

Duplicate FOREIGN KEY constraint error on setup:upgrade due to nullable columns in Optimizer db_schema.xml

When running bin/magento setup:upgrade, Magento repeatedly tries to re-apply foreign key constraints defined in the module Smile_ElasticsuiteOptimizer.
This leads to a fatal SQL error:

SQLSTATE[HY000]: General error: 1826 Duplicate FOREIGN KEY constraint name 'magento/FK_DECB3B36711079998CA4D3DB38F2E0EB'

The issue occurs even on a clean Magento installation, with no previous ElasticSuite data or schema.

Preconditions

Install the module and run the setup:upgrade at least twice. The first run is successful, but from the second run onwards it returns an error.

Magento Version: Both Magento Opensource and Adobe Commerce and Mage-OS tested error in versions

• 2.4.6-p12
• 2.4.8
• 2.4.8-p1
• 2.4.8-p2

ElasticSuite Version: 2.11.15 and 2.11.15.1

Environment: Both production mode and developer mode

Third party modules: none, clean Magento installation

Steps to reproduce

1. Start from a clean Magento installation (no existing database).
2. Install ElasticSuite following the official documentation.
3. Run: bin/magento setup:upgrade → the setup completes successfully.
4. Run the same command again: bin/magento setup:upgrade → the command fails with:
   SQLSTATE[HY000]: General error: 1826 Duplicate FOREIGN KEY constraint name 'magento/FK_DECB3B36711079998CA4D3DB38F2E0EB'

Expected result

setup:upgrade should complete successfully on subsequent executions with no schema differences detected.

Actual result

setup:upgrade always tries to re-apply the same foreign key constraints, causing a MySQL duplicate FK error.

Proposed fix:

This error occurs because Magento first creates columns as "nullable = true" and then sets the foreign key, causing MySQL to change the column to "nullable = false". From the second setup upgrade, Magento sees a discrepancy between the db_schema and what is set in the database and tries to reapply the constraint, generating the duplicate key error.
Change both columns to "nullable = false" in db_schema.xml:

<column name="category_id" xsi:type="int" unsigned="true" nullable="false" comment="Category ID"/>
<column name="query_id" xsi:type="int" unsigned="true" nullable="false" comment="Query ID"/>

This makes the declarative schema idempotent and eliminates the recurring foreign key re-creation.

Additional notes:

After applying this change, repeated executions of bin/magento setup:upgrade succeed without error on all tested Magento versions.

[SamueleMartini]

I made a pull request to fix this issue: #3726

[vahonc]

Hello @SamueleMartini,

Thank you for your feedback.

We already have a similar issue #3498 where a certain alternative solution was also proposed.

I will try to reproduce this issue according to your steps on a fresh Magento installation.

Install ElasticSuite following the official documentation.

As for this, could you provide some details on what exactly you are running or configuring?

BR,
Vadym

[SamueleMartini]

@vahonc In a clean Magento installation, I simply followed the guide in the readme here on GitHub: https://github.com/Smile-SA/elasticsuite/wiki/ModuleInstall
Steps:
composer require smile/elasticsuite ~2.11.13
bin/magento module:enable Smile_ElasticsuiteCore Smile_ElasticsuiteCatalog Smile_ElasticsuiteCatalogGraphQl Smile_ElasticsuiteSwatches Smile_ElasticsuiteCatalogRule Smile_ElasticsuiteVirtualCategory Smile_ElasticsuiteThesaurus Smile_ElasticsuiteCatalogOptimizer Smile_ElasticsuiteTracker Smile_ElasticsuiteAnalytics Smile_ElasticsuiteAdminNotification Smile_ElasticsuiteIndices

bin/magento config:set -le smile_elasticsuite_core_base_settings/es_client/servers localhost:9200
bin/magento config:set -le smile_elasticsuite_core_base_settings/es_client/enable_https_mode 0
bin/magento config:set -le smile_elasticsuite_core_base_settings/es_client/enable_http_auth 0
bin/magento config:set -le smile_elasticsuite_core_base_settings/es_client/http_auth_user ""
bin/magento config:set -le smile_elasticsuite_core_base_settings/es_client/http_auth_pwd ""
bin/magento config:set -le catalog/search/engine elasticsuite
bin/magento app:config:import

bin/magento setup:upgrade

Everything works fine so far.
If you run bin/magento setup:upgrade a second time, the error I mentioned in the issue is displayed.

I followed the process of applying the schema during setup:upgrade using xdebug and checking the status of the database columns step by step, and I confirm that Magento first creates columns as "nullable = true" and then sets the foreign key, causing MySQL to change the column to "nullable = false". From the second setup upgrade, Magento sees a discrepancy between the db_schema and what is set in the database and tries to reapply the constraint, generating the duplicate key error.

It is not a conceptual or technical error; a column with a foreign key can be nullable if it does not violate referential integrity. It is Magento itself that, from a certain version onwards, when it sees a constraint, turns the column to not nullable. In fact, with Magento 2.4.6-p12 and 2.4.8, it gives an error, while with 2.4.6-p9, it seems to me that it does not give any error.

The fix I applied in my pull request is decisive.

[vahonc]

@SamueleMartini,

I was unable to reproduce this issue in my test environment, even when I tried installing Magento 2.4.6-p12 CE and 2.4.8-p2 EE versions and running the setup:upgrade command twice.

I think this issue could be linked to the DB software and its version. I mostly use MySQL 8 in my test environments, but as I wrote before, we had a similar issue, and it was related to the Maria database.

So, could you please specify what DB software and version you are using?

BR,
Vadym

[SamueleMartini]

@vahonc The database is MariaDB 10.6, 10.11, and 11.4.
I haven't tested the issue with MySQL 8, but I have tested my solution and it doesn't give any errors. I confirm that my modification is solid.

[rbayet]

Hello @SamueleMartini, the issue is that the nullability serves a function as an optimizer can have

• a limitation on one or several categories
  • while at the same time no limitation at all on search queries
• or a limitation on one or several search queries
  • while at the same time no limitation at all on categories

So the table needs to be able to have entries where

• the optimizer_id and category_id are defined but the query_id is NULL
• the optimizer_id and query_id are defined but the category_id is NULL

Your PR solves the technical problem by introducing a functional one: you can no longer create an optimizer applicable on catalog and search everywhere BUT on a category.

Regards,