Mysql转储还原失败:无法添加外键约束

Question

我正在尝试恢复使用mysqldump创建的转储。在恢复时，我得到了这个

第63行出现错误1215（HY000）：无法添加外键约束

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 */;

create table语句的顺序是相同的。

SHOW ENGINE INNODB STATUS\G给我的是：

------------------------
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.

有人能告诉我这里发生了什么事吗？

Answer 1

我也收到了这个错误。

我想你和我做了同样的事情：将整个DB设置为UTF8，然后将一些列/表更改为UTF8MB4。

我不知道该怎么解决。但是，有一个解决方法：在转储SQL文件中将所有UTF8MB4更改回UTF8，将其恢复到DB中，并通过以下命令手动将特定列更改为UTF8MB4：

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;

Answer 2

我的转储和数据库中没有utf8mb4字符集，但出现了问题。

我解决了删除所有表并在之后恢复我的转储的问题。

使用以下内容：如何在没有DROP数据库权限的情况下从命令行中删除所有MySQL表？

Answer 3

问题的另一种可能解释是，当执行mysqldump时，转储文件顶部生成的CREATE TABLE节可能不完整。转储文件通常以一段ALTER TABLE语句结尾，这些语句用于创建主键、外键和索引等。

当您恢复这样的转储文件（或提取）时，MySQL会尝试在数据到达ALTER TABLE语句之前插入数据。当某些数据库文件已经存在（即只有一个表正在恢复）时，这可能会生成OP提到的错误。为了解决这个问题，您可以修改生成的CREATE TABLE语句，使其完整，并且不需要任何其他ALTER TABLE语句。

Answer 4

无论这对谁有帮助，我都遇到了同样的问题，因为我使用了mysqldump的--compact选项。

事实证明，此选项会生成一个不设置FOREIGN_KEY_CHECKS=0的脚本。

文档中没有写到此选项会生成无法按原样用于数据库重建的转储，因此请注意这一点。