CiviCRM Community Forums (archive)

Trying to upgrade my 1.9 CiviCRM to 2.0.  I had the 1.9 running on MySQL 4.  I moved the data over to my dev server that runs MySQL 5.  The site works fine as 1.9 on MySQL 5.  But now I'm trying to upgrade and getting a ton of errors:

I get to step 5 (custom data upgrade) and as soon as I try running that step I get the following. I'm doing this on a PHP 5.x / MySQL 5.x server running Drupal 5.x.

    Sorry. A non-recoverable error has occurred.

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] =>
INSERT INTO custom_value_1_Membership (domain_id,entity_id,Mailing_Address_State_Province_)
SELECT 1, cv.entity_id, cv.int_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=15
ON DUPLICATE KEY UPDATE Mailing_Address_State_Province_=cv.int_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ogami_civi_new/custom_value_1_membership`, CONSTRAINT `FK_custom_value_1_Membership_Mailing_Address_State_Province_` FOREIGN KEY (`Mailing_Address_State_Province_`) REFERENCES `civicrm_state_)]
    [type] => DB_Error
    [user_info] =>
INSERT INTO custom_value_1_Membership (domain_id,entity_id,Mailing_Address_State_Province_)
SELECT 1, cv.entity_id, cv.int_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=15
ON DUPLICATE KEY UPDATE Mailing_Address_State_Province_=cv.int_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ogami_civi_new/custom_value_1_membership`, CONSTRAINT `FK_custom_value_1_Membership_Mailing_Address_State_Province_` FOREIGN KEY (`Mailing_Address_State_Province_`) REFERENCES `civicrm_state_)]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="
INSERT INTO custom_value_1_Membership (domain_id,entity_id,Mailing_Address_State_Province_)
SELECT 1, cv.entity_id, cv.int_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=15
ON DUPLICATE KEY UPDATE Mailing_Address_State_Province_=cv.int_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`ogami_civi_new/custom_value_1_membership`, CONSTRAINT `FK_custom_value_1_Membership_Mailing_Address_State_Province_` FOREIGN KEY (`Mailing_Address_State_Province_`) REFERENCES `civicrm_state_)]"]
)


Here's the backtrace:

backTrace

C:\www\ousa\sites\all\modules\civicrm\CRM\Core\Error.php, backtrace, 148
, handle,
C:\www\ousa\sites\all\modules\civicrm\packages\PEAR.php, call_user_func, 901
C:\www\ousa\sites\all\modules\civicrm\packages\DB.php, PEAR_Error, 888
C:\www\ousa\sites\all\modules\civicrm\packages\PEAR.php, DB_Error, 563
C:\www\ousa\sites\all\modules\civicrm\packages\DB\common.php, raiseError, 1849
C:\www\ousa\sites\all\modules\civicrm\packages\DB\mysql.php, raiseError, 894
C:\www\ousa\sites\all\modules\civicrm\packages\DB\mysql.php, mysqlRaiseError, 324
C:\www\ousa\sites\all\modules\civicrm\packages\DB\common.php, simpleQuery, 1162
C:\www\ousa\sites\all\modules\civicrm\packages\DB\DataObject.php, query, 2343
C:\www\ousa\sites\all\modules\civicrm\packages\DB\DataObject.php, _query, 1566
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\DAO.php, query, 716
C:\www\ousa\sites\all\modules\civicrm\CRM\Upgrade\Form.php, executeQuery, 126
C:\www\ousa\sites\all\modules\civicrm\CRM\Upgrade\TwoZero\Form\Step5.php, runQuery, 177
C:\www\ousa\sites\all\modules\civicrm\CRM\Upgrade\Form.php, upgrade, 114
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\StateMachine.php, postProcess, 166
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\QuickForm\Action\Next.php, perform, 63
C:\www\ousa\sites\all\modules\civicrm\packages\HTML\QuickForm\Controller.php, perform, 195
C:\www\ousa\sites\all\modules\civicrm\packages\HTML\QuickForm\Page.php, handle, 95
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\Controller.php, handle, 223
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\Invoke.php, run, 203
C:\www\ousa\sites\all\modules\civicrm\CRM\Core\Invoke.php, upgrade, 150
C:\www\ousa\sites\all\modules\civicrm\drupal\civicrm.module, invoke, 321
, civicrm_invoke,
C:\www\ousa\includes\menu.inc, call_user_func_array, 418
C:\www\ousa\index.php, menu_execute_active_handler, 15



Any assistance here would be much appreciated!

Thanks,

-backdrifting

hi folks,

well, after many hours of head-scratching and sql-unking, i think i discovered the problem (or at least a workaround).  (fwiw, the database i'm working with is pretty big and corrupt, due primarily to editing in multiple tabs, i believe.  i also deleted all the entries in custom_value that had all data fields null or empty -- 5000+ out of 14000!)

basically, i think there's a bug in the Step5.php upgrade script.  my solution at least allowed me to finish the upgrade with data inserted (still have to see whether it's the correct data...).

in essence, the problem seems to be that the script doesn't consider the case in which custom values are linked to multiple entity_tables.  ie. the same group & field will have some values linked to one table (activity, say) and others to another (eg. contact).  apparently this is a fairly rare case.  here was my solution.

edit sites/all/modules/civicrm/CRM/Upgrade/TwoZero/Form/Step5.php

here's my diff from the 2.0.6 version:

173d171
<         foreach (array('civicrm_contact', 'civicrm_activity', 'civicrm_meeting', 'civicrm_phonecall') as $entityTable) { 
182d179
< and cv.entity_table = '{$entityTable}'
207,208d202
<           }

in other words, i did an sql search to find the list of the entity_tables in custom_value (eg. "select distinct entity_table from custom_value" or the like).  i got the above four.  then, i repeated the main section of the upgrade script that defines the columns in the new custom group tables, once for each entity_table, adding to the sql a clause that confined it to that table.  (if any of your custom values are files, that section of the script should be edited too.)

and it worked!

hth,
.b