Discovery
In Y1 we are using a program that is called db-archiver. This tool is taking a dump of the production database and cleaning out a lot of sensitive data from it (like for example access logs, customer address data, credentials). The cleaned db dumps then are used for setting up showrooms (used by the quality assurance) or also for setting up developers local environments. This is a measurement to protect the data of our customers. It enables us to still work on data that is close to the production environment, without spreading sensitive data on many places.
A work college was trying to create a showroom, that failed. I found out that the showroom was not able to import a valid database. Checking the db-archiver I found a brocken-pipe error and than this MySQL error:
ERROR 1062 (23000) at line 1019: Duplicate entry '2-54-0' for key 'CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID'SQL Key Constraint
Looking at the dump I was identifying that this error message belongs to the `catalog_category_entity_int` table of an Magento 2.4.0 system:
CREATE TABLE `catalog_category_entity_int` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`value` int(11) DEFAULT NULL COMMENT 'Value',
`row_id` int(10) unsigned NOT NULL COMMENT 'Version Id',
PRIMARY KEY (`value_id`),
UNIQUE KEY `CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID` (`row_id`,`attribute_id`,`store_id`),
KEY `CATALOG_CATEGORY_ENTITY_INT_ATTRIBUTE_ID` (`attribute_id`),
KEY `CATALOG_CATEGORY_ENTITY_INT_STORE_ID` (`store_id`),
KEY `CATALOG_CATEGORY_ENTITY_INT_ROW_ID` (`row_id`),
CONSTRAINT `CATALOG_CATEGORY_ENTITY_INT_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE,
CONSTRAINT `CAT_CTGR_ENTT_INT_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`row_id`) REFERENCES `catalog_category_entity` (`row_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20761 DEFAULT CHARSET=utf8 COMMENT='Catalog Category Integer Attribute Backend Table';Checking for Violations
First I had manually resolved the violation that was mentioned in the error message of MySQL. Unlikely there has been popping up just the next constraint violation up. So I decided to check how bad the unique key violation is in the current database:
mysql> select count(*) from catalog_category_entity_int group by attribute_id, store_id, row_id HAVING COUNT(*) > 1 order by row_id;+----------+
| count(*) |
+----------+
| 2 |[...]196 rows in set
This shows that there are some duplicated entries wich are violating the key constraint. A bit more inspection of the data turns out that it only affecting all entries for attribute_id=54 and store_id=0. This are duplicated but beside that the table is looking fine for constraints.
The row_id is the versioning and actually in our store the store_id 0 is not in use. So it should be pretty straight forward to brush the duplicates out of the database without any worries about the shop. You might want evaluate this in your case, before taking actions in fixing it.
Fixing
As I was too lazy to go through all the rows manually, I decided to go with an delete with sub-select:
mysql>
Delete from catalog_category_entity_int where value_id in (
select value_id from catalog_category_entity_int
group by attribute_id, store_id, row_id
HAVING COUNT(*) > 1
);ERROR 1093 (HY000): You can’t specify target table ‘catalog_category_entity_int’ for update in FROM clause
Well this doesn’t went well. Still I remembered that we can outsmart MySQL a bit with with introducing a second sub-select query:
mysql>
Delete from catalog_category_entity_int where value_id in (
select value_id from (
select value_id from catalog_category_entity_int
group by attribute_id, store_id, row_id
HAVING COUNT(*) > 1
)
x
);Query OK, 196 rows affected
This is finally also resolving the issue. The running of the db-archiver is running as expected and no more key violations are in the database.
Causes
We have not investigated what has caused this unique key violation. A working college suggested that the Magento upgrade scripts are sometimes not operating with disabled key constraints. As we have updated this installation shortly, there is a good chance that this was caused by executing the Magento core upgrade scripts.
Takeaway
If you seriously need to disable key constraints, during some operation take care and check that the db is in a good condition after your are done.
Thanks to my Colleagues
Thanks to Anton Boritskiy for sharing his Magento knowledge.
