Migration error: “The user specified as a definer does not exist”

Question

I recently moved the site to a new server and changed from a remote to a local MySQL server with different credentials. Most things seem to be working fine, but even though I've flushed caches, editing phone numbers leads to an AJAX "Network Error" message and the following in my logs:

$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_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id )
    VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 )
    [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') does not exist]
  [type] => DB_Error
  [user_info] => INSERT INTO civicrm_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id )
    VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 )
    [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') 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_phone (contact_id , location_type_id , is_primary , is_billing , phone , phone_ext , phone_type_id ) VALUES ( 200025 , 2 , 1 , 0 , '6464646464' , NULL , 1 ) [nativecode=1449 ** The user specified as a definer ('OLDUSER'@'%') does not exist]"]
)

OLDUSER is nowhere in my configs and I have run both drush cc all and drush cvapi system.flush. Any ideas?

Answer 1

Maybe you do not have ssh access and MySQL access. But this can also done from the user interface. To rebuild the triggers use the url:

http://<<servername>/civicrm/menu/rebuild?reset=1&triggerRebuild=1

(This is documented in the system admin manual.)

Recreating the Case Views can be done by disabling the case module and enabling it again (Goto Administer -> System Settings -> Components)

Answer 2

Xavier, your answer would have worked if I had had the luxury of re-importing. It turns out that there is a single function in the CiviCRM DB that for some reason uses a hard-coded username and password. I went in to the DB, located the "civicrm_strip_non_numeric" function and updated the username and password. Problem solved.

Answer 3

did you dump the old site and restored it on the new server?

check that mysql dump file, it should contain the olduser string, replace it in that file with the new one before doing the restore and it should work fine.

Answer 4

Similar to the accepted answer, in my case there were two case views which contained the incorrect definer information. Performed the alter query on the view and it was fixed.

Those view names were - civicrm_view_case_activity_recent and civicrm_view_case_activity_upcoming. Steps performed -

• Execute this statement.

  SHOW CREATE VIEW civicrm_view_case_activity_recent.
  

• Copy the create view syntax.

• replace incorrect username with the correct one.
• replace CREATE word with ALTER.
• Execute the query and repeat the steps for civicrm_view_case_activity_upcoming.

Answer 5

The database triggers and the views can be recreated by using some code from the installation procedure. You can execute them with drush. To rebuild the triggers do

drush cvapi System.flush triggers=1 

This also rebuilds the civicrm_strip_non_numeric. Unfortunately this does not work for the views. But this can be done with:

drush eval 'civicrm_initialize();CRM_Case_BAO_Case::createCaseViews()'

On other CMS systems you can do the same with cv:

cv api3 System.flush triggers=1 
cv ev 'CRM_Case_BAO_Case::createCaseViews()'