Bug #74659 Foreign key constraint ignored when index is removed

• Submitted: 2 Nov 2014 10:51
• Modified: 12 Oct 2015 5:34
• Reporter: Daniël van Eeden (OCA)
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.6.21, 5.6.22, 5.5.41
• OS: Any
• CPU Architecture: Any
• Tags: foreign key, INDEX, innodb

[2 Nov 2014 10:51] Daniël van Eeden

Description:
When the index for a foreign key is removed this basically disables the constraint.

This is related to Bug #68148.

How to repeat:
mysql [fktest] > select @@session.foreign_key_checks;
+------------------------------+
| @@session.foreign_key_checks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql [fktest] > create table t1 (id int not null primary key, name varchar(100) not null);
Query OK, 0 rows affected (0.01 sec)

mysql [fktest] > create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql [fktest] > insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [fktest] > insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql [fktest] > set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)

mysql [fktest] > alter table t2 drop index `fk_fid`;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [fktest] > set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)

mysql [fktest] > insert into t2 values(4, 99, 'test 1');
Query OK, 1 row affected (0.00 sec)

mysql [fktest] > show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.00 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
|  4 |  99 | test 1 |
+----+-----+--------+
4 rows in set (0.00 sec)

------------
After a restart t2 is not accessible 

mysql [fktest] > select * from t2;
ERROR 1146 (42S02): Table 'fktest.t2' doesn't exist
mysql [fktest] > show create table t2\G
ERROR 1146 (42S02): Table 'fktest.t2' doesn't exist

The following messages are printed to the error log as expected:
2014-11-02 11:42:53 17852 [Warning] InnoDB: Load table 'fktest/t2' failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
2014-11-02 11:42:53 17852 [Warning] InnoDB: Cannot open table fktest/t2 from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

After disabling foreign key checks t2 becomes accessible again.

But then I can enable the foreign key checks and t2 is still available. Even after running 'FLUSH TABLES t2' to get MySQL to close and open the table again.

Suggested fix:
If the fk constraint can't be checked then raise an error instead of ignoring the fk constraint. Or at least generate a warning. Maybe also print a log message to the error log.

Don't only check the fk constraint the first time the table is opened (after startup).

Maybe add the missing index when the table is openend and the print a warning/notice to the error log?

[3 Nov 2014 7:14] MySQL Verification Team

Hello Daniël,

Thank you for the bug report and test case.
Confirmed that 5.6.21/5.6.22 affected.

Thanks,
Umesh

[3 Nov 2014 7:14] MySQL Verification Team

// 5.6.21/5.6.22

mysql> select @@session.foreign_key_checks;
+------------------------------+
| @@session.foreign_key_checks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql> create table t1 (id int not null primary key, name varchar(100) not null);
Query OK, 0 rows affected (0.02 sec)

mysql>  create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_fid` (`fid`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.00 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
+----+-----+--------+
3 rows in set (0.00 sec)

mysql> set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t2 drop index `fk_fid`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(4, 99, 'test 1');
Query OK, 1 row affected (0.01 sec)

mysql> show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.00 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
|  4 |  99 | test 1 |
+----+-----+--------+
4 rows in set (0.00 sec)

mysql> \q
Bye
[root@cluster-repo mysql-advanced-5.6.22]# ps aux|grep mysqld
root     25886  0.3  9.8 3413872 600648 pts/0  Sl   13:55   0:01 /data/ushastry/server/mysql-advanced-5.6.22/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-advanced-5.6.22 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --user=root
root     25951  0.0  0.0 103256   832 pts/0    R+   14:02   0:00 grep mysqld
[root@cluster-repo mysql-advanced-5.6.22]# kill -9 25886
[root@cluster-repo mysql-advanced-5.6.22]#
[1]+  Killed                  /data/ushastry/server/mysql-advanced-5.6.22/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-advanced-5.6.22 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --user=root 2>&1
[root@cluster-repo mysql-advanced-5.6.22]# /data/ushastry/server/mysql-advanced-5.6.22/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-advanced-5.6.22 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --user=root 2>&1 &
[1] 25952
[root@cluster-repo mysql-advanced-5.6.22]#
[root@cluster-repo mysql-advanced-5.6.22]#
[root@cluster-repo mysql-advanced-5.6.22]# 2014-11-05 14:03:08 0 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.
2014-11-05 14:03:08 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

[root@cluster-repo mysql-advanced-5.6.22]# bin/mysql -u root -p test
Enter password:
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 1
Server version: 5.6.22-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2014, 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)

mysql> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

[3 Nov 2014 7:15] MySQL Verification Team

// 5.7.6 - Doesn't allow to drop index

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.6                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.6-m16-enterprise-commercial-advanced                |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> show variables like '%sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.foreign_key_checks;
+------------------------------+
| @@session.foreign_key_checks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql> create table t1 (id int not null primary key, name varchar(100) not null);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_fid` (`fid`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.01 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
+----+-----+--------+
3 rows in set (0.00 sec)

mysql> set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t2 drop index `fk_fid`;
ERROR 1553 (HY000): Cannot drop index 'fk_fid': needed in a foreign key constraint
mysql>

[3 Nov 2014 7:24] MySQL Verification Team

// 5.5.41
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.41                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.41                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> select @@session.foreign_key_checks;
+------------------------------+
| @@session.foreign_key_checks |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set (0.00 sec)

mysql>  create table t1 (id int not null primary key, name varchar(100) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_fid` (`fid`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.00 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
+----+-----+--------+
3 rows in set (0.00 sec)

mysql>  set foreign_key_checks=off;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t2 drop index `fk_fid`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set foreign_key_checks=on;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(4, 99, 'test 1');
Query OK, 1 row affected (0.00 sec)

mysql> show create table t1\Gshow create table t2\Gselect * from t1; select * from t2;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

+----+--------+
| id | name   |
+----+--------+
|  1 | test 1 |
|  2 | test 2 |
|  3 | test 3 |
+----+--------+
3 rows in set (0.00 sec)

+----+-----+--------+
| id | fid | name   |
+----+-----+--------+
|  1 |   1 | test 1 |
|  2 |   1 | test 2 |
|  3 |   2 | test 3 |
|  4 |  99 | test 1 |
+----+-----+--------+
4 rows in set (0.00 sec)

mysql> \q
Bye
[root@cluster-repo mysql-5.5.41]# ps aux|grep mysqld
root     26148  0.4  3.4 2649588 212704 pts/0  Sl   14:26   0:00 /data/ushastry/server/mysql-5.5.41/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-5.5.41 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --user=root
root     26174  0.0  0.0 103256   828 pts/0    S+   14:29   0:00 grep mysqld
[root@cluster-repo mysql-5.5.41]# kill -9 26148
[root@cluster-repo mysql-5.5.41]# /data/ushastry/server/mysql-5.5.41/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-5.5.41 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock  --port=3306 --log-error=/tmp/bug/log.err --user=root 2>&1 &
[2] 26176
[1]   Killed                  /data/ushastry/server/mysql-5.5.41/bin/mysqld --innodb_buffer_pool_size=2147483648 --basedir=/data/ushastry/server/mysql-5.5.41 --datadir=/tmp/bug --core --socket=/tmp/mysql.sock --port=3306 --log-error=/tmp/bug/log.err --user=root 2>&1
[root@cluster-repo mysql-5.5.41]# 141105 14:29:14 [Warning] Using unique option prefix core instead of core-file is deprecated and will be removed in a future release. Please use the full name instead.

[root@cluster-repo mysql-5.5.41]# bin/mysql -u root -p test
Enter password:
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 1
Server version: 5.5.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> select * from t2;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

[3 Nov 2014 8:04] Daniël van Eeden

PostgreSQL 9.4 does not create the index on the child table and correctly enforces the constraint. The same is true for SQLite3.

fktest=# create table t1 (id int not null primary key, name varchar(100) not null);                                                                             CREATE TABLE
fktest=# create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id));
CREATE TABLE
fktest=# \d t1
              Table "public.t1"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(100) | not null
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "t2" CONSTRAINT "fk_fid" FOREIGN KEY (fid) REFERENCES t1(id)

fktest=# \d t1
              Table "public.t1"
 Column |          Type          | Modifiers 
--------+------------------------+-----------
 id     | integer                | not null
 name   | character varying(100) | not null
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "t2" CONSTRAINT "fk_fid" FOREIGN KEY (fid) REFERENCES t1(id)

fktest=# insert into t1 values (1,'test');
INSERT 0 1
fktest=# insert into t2 values (1, 99, 'test');
ERROR:  insert or update on table "t2" violates foreign key constraint "fk_fid"
DETAIL:  Key (fid)=(99) is not present in table "t1".

$ sqlite3 /tmp/foo
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> create table t1 (id int not null primary key, name varchar(100) not null);
sqlite> create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT fk_fid FOREIGN KEY (fid) REFERENCES t1 (id));
sqlite> .indices t1
sqlite_autoindex_t1_1
sqlite> .indices t2
sqlite_autoindex_t2_1
sqlite> insert into t1 values (1,'test');
sqlite> insert into t2 values (1, 1,'test');
sqlite> insert into t2 values (1, 99,'test');
Error: UNIQUE constraint failed: t2.id
sqlite>

[10 Nov 2014 10:59] Marko Mäkelä

Posted by developer:
 
I implemented ALTER TABLE...DROP FOREIGN KEY and ALTER TABLE...DROP INDEX for ALGORITHM=INPLACE in MySQL 5.6, in WL#5545 and WL#5526. Jimmy Yang implemented ALTER TABLE...ADD FOREIGN KEY...ALGORITHM=INPLACE in WL#6251 (when foreign_key_checks=off).

That said, this seems to be a design problem in all InnoDB versions.

The foreign key checks are implemented by row_ins_foreign_check_on_constraint() and row_upd_check_references_constraints(). If you drop the 'backing index' from the child table, then it seems that the check will be skipped, by design.

In InnoDB, a DML operation is implemented like this:

1. Write undo log record.
2. Check lock on the clustered index.
3. Attempt to modify the clustered index.
4. For each secondary index:
4a. Check lock on the secondary index.
4b. Attempt to modify the secondary index.

The foreign key check would normally be performed at step 4b, and a failure would lead to a partial rollback, going back to undo log as it was before step 1.

As far as I can tell, rollback, purge and MVCC are unaffected by this bug. In a cascaded operation, step 3 or step 4b would generate undo log on modifying parent or child tables.

I guess that PostgreSQL and SQLite are performing table scans in order to satisfy foreign key constraints when there is no underlying index. The current design of InnoDB does not allow that. I do not think that we can change this behaviour in MySQL 5.7 or earlier.

In current versions of MySQL (up to version 5.7), it should be doable to set a flag when some underlying index for a FOREIGN KEY constraint is missing. When the flag is set, we should refuse any operation that would generate undo log.

[12 Oct 2015 5:34] ADITYA ANANTHAPADMANABHA

Duplicate of http://bugs.mysql.com/bug.php?id=70260