Cannot add contact 'The user specified as definer does not exist'

[nsjski]

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...

Code: [Select]

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)

Code: [Select]

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.

[Shai]

nsjski,

I think I know your problem.

The .sql file that you used to migrate the site had the database user from your old site hard-coded into the sql import. This is what I would do (this presumes you don't have in new data added at the new site).
1. Drop all the tables on your new site.
2. Using a text editor, open the .sql file you are importing. (If the file is compressed -- .gz or .zip, you need to decompress first.) Search on "DEFINER" and each time replace the value of that parameter with the new mysql user associated with the civicrm database. You can also simply remove the DEFINER statements as well. But I actually think there is a greater chance you'll break the statement and cause an error than if you simply replace the mysql user name from the original db with the new one. Save the file after making changes.
3. Import edited .sql file
4. Try adding a contact.

Good luck and do report back.

Shai Gluskin

[DaveFF]

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.