Forums

Foreign key violation when upgrading from 5.7.5.13 to 8.5.2

Permalink
I need to upgrade from 5.7.5.13 to 8.5.2. Im always getting a foreign key violation:
An exception occurred while executing 'INSERT INTO CollectionAttributeValues (cID, cvID, akID, avID) VALUES (?, ?, ?, ?)' with params [284, "1", 5, null]: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`XYZ`.`CollectionAttributeValues`, CONSTRAINT `FK_BB9995FCB6561A7E` FOREIGN KEY (`akID`) REFERENCES `AttributeKeys` (`akID`))

This error comes from: 
$this->createSinglePage('/dashboard/system/files/thumbnails/options', 'Thumbnail Options', ['exclude_nav' => true, 'meta_keywords' => 'thumbnail, format, png, jpg, jpeg, quality, compression, gd, imagick, imagemagick, transparency']);

in Concrete\Core\Updater\Migrations\AbstractMigration createSinglePage
(…/src/Updater/Migrations/Migrations/Version20170202000000.php:44)
The mySQL user has all permission, including the REFERENCES permission
Can anyone help? Thanks in advance
 
ConcreteOwl replied on at Permalink Reply
ConcreteOwl
Take a look in the AttributeKeys table of the database for an akID of 5 ...
If it does not exist, or there are more than one, there is your problem
stabtoto replied on at Permalink Reply 1 Attachment
Thanks for the tip. I checked the AttributeKeys Table and i got exactly one entry with akId 5, see attached screenshot. any other guesses? greetings and stay healthy!
cahrens replied on at Permalink Best Answer Reply
I had the exact same issue with the same versions. I dropped the FK, then refreshed. I got a similar error with another table, so I dropped that FK too. I repeated this a few times and ended up dropping 4 or 5 FKs, but eventually the upgrade finished. It appears ok. I may give it a few days and then add those keys back in.

Sidenote - I looked at the data and the attempted SQL and can't figure out why those keys were preventing the updates/deletes from running.... oh well.

Also, after I got this all to work, I noticed another post with a similar issue, and a different solution by upgrading to another version first. https://www.concrete5.org/community/forums/installation/5.7.5.13-to-...
stabtoto replied on at Permalink Reply
It all worked out with this approach. I deleted the Foreign Keys for the tables: FileAttributeValues, CollectionAttributeValues and UserAttributeValues.
After the update the foreign keys were already created again, so I didnt have to create them again... Thanks a lot for your help and advice!