5.7.5.13 to 5.8.2.1 Fails "CollectionAttributeValues Integrity constraint violation"

Permalink December 11, 2019 at 7:38 PM

I cannot upgrade from 5.7.5.13 to 5.8.2.1 (available in the auto updater) or 5.8.5.2 (manually). I CAN however upgrade to 5.8.1.0, but not any further.

I keep encountering this specific error:

An exception occurred while executing 'INSERT INTO CollectionAttributeValues (cID, cvID, akID, avID) VALUES (?, ?, ?, ?)' with params ["298", "1", 5, null]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sandbox_tanz`.`CollectionAttributeValues`, CONSTRAINT `FK_BB9995FCA2A82A5D` FOREIGN KEY (`avID`) REFERENCES `AttributeValues` (`avID`))

The error then persists and I cannot view the site any longer until I fully restore both files and database.

According to this string of github issues, it "should" be fixed, but I am still experiencing the problem.https://github.com/concrete5/concrete5/issues/6035...

I've investigated a few possibilities, turned strict mode off on MySQL with no effect.

andreatestdemo replied on Feb 29, 2020 at 2:22 pm Permalink Reply

I have the same problem.

An exception occurred while executing 'INSERT INTO CollectionAttributeValues (cID, cvID, akID, avID) VALUES (?, ?, ?, ?)' with params ["518", "1", 5, null]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`sb_20200229a`.`CollectionAttributeValues`, CONSTRAINT `FK_BB9995FCB6561A7E` FOREIGN KEY (`akID`) REFERENCES `AttributeKeys` (`akID`))

I tried to upgrade from 5.7.5.13 to 8.2.1.

martinbuerge replied on Mar 2, 2020 at 4:41 pm Permalink Reply

I think the problem isn't related to the one you linked on github (duplicate keys vs integrity constraint violation on foreign key)

It seems to be a bug of mysql or something. Recreating the foreign keys with the exact same values helped. After it i could upgrade.
In one case I exported the database and recreated it and after that the upgrade also worked.

andreatestdemo replied on Mar 13, 2020 at 11:16 am Permalink Reply

I have the exact same problem and I have been trying for days and hours now. 5.7.5.13 to 8.1.0 works fine. 8.1.0 to 8.2.1 creates the error you mentioned. Somehow I managed once but even though I wrote down almost every step I took I cannot reproduce it successfully. I will try if duplicating the database helps.

EDIT: It helped! Thank you martinbuerge, you made my day.

The following path worked for me:
- upgrade from 5.7.5.13 to 8.1.0
- duplicate the database and use the new one
- upgrade from 8.1.0 to 8.2.1