1

After running the command to upgrade the database I got a constraint violation error.

ERROR USERINFO: ALTER TABLE civicrm_case ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails...

I tried adding the foreign key in the database but that did not work. I got this error: MySQL ERROR 1005: Can’t create table (errno: 150)

The Foreign Key exists on the my live site's db so I imported a db backup from the lives site. Ran the db update command again and got the same error.

How do I prevent the migration from deleting the foreign key?

This is my civicrm_case:

CREATE TABLE civicrm_case (

id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID', case_type_id int(10) unsigned NOT NULL COMMENT 'FK civicrm_case_type.id', subject varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Short name of the case.', start_date date DEFAULT NULL COMMENT 'Date on which given case starts.', end_date date DEFAULT NULL COMMENT 'Date on which given case ends.', details text COLLATE utf8_unicode_ci COMMENT 'Details about the meeting (agenda, notes, etc).', status_id int(10) unsigned NOT NULL COMMENT 'Id of case status.', is_deleted tinyint(4) DEFAULT '0', PRIMARY KEY (id), KEY index_case_type_id (case_type_id), KEY index_is_deleted (is_deleted) ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  • That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2 – user4278 May 1 '18 at 1:18
  • have you moved the db (eg by taking a backup and reading in to a new or other db)? – petednz - fuzion May 2 '18 at 19:37
  • I imported a back up of the db after the db upgrade failed. I also tried importing the civicrm db from the live site. – Rdarris May 3 '18 at 14:51
2

A workaround to this is to comment out the SQL lines in the file that drop, modify and rebuild the FK. /CRM/Upgrade/Incremental/sql/4.6.18.mysql.tlp

This will have to be done for every upgrade with a db that does not include the FK.

1

That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2

Here is how that table should look

CREATE TABLE `civicrm_case` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID', `case_type_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_case_type.id', `subject` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Short name of the case.', `start_date` date DEFAULT NULL COMMENT 'Date on which given case starts.', `end_date` date DEFAULT NULL COMMENT 'Date on which given case ends.', `details` text COLLATE utf8_unicode_ci COMMENT 'Details about the meeting (agenda, notes, etc).', `status_id` int(10) unsigned NOT NULL COMMENT 'Id of case status.', `is_deleted` tinyint(4) DEFAULT '0', `created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the case was created.', `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the case (or closely related entity) was created or modified or deleted.', PRIMARY KEY (`id`), KEY `index_case_type_id` (`case_type_id`), KEY `index_is_deleted` (`is_deleted`), CONSTRAINT `FK_civicrm_case_case_type_id` FOREIGN KEY (`case_type_id`) REFERENCES `civicrm_case_type` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is in civicrm.domain.version?

  • The version of Civi is actually 4.7.13 – Rdarris May 2 '18 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.