magento2: Missing unique key on eav_attribute_option_value table

this is backward incompatible change in terms that some DBs could have duplicated data, and adding unique key might potentially fail.

@ihor-sviziev:

I encountered this just today actually, while writing a Data patch I had made an assumption that such a key would exist and therefore was performing $connection->insertOnDuplicate($table, $data, $fields). Due to the lack of the UNIQUE constraint however, duplicated rows of store_id and option_id combinations were created.

In any case, I’d also like to report that any store out there that does have such records in their database, already have a broken installation. As a collection object for the attribute’s options will fail when \Magento\Framework\Data\Collection::addItem() is invoked on a secondary record with the same store_id and option_id. This is because the query for the collection:

SELECT `main_table`.*, `sort_alpha_value`.`value` 
FROM `eav_attribute_option` AS `main_table`  
LEFT JOIN `eav_attribute_option_value` AS `sort_alpha_value` 
  ON sort_alpha_value.option_id = main_table.option_id 
  AND sort_alpha_value.store_id = 0 
WHERE (`main_table`.`attribute_id` = 'REPLACE_THIS_WITH_ATTRIBUTE_ID_VALUE') 
ORDER BY
  main_table.sort_order ASC,
  sort_alpha_value.value ASC;

Will return two records per option_id in this scenario. Example:

+-----------+--------------+------------+-------------------+
| option_id | attribute_id | sort_order | value             |
+-----------+--------------+------------+-------------------+
|         5 |          872 |         10 | Black             |
|         5 |          872 |         10 | Black             |
+-----------+--------------+------------+-------------------+

Since option_id is the unique identifier for the collection’s item model object, an exception is thrown.

So… Long story short… while I technically agree that adding such a UNIQUE constraint breaks backwards incompatibility… any installation with such data, would have had to gone through some wild extra lengths to have their site continue to work with such junk data.

@tzyganu thank you for the suggested fix, applying to my project in 2.4.0.

@ihor-sviziev Thanks for the explanation. You are right… from the abstract code point of view.
But if there are duplicate values nothing works. You will get errors when retrieving attribute options. You can try it and see. I think it is completely safe to fix this.

Hi @tzyganu. Thank you for your report. The issue has been fixed in magento/magento2#24720 by @UncleTioma in 2.3-develop branch Related commit(s):

• 502146f7f2d086b3a1beaea41de3edc488f78b3c

The fix will be available with the upcoming 2.3.4 release.

Verified the issue on latest Magento 2.4-develop instance and the issue is reproducible. Hence confirming this issue. There is no restriction to add duplicate values for option_id. Adding duplicate values for option_id with same store_id is returning no error in eav_attribute_option_value DB table. Adding so is breaking Layered Navigation on Front end Search Results page. Hence confirming this issue. Layered navigation disappeared on store front.

Hi @ihor-sviziev , it makes sense, I have changed the priority, thank you for the input!

I don’t understand where this is breaking BC, but anyway, the beauty of the declarative schema is that it does not matter if it’s in the core or not.
If you have this problem, just add the constraint in one of your custom modules and it will work. And if this reaches the core it will still work even if it’s duplicated in your module

        <constraint xsi:type="unique" referenceId="EAV_ATTRIBUTE_OPTION_VALUE_STORE_ID_OPTION_ID">
            <column name="store_id"/>
            <column name="option_id"/>
        </constraint>