Upgrader script doesn't correctly convert custom values over

[mjlucas]

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:

Code: [Select]

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:

Code: [Select]

            $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();



[Donald Lobo]

1. can you give us a few more details about what gets transferred wrong? I'm not sure I understand the below

2. can u email the 1.9 db to deepak at webaccess.co.in. we'll use the db to potentially fix the issue before our next beta release

thanx

lobo

[mjlucas]

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.

[Donald Lobo]

If you can send us both a small test database and your full 1.9 database, we'll check and figure out a fix

I'm not sure i understand you completely. yes there are two unique indexes, id and (domain_id, entity_id). However the first is an "auto increment" so should never match on an INSERT statement (since we dont send in a value). so we are left with only one unique key that a potential INSERT can match on, in which case it updates the matched row

can you point me to the mysql documentation which hints of this bug

thanx

lobo

[mjlucas]

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.