CiviCRM Community Forums (archive)

I've recently done a full test upgrade of our 1.9 CiviCRM installation to 2.0 Beta 3. Everything upgraded correctly except for the conversion of the civicrm_custom_value data. What is happening that on the first field upgrading that the data is correctly stored against the users entity_id however on the second field upgrading the data is stored against the wrong entity_id thereby corrupting the custom data.

I can confirm that the when manually running the conversion sql statements (http://civicrm.org/node/280) I can reproduce the error. It appears to do with the tables having two unique indexes (id, domain_id & entity_id) and having the wrong row updated when a duplicate is found. It may be due to custom data groups/fields been added after some contacts were created and later updated.

By dropping the id field from the civicrm_value_1_... table prior to migrating data and then recreating the id field after all values have been migrated cleanly migrates the data. (One other method that works is to sort the SELECT FROM query by cv.entity id)

Has anyone else seen invalid custom data upgrades?

civicrm/CRM/Upgrade/TwoZero/Form/Step5.php

removing id:
while ($group->fetch()) {
+            $query = "ALTER TABLE {$group->table_name} DROP `id`";
+            $res = $this->runQuery($query);
+            $res->free();

            $field =& new CRM_Core_DAO_CustomField();

adding id:
            $field->free();

+            $query = "ALTER TABLE {$group->table_name} ADD `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST";
+            $res = $this->runQuery($query);
+            $res->free();



Say I am converting a custom group with more than 1 field. On the first pass (first field) of the data migration (INSERT INTO ... ON DUPLICATE) the data is correctly copied over (column values match the correct rows entity_id). On the second pass (second field) the field values don't always get copied over to the correct entity_id row (Some data is lost, other data is overridden). This means that users have custom data that belongs to another entity.

I can confirm that if I manually run the (INSERT INTO ... ON  DUPLICATE) query for just the second field then the data is copied over correctly. The bug seems to be when the ON DUPLICATE update function is call and the incorrect row is identified (The MySQL manual hints to this saying not to use this query for tables with multiple unique indexes, the custom_value_1... tables have two unique indexes) and updated.

I'll send through the 1.9 database. However I do have a smaller test case which will show the problem up right away. The cause I believe is on the database query side and not something in the code.

Originally that's how I thought the ON DUPLICATE would work. However I was only able to bring over my custom data when I removed the primary key id from the new table while bring the data over.

I'm just cleaning up the data now as the is a lot of personal information in there that I can't distribute.