1

I have two databases (com and usa) with a table with the following schema:

Create Table: CREATE TABLE `eav_attribute` (
  `attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Id',
  `entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
  `attribute_code` varchar(255) NOT NULL COMMENT 'Attribute Code',
  PRIMARY KEY (`attribute_id`),
  UNIQUE KEY `UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`),
  KEY `IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID` (`entity_type_id`),
  CONSTRAINT `FK_EAV_ATTRIBUTE_ENTITY_TYPE_ID_EAV_ENTITY_TYPE_ENTITY_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=212 DEFAULT CHARSET=utf8 COMMENT='Eav Attribute'
1 row in set (0.00 sec)

I'd like to make the attribute_ids of USA the same as COM (where code + type match). This query shows my expected result by joining the two tables:

SELECT usa_attribute.attribute_id,
       usa_attribute.entity_type_id,
       usa_attribute.attribute_code,
       com_attribute.attribute_id,
       com_attribute.entity_type_id,
       com_attribute.attribute_code
FROM eav_attribute usa_attribute
LEFT JOIN com.eav_attribute com_attribute
          ON ( usa_attribute.entity_type_id = com_attribute.entity_type_id
           AND usa_attribute.attribute_code = com_attribute.attribute_code );

+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+
| attribute_id | entity_type_id | attribute_code                 | attribute_id | entity_type_id | attribute_code                 |
+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+
|          615 |              1 | confirmation                   |           16 |              1 | confirmation                   |
|          616 |              1 | created_at                     |          481 |              1 | created_at                     |
|          602 |              1 | created_in                     |            3 |              1 | created_in                     |
|          612 |              1 | default_billing                |           13 |              1 | default_billing                |
|          613 |              1 | default_shipping               |           14 |              1 | default_shipping               |
|          782 |              1 | disable_auto_group_change      |          571 |              1 | disable_auto_group_change      |
|          610 |              1 | dob                            |           11 |              1 | dob                            |
|          647 |              3 | children                       |           46 |              3 | children                       |
|          656 |              3 | children_count                 |           55 |              3 | children_count                 |
|          706 |              3 | custom_apply_to_products       |          494 |              3 | custom_apply_to_products       |
|          649 |              3 | custom_design                  |           48 |              3 | custom_design                  |
|          651 |              3 | custom_design_from             |           50 |              3 | custom_design_from             |
|          652 |              3 | custom_design_to               |           51 |              3 | custom_design_to               |
|          654 |              3 | custom_layout_update           |           53 |              3 | custom_layout_update           |
|          705 |              3 | custom_use_parent_settings     |          493 |              3 | custom_use_parent_settings     |
|          658 |              3 | default_sort_by                |          486 |              3 | default_sort_by                |
|          635 |              3 | description                    |           34 |              3 | description                    |
|          640 |              3 | display_mode                   |           39 |              3 | display_mode                   |
|          707 |              3 | filter_price_range             |          495 |              3 | filter_price_range             |
|          636 |              3 | image                          |           35 |              3 | image                          |
|          704 |              3 | include_in_menu                |          490 |              3 | include_in_menu                |
|          633 |              3 | is_active                      |           32 |              3 | is_active                      |
|          642 |              3 | is_anchor                      |           41 |              3 | is_anchor                      |
|          810 |              3 | is_spacer                      |          599 |              3 | is_spacer                      |
|          641 |              3 | landing_page                   |           40 |              3 | landing_page                   |
|          655 |              3 | level                          |           54 |              3 | level                          |
|          639 |              3 | meta_description               |           38 |              3 | meta_description               |
|          638 |              3 | meta_keywords                  |           37 |              3 | meta_keywords                  |
|          637 |              3 | meta_title                     |           36 |              3 | meta_title                     |
|          632 |              3 | name                           |           31 |              3 | name                           |
|          653 |              3 | page_layout                    |           52 |              3 | page_layout                    |
|          643 |              3 | path                           |           42 |              3 | path                           |
|          646 |              3 | path_in_store                  |           45 |              3 | path_in_store                  |
|          644 |              3 | position                       |           43 |              3 | position                       |
|          719 |              3 | thumbnail                      |          568 |              3 | thumbnail                      |
|          634 |              3 | url_key                        |           33 |              3 | url_key                        |
|          648 |              3 | url_path                       |           47 |              3 | url_path                       |
|          679 |              4 | color                          |           76 |              4 | color                          |
|          772 |              4 | color_description              |          469 |              4 | color_description              |
|          667 |              4 | cost                           |           64 |              4 | cost                           |
|          788 |              4 | country_of_manufacture         |          577 |              4 | country_of_manufacture         |
|          702 |              4 | created_at                     |          488 |              4 | created_at                     |
|          691 |              4 | custom_design                  |           86 |              4 | custom_design                  |
|          692 |              4 | custom_design_from             |           87 |              4 | custom_design_from             |
|          693 |              4 | custom_design_to               |           88 |              4 | custom_design_to               |
|          694 |              4 | custom_layout_update           |           89 |              4 | custom_layout_update           |
|          660 |              4 | description                    |           57 |              4 | description                    |
|          740 |              4 | e3ac01                         |          539 |              4 | e3ac01                         |
|          741 |              4 | e3ac02                         |          540 |              4 | e3ac02                         |
|          742 |              4 | e3ac03                         |          541 |              4 | e3ac03                         |
|          743 |              4 | e3ac04                         |          542 |              4 | e3ac04                         |
|          744 |              4 | e3ac05                         |          543 |              4 | e3ac05                         |
|          745 |              4 | e3ac06                         |          544 |              4 | e3ac06                         |
|          746 |              4 | e3ac07                         |          545 |              4 | e3ac07                         |
|          747 |              4 | e3ac08                         |          546 |              4 | e3ac08                         |
|          748 |              4 | e3ac09                         |          547 |              4 | e3ac09                         |
|          730 |              4 | e3cdes                         |          456 |              4 | e3cdes                         |
|          735 |              4 | e3col                          |          461 |              4 | e3col                          |
|          725 |              4 | e3cono                         |          451 |              4 | e3cono                         |
|          749 |              4 | e3cost                         |          548 |              4 | e3cost                         |
|          731 |              4 | e3csds                         |          457 |              4 | e3csds                         |
|          758 |              4 | e3ris1                         |          553 |              4 | e3ris1                         |
+--------------+----------------+--------------------------------+--------------+----------------+--------------------------------+

Before I run the query I can confirm that USA has no attribute_id = 51:

# There is no attribute_id before I begin the copy
mysql> select * from eav_attribute where attribute_id = 51;
Empty set (0.00 sec)

However the update causes a FK constraint error. Any idea why? How do I do this update successfully?

UPDATE eav_attribute usa_attribute, 
       com.eav_attribute com_attribute
SET usa_attribute.attribute_id = com_attribute.attribute_id
WHERE usa_attribute.entity_type_id = com_attribute.entity_type_id
      AND usa_attribute.attribute_code = com_attribute.attribute_code

Upholding foreign key constraints for table 'eav_attribute', entry '51', key 1 would lead to a duplicate entry

1 Answer 1

1

Ok, so I should have guessed - when getting a FK error I should check other tables referencing this table to see if ON UPDATE CASCADE might cause issues. To do this I did the following query:

SELECT table_name, 
       column_name, 
       constraint_name, 
       referenced_table_name, 
       referenced_column_name 
FROM information_schema.key_column_usage 
WHERE referenced_table_name = 'eav_attribute' AND table_schema = 'usa';

+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+
| table_name                           | column_name  | constraint_name                                                  | referenced_table_name | referenced_column_name |
+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+
| catalog_category_entity_datetime     | attribute_id | FK_CAT_CTGR_ENTT_DTIME_ATTR_ID_EAV_ATTR_ATTR_ID                  | eav_attribute         | attribute_id           |
| catalog_category_entity_decimal      | attribute_id | FK_CAT_CTGR_ENTT_DEC_ATTR_ID_EAV_ATTR_ATTR_ID                    | eav_attribute         | attribute_id           |
| catalog_category_entity_int          | attribute_id | FK_CAT_CTGR_ENTT_INT_ATTR_ID_EAV_ATTR_ATTR_ID                    | eav_attribute         | attribute_id           |
| catalog_category_entity_text         | attribute_id | FK_CAT_CTGR_ENTT_TEXT_ATTR_ID_EAV_ATTR_ATTR_ID                   | eav_attribute         | attribute_id           |
......many more
| googlebase_attributes                | attribute_id | GOOGLEBASE_ATTRIBUTES_ATTRIBUTE_ID                               | eav_attribute         | attribute_id           |
| salesrule_product_attribute          | attribute_id | FK_SALESRULE_PRD_ATTR_ATTR_ID_EAV_ATTR_ATTR_ID                   | eav_attribute         | attribute_id           |
| weee_tax                             | attribute_id | FK_WEEE_TAX_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID              | eav_attribute         | attribute_id           |
+--------------------------------------+--------------+------------------------------------------------------------------+-----------------------+------------------------+

I was then able to do a quick select against these tables:

select * from catalog_category_entity_datetime where attribute_id = 51;
select * from catalog_category_entity_decimal where attribute_id = 51;
select * from catalog_category_entity_int where attribute_id = 51;
select * from catalog_category_entity_text where attribute_id = 51;
..... many more
select * from googlebase_attributes where attribute_id = 51;
select * from salesrule_product_attribute where attribute_id = 51;
select * from weee_tax where attribute_id = 51;

Then I could see that two tables had references to my first table (eav_attribute) with the values '51'.

Not sure how these got there, but since they dont match the FK I could happily delete them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.