data-migration-tool
data-migration-tool copied to clipboard
magento
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11-general' for key 'EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE'
Preconditions
- Magento 1.9.4.3 to Magento 2.4.2 data migration
- 2.4.2 Data migration tool
Steps to reproduce
- bin/magento migrate:data
Actual result
- SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '11-general' for key 'EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE'
Additional notes
Migration runs successfully using M2.4.1 & 2.4.1 migration tool.
Hi @timpea. Thank you for your report. To help us process this issue please make sure that you provided sufficient information.
Please, add a comment to assign the issue: @magento I am working on this
- Join Magento Community Engineering Slack and ask your questions in #github channel.
![m2-assistant[bot] avatar](https://avatars.githubusercontent.com/in/27048?v=4 "Published by a pub.dev verified publisher"){kind=small}
It appears to be an issue with some of the items in the eav_attribute_group M1 table, running this on the M1 database before running data migration appears to have fixed it.
Word of warning to anyone reading this, please don't use this SQL on a live M1 site.
DELETE FROM eav_attribute_group WHERE attribute_set_id > 8 and attribute_set_id not in (select attribute_set_id from eav_attribute_set where entity_type_id = 4);
I think I found why this constraint violation is triggered.
In my case, that was caused by a custom order attribute in M1. The attribute is imported first and the missing attribute group is created. Then when attribute groups are imported, there is a conflict because it already exist.
I quickfixed it by using insertOrDuplicate.
File magento/data-migration-tool/src/Migration/Step/Eav/Data.php :
line 592 :
private function saveRecords($document, $recordsToSave, $updateOnDuplicate=false) // FIX : expose updateOnDuplicate
{
if (is_object($document)) {
$document = $document->getName();
}
$this->destination->saveRecords($document, $recordsToSave, $updateOnDuplicate); // FIX : expose updateOnDuplicate
}
line 372 :
private function migrateAttributeGroups($attributeGroupIds)
{
//...
$this->saveRecords($destinationDocument, $recordsToSave, true); // FIX : update record if already exist
}
+1 I have the same problem ... is it possible to add this fix as a pull and merge?
Just ran into the same issue, thanks to @jriboux-advisa I could fix it easily.
Created a pull request, we'll see: https://github.com/magento/data-migration-tool/pull/876
So, there is an official answer:
Magento 1 has unique key UNQ_EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_NAME by default. It prevents duplication of records. The fix in this pull request hides the issue of duplicated records. In case when the unique key was removed allowing two attribute groups having the same name but include two different attributes, after migration only one attribute will be included into one group. Duplication of attribute groups should be addressed in Magento 1 DB before migration source: https://github.com/magento/data-migration-tool/pull/876#issuecomment-894450178
To be honest I don't understand this answer. It doesn't give a suggestion on the course of action to resolve this on M1 at all. @victor-v-rad Could you perhaps give us some more insight on this and how to troubleshoot it on the M1 database?
How can we fix the issue so that migraton can occur without destroying M1 database?
From what I can see in my case on the M1 database table the unique restriction is active and there's no violation in it.
More specifically in Magento 1 I see
create table mdsft_eav_attribute_group
(
attribute_group_id smallint unsigned auto_increment comment 'Attribute Group Id'
primary key,
attribute_set_id smallint unsigned default 0 not null comment 'Attribute Set Id',
attribute_group_name varchar(255) not null comment 'Attribute Group Name',
sort_order smallint default 0 not null comment 'Sort Order',
default_id smallint unsigned default 0 null comment 'Default Id',
constraint UNQ_EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_NAME
unique (attribute_set_id, attribute_group_name)
)
comment 'Eav Attribute Group' engine=InnoDB charset=utf8;
create index IDX_EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_SORT_ORDER
on mdsft_eav_attribute_group (attribute_set_id, sort_order);
With data
+------------------+----------------+--------------------+----------+----------+
|attribute_group_id|attribute_set_id|attribute_group_name|sort_order|default_id|
+------------------+----------------+--------------------+----------+----------+
|7 |12 |General Information |10 |1 |
+------------------+----------------+--------------------+----------+----------+
On Magento 2 I can see the table has an extra column called attribute_group_code
create table eav_attribute_group
(
attribute_group_id smallint unsigned auto_increment comment 'Attribute Group ID'
primary key,
attribute_set_id smallint unsigned default 0 not null comment 'Attribute Set ID',
attribute_group_name varchar(255) null comment 'Attribute Group Name',
sort_order smallint default 0 not null comment 'Sort Order',
default_id smallint unsigned default 0 null comment 'Default ID',
attribute_group_code varchar(255) not null comment 'Attribute Group Code',
tab_group_code varchar(255) null comment 'Tab Group Code',
constraint EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE
unique (attribute_set_id, attribute_group_code),
constraint EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_NAME
unique (attribute_set_id, attribute_group_name),
constraint EAV_ATTR_GROUP_ATTR_SET_ID_EAV_ATTR_SET_ATTR_SET_ID
foreign key (attribute_set_id) references eav_attribute_set (attribute_set_id)
on delete cascade
)
comment 'Eav Attribute Group' charset = utf8;
create index EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_SORT_ORDER
on eav_attribute_group (attribute_set_id, sort_order);
which is by default populated with the following data
+------------------+----------------+--------------------+----------+----------+--------------------+--------------+
|attribute_group_id|attribute_set_id|attribute_group_name|sort_order|default_id|attribute_group_code|tab_group_code|
+------------------+----------------+--------------------+----------+----------+--------------------+--------------+
|4 |3 |General Information |2 |0 |general-information |NULL |
+------------------+----------------+--------------------+----------+----------+--------------------+--------------+
But I still get the exception
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '12-general-information' for key 'EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE'
During migration and before the exception is thrown, I can see the table value in the M2 Database changes from attribute_set_id = 3 to attribute_set_id = 12
Then the exception is thrown.
It seems it's not handling the existing attributes in M2 correctly.
I'm also encountering this integrity constraint violation even though there is no obvious violation of constraint EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE within the M1 database.
I've beein digging further into the code and it seems the records are transformed from the default attribute set that already exists on Magento 2 fresh installation with attribute_set_id = 4 to attribute_set_id = 12 during createProductAttributeSetStructures()
Which tries to match existing documents with the source documents and alters their IDs. However, these altered values still remain in the source records, and the data migration tool is trying to insert them once again after transforming them.
So we end up with a duplicate insert which fails.
I think if the tool has transformed an existing document in M2 to match the value in the source records from M1, it should also remove it from the source records to transform and insert in the migrateCustomAttributeGroups() step.
Then this redundant insert would be skipped.
This seems like a bug on the tool itself and not on the data from the source.
Basically the dataset inside M2 from these values before createProductAttributeSetStructures
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
|attribute_group_id|attribute_set_id|attribute_group_name |sort_order|default_id|attribute_group_code |tab_group_code|
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
|1 |1 |General |1 |1 |general |NULL |
|2 |2 |General |1 |1 |general |NULL |
|3 |3 |General |10 |1 |general |NULL |
|4 |3 |General Information |2 |0 |general-information |NULL |
|5 |3 |Display Settings |20 |0 |display-settings |NULL |
|6 |3 |Custom Design |30 |0 |custom-design |NULL |
|7 |4 |Product Details |10 |1 |product-details |basic |
|8 |4 |Advanced Pricing |40 |0 |advanced-pricing |advanced |
|9 |4 |Search Engine Optimization|30 |0 |search-engine-optimization|basic |
|10 |4 |Images |20 |0 |image-management |basic |
|11 |4 |Design |50 |0 |design |advanced |
|12 |4 |Autosettings |60 |0 |autosettings |advanced |
|13 |4 |Content |15 |0 |content |basic |
|14 |4 |Schedule Design Update |55 |0 |schedule-design-update |advanced |
|15 |5 |General |1 |1 |general |NULL |
|16 |6 |General |1 |1 |general |NULL |
|17 |7 |General |1 |1 |general |NULL |
|18 |8 |General |1 |1 |general |NULL |
|19 |4 |Bundle Items |16 |0 |bundle-items |NULL |
|20 |4 |Gift Options |61 |0 |gift-options |NULL |
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
Turns to this dataset after createProductAttributeSetStructures
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
|attribute_group_id|attribute_set_id|attribute_group_name |sort_order|default_id|attribute_group_code |tab_group_code|
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
|1 |1 |General |1 |1 |general |NULL |
|2 |2 |General |1 |1 |general |NULL |
|3 |12 |General |10 |1 |general |NULL |
|4 |12 |General Information |2 |0 |general-information |NULL |
|5 |12 |Display Settings |20 |0 |display-settings |NULL |
|6 |12 |Custom Design |30 |0 |custom-design |NULL |
|7 |9 |Product Details |10 |1 |product-details |basic |
|8 |9 |Advanced Pricing |40 |0 |advanced-pricing |advanced |
|9 |9 |Search Engine Optimization|30 |0 |search-engine-optimization|basic |
|10 |9 |Images |20 |0 |image-management |basic |
|11 |9 |Design |50 |0 |design |advanced |
|12 |9 |Autosettings |60 |0 |autosettings |advanced |
|13 |9 |Content |15 |0 |content |basic |
|14 |9 |Schedule Design Update |55 |0 |schedule-design-update |advanced |
|15 |18 |General |1 |1 |general |NULL |
|16 |30 |General |1 |1 |general |NULL |
|17 |55 |General |1 |1 |general |NULL |
|18 |51 |General |1 |1 |general |NULL |
|19 |9 |Bundle Items |16 |0 |bundle-items |NULL |
|20 |9 |Gift Options |61 |0 |gift-options |NULL |
|21 |40 |Product Details |10 |1 |product-details |basic |
|22 |40 |Advanced Pricing |40 |0 |advanced-pricing |advanced |
|23 |40 |Search Engine Optimization|30 |0 |search-engine-optimization|basic |
|24 |40 |Images |20 |0 |image-management |basic |
|25 |40 |Design |50 |0 |design |advanced |
|26 |40 |Autosettings |60 |0 |autosettings |advanced |
|27 |40 |Content |15 |0 |content |basic |
|28 |40 |Schedule Design Update |55 |0 |schedule-design-update |advanced |
|29 |40 |Bundle Items |16 |0 |bundle-items |NULL |
|30 |40 |Gift Options |61 |0 |gift-options |NULL |
+------------------+----------------+--------------------------+----------+----------+--------------------------+--------------+
And then it tries to insert again the records from 12-general-information in my case in step migrateCustomAttributeGroups
and throws the exception SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '12-general-information' for key 'EAV_ATTRIBUTE_GROUP_ATTRIBUTE_SET_ID_ATTRIBUTE_GROUP_CODE'
Any ideas on how to solve this ? It seems a lot of people are being affected by this.
If it's replacing the records that already exist, then shouldn't it remove them from the source records to skip inserting them again a second time?
What info can I provide that would help the team fix this bug?
Nope as there's no info from the devs to assist us .
I actually moved to other migration tools for the data and keep this one for the settings