Major Bug in 4.5 Upgrade: Mixed Keys Being Referenced

[PrincessAng417]

This is going to be a tad difficult to explain, but I am going to try my best.

Upon upgrade, my Custom Data Groups on Case types were incorrect.  Custom Data Groups on outcome-type Cases were showing themselves on our application-type Cases.  If I manually re-corrected them to appear on the "appropriate" Case Type, the change would stick.

So I went into the DB custom_group table. 

• In the "extends_entity_column_value" field, for the Custom Groups extending Cases that I hadn't manually updated through the Civi interface, the values match the Case Type reference (from the option_value table, group 26-- I don't know if that group number is standard or not). 
• For the Custom Groups that I *had* updated manually in the interface, the values were *different*.

For the Custom Groups that I *had* updated manually, the reference matches the Case Type in the URL from  /civicrm/a/#/caseType/NN (where NN is some Case Type reference).  But that Case Type number is not the same as the Case Type reference in the option_value table.  In the option_value table, the keys were assigned in order of the option being created.  In the URL Case Type key, it seems to have assigned a key in order of alphabetical listing of the Case Type Name.  Of course, the problem is: they're not the same. 

So the custom_group table seems to be referencing the "new" Case Type IDs (and I can't find where those are stored).  I presume {almost?} everything else is referencing the option_value table.  But in 4.4.x, those Case Type IDs meant something different.  So now there is a mix of keys and all of the Custom Data, with regard to Cases, is a mess. (Activities and Contacts seem to be fine, which is why I am posting this here and not on the 4.5 forum.)

[totten]

There are loosely two ways to rationalize the bug; either: (a) the case-type IDs were intended to change during the upgrade -- and some FK's (like civcrm_custom_group.extends_entity_column_value) were left behind, or (b) the case-type IDs were intended to stay the same during the upgrade -- but didn't.

Skimming the migration code (CRM/Upgrade/Incremental/sql/4.5.alpha1.mysql.tpl), it appears to revise civicrm_case.case_type_id to use the new ID's instead of the old OVs -- suggesting that (a) was the intent. However, I think this issue demonstrates that it's better to preserve the ID's. (Custom-data isn't the only place where we have soft FKs on case_type_id -- there are also reports, saved-searches, and third-party API code.)

The sucky thing here is that (for anyone who upgraded to 4.5.0) we've basically lost the old case-type IDs (i.e. the records in civicrm_option_value have been deleted), so I don't think there's a way to automatically cleanup. For anyone who's upgraded to 4.5.0, I think the best we can do is advise them to examine/validate any custom-data, smart-groups, reports, or downstream code which references specific case-types.

For new upgrades (e.g. 4.4.x => 4.5.1), I think we should change the logic in 4.5.alpha1.mysql.tpl so that it preserves the IDs. To wit:

Code: [Select]

-- OLD: Assign new IDs in a way which probabilistically matches old values in some common cases
INSERT IGNORE INTO civicrm_case_type
  (name, ...)
  SELECT name, ...
  FROM civicrm_option_value
  WHERE option_group_id = @option_group_id_case_type;

-- NEW: Assign new IDs to explicitly match old IDs
INSERT IGNORE INTO civicrm_case_type
  (id, name, ...)
  SELECT value, name, ...
  FROM civicrm_option_value
  WHERE option_group_id = @option_group_id_case_type;
ALTER TABLE table_name AUTO_INCREMENT = (select max(id) from civicrm_case_type);
-- NOTE: I haven't used ALTER TABLE like this before - it may need some rewriting/tweaking.

I've filed an issue: https://issues.civicrm.org/jira/browse/CRM-15343

[PrincessAng417]

I am so thankful that you understood the issue! I didn't know how on earth I could better explain what was going on.

Thank you for filing such a comprehensive description on the issue-- it helped me to understand it even more.