Page Comparison

After modules reinstallation you may face the next issue:

Image Removed

Image Added

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cabinets_staging`.`tnw_salesforce_entity_queue_relation`, CONSTRAINT `FK_CCC1AE8889A03ECB2CD24E793F468CD5` FOREIGN KEY (`parent_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON D), query was: INSERT INTO `tnw_salesforce_entity_queue` (`queue_id`,`entity_id`,`entity_load`,`entity_load_additional`,`entity_type`,`object_type`,`sync_type`,`sync_attempt`,`status`,`code`,`description`,`website_id`,`transaction_uid`,`additional_data`,`identify`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `queue_id` = VALUES(`queue_id`), `entity_id` = VALUES(`entity_id`), `entity_load` = VALUES(`entity_load`), `entity_load_additional` = VALUES(`entity_load_additional`), `entity_type` = VALUES(`entity_type`), `object_type` = VALUES(`object_type`), `sync_type` = VALUES(`sync_type`), `sync_attempt` = VALUES(`sync_attempt`), `status` = VALUES(`status`), `code` = VALUES(`code`), `description` = VALUES(`description`), `website_id` = VALUES(`website_id`), `transaction_uid` = VALUES(`transaction_uid`), `additional_data` = VALUES(`additional_data`), `identify` = VALUES(`identify`)

The foreign key exception is related to the module re-installation.

The Magento store applied sql patches list in the patch_list table.

Actually, when we do the second time installation, Magento has our patches list in the patch_list table already and "think" all of them were applied and skip them.

The "Foreign key SQL error" happens because the second module installation misses some foreign key properties for the tnw_salesforce_entity_queue_relation table.

Solutions

I. Correct way (recommended)

Correct the “patch_list“ table contend to allow apply the necessary patch again

Remove related patch record:

Code Block

language	sql

DELETE FROM patch_list WHERE patch_name = 'TNW\\Salesforce\\Setup\\Patch\\Schema\\QueueRelationForeignKeyFix';

Apply patch again:
Insert excerpt
_after_installation
_after_installation
nopanel true

II. Alternative fix (NOT recommended):

Expand

Check the tnw_salesforce_entity_queue_relation table foreign keys
Code Block
language sql
SHOW CREATE TABLE tnw_salesforce_entity_queue_relation;

Analyze foreign keys data:

Code Block

language	sql

CREATE TABLE `tnw_salesforce_entity_queue_relation` (
    `queue_id` varchar(32) NOT NULL,
    `parent_id` varchar(32) NOT NULL,
    UNIQUE KEY `TNW_SALESFORCE_ENTITY_QUEUE_RELATION_QUEUE_ID_PARENT_ID` (`queue_id`,`parent_id`),
    KEY `FK_CCC1AE8889A03ECB2CD24E793F468CD5` (`parent_id`),
    CONSTRAINT `FK_BC913D0C23021919FC91247BBA30B1FE` FOREIGN KEY (`queue_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON DELETE CASCADE,
    CONSTRAINT `FK_CCC1AE8889A03ECB2CD24E793F468CD5` FOREIGN KEY (`parent_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tnw_salesforce_entity_queue_relation'

This table has 2 foreign keys:

Code Block
language sql
FK_BC913D0C23021919FC91247BBA30B1FE
Code Block
language sql
FK_CCC1AE8889A03ECB2CD24E793F468CD5

We need to drop these 2 keys:

Code Block

language	sql

alter table tnw_salesforce_entity_queue_relation drop foreign key FK_BC913D0C23021919FC91247BBA30B1FE;
alter table tnw_salesforce_entity_queue_relation drop foreign key FK_CCC1AE8889A03ECB2CD24E793F468CD5;

Add these keys with the necessary properties:

Code Block

language	sql

alter table tnw_salesforce_entity_queue_relation
    add constraint queue_relation_queue_queue_id_fk
        foreign key (queue_id) references tnw_salesforce_entity_queue (queue_id)
            on update cascade on delete cascade;

alter table tnw_salesforce_entity_queue_relation
    add constraint queue_relation_queue_parent_id_fk
        foreign key (parent_id) references tnw_salesforce_entity_queue (queue_id)
            on update cascade on delete cascade;

Versions Compared

Old Version 4

New Version Current

Key

Solutions

I. Correct way (recommended)

II. Alternative fix (NOT recommended):

Related articles