11

I'm trying to restore a dump that I created using mysqldump. On restoring the same, I get this

ERROR 1215 (HY000) at line 63: Cannot add foreign key constraint

DROP TABLE IF EXISTS `channel_tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `channel_tags` (
  `channel_tag_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `channel_id` bigint(20) NOT NULL,
  `tag_name` varchar(60) NOT NULL,
  PRIMARY KEY (`channel_tag_id`),
  KEY `channel_id_idx` (`channel_id`),
  KEY `tag_name_idx` (`tag_name`),
  CONSTRAINT `ct_channel_fk` FOREIGN KEY (`channel_id`) REFERENCES `channel_shard` (`channel_id`),
  CONSTRAINT `ct_tag_fk` FOREIGN KEY (`tag_name`) REFERENCES `tags` (`tag_name`)
) ENGINE=InnoDB AUTO_INCREMENT=833 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

DROP TABLE IF EXISTS `tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tags` (
  `tag_name` varchar(60) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

The order of the create table statements is the same.

SHOW ENGINE INNODB STATUS\G gives me this:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2015-12-07 17:20:16 1ac30b000 Error in foreign key constraint of table sde/channel_tags:
 FOREIGN KEY (`tag_name`) REFERENCES `tags` (`tag_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Can someone let me know what's going on here?

Saksham Gupta
  • 223
  • 1
  • 2
  • 8
  • 1
    The foreign key doesn't exist, due to alphabetical order of creating the tables. Since `channel_tags` can't "see" that the table `tags` exists, it can't create the FK - and you get a failure. The usual way of dealing with this is that you export create table statements **without any foreign key constraits** and after all the tables are created, you run `alter table` statements in which foreign key constraits are added. Your import will work if you create table `tags` before you create `channel_tags`. – Mjh Dec 07 '15 at 12:26
  • 3
    mysqldump generates a conditional: `/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;` on top of every dump file. So I doubt that's the reason. – Saksham Gupta Dec 07 '15 at 12:28
  • 2
    Btw, removing the default utfmb8 charset from both tables resolves the issue. Not sure how to go about fixing the dump generation. – Saksham Gupta Dec 07 '15 at 12:29
  • 2
    Had the same problem; solved by removing all instances of `DEFAULT CHARSET=utf8mb4` from the dump file; cannot for the life of me figure out why this is. Please someone out there enlighten me. – Ithar Mar 15 '16 at 11:23

4 Answers4

5

I got this error too.

I guess you did the same thing as me: setup the whole DB as UTF8 and change some columns/tables to UTF8MB4 after that.

I have no idea how to resolve it. But, there's a workaround: change all UTF8MB4 back to UTF8 in the dump SQL file, restore it into DB, and alter the specific column to UTF8MB4 manually by these command:

ALTER DATABASE [dbname] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE [tablename] CHANGE [colname] [colname] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE [tablename] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;
Unheilig
  • 16,196
  • 193
  • 68
  • 98
Wilson Li
  • 51
  • 1
  • 2
  • LOL, Thank you for helping me correct the mistake , Unheilig. – Wilson Li Apr 03 '17 at 00:32
  • I had the same issue -- my local DB was default UTF8, and the remove DB was default Latin1, so I was unable to restore a mysqldump file with this cryptic error. I'd love to see a better fix than manually removing the "default charset" statements from the dump file. – Rich Apr 26 '18 at 10:33
  • 1
    If you run it as a script you probably would need to use SET GLOBAL FOREIGN_KEY_CHECKS=0; – Stan Sokolov Jul 26 '18 at 16:41
5

I had no utf8mb4 charsets in my dump and db, but got the problem.

I solved it removing all the tables and restoring my dump after.

Using the following : How to remove all MySQL tables from the command-line without DROP database permissions?

Marc Maurice
  • 51
  • 1
  • 2
0

Another possible explanation for the problem is that when you do a mysqldump, the generated CREATE TABLE stanzas at the top of the dump file can be incomplete. The dump file usually ends with a section of ALTER TABLE statements, which are used to create the Primary Key, Foreign Keys and Indexes, etc.

When you are restoring such a dump file (or extract), MySQL tries to insert the data before it gets to the ALTER TABLE statements. This can generate the error the OP mentioned when some of the database files already exist (i.e. only one table is being restored). To overcome this issue, what you can do is modify the generated CREATE TABLE statement such that is it complete and does not require any further ALTER TABLE statements.

gone
  • 1,079
  • 5
  • 13
  • 31
0

For whoever this may help, I had the same issue because I used the --compact option of mysqldump.

It turns out that this option produces a script that does not set FOREIGN_KEY_CHECKS=0.

It is not written in the documentation that this option produces dumps that cannot be used as-is for database reconstruction, so beware of that.

user209974
  • 1,737
  • 2
  • 15
  • 31