Bug #78955 Import Dump with InnoDB Fulltext Index and Constraint fails
Submitted: 24 Oct 2015 21:28 Modified: 13 Nov 2015 13:34
Reporter: Nico Niepraschk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.7.9, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: constraint, fulltext, innodb, regression

[24 Oct 2015 21:28] Nico Niepraschk 
Description:
"SET FOREIGN_KEY_CHECKS = 0" will be ignored when a fulltext index is present

How to repeat:
This fails with "ERROR 1215 (HY000): Cannot add foreign key constraint":

SET FOREIGN_KEY_CHECKS = 0;

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 DEFAULT;

----------------

This works:

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `table` (
  `node` int(10) unsigned NOT NULL,
  `index` longtext,
  PRIMARY KEY (`node`),
  FOREIGN KEY (`node`) REFERENCES `node` (`node`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT;
[24 Oct 2015 21:32] Nico Niepraschk 
Version concretised
[25 Oct 2015 12:17] MySQL Verification Team 
Hello Nico Niepraschk,

Thank you for the report.
Verified as described with 5.7.9/5.7.10 builds.

Thanks,
Umesh
[25 Oct 2015 12:18] MySQL Verification Team 
test results

Attachment: 78955.results (application/octet-stream, text), 13.37 KiB.
[12 Nov 2015 3:11] Shaohua Wang 
Posted by developer:
 
we call fts_create_index_tables to create fts aux tables in row_create_index_for_mysql(), and commit the trx, which reset trx_t::check_foreigns to true. The solution is calling ts_create_index_tables_low instead.
[13 Nov 2015 13:34] Daniel Price 
Posted by developer:
 
Fixed as of the upcoming 5.7.11, 5.8.0 releases, and here's the changelog entry:

Creating a table with a full-text index and a foreign key constraint
would fail when foreign_key_checks is disabled. 

Thank you for the bug report.
[15 Jan 2016 5:58] MySQL Verification Team 
Bug #79992 marked as duplicate of this