CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
Pages: [1]

Author Topic: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints  (Read 1223 times)

DerekL

  • I post frequently
  • ***
  • Posts: 132
  • Karma: 1
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.1.54
  • PHP version: 5.2.17
CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 02, 2014, 10:57:17 am
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
« Last Edit: April 02, 2014, 09:07:32 pm by DerekL »

DerekL

  • I post frequently
  • ***
  • Posts: 132
  • Karma: 1
  • CiviCRM version: 4.5.5
  • CMS version: Drupal 7.34
  • MySQL version: 5.1.54
  • PHP version: 5.2.17
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 03, 2014, 10:22:28 am
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

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 05, 2014, 08:55:17 am
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4640
  • Karma: 176
    • CiviHosting
  • CiviCRM version: Latest
  • CMS version: Mostly WordPress and Drupal
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 06, 2014, 11:09:28 am
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
CiviHosting and CiviOnline -- The CiviCRM hosting experts, since 2007

See here for the official: What to do if you think you've found a bug.

wrender

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 07, 2014, 08:09:08 am
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

  • I’m new here
  • *
  • Posts: 26
  • Karma: 0
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
April 08, 2014, 04:20:20 pm
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

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
August 27, 2014, 03:57:00 pm
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
 
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints
August 27, 2014, 04:35:36 pm
https://issues.civicrm.org/jira/browse/CRM-15196
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Upgrading CiviCRM (Moderator: Deepak Srivastava) »
  • CiviCRM 4.3.8 -> 4.4.4 Upgrade Error - FK Constraints

This forum was archived on 2017-11-26.