2

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?

Improve this question
2
  • 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
    – Tobias Krause
    Feb 14, 2020 at 6:38
  • 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
    – Luke Stewart
    Feb 15, 2020 at 22:19

1 Answer 1

Reset to default
9

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

Improve this answer
1
  • 1
    To make a dump without triggers and function: mysqldump --skip-triggers --skip-routines
    – Tobias Krause
    Feb 14, 2020 at 8:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.