Salut. I am following this wonderful guide https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/ in order to migrate civicrm from one server to another (wordpress, external civicrm database).

It is unlclear to me, however, if I need to completely clear the database of the new civicrm install on the new server, before importing the dump file there. I do get quite some errors when I import into an already existing structure (f.e. table already exists, trigger already exists, foreign key....).

My case is double-complex, it seems, since the original dump is mysql 5.7 and the new database is mysql 8.0. I know mysql version should be the same, but it is impossible in this case.

Has anybody tried this yet? If the mysql version does not match, is it a lost cause?

Thanks for any hints. Michael


Are you using phpmyadmin to export the old one? You can check the box for ADD DROP TABLE which should solve this. There should also be an option to disable key checks but the drop table might add that automatically.

Using mysqldump on the command line it does this by default, and also by default it disables key checks.

  • Thank you very much for your reply. I set up a new test with tables only (no data), which makes the testing faster. I exported from phpmyadmin without data (and excluding the 4 cache and 1 domain table, as suggested in the tutorial). I also set DROP TABLE and deactivated the foreign key option (see image two). I tried to import into a fresh install of civicrm, during import in mysql I also deactivated the foreign key option (see image three). This resulted in following error message: – Mich___ Apr 15 at 12:11
  • #1822 - Failed to add the foreign key constraint. Missing index for constraint 'FK_civicrm_acl_cache_acl_id' in the referenced table 'civicrm_acl' – Mich___ Apr 15 at 12:11
  • If I empty the database before import via phpmyadmin, I can import without error message, but I think that is not the correct way to do it? (I never got civicrm to work this way). Therefore I better try to import into the already existing civicrm database structure (without emptying it before). Or is there no difference between this procedure and using drop table via the imported dump? Thanks for any further hints! – Mich___ Apr 15 at 12:11
  • [![export][1]][1] [![export][2]][2] [![import][3]][3] [1]: i.stack.imgur.com/oJFUa.png [2]: i.stack.imgur.com/dO8q0.png [3]: i.stack.imgur.com/uEMTp.png – Mich___ Apr 15 at 12:11
  • Detailed error message: CREATE TABLE civicrm_acl ( id int(10) UNSIGNED NOT NULL COMMENT 'Unique table ID', name varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'ACL Name.', deny tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Is this ACL entry Allow (0) or Deny (1) ?', entity_table varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Table of the object possessing this ACL entry (Contact, Group, or ACL Group)', – Mich___ Apr 15 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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