CiviCRM Community Forums (archive)

Entering new contact details into civicrm/contact/add and saving results in

Sorry but we are not able to provide this at the moment.
DB Error: unknown error

Return to home page.

This is with a site that has been migrated fairly recently.  Other civi functions seem to be working, we just mailed out a newsletter.

There was nothing new in the drupal log, but the civicrm log file shows something about foreign keys...

Jul 01 00:01:12  [info] $backTrace = #0 /home/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(751): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /home/public_html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/public_html/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: constraint violation", -3, 16, (Array:2), "\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#4 /home/public_html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-3, 16, (Array:2), "\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#5 /home/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -3, NULL, NULL, "\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...", "DB_Error", TRUE)
#6 /home/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-3, NULL, NULL, NULL, "1452 ** Cannot add or update a child row: a foreign key constraint fails (`pg...")
#7 /home/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#9 /home/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#10 /home/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#11 /home/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(155): DB_DataObject->query("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#12 /home/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(917): CRM_Core_DAO->query("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...", TRUE)
#13 /home/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContact.php(782): CRM_Core_DAO::executeQuery("\nREPLACE INTO civicrm_group_contact ( group_id, contact_id, status )\nVALUES...")
#14 /home/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/GroupContact.php(148): CRM_Contact_BAO_GroupContact::bulkAddContactsToGroup((Array:1), "4", "API", "Added", NULL)
#15 /home/public_html/sites/all/modules/civicrm/api/v3/GroupContact.php(202): CRM_Contact_BAO_GroupContact::addContactsToGroup((Array:1), "4", "API", "Added", NULL)
#16 /home/public_html/sites/all/modules/civicrm/api/v3/GroupContact.php(109): _civicrm_api3_group_contact_common((Array:3), "Added")
#17 /home/public_html/sites/all/modules/civicrm/api/api.php(75): civicrm_api3_group_contact_create((Array:3))
#18 /home/public_html/sites/all/modules/civicrm/drupal/modules/civicrm_group_roles/civicrm_group_roles.module(650): civicrm_api("GroupContact", "create", (Array:3))
#19 /home/public_html/sites/all/modules/civicrm/drupal/modules/civicrm_group_roles/civicrm_group_roles.module(69): civicrm_group_roles_add_remove_groups((Array:7), Object(stdClass), "add")
#20 /home/public_html/modules/user/user.module(101): civicrm_group_roles_user_update((Array:5), Object(stdClass), "account")
#21 /home/public_html/modules/user/user.module(564): user_module_invoke("update", (Array:5), Object(stdClass), "account")
#22 /home/public_html/sites/all/modules/civicrm/drupal/modules/civicrm_member_roles/civicrm_member_roles.module(587): user_save(Object(stdClass), (Array:1))
#23 /home/public_html/sites/all/modules/civicrm/drupal/modules/civicrm_member_roles/civicrm_member_roles.module(111): _civicrm_member_roles_sync("10")
#24 /home/public_html/sites/all/modules/civicrm/drupal/modules/civicrm_member_roles/civicrm_member_roles.module(93): civicrm_member_roles_sync_user(Object(stdClass))
#25 /home/public_html/modules/user/user.module(101): civicrm_member_roles_user_login((Array:21), Object(stdClass), NULL)
#26 /home/public_html/modules/user/user.module(2272): user_module_invoke("login", (Array:21), Object(stdClass))
#27 /home/public_html/modules/user/user.module(2285): user_login_finalize((Array:21))
#28 /home/public_html/includes/form.inc(1513): user_login_submit((Array:24), (Array:21))
#29 /home/public_html/includes/form.inc(903): form_execute_handlers("submit", (Array:24), (Array:21))
#30 /home/public_html/includes/form.inc(385): drupal_process_form("user_login", (Array:24), (Array:21))
#31 /home/public_html/includes/form.inc(130): drupal_build_form("user_login", (Array:21))
#32 /home/public_html/modules/user/user.pages.inc(560): drupal_get_form("user_login")
#33 [internal function](): user_page()
#34 /home/public_html/includes/menu.inc(517): call_user_func_array("user_page", (Array:0))
#35 /home/public_html/index.php(21): menu_execute_active_handler()
#36 {main}

I found this link which looked helpful http://www.trellon.com/content/blog/civicrm-upgrade-foreign-keys-contraint-violation-issue

The error message points to civicrm_group_contact, but I cannot find anything wrong with it.

select * from `civicrm_group_contact` where contact_id is not null and contact_id not in (select id from `civicrm_contact`) ORDER BY `contact_id` ASC     
returns 0 records

select * from `civicrm_group_contact` where contact_id is not null and contact_id in (select id from `civicrm_contact`) ORDER BY `contact_id` ASC    
returns 2305 records (there are 2305 records in civcrm_group_contact)

select * from `civicrm_group_contact` where group_id is not null and group_id not in (select id from `civicrm_group`) ORDER BY `group_id` ASC      
returns 0 records

select * from `civicrm_group_contact` where group_id is not null and group_id in (select id from `civicrm_group`) ORDER BY `group_id` ASC      
returns 2305 records




This is what gets logged when I try to create an individual contact without assigning them to any groups (I see the same DB error page)


Jul 04 09:40:30  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'Downing, Joe' , 'Joe Downing' ,  NULL , '' , 'en_US' , 'Both' , '5b65b8deae40abe1009cbcf755ef531a' ,  NULL , 'Joe' ,  NULL , 'Downing' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1449 ** The user specified as a definer ('cpses_pg20l6YoXj'@'localhost') does not exist]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'Downing, Joe' , 'Joe Downing' ,  NULL , '' , 'en_US' , 'Both' , '5b65b8deae40abe1009cbcf755ef531a' ,  NULL , 'Joe' ,  NULL , 'Downing' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1449 ** The user specified as a definer ('cpses_pg20l6YoXj'@'localhost') does not exist]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email , do_not_phone , do_not_mail , do_not_sms , do_not_trade , is_opt_out , external_identifier , sort_name , display_name , nick_name , preferred_communication_method , preferred_language , preferred_mail_format , hash , source , first_name , middle_name , last_name , prefix_id , suffix_id , email_greeting_id , email_greeting_custom , postal_greeting_id , postal_greeting_custom , addressee_id , addressee_custom , job_title , gender_id , birth_date , is_deceased ) VALUES ('Individual' ,  NULL ,  0 ,  0 ,  0 ,  0 ,  0 ,  0 ,  NULL , 'Downing, Joe' , 'Joe Downing' ,  NULL , '' , 'en_US' , 'Both' , '5b65b8deae40abe1009cbcf755ef531a' ,  NULL , 'Joe' ,  NULL , 'Downing' ,  NULL ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  1 ,  NULL ,  NULL ,  NULL ,  NULL ,  0 )  [nativecode=1449 ** The user specified as a definer ('cpses_pg20l6YoXj'@'localhost') does not exist]"]
)


Jul 04 09:40:30  [info] $backTrace = #0 /home/pghtrail/public_html/sites/all/modules/civicrm/CRM/Core/Error.php(196): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/pghtrail/public_html/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/pghtrail/public_html/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...")
#4 /home/pghtrail/public_html/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-1, 16, (Array:2), "INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...")
#5 /home/public_html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -1, NULL, NULL, "INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...", "DB_Error", TRUE)
#6 /home/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-1, NULL, NULL, NULL, "1449 ** The user specified as a definer ('cpses_pg20l6YoXj'@'localhost') does...")
#7 /home/public_html/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/public_html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...")
#9 /home/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...")
#10 /home/public_html/sites/all/modules/civicrm/packages/DB/DataObject.php(1055): DB_DataObject->_query("INSERT INTO civicrm_contact (contact_type , contact_sub_type , do_not_email ,...")
#11 /home/public_html/sites/all/modules/civicrm/CRM/Core/DAO.php(287): DB_DataObject->insert()
#12 /home/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(218): CRM_Core_DAO->save()
#13 /home/public_html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(314): CRM_Contact_BAO_Contact::add((Array:82))
#14 /home/public_html/sites/all/modules/civicrm/CRM/Contact/Form/Contact.php(986): CRM_Contact_BAO_Contact::create((Array:82), TRUE, FALSE, TRUE)
#15 /home/public_html/sites/all/modules/civicrm/CRM/Core/Form.php(246): CRM_Contact_Form_Contact->postProcess()
#16 /home/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(151): CRM_Core_Form->mainProcess()
#17 /home/public_html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Upload.php(128): CRM_Core_QuickForm_Action_Upload->realPerform(Object(CRM_Contact_Form_Contact), "upload")
#18 /home/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Upload->perform(Object(CRM_Contact_Form_Contact), "upload")
#19 /home/public_html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Contact), "upload")
#20 /home/public_html/sites/all/modules/civicrm/CRM/Core/Controller.php(316): HTML_QuickForm_Page->handle("upload")
#21 /home/public_html/sites/all/modules/civicrm/CRM/Utils/Wrapper.php(117): CRM_Core_Controller->run()
#22 /home/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(266): CRM_Utils_Wrapper->run("CRM_Contact_Form_Contact", "New Contact", (Array:1))
#23 /home/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(70): CRM_Core_Invoke::runItem((Array:13))
#24 /home/public_html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
#25 /home/public_html/sites/all/modules/civicrm/drupal/civicrm.module(436): CRM_Core_Invoke::invoke((Array:3))
#26 [internal function](): civicrm_invoke("contact", "add")
#27 /home/public_html/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:2))
#28 /home/public_html/index.php(21): menu_execute_active_handler()
#29 {main}








Suggestions welcome!

------

HA!   Fixed, thanks to post from Lobo in this thread.     http://forum.civicrm.org/index.php/topic,31014.0.html
on rewriting triggers.
http://wiki.civicrm.org/confluence/display/CRMDOC/Installation+and+Configuration+Trouble-shooting#InstallationandConfigurationTrouble-shooting-civicrm_strip_non_numericdoesnotexisterror

The foreign key thing was a red herring.

We've had this problem too when getting the latest snapshot from our live server to our dev machines. Another way of solving it is to pipe the mysqldump output through sed either when you're taking the snapshot or importing it. You could recreate a user with the same name but I don't know what the minimum required privs would be.