MySQL Bugs: #89220: MySql - Error Code: 1215. Cannot add foreign key constraint if fulltext index ad

Bug #89220	MySql - Error Code: 1215. Cannot add foreign key constraint if fulltext index ad
Submitted:	13 Jan 2018 11:53	Modified:	13 Jan 2018 20:32
Reporter:	Akshay Vadher	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S2 (Serious)
Version:	5.7.8-rc	OS:	Ubuntu (Ubuntu (14.04.4 LTS (GNU/Linux 3.19.0-25-generic x86_64)))
Assigned to:		CPU Architecture:	Any
Tags:	fulltext

Description:
Adding a fulltext index in DDL says foreign key issue

How to repeat:
I have a database and i took up a dump using --no-data. I am trying to import that DB structure in an empty DB.

I am ignoring other queries, following the queries I am trying to execute,

set foreign_key_checks=0;
DROP TABLE IF EXISTS `ACTIVITY`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ACTIVITY` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  ...
  `ACTIVITY_BY_ID` int(11) DEFAULT NULL,
  `ENTITY_TYPE` varchar(45) COLLATE utf8_unicode_ci NOT NULL, 
  `ACTIVITY_TYPE` varchar(45) CHARACTER SET utf8 NOT NULL, 
  ...
  PRIMARY KEY (`ID`),
  KEY `FK_ACTIVITY_TO_USER_idx` (`ACTIVITY_BY_ID`),
  FULLTEXT KEY `FK_FULL_TEXT_ACTIVITY_TYPE` (`ACTIVITY_TYPE`), -- this
  FULLTEXT KEY `FK_FULL_TEXT_ENTITY_TYPE` (`ENTITY_TYPE`), -- this
  ...
  CONSTRAINT `FK_ACTIVITY_TO_USER` FOREIGN KEY (`ACTIVITY_BY_ID`) REFERENCES `USER` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
The above code fails saying Error Code: 1215. Cannot add foreign key constraint

It should work as foreign_key_checks is set to 0.

Below is the sql for importing USER table. However this sql is being executed later.

DROP TABLE IF EXISTS `USER`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `USER` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  ...
  PRIMARY KEY (`ID`),
  ..
) ENGINE=InnoDB AUTO_INCREMENT=4070 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
If I execute the USER script first and then ACTIVITY, it works, otherwise it does not.

The lines I marked with --this for the table ACTIVITY is causing the issue. If I remove the two lines, code works fine.

Why a fulltext index create a foreign key issue?

https://stackoverflow.com/questions/48239328/mysql-error-code-1215-cannot-add-foreign-key-...

Thank you for the bug report. Please, first when reporting a bug follow the instructions how to report a bug at:

https://bugs.mysql.com/how-to-report.php

Where you can see:

"Always search the bug database first."

"Be brief, but don't leave any important details out." (You have provided partial CREATE TABLE statement, so no test repeatable case).

"Don't report bugs about old versions." You are using a quite older version 5.7.8 when current released version is: 5.7.20.

This bug report looks like duplicate of which was fixed on 5.7.11.

https://bugs.mysql.com/bug.php?id=78955

miguel@tikal:~ $ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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 bg;
Query OK, 1 row affected (0.00 sec)

mysql> USE bg;
Database changed
mysql> SET FOREIGN_KEY_CHECKS = 0;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `table` (   `node` int(10) unsigned NOT NULL,   `index` longtext,   PRIMARY KEY (`node`),   FULLTEXT KEY `index` (`index`),   FOREIGN KEY (`node`) REFERENCES `node` (`node`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB;
Query OK, 0 rows affected (8.06 sec)

mysql> SHOW CREATE TABLE `table`\G
*************************** 1. row ***************************
       Table: table
Create Table: CREATE TABLE `table` (
  `node` int(10) unsigned NOT NULL,
  `index` longtext,
  PRIMARY KEY (`node`),
  FULLTEXT KEY `index` (`index`),
  CONSTRAINT `table_ibfk_1` FOREIGN KEY (`node`) REFERENCES `node` (`node`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec