CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints

[DerekL]

Hi Folks,

In trying to upgrade my CiviCRM deployment I came across the following error. I have unfortunately no idea how to deal with such a thing. Could you perhaps lend a hand?

UserInfo   ALTER TABLE `civicrm_participant` CHANGE COLUMN `event_id` `event_id` INT(10) UNSIGNED NOT NULL, CHANGE COLUMN `contact_id` `contact_id` INT(10) UNSIGNED NOT NULL [nativecode=1832 ** Cannot change column 'contact_id': used in a foreign key constraint 'FK_civicrm_participant_contact_id']
DebugInfo   ALTER TABLE `civicrm_participant` CHANGE COLUMN `event_id` `event_id` INT(10) UNSIGNED NOT NULL, CHANGE COLUMN `contact_id` `contact_id` INT(10) UNSIGNED NOT NULL [nativecode=1832 ** Cannot change column 'contact_id': used in a foreign key constraint 'FK_civicrm_participant_contact_id']

I'm running mysql 5.6 and PHP 5.4 on D7.


-------------Update-------------------
I tried removing the offending FK, only to be greeted with another FK constraint error, this time on FK_civicrm_participant_event_id.
At this point in time I decided to upgrade to 4.3.8 from 4.2.6. This worked without issue, but attempting an upgrade from 4.3.8 to 4.4.4 failed at the exact same spot.  Any ideas?
Thanks!
-d

[DerekL]

It seems something was skipped somewhere. After about 10 DB restores I removed the FK's from the DB and stepped up minor versions until I reached 4.4.4. Phew.

Now to put back the FKs...onward!

Thanks
-d

[wrender]

Was really happy to see that someone else is also having the same issue.  I am getting the exact same error when trying to upgrade from 4.3.8 to 4.4.4.  I am just wondering, how do you remove and then re-add the foreign keys?  Also, will removing and re-adding the foreign keys cause any data to be lost?

Here is my error during upgrade:

Code: [Select]

[Error: Upgrade DB to 4.4.alpha1: SQL]
Error Field Error Value
Type DB_Error
Code -1
Message DB Error: unknown error
Mode 16
UserInfo ALTER TABLE `civicrm_participant` CHANGE COLUMN `event_id` `event_id` INT(10) UNSIGNED NOT NULL, CHANGE COLUMN `contact_id` `contact_id` INT(10) UNSIGNED NOT NULL [nativecode=1832 ** Cannot change column 'contact_id': used in a foreign key constraint 'FK_civicrm_participant_contact_id']
DebugInfo ALTER TABLE `civicrm_participant` CHANGE COLUMN `event_id` `event_id` INT(10) UNSIGNED NOT NULL, CHANGE COLUMN `contact_id` `contact_id` INT(10) UNSIGNED NOT NULL [nativecode=1832 ** Cannot change column 'contact_id': used in a foreign key constraint 'FK_civicrm_participant_contact_id']

[Hershel]

No, removing/adding keys won't lose data. The recommended method of insuring schema integrity is documented here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

[wrender]

I tried rebuilding the database using the "ensuring schema integrity" instructions.  No luck.  I am still getting the same foreign key constraint errors.  I will try removing the foreign keys now.

[wrender]

In case anyone else is having this issue. Here is how I fixed it. Thanks for the help above people.

- Login to phpmyadmin, navigate to the "civicrm_participant" table. Select "structure" -> scroll down and select "Relation view".  Set all of the relations to blank field to disable them.
- Put the CiviCRM 4.4.4 code in place, and run the regular upgrade process (It now runs without error).
- Put the foreign key constraints back in place and rebuild the database by following the instructions here:  http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades

[Eileen]

I have also hit this - the problem seems to be related to mysql server version (we are running 5.6.17)

http://stackoverflow.com/questions/17015844/mysql-5-6-foreign-key-constraint-error-didnt-occur-in-5-5

We don't have a data integrity error as the table in question is empty
 

[Eileen]

https://issues.civicrm.org/jira/browse/CRM-15196