setup:upgrade fails on smile_elasticsuite_optimizer_limitation PRIMARY constraint

[gerrits-ecommerce]

When trying to run bin/magento setup:upgrade an error is thrown:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '21' for key 'smile_elasticsuite_optimizer_limitation.PRIMARY', query was: ALTER TABLE smile_elasticsuite_optimizer_limitation ADD CONSTRAINT PRIMARY KEY (optimizer_id)

In out clients existing database there are multiple rows in the table: "smile_elasticsuite_optimizer_limitation" with the same optimizer_id value.

This causes the file following file to throw an error on the primary key contarint:

vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml line: 106

Preconditions

Environment has exisiting optimizers with different search terms. The table "smile_elasticsuite_optimizer_limitation" has multple rows with the same optimizer_id.

Magento Version : 2.4.6

ElasticSuite Version : 2.11.0

Environment : Production and Developer

Steps to reproduce

1. Add an optimizer with multiple search terms
2. run bin/magento setup:upgrade

Expected result

1. Updates DB schemes

Actual result

1. error thrown on constraint

[vahonc]

Hello @gerrits-ecommerce

It seems this bug occurred after merging this PR #2148.

The smile_elasticsuite_optimizer_limitation table has a primary key constraint defined on the optimizer_id column. This means that each row in this table must have a unique optimizer_id value. Therefore, if we try to insert multiple rows with the same optimizer_id value, we will get a unique constraint violation error.

I have one hotfix, I need to discuss it with my team, but maybe you could test it on your project.

So, If we need to have multiple rows in the smile_elasticsuite_optimizer_limitation table with the same optimizer_id value, we should remove the primary key constraint on the optimizer_id column.

In vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml line: 106 remove these lines:

<constraint xsi:type="primary" referenceId="PRIMARY">
     <column name="optimizer_id"/>
</constraint> 

Instead of these, we can define a unique index on the combination of optimizer_id, category_id, and query_id columns to enforce uniqueness across these columns.

So, add next lines at the end of db_schema.xml just before </table> tag:

<index referenceId="SMILE_ELASTICSUITE_OPTIMIZER_LIMITATION_UNIQUE" indexType="btree">
     <column name="optimizer_id"/>
     <column name="category_id"/>
     <column name="query_id"/>
</index>

After that, you should run bin/magento setup:upgrade command to update DB schema (it should executed without any errors).

The last step, you should delete the primary key from smile_elasticsuite_optimizer_limitation table.

Just click on the PRIMARY row and press Drop Index on the right side.

p.s. Maybe you will need to delete the primary index before running bin/magento setup:upgrade command. In my test environment, I don't have existed optimizers with multiple optimizer_id rows. But I have a similar issue when I try to apply my newly created optimizer to only selected search terms and after that, I cannot Save it because I get an error: Could not save the optimizer: Unique constraint violation found. Thus the order of the last steps may be slightly different.

BR,
Vadym

[gerrits-ecommerce]

Hey @vahonc,

Thank you for the quick response. I tested your solution in our envoirement and it works as expected. I can now correctly run the setup:upgrade command and create new optimizers.

Below is a temporary composer patch i created.

diff --git a/vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml b/vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml
index c003fc9d..e9592404 100644
--- a/vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml
+++ b/vendor/smile/elasticsuite/src/module-elasticsuite-catalog-optimizer/etc/db_schema.xml
@@ -103,9 +103,6 @@
                 nullable="true"
                 unsigned="true"
                 comment="Query ID"/>
-        <constraint xsi:type="primary" referenceId="PRIMARY">
-            <column name="optimizer_id"/>
-        </constraint>
         <constraint xsi:type="foreign"
                     referenceId="FK_29EE1ECD41B422FDFF017973D0039789"
                     table="smile_elasticsuite_optimizer_limitation"
@@ -130,5 +127,10 @@
         <index referenceId="SMILE_ELASTICSUITE_OPTIMIZER_LIMITATION_QR_ID_SRCH_QR_QR_ID" indexType="btree">
             <column name="query_id"/>
         </index>
+        <index referenceId="SMILE_ELASTICSUITE_OPTIMIZER_LIMITATION_UNIQUE" indexType="btree">
+            <column name="optimizer_id"/>
+            <column name="category_id"/>
+            <column name="query_id"/>
+        </index>
     </table>
 </schema>

Please keep us informed when you fix the issue in the module itself.

[DemystifyDigitalLimited]

I am experiencing this with version 2.11

[vahonc]

Hello @DemystifyDigitalLimited,

First of all, the initial issue was resolved and therefore this ticket was closed. If you have the same or similar problem please create a new ticket, provide a link to the old (this) issue, and add more details, especially about the version of the Elasticsuite module, because 2.11 is very far from a clear understanding of which exactly version you are using (currently the last version is 2.11.10).

Check out the solutions suggested here, if that doesn't help, as I said, feel free to create a new issue with more details.

BR,
Vadym