CiviCRM Community Forums (archive)

I am trying to upgrade from 1.8->2.04 and my custom field values are messed up for many custom fields... It appears that values for one contact are being copied from a different contact.  Obviously a major problem! 

Any idea how I debug this problem?  Or is there some manual process I can do?  There are no errors shown in the upgrade process and I think I ran all the correct steps. 

Ok, I've done an upgrade from 1.8->1.9 which worked correctly.  The problem happens with 1.9 -> 2.0 and I believe I have found a bug.

I have reviewed a file CRM/Upgrade/TwoZero/Form/Step5.php which does the custom field upgrades. There is an SQL statement on line 177:

INSERT INTO {$group->table_name} (domain_id,entity_id,{$field->column_name})
SELECT $domainID, cv.entity_id, $col FROM civicrm_custom_value cv
WHERE cv.custom_field_id={$field->id}
ON DUPLICATE KEY UPDATE {$field->column_name}={$col}

It seems the "ON DUPLICATE KEY UPDATE" part is causing incorrect values to be put into the table. I am not an expert on this feature so I can't explain the reasons but I ran some statements manually and it put in the wrong values.

I believe I have a fix, that is to insert all the rows first, then to update the fields. I added the following before while ($field->fetch()) { on line 172.

$query = "
INSERT INTO {$group->table_name} (domain_id,entity_id)
SELECT DISTINCT $domainID, cv.entity_id
FROM civicrm_custom_value cv";
$res    = $this->runQuery( $query );
$res->free();

Then a few lines down I changed the first-mentioned SQL query to this:

UPDATE {$group->table_name} cvu, civicrm_custom_value cv
SET cvu.{$field->column_name} = $col
WHERE cvu.domain_id=$domainID AND cvu.entity_id=cv.entity_id AND cv.custom_field_id={$field->id}

I ran the upgrade process with this modification and all my custom values seem correct now. I would appreciate any feedback on this - have I done the right thing? Am I right that this a bug? Surely this is affecting other people?

MySQL version is 5.0.38

Ok, I have upgraded to MySQL 5.0.51a and the CiviCRM upgrade seems to have worked properly this time. My custom field values seem to be correct. So it would seem it was an issue with my earlier version of MySQL. 

I don't know if I would have solved it without your suggestion - so thanks very much!