CiviCRM Community Forums (archive)

Hi,

while upgrading from 1.9.11960 to 2.0 I have an error. I have followed the instructions here:
http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0

and have reconstructed the database from civicrm_41.mysql, but no luck.

The error I have is:

---
    Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_activity_type_id` FOREIGN KEY (`activity_type_id`) REFERENCES `civicrm_option_value` (`id`)), 1452

    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 civicrm_activity (`source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `due_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`)
SELECT ah.entity_id, ah.activity_id, @ov_id_event, ah.activity_summary,
       ah.activity_date, NULL, NULL, NULL, NULL, NULL,
       ah.activity_summary, @ov_id_completed, NULL, NULL, ah.is_test
FROM civicrm_activity_history ah
WHERE ah.module IN ('CiviEvent')
GROUP BY ah.module, ah.entity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_activity_type_id` FOREIGN KEY (`activity_type_id`) REFERENCES `civicrm_option_value` (`id`))]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_activity (`source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `due_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`)
SELECT ah.entity_id, ah.activity_id, @ov_id_event, ah.activity_summary,
       ah.activity_date, NULL, NULL, NULL, NULL, NULL,
       ah.activity_summary, @ov_id_completed, NULL, NULL, ah.is_test
FROM civicrm_activity_history ah
WHERE ah.module IN ('CiviEvent')
GROUP BY ah.module, ah.entity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_activity_type_id` FOREIGN KEY (`activity_type_id`) REFERENCES `civicrm_option_value` (`id`))]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_activity (`source_contact_id`, `source_record_id`, `activity_type_id`, `subject`, `activity_date_time`, `due_date_time`, `duration`, `location`, `phone_id`, `phone_number`, `details`, `status_id`, `priority_id`, `parent_id`, `is_test`)
SELECT ah.entity_id, ah.activity_id, @ov_id_event, ah.activity_summary,
       ah.activity_date, NULL, NULL, NULL, NULL, NULL,
       ah.activity_summary, @ov_id_completed, NULL, NULL, ah.is_test
FROM civicrm_activity_history ah
WHERE ah.module IN ('CiviEvent')
GROUP BY ah.module, ah.entity_id [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_activity`, CONSTRAINT `FK_civicrm_activity_activity_type_id` FOREIGN KEY (`activity_type_id`) REFERENCES `civicrm_option_value` (`id`))]"]
)
---

I have looked in the forums and I realized I have done something like this guy:
http://forum.civicrm.org/index.php/topic,2499.0.html

i.e. I have deleted all of some standard options from CiviCRM (namely: all name suffixes for contacts, for in Italy we don't
use them). I have tried to recreate them but it gives the same error (so I'm not really sure that is the problem).

Does anybody have an idea of what's the problem and what can I do to solve that?

thanks

Eugenia

I am in process of upgrading my site from 1.9 to 2.0 beta 3. After pressing continue on the last step of upgrade process, I get a foreign key error as well.

backTrace

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

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] =>
INSERT INTO custom_value_1_File_Upload (domain_id,entity_id,Attachment)
SELECT 1, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=2
ON DUPLICATE KEY UPDATE Attachment=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19Fix/custom_value_1_File_Upload`, CONSTRAINT `FK_custom_value_1_File_Upload_Attachment` FOREIGN KEY (`Attachment`) REFERENCES `civicrm_file` (`id`) ON DELETE SET NULL)]
    [type] => DB_Error
    [user_info] =>
INSERT INTO custom_value_1_File_Upload (domain_id,entity_id,Attachment)
SELECT 1, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=2
ON DUPLICATE KEY UPDATE Attachment=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19Fix/custom_value_1_File_Upload`, CONSTRAINT `FK_custom_value_1_File_Upload_Attachment` FOREIGN KEY (`Attachment`) REFERENCES `civicrm_file` (`id`) ON DELETE SET NULL)]
    [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_File_Upload (domain_id,entity_id,Attachment)
SELECT 1, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=2
ON DUPLICATE KEY UPDATE Attachment=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm19Fix/custom_value_1_File_Upload`, CONSTRAINT `FK_custom_value_1_File_Upload_Attachment` FOREIGN KEY (`Attachment`) REFERENCES `civicrm_file` (`id`) ON DELETE SET NULL)]"]
)


Any ideas on how to resolve? Thanks much,
Emily[/code]

Has there been any progress on this?  I'm also working on an update that's Drupal based and failing with Foreign key problems.  I rebuilt the database, which got me from step 2 to step 5, but now I'm hung up there:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] =>
INSERT INTO custom_value_11155_Test_Custom_Data_1 (domain_id,entity_id,Upload_a_file)
SELECT 11155, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=5
ON DUPLICATE KEY UPDATE Upload_a_file=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`sample_civicrm19fix/custom_value_11155_Test_Custom_Data_1`, CONSTRAINT `FK_custom_value_11155_Test_Custom_Data_1_Upload_a_file` FOREIGN KEY (`Upload_a_file`) REFERENCES `civicrm_file` (`id`) )]
    [type] => DB_Error
    [user_info] =>
INSERT INTO custom_value_11155_Test_Custom_Data_1 (domain_id,entity_id,Upload_a_file)
SELECT 11155, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=5
ON DUPLICATE KEY UPDATE Upload_a_file=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`sample_civicrm19fix/custom_value_11155_Test_Custom_Data_1`, CONSTRAINT `FK_custom_value_11155_Test_Custom_Data_1_Upload_a_file` FOREIGN KEY (`Upload_a_file`) REFERENCES `civicrm_file` (`id`) )]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="
INSERT INTO custom_value_11155_Test_Custom_Data_1 (domain_id,entity_id,Upload_a_file)
SELECT 11155, cv.entity_id, cv.char_data FROM civicrm_custom_value cv
WHERE cv.custom_field_id=5
ON DUPLICATE KEY UPDATE Upload_a_file=cv.char_data [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`sample_civicrm19fix/custom_value_11155_Test_Custom_Data_1`, CONSTRAINT `FK_custom_value_11155_Test_Custom_Data_1_Upload_a_file` FOREIGN KEY (`Upload_a_file`) REFERENCES `civicrm_file` (`id`) )]"]
)
I've also got it in a slightly strange setup, but I don't THINK this is related.  In short I'm setup so that <drupal_root>/sites/sample/modules/civicrm/ is a link to a shared code base.  My reasoning behind this I'll discuss under the install section, since I want to test the theory that lead me to setup this way.

Aaron[/code]

i am also hang up in step 5 if you find any solution let me also know.

Acrosman,

Thanks for the db.

The error you encountered was a bug related with custom field of type 'File', for which an issue has been filed and fixed - http://issues.civicrm.org/jira/browse/CRM-2805

The fix will be available with next beta release (expected in next 24 - 48 hrs), so better to wait for it (shouldn't be a problem for you, since i have mailed you the upgraded db).

Thanks a lot for helping us make it better

Hi,

And to answer my own question: I put back the 1.9 db, svn updated, ran the update again and this time it rolled nicely.

X+

I suspect this being a schema related issue.

Can you try making schema inline with 1.9 db (section 4, http://wiki.civicrm.org/confluence/display/CRMDOC/Upgrade+Drupal+Sites+to+2.0) ?

And then start with the upgrade and check if the same problem appears.