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?
The usual cause are generally a mismatch in the type of the column of the primary table and the foreign table. It can also be a mismatch in the Engine type of two tables i.e. MyISAM or InnoDB. Datatype both columns should have same datatype. int(11) on one table and smallint(5) on another will cause problem.
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
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?
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With