Answers.Body: <p>You can export the civicrm database without triggers and functions and After importing and making sure you have CiviCRM working, recreate the triggers by running 'civicrm/menu/rebuild?reset=1&amp;triggerRebuild=1' in browser </p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-14T08:38:38.740Z
Answers.Text: To make a dump without triggers and function: `mysqldump --skip-triggers --skip-routines`
Answers.UserDisplayName: Tobias Krause
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-02-13T18:29:08.470Z
Answers.LastActivityDate: 2020-02-13T18:29:08.470Z
Answers.UserDisplayName: Pradeep Nayak
Body: <p>I will move a CiviCRM installation to another server and realized some strange database issues: it was not possible to save contacts with phone number anymore. The problem are the triggers in the database: both <code>civicrm_phone_before_update</code> and <code>civicrm_phone_before_insert</code> call a function <code>civicrm_strip_non_numeric()</code>. On export of the whole database these triggers and functions are exported with "definers" - database users which do not exist anymore in the new database.</p>
  
  <p>So it seems that you cannot easily dump your CiviCRM database and import your dump to a fresh database. How should I process that?</p>
  
  <p>And why are there these triggers in the database? Why is this not handled on the level of the app?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-14T06:38:12.013Z
Comments.Text: I am not sure... does --skip-triggers also skip functions? The main problem for me is the civicrm_strip_non_numeric() which definer is not the current database user. The triggers are working but when the method is called I get the error about the invalid definer
Comments.UserDisplayName: Tobias Krause
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2020-02-15T22:19:59.483Z
Comments.Text: You should be able to run the perl referenced in the above to remove all of this: perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql
Comments.UserDisplayName: Luke Stewart
Tags: <database><trigger>
Title: How do I safely export my database and import it on a new server?
ThreadText: # How do I safely export my database and import it on a new server?
  
  (Asked by: username_0 on 2020-02-13)
  
  I will move a CiviCRM installation to another server and realized some strange database issues: it was not possible to save contacts with phone number anymore. The problem are the triggers in the database: both `civicrm_phone_before_update` and `civicrm_phone_before_insert` call a function `civicrm_strip_non_numeric()`. On export of the whole database these triggers and functions are exported with "definers" - database users which do not exist anymore in the new database.
  
  So it seems that you cannot easily dump your CiviCRM database and import your dump to a fresh database. How should I process that?
  
  And why are there these triggers in the database? Why is this not handled on the level of the app?
  
  **Tags:** database, trigger
  
  > **Comments:**
  > **username_0**: I am not sure... does --skip-triggers also skip functions? The main problem for me is the civicrm_strip_non_numeric() which definer is not the current database user. The triggers are working but when the method is called I get the error about the invalid definer  
  > **username_1**: You should be able to run the perl referenced in the above to remove all of this: perl -pi -e 's#\/\*\!5001[7|3].*?`[^\*]*\*\/##g' /path/to/mysqldump.sql  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2020-02-13. Score: 7 | Accepted answer
  
  You can export the civicrm database without triggers and functions and After importing and making sure you have CiviCRM working, recreate the triggers by running 'civicrm/menu/rebuild?reset=1&triggerRebuild=1' in browser
  
  > **Comments:**
  > **username_0**: To make a dump without triggers and function: `mysqldump --skip-triggers --skip-routines`  
  
  ---
  
  