Upgrade Aimeos 21.4.1 (TYPO3)

[hansklatt]

Hello,
i have a typo3 v10 page with Aimeos 21.4.1 with PHP 7.4.

I executed the update-script and get this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails ("database_user"."#sql-7e1d_bbdae10", CONSTRAINT "fk_msproli_pid" FOREIGN KEY ("parentid") REFERENCES "mshop_product" ("id") ON DELETE CASCADE ON UPDATE CASCADE): ALTER TABLE mshop_product_list ADD CONSTRAINT fk_msproli_pid FOREIGN KEY (parentid) REFERENCES mshop_product (id) ON UPDATE CASCADE ON DELETE CASCADE

I think the problem is the mshop_product_list table, but i dont know why.

How can i fix this Problem? And we want to stay on TYPO3 10.

The Shop work otherwise regular and the CMS is composer-based.

Thanks.

[boettner]

Hi Hans,

you have records in mshop_product_list referencing records in mshop_product which arent´t present anymore. Try to identify those records with queries like this. I can´t combine them properly from the top of my head atm. But maybe this helps as a first step:
SELECT GROUP_CONCAT(DISTINCT parentid) FROM `mshop_product_list`
SELECT GROUP_CONCAT(DISTINCT id) FROM `mshop_product`

They return two comma separated lists which you have to compare for the missing records to identify mshop_product_list.parentid that is not available in mshop_product.id. Delete those mshop_product_list entries and it should work.

Maybe you get similar errors for other relations. You could adapt the SQL for all those relations accordingly.

Best
Robert.

[aimeos]

The problems occurs if you allow the TYPO3 install tool to remove the foreign key constraints Aimeos is using (TYPO3 doesn't support foreign key constraints). If you delete products/categories/attributes, the references in the mshop_*_list tables are not removed automatically. When the setup script tries to re-add the foreign key constraints, this fails because the database complains about those left over records without a related record in the parent table.

Use this SQL statement to remove the old records e.g. in the mshop_product_lists table:

Code: Select all

DELETE mshop_product_list
FROM mshop_product_list
LEFT JOIN mshop_product ON mshop_product_list.parentid=mshop_product.id
WHERE mshop_product.id IS NULL