Answers.Body: <p>It looks like your error message is truncated, which makes this a little hard to troubleshoot, so if you can find a way to generate a non-truncated version of this message, I think the solution should be clearer.</p>
  
  <p>That said - I'm looking at a CiviCRM database dump.  The first two tables to be created are <code>civicrm_acl</code> and <code>civicrm_acl_cache</code>.  Your error references <code>civicrm_acl</code> - but <code>civicrm_acl</code> doesn't have any foreign key constraints defined, so it makes no sense that you'd get this error on the line in your question.</p>
  
  <p>However, <code>civicrm_acl_cache</code> DOES have a foreign key constraint - on <code>civicrm_acl</code>.  So I would assume that one of the following is true:</p>
  
  <ul>
  <li>Your PHPMyAdmin user doesn't have the permission to create <code>civicrm_acl</code>, or perhaps to insert data into it.</li>
  <li>Your database dump you're loading is missing data from <code>civicrm_acl</code>.</li>
  </ul>
  
  <p>Please check the following:</p>
  
  <ul>
  <li>Does <code>civicrm_acl</code> exist after you've imported it? If not, check permissions.</li>
  <li>Does <code>civicrm_acl</code> exist but contain no data?  If yes, open your database dump in a text file and ensure that there's a line starting with <code>INSERT INTO `civicrm_acl` VALUES</code>.  That line should be between the lines starting with <code>CREATE TABLE `civicrm_acl`</code> and <code>CREATE TABLE `civicrm_acl_cache`</code> - somewhere right near the top of the file.</li>
  <li>If that line DOES exist, you might not have <code>INSERT</code> permissions.</li>
  <li>If that line doesn't exist, you should try getting a dump that does.</li>
  </ul>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T16:22:47.497Z
Answers.Text: Thanks for the feedback.
  
  • civicrm_acl has data in the dump.
  • civicrm_acl doesn't exist after importing it.
  • the sql user does have INSERT permissions.
  
  Something I've learned since - I _can_ upload the backup without issue if the new databse is empty of tables. I only fear it'll leave any missing pieces from the old database intact. Time to test it next.
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T16:30:06.777Z
Answers.Text: Ideally the new database IS empty of tables - you might run into issues (including this one!) if your database isn't.  Importing the dump will create the tables; it's actually best to `DROP` the database then `CREATE` the database fresh before importing your dump.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T17:01:33.837Z
Answers.Text: Ah. Ok. Well that solves that mystery. I don't understand why it wouldn't drop and create the tables but I'm glad it's figured out. I must have overlooked one of the steps in the guide.
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T19:02:01.097Z
Answers.Text: Yeah - the migration guide is an amalgam of multiple guides, and I can see the problem.  It tells you to export from PHPMyAdmin with the "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement" option. That makes sense with a different migration strategy that involves putting CiviCRM in the file system but not running the installer. I'll submit a ticket for this to get cleaned up.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T19:06:28.513Z
Answers.Text: Submitted a ticket to improve the docs [here](https://github.com/civicrm/civicrm-sysadmin-guide/issues/85).  The advice to select the DROP TABLE option isn't good advice; it caused this problem and should be removed.
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-10T09:29:09.443Z
Answers.Text: Many thanks. That's one part of the guide I did follow. Ha!
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T14:17:51.480Z
Answers.LastActivityDate: 2018-04-09T19:22:19.463Z
Answers.LastEditDate: 2018-04-09T19:22:19.463Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>You'll likely need to make some temporary changes to your files to avoid the foreign key error. I do the same when I have to do an import to fix schema integrity. </p>
  
  <p>Here's the page I am referencing: <a href="https://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades" rel="nofollow noreferrer">https://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades</a></p>
  
  <p>Here's what it says:</p>
  
  <p>A note on possible Foreign Key Constraint Violation errors during import (and what to do about them): You may receive Foreign Key constraint violation errors on import because of the order in which the tables are exported and then imported, which is alphabetical. </p>
  
  <p>For example, data for the civicrm_activity_assignment table would be imported before data for the civicrm_contact table, violating the Foreign Key constraint defined on the civicrm_activity_assignment.assignee_contact_id field. You can avoid this by instructing MySQL to disable Foreign Key checks before the import and to re-enable it when the import is complete. </p>
  
  <p>Before importing the datafile.sql file, open it in a text editor and add the following MySQL code to the first line of the file:</p>
  
  <p><code>SET foreign_key_checks = 0;</code> </p>
  
  <p>And append the following code to the last line of the file:</p>
  
  <p><code>SET foreign_key_checks = 1;</code></p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T11:58:58.657Z
Answers.Text: Maybe I need to try to redo the imports but I _think_ I've tested importing files with or without the foreign key check. I even made two separate files labeled as such, but I get this error regardless. Thanks for the response.
  
  I'll redo it just because I'm paranoid. Also, does the PHPmyAdmin import setting for "Enable foreign key checks" override the SQL file's choice?
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T12:06:49.983Z
Answers.Text: I just checked - no dice. I even get the error with the backup sql of a fresh civicrm install. What a mess. Somehow it's moved on from acl to:
  
  getting hung up on `civicrm_activity`
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T21:42:01.703Z
Answers.Text: So you took and added the two items above to the SQL file you are importing? Because you'll get that pretty much whenever you're importing a SQL backup because it is looking for links to other tables that aren't created yet. And yes, that might override your settings. I don't have that turned on for mine, so I haven't tested it.
Answers.UserDisplayName: Jenni Simonis
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-13T14:18:07.377Z
Answers.Text: I checked the dumps for the Foreign Key settings, and they had them in it. It looks like the issue was most likely due to existing tables, as per petednz's comment. Regardless, thanks for the help.
Answers.UserDisplayName: Evan
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-09T11:41:57.580Z
Answers.LastActivityDate: 2018-04-09T11:41:57.580Z
Answers.UserDisplayName: Jenni Simonis
Body: <p>I'm having multiple issues with a CiviCRM 4.7.17 on WordPress 4.9.5 which has encouraged me to make different attempts to migrate the site following the official <a href="https://docs.civicrm.org/sysadmin/en/latest/misc/switch-servers/#moving-the-database-using-phpmyadmin" rel="nofollow noreferrer">SysAdmin Guide</a>. The closest I've gotten so far is:</p>
  
  <ul>
  <li>Clean WordPress install</li>
  <li>a migration of Wordpress.</li>
  <li>Removed all CiviCRM plugin files, settings, and the civi sql tables.</li>
  <li>Installed CiviCRM (same ver. as the original site) but I made a separate SQL database - could this cause issues when migrating?</li>
  </ul>
  
  <p>Attempted to import the original civi database via phpmyadmin but receive the following:</p>
  
  <pre><code>  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)',
    `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[...]
  MySQL said: Documentation
  
  #1215 - Cannot add foreign key constraint
  </code></pre>
  
  <p>After looking at various posts with similar errors, I can't make the import work. What gives?</p>
  
  <p>I believe the database itself is screwed up beyond my limited understanding which makes me think a "start from scratch" would be useful but losing all the data isn't acceptable. (One issue I've been having lead me realize other things were wrong: <a href="https://civicrm.stackexchange.com/questions/22945/">Contacts not merged When creating Account on Event Page - DB Error: already exists</a>)</p>
  
  <p>Is it at all possible to export all the data that's been user-created with Civi to import on a clean Civi install besides the way listed in the SysAdmin guide?</p>
  
  <p>ie. Move contacts (I know this can be exported via Civi), their history, custom profiles, custom fields, events, etc. to the testing site.</p>
  
Tags: <wordpress><mysql><migration>
Title: Issues with Migration - Cannot add foreign key constraint
ThreadText: # Issues with Migration - Cannot add foreign key constraint
  
  (Asked by: username_0 on 2018-04-09)
  
  I'm having multiple issues with a CiviCRM 4.7.17 on WordPress 4.9.5 which has encouraged me to make different attempts to migrate the site following the official SysAdmin Guide. The closest I've gotten so far is:
  
  Clean WordPress install
  a migration of Wordpress.
  Removed all CiviCRM plugin files, settings, and the civi sql tables.
  Installed CiviCRM (same ver. as the original site) but I made a separate SQL database - could this cause issues when migrating?
  
  Attempted to import the original civi database via phpmyadmin but receive the following:
    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)',
    `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[...]
  MySQL said: Documentation
  
  #1215 - Cannot add foreign key constraint
  
  After looking at various posts with similar errors, I can't make the import work. What gives?
  I believe the database itself is screwed up beyond my limited understanding which makes me think a "start from scratch" would be useful but losing all the data isn't acceptable. (One issue I've been having lead me realize other things were wrong: Contacts not merged When creating Account on Event Page - DB Error: already exists)
  Is it at all possible to export all the data that's been user-created with Civi to import on a clean Civi install besides the way listed in the SysAdmin guide?
  ie. Move contacts (I know this can be exported via Civi), their history, custom profiles, custom fields, events, etc. to the testing site.
  
  **Tags:** wordpress, mysql, migration
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2018-04-09. Score: 3 | Accepted answer
  
  It looks like your error message is truncated, which makes this a little hard to troubleshoot, so if you can find a way to generate a non-truncated version of this message, I think the solution should be clearer.
  
  That said - I'm looking at a CiviCRM database dump. The first two tables to be created are `civicrm_acl` and `civicrm_acl_cache`. Your error references `civicrm_acl` - but `civicrm_acl` doesn't have any foreign key constraints defined, so it makes no sense that you'd get this error on the line in your question.
  
  However, `civicrm_acl_cache` DOES have a foreign key constraint - on `civicrm_acl`. So I would assume that one of the following is true:
  
  * Your PHPMyAdmin user doesn't have the permission to create `civicrm_acl`, or perhaps to insert data into it.
  * Your database dump you're loading is missing data from `civicrm_acl`.
  
  Please check the following:
  
  * Does `civicrm_acl` exist after you've imported it? If not, check permissions.
  * Does `civicrm_acl` exist but contain no data? If yes, open your database dump in a text file and ensure that there's a line starting with `INSERT INTO `civicrm_acl` VALUES`. That line should be between the lines starting with `CREATE TABLE `civicrm_acl`` and `CREATE TABLE `civicrm_acl_cache`` - somewhere right near the top of the file.
  * If that line DOES exist, you might not have `INSERT` permissions.
  * If that line doesn't exist, you should try getting a dump that does.
  
  > **Comments:**
  > **username_0**: Thanks for the feedback.  • civicrm_acl has data in the dump. • civicrm_acl doesn't exist after importing it. • the sql user does have INSERT permissions.  Something I've learned since - I _can_ upload the backup without issue if the new databse is empty of tables. I only fear it'll leave any missing pieces from the old database intact. Time to test it next.  
  > **username_1**: Ideally the new database IS empty of tables - you might run into issues (including this one!) if your database isn't.  Importing the dump will create the tables; it's actually best to `DROP` the database then `CREATE` the database fresh before importing your dump.  
  > **username_0**: Ah. Ok. Well that solves that mystery. I don't understand why it wouldn't drop and create the tables but I'm glad it's figured out. I must have overlooked one of the steps in the guide.  
  > **username_1**: Yeah - the migration guide is an amalgam of multiple guides, and I can see the problem.  It tells you to export from PHPMyAdmin with the "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement" option. That makes sense with a different migration strategy that involves putting CiviCRM in the file system but not running the installer. I'll submit a ticket for this to get cleaned up.  
  > **username_1**: Submitted a ticket to improve the docs [here](https://github.com/civicrm/civicrm-sysadmin-guide/issues/85).  The advice to select the DROP TABLE option isn't good advice; it caused this problem and should be removed.  
  > **username_0**: Many thanks. That's one part of the guide I did follow. Ha!  
  
  ---
  
  ### Answer by username_2 on 2018-04-09. Score: 2
  
  You'll likely need to make some temporary changes to your files to avoid the foreign key error. I do the same when I have to do an import to fix schema integrity.
  
  Here's the page I am referencing: <https://wiki.civicrm.org/confluence/display/CRMDOC42/Ensuring+Schema+Integrity+on+Upgrades>
  
  Here's what it says:
  
  A note on possible Foreign Key Constraint Violation errors during import (and what to do about them): You may receive Foreign Key constraint violation errors on import because of the order in which the tables are exported and then imported, which is alphabetical.
  
  For example, data for the civicrm\_activity\_assignment table would be imported before data for the civicrm\_contact table, violating the Foreign Key constraint defined on the civicrm\_activity\_assignment.assignee\_contact\_id field. You can avoid this by instructing MySQL to disable Foreign Key checks before the import and to re-enable it when the import is complete.
  
  Before importing the datafile.sql file, open it in a text editor and add the following MySQL code to the first line of the file:
  
  `SET foreign_key_checks = 0;`
  
  And append the following code to the last line of the file:
  
  `SET foreign_key_checks = 1;`
  
  > **Comments:**
  > **username_0**: Maybe I need to try to redo the imports but I _think_ I've tested importing files with or without the foreign key check. I even made two separate files labeled as such, but I get this error regardless. Thanks for the response.  I'll redo it just because I'm paranoid. Also, does the PHPmyAdmin import setting for "Enable foreign key checks" override the SQL file's choice?  
  > **username_0**: I just checked - no dice. I even get the error with the backup sql of a fresh civicrm install. What a mess. Somehow it's moved on from acl to:  getting hung up on `civicrm_activity`  
  > **username_2**: So you took and added the two items above to the SQL file you are importing? Because you'll get that pretty much whenever you're importing a SQL backup because it is looking for links to other tables that aren't created yet. And yes, that might override your settings. I don't have that turned on for mine, so I haven't tested it.  
  > **username_0**: I checked the dumps for the Foreign Key settings, and they had them in it. It looks like the issue was most likely due to existing tables, as per petednz's comment. Regardless, thanks for the help.  
  
  ---
  
  