Integrity constraint violation : SHIPPERHQ_QUOTE_ITEM_DETAIL_QUOTE_ITEM_ID_QUOTE_ITEM_ITEM_ID

[isevchris]

On table: shipperhq_quote_item_detail there is a foreign key constraint on quote_item_id which references quote_item.item_id - ON DELETE CASCADE ON UPDATE RESTRICT

This can get tripped at:

ShipperHQ\Shipper\Helper\CarrierGroup->saveCarrierGroupItem() called at [vendor/shipperhq/module-shipper/src/Model/Carrier/Shipper.php:912]

The log states that the following query is being executed:

INSERT INTO `shipperhq_quote_item_detail` (`carrier_group_id`, `carrier_group`) VALUES (?, ?)

That in itself shouldn't be possible as there's no value (or default) for quote_item_id

This seems to get triggered (on a client's install) when adding a new item to the cart, after visiting the checkout - to replicate:

1. Add a product to your cart
2. Go to the checkout
3. Add a delivery address and select a shipment type
4. Proceed to the payment step
5. (realise you've forgotten something and) Return to the shop
6. Attempt to add another product to your cart

This should result in a "We can't add this item to your basket right now." error - with the above details in the system.log

[wsagen]

That foreign key was removed from our application some time ago. I assume you are running the latest version of our software and have ensured after upgrading you have completed the update to ensure any database changes have taken effect. https://docs.shipperhq.com/installing-magento-2-shipperhq-extension/#Updating_Existing_Installation

Assuming you have completed the above, there may be a step up of versions from old version to recent, and somehow that clean up to remove this foreign key was not executed.

You can manually remove the foreign key via

SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'shipperhq_quote_item_detail' AND TABLE_SCHEMA = '' AND REFERENCED_TABLE_NAME IS NOT NULL;

This should give you the correct foreign key constraint name.

You can manually remove it from your database by copying the above foreign key constraint name into the following command ALTER TABLE shipperhq_quote_item_detail DROP FOREIGN KEY ;

[wsagen]

We will reach out via email also to ensure you have resolved this issue.

[isevchris]

Thanks, that seems to have fixed it.

The site has the latest version of the plugin as listed on the Adobe Commerce Marketplace (20.53.2), however it appears there is a newer version available (20.54.1).

So I don't know whether the foreign key restriction was removed between those versions, or whether something went awry during an update and it wasn't removed - but removing it removed the issue.