Upgrade fail 4.4.9 > 4.5.1 re: case_type_id foreign key constraint.

[Upperholme]

I'm working on an upgrade form 4.4.9 to 4.5.1 on a Wordpress site. I'm taking the upgrade in small steps, so moving to 4.5.1 (in order to avoid the mess-up of CiviCase case type ids that happened in 4.5.0) prior to then going to 4.5.6.

My upgrade process stalls 4.5.alpha 1 with the following error:

[Error: Upgrade DB to 4.5.alpha1: SQL]
Type:   DB_Error
Code:   -3
Message:   DB Error: constraint violation
Mode:   16
UserInfo:   ALTER TABLE civicrm_case MODIFY case_type_id int(10) unsigned COLLATE utf8_unicode_ci NULL COMMENT 'FK to civicrm_case_type.id', ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) ON DELETE SET NULL [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`newdemand`.`#sql-166d7_137`, CONSTRAINT `FK_civicrm_case_case_type_id` FOREIGN KEY (`case_type_id`) REFERENCES `civicrm_case_type` (`id`) ON DELETE SET NULL)]

Should I skip 4.5.1 and try to go straight to a later version?

Any advise welcome on how to approach this.

[Upperholme]

Answering my own post:

Got round this as follows:

1. Located the correct sql files in CRM/Upgrade/Incremental/sql  - the error message indicates where in the upgrade process the problem occurs.

2. Edit the sql file to turn off the foreign key check just prior to the offending sql statement (add in SET foreign_key_checks = 0;)

3. Save the edited sql file and run the upgrade a gain on a copy of the db that had been backed up just prior to the failed upgrade attempt.

4. Upgrade completes successfully - hurrah!

5. Run the upgraded db through the schema integrity check as described at: http://wiki.civicrm.org/confluence/display/CRMDOC/Ensuring+Schema+Integrity+on+Upgrades - remembering to include any tables created by extensions.

6. Take a look to see that stuff works.

7. Breathe sigh of relief and wonder why this issues occur in the first place? After all, I ran the db through the integrity check prior to beginning the upgrade so I can't really understand why it should be non-standard? Wondering if it might be down to the presence of tables introduced by extensions (in this case the gift Aid extensions are in place and CiviVolunteer, both of which implement their own tables.

Currently the upgrade documentation makes no mention of how installed extensions should be handled. Any thoughts on best practice on that issue?

Hopefully this might help others caught in similar boats in the future.