Answers.Body: <p>Are you using phpmyadmin to export the old one? You can check the box for <code>ADD DROP TABLE</code> which should solve this. There should also be an option to disable key checks but the drop table might add that automatically.</p>
  <p>Using mysqldump on the command line it does this by default, and also by default it disables key checks.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:11:02.367Z
Answers.Text: 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:
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:11:08.147Z
Answers.Text: #1822 - Failed to add the foreign key constraint. Missing index for constraint 'FK_civicrm_acl_cache_acl_id' in the referenced table 'civicrm_acl'
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:11:40.047Z
Answers.Text: 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!
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:11:46.393Z
Answers.Text: [![export][1]][1]
  [![export][2]][2]
  [![import][3]][3]
    [1]: https://i.sstatic.net/oJFUa.png
    [2]: https://i.sstatic.net/dO8q0.png
    [3]: https://i.sstatic.net/uEMTp.png
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:12:31.793Z
Answers.Text: 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)',
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T12:12:35.717Z
Answers.Text: `entity_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'ID of the object possessing this ACL',
    `operation` varchar(8) COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?',
    `object_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The table of the object controlled by this ACL entry',
    `object_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'The ID of the object controlled by this ACL entry',
    `acl_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'If this is a grant/revoke entry, what table are we granting?',
    `acl_id` i[...]
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T13:40:29.407Z
Answers.Text: I found this thread with a similar issue: https://civicrm.stackexchange.com/questions/15282/mysql-errors-when-transferring-database-from-one-server-to-another
  The user solved it by deleting Definer (referencing old username and password). I will try that.
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T13:50:24.073Z
Answers.Text: Still the same error message.
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T13:56:24.007Z
Answers.Text: In the export file, is there anything that says something like "ALTER TABLE xxx DISABLE KEYS" or "SET FOREIGN_KEY_CHECKS=0"?
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-15T14:24:10.887Z
Answers.Text: Thanks. There is FOREIGN_KEY_CHECKS=0 and ALTER TABLE  (f.e. ALTER TABLE `backend_layout`) (but not Disable Keys)....
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-16T08:20:45.963Z
Answers.Text: When I import my dump into an emptied database (of the new civicrm install) and load the wordpress page later, it is not accessible anymore. Critical Error message on white screen. I guess it is easier to find the mistakes when I import into the not-emptied data-structure of the new civicrm-install. I have been looking into documentation/experiences of importing a mysql 5.7 dump into the new mysql 8.0, but there is none. I don't have the rights on the source server to use mysql 8.0`s compatibility tool. Kind of lost at the moment.
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-16T12:14:52.457Z
Answers.Text: What privileges does the mysql user have on the new database? https://docs.civicrm.org/installation/en/latest/general/requirements/#mysql-permissions
Answers.UserDisplayName: Demerit
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-16T17:43:17.910Z
Answers.Text: Thanks! The user has all rights (root rights, since it is our own server). In PHPMyadmin it says: ALL PRIVILEGES GRANT YES. I managed to import the structure of the original database onto the new civicrm install without error message, when I choose every available option to check in phpmyadmin, including drop table, if not exists, create view, create procedure, create trigger, trunctuate tables, insert ignore keys, insert delayed ..... but when I try to import the whole package with the same settings, I get an error message again:
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-16T17:43:21.507Z
Answers.Text: ERROR 1347 (HY000) at line 406516: 'gemeingut_civicrm.civicrm_batch_de_DE' is not VIEW
Answers.UserDisplayName: Mich___
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-04-14T17:09:01.690Z
Answers.LastActivityDate: 2021-04-14T17:09:01.690Z
Answers.UserDisplayName: Demerit
Body: <p>Salut. I am following this wonderful guide <a href="https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/" rel="nofollow noreferrer">https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/</a>
  in order to migrate civicrm from one server to another (wordpress, external civicrm database).</p>
  <p>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....).</p>
  <p>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.</p>
  <p>Has anybody tried this yet? If the mysql version does not match, is it a lost cause?</p>
  <p>Thanks for any hints.
  Michael</p>
  
Tags: <wordpress><upgrade><migration>
Title: Do I have to empty the database of a fresh civicrm install, before I import a dump from another server? (and a question about mysql 5.7 dump ->8.0)
ThreadText: # Do I have to empty the database of a fresh civicrm install, before I import a dump from another server? (and a question about mysql 5.7 dump ->8.0)
  
  (Asked by: username_0 on 2021-04-14)
  
  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
  
  **Tags:** wordpress, upgrade, migration
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2021-04-14. Score: 1
  
  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.
  
  > **Comments:**
  > **username_0**: 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:  
  > **username_0**: #1822 - Failed to add the foreign key constraint. Missing index for constraint 'FK_civicrm_acl_cache_acl_id' in the referenced table 'civicrm_acl'  
  > **username_0**: 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!  
  > **username_0**: [![export][1]][1] [![export][2]][2] [![import][3]][3]   [1]: https://i.sstatic.net/oJFUa.png   [2]: https://i.sstatic.net/dO8q0.png   [3]: https://i.sstatic.net/uEMTp.png  
  > **username_0**: 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)',  
  > **username_0**: `entity_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'ID of the object possessing this ACL',   `operation` varchar(8) COLLATE utf8_unicode_ci NOT NULL COMMENT 'What operation does this ACL entry control?',   `object_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'The table of the object controlled by this ACL entry',   `object_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'The ID of the object controlled by this ACL entry',   `acl_table` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'If this is a grant/revoke entry, what table are we granting?',   `acl_id` i[...]  
  > **username_0**: I found this thread with a similar issue: https://civicrm.stackexchange.com/questions/15282/mysql-errors-when-transferring-database-from-one-server-to-another The user solved it by deleting Definer (referencing old username and password). I will try that.  
  > **username_0**: Still the same error message.  
  > **username_1**: In the export file, is there anything that says something like "ALTER TABLE xxx DISABLE KEYS" or "SET FOREIGN_KEY_CHECKS=0"?  
  > **username_0**: Thanks. There is FOREIGN_KEY_CHECKS=0 and ALTER TABLE  (f.e. ALTER TABLE `backend_layout`) (but not Disable Keys)....  
  > **username_0**: When I import my dump into an emptied database (of the new civicrm install) and load the wordpress page later, it is not accessible anymore. Critical Error message on white screen. I guess it is easier to find the mistakes when I import into the not-emptied data-structure of the new civicrm-install. I have been looking into documentation/experiences of importing a mysql 5.7 dump into the new mysql 8.0, but there is none. I don't have the rights on the source server to use mysql 8.0`s compatibility tool. Kind of lost at the moment.  
  > **username_1**: What privileges does the mysql user have on the new database? https://docs.civicrm.org/installation/en/latest/general/requirements/#mysql-permissions  
  > **username_0**: Thanks! The user has all rights (root rights, since it is our own server). In PHPMyadmin it says: ALL PRIVILEGES GRANT YES. I managed to import the structure of the original database onto the new civicrm install without error message, when I choose every available option to check in phpmyadmin, including drop table, if not exists, create view, create procedure, create trigger, trunctuate tables, insert ignore keys, insert delayed ..... but when I try to import the whole package with the same settings, I get an error message again:  
  > **username_0**: ERROR 1347 (HY000) at line 406516: 'gemeingut_civicrm.civicrm_batch_de_DE' is not VIEW  
  
  ---
  
  