4

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.

3

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.
  • 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. – Evan Apr 9 '18 at 16:22
  • 2
    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. – Jon G - Megaphone Tech Apr 9 '18 at 16:30
  • 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. – Evan Apr 9 '18 at 17:01
  • 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. – Jon G - Megaphone Tech Apr 9 '18 at 19:02
  • 1
    Submitted a ticket to improve the docs here. The advice to select the DROP TABLE option isn't good advice; it caused this problem and should be removed. – Jon G - Megaphone Tech Apr 9 '18 at 19:06
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;

  • 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? – Evan Apr 9 '18 at 11:58
  • 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 – Evan Apr 9 '18 at 12:06
  • 1
    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. – Jenni Simonis Apr 9 '18 at 21:42
  • 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. – Evan Apr 13 '18 at 14:18

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.