// Schema used for tests DROP TABLE IF EXISTS t2; DROP TABLE IF EXISTS t1; CREATE TABLE t2 ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE t1 ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, title longtext, PRIMARY KEY (id), FULLTEXT idx (title), FOREIGN KEY (id) REFERENCES t2 (id) ON DELETE CASCADE ON UPDATE CASCADE ); // 5.6.28 - not affected [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> DROP TABLE IF EXISTS t2; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE t2 ( -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> name CHAR(60) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE t1 ( -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> title longtext, -> PRIMARY KEY (id), -> FULLTEXT idx (title), -> FOREIGN KEY (id) REFERENCES t2 (id) ON DELETE CASCADE ON UPDATE CASCADE -> ); Query OK, 0 rows affected (0.01 sec) mysql> \q Bye -- backup [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock --databases test > /tmp/78955.sql -- drop schema, restore and confirm [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop database test; Query OK, 2 rows affected (0.01 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < /tmp/78955.sql [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.28: bin/mysql -uroot -S /tmp/mysql_ushastry.sock test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.6.28-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | | t2 | +----------------+ 2 rows in set (0.00 sec) ### 5.7.9 - affected [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.9 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> DROP TABLE IF EXISTS t2; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> DROP TABLE IF EXISTS t1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE t2 ( -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> name CHAR(60) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE TABLE t1 ( -> id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> title longtext, -> PRIMARY KEY (id), -> FULLTEXT idx (title), -> FOREIGN KEY (id) REFERENCES t2 (id) ON DELETE CASCADE ON UPDATE CASCADE -> ); Query OK, 0 rows affected (0.01 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysqldump -uroot -S /tmp/mysql_ushastry.sock --databases test > /tmp/78955_5.7.9.sql [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: -- drop schema, restore and confirm [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.9 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> drop database test; Query OK, 2 rows affected (0.01 sec) mysql> \q Bye -- [umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock --verbose < /tmp/78955_5.7.9.sql -------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ -------------- -------------- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ -------------- -------------- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ -------------- -------------- /*!40101 SET NAMES utf8 */ -------------- -------------- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ -------------- -------------- /*!40103 SET TIME_ZONE='+00:00' */ -------------- -------------- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ -------------- -------------- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ -------------- -------------- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ -------------- -------------- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ -------------- -------------- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -------------- -------------- DROP TABLE IF EXISTS `t1` -------------- -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- -------------- /*!40101 SET character_set_client = utf8 */ -------------- -------------- CREATE TABLE `t1` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` longtext, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`title`), CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1215 (HY000) at line 33: Cannot add foreign key constraint ### 5.7.10 - affected [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock --verbose < /tmp/78955_5.7.9.sql -------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ -------------- -------------- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ -------------- -------------- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ -------------- -------------- /*!40101 SET NAMES utf8 */ -------------- -------------- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ -------------- -------------- /*!40103 SET TIME_ZONE='+00:00' */ -------------- -------------- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ -------------- -------------- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ -------------- -------------- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ -------------- -------------- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ -------------- -------------- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -------------- -------------- DROP TABLE IF EXISTS `t1` -------------- -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- -------------- /*!40101 SET character_set_client = utf8 */ -------------- -------------- CREATE TABLE `t1` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` longtext, PRIMARY KEY (`id`), FULLTEXT KEY `idx` (`title`), CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- ERROR 1215 (HY000) at line 33: Cannot add foreign key constraint ######### without FULLTEXT [umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: bin/mysql -uroot -S /tmp/mysql_ushastry.sock --verbose < /tmp/78955_5.7.9.sql -------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ -------------- -------------- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */ -------------- -------------- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */ -------------- -------------- /*!40101 SET NAMES utf8 */ -------------- -------------- /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */ -------------- -------------- /*!40103 SET TIME_ZONE='+00:00' */ -------------- -------------- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */ -------------- -------------- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */ -------------- -------------- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */ -------------- -------------- /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */ -------------- -------------- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -------------- -------------- DROP TABLE IF EXISTS `t1` -------------- -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- -------------- /*!40101 SET character_set_client = utf8 */ -------------- -------------- CREATE TABLE `t1` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `title` longtext, PRIMARY KEY (`id`), CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- -------------- /*!40101 SET character_set_client = @saved_cs_client */ -------------- -------------- LOCK TABLES `t1` WRITE -------------- -------------- /*!40000 ALTER TABLE `t1` DISABLE KEYS */ -------------- -------------- /*!40000 ALTER TABLE `t1` ENABLE KEYS */ -------------- -------------- UNLOCK TABLES -------------- -------------- DROP TABLE IF EXISTS `t2` -------------- -------------- /*!40101 SET @saved_cs_client = @@character_set_client */ -------------- -------------- /*!40101 SET character_set_client = utf8 */ -------------- -------------- CREATE TABLE `t2` ( `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `name` char(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -------------- -------------- /*!40101 SET character_set_client = @saved_cs_client */ -------------- -------------- LOCK TABLES `t2` WRITE -------------- -------------- /*!40000 ALTER TABLE `t2` DISABLE KEYS */ -------------- -------------- /*!40000 ALTER TABLE `t2` ENABLE KEYS */ -------------- -------------- UNLOCK TABLES -------------- -------------- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */ -------------- -------------- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */ -------------- -------------- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */ -------------- -------------- /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */ -------------- -------------- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */ -------------- -------------- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */ -------------- -------------- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */ -------------- -------------- /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */ --------------