CiviCRM Community Forums (archive)

I am hoping that someone has come across this similar issue.

I did an incremental upgrade of a civicrm 1.9.11960 install when version 4.1.2 had just come out, and while I had lots of difficulties with the 1.9 -> 2.0 increment, the rest of the increments went through smoothly.  I followed these increments:

1.9.11960   -> 2.0
2.0          -> 2.1
2.1          -> 2.2.6
2.2.6       -> 3.0
3.0          -> 3.1
3.1          -> 3.3.5
3.3.5       -> 3.4.8
3.4.8       -> 4.0.8
4.0.8       -> 4.1.2

Along with a drupal 5 to 6 to 7 upgrade.

Upon initial analysis, the resulting system was in pretty great shape.  There were a few minor bugs that I had found but the system and the data seemed to be in great shape. 

Until I looked deeper and found that some of the custom fields data never got migrated / converted to the new table formats.

In this implementation we have a set of custom fields called 'Patient Services' that are of type 'any' and used for 'activities'.  There are 3 fields, 2 alphanumeric checkboxes and 1 textarea.  I see that the table was made to hold this data per activity.  Here is the 'SHOW CREATE TABLE' result:

| custom_value_1_Patient_Services | CREATE TABLE `custom_value_1_Patient_Services` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Default MySQL primary key',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Table that this extends',
  `Patient_Services` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Type_of_Call` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Medical_Treatment_Question` text COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_entity_id` (`entity_id`),
  KEY `FK_custom_value_1_Patient_Services_entity_id` (`entity_id`),
  KEY `INDEX_Patient_Services` (`Patient_Services`),
  KEY `INDEX_Type_of_Call` (`Type_of_Call`),
  CONSTRAINT `FK_custom_value_1_Patient_Services_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10322 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Something I have noticed is why these custom value tables don't have the 'civicrm_' table prefix like all other civicrm tables, but this is not a main concern.

When adding a new activity to a contact and filling out these custom fields, the custom field values properly save to this table and properly display for that activity. 

My concern is that this data is missing for all of these fields for all existing contacts that had values in the old 1.9 system.

So far, the missing data seems only related to these specific custom fields, I have not confirmed other custom field types missing data yet.

Are there known issues with the custom fields upgrade process?  I am not sure which version first has the new custom fields data structures, but could it be that I need to change my incremental upgrade process to include a version that contains possible fixes to this?  Maybe it was a bug in the 1.9 system with how these custom fields were created and how the data was stored that was not accounted for in the upgrade scripts?

Any help is appreciated here.  In the meantime I will be debugging the upgrade scripts and post my findings here in case the civicrm community doesn't know about these issues.

Thanks,
Ian H

Thanks for the response.  For the most part, all of these upgrade steps seemed necessary at the time.  For example, when trying a 2.0 to 2.2.6 or 2.0 to 3.0 upgrade, I ran into many errors.  So I would back up and do a 2.0 to 2.1 to 2.2.6 to 3.0 and had 0 errors during upgrade.  Maybe I did something wrong.  But our client was using the 1.9 system for a very long time and there were lots of things 'off' about the data (inconsistencies) and maybe these were the things that made it necessary for us to do a more granular upgrade.

Could you give me a rough idea of when the custom fields received the new data format?  When the civicrm_custom_field, civicrm_custom_value, etc of v1.9 were turned into individual tables (custom_value_1_xxx_xxx).  This will help a lot.

Thanks,
Ian H