Details
Description
(@Kurund, if this makes sense, can it be reassigned to someone on the India team?)
(Based on http://forum.civicrm.org/index.php/topic,34236.0.html )
In the upgrade from CiviCRM 4.4.x to 4.5.0 (CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl), the list of case-types is read from civicrm_option_value and migrated to civicrm_case_type. This migration may change the case-type-id[APPENDIX]. The original 4.5.alpha1.mysql.tpl attempts to address this change by updating civicrm_case.case_type_id to match the new IDs.
Unfortunately, this is not enough – it fails to migrate case-type-id references in custom-data groups, in reports, in saved-searches, or in custom-code/third-party-code. Rather than try to hunt-down all of these, we should simply preserve the original IDs. Specific recommendations:
1. To fix/avoid the problem for anyone who upgrades 4.4.x=>4.5.1:
1.a. In 4.5.alpha1.mysql.tpl, find the "INSERT IGNORE INTO civicrm_case_type" statement. Set the "id" based on the old "value.
1.b. In 4.5.alpha1.mysql.tpl, update the auto-increment counter to avoid problems in future inserts. ("ALTER TABLE tablename AUTO_INCREMENT = 123;")
1.c. In 4.5.alpha1.mysql.tpl, remove the migration logic for civicrm_case.case_type_id. This is no longer needed if the IDs are the same.
2. Regarding cleanup for sites that did a =>4.5.0 upgrade:
2.a. I don't think it's possible for us to automatically repair sites. The upgrade to 4.5.0 destroyed the old option-value records. At best, if one had an old backup (pre-4.5.0), one might be able to extract the details, but that's going to be a finnicky process. I'm +1 for simply advising admins of the problem so that they can address it
2.b. If a site is upgrading 4.5.0=>newer and if it has any CiviCase data, then the upgrader should display a post-upgrade message: "Warning: If you use CiviCase, it is possible that previous upgrades corrupted some CiviCase metadata. If you have not already done so, please identify any custom field sets, smart groups, or reports which refer to CiviCase and ensure that they are properly configured."
------------------
[APPENDIX]
In some simple cases, the new case-type-id might still match the old case-type-option-value. However, there are a number of ways to break this. To name one:
1. Do a clean 4.4 install with the two case-types (#1 housing support; #2 adult day-care referral)
2. Delete the first one (housing support).
3. Run the upgrade
4. Observe: Adult day-care referral now has id #1.