[MDEV-14335] foreign key silently becomes broken Created: 2017-11-09  Updated: 2019-09-30  Resolved: 2019-09-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2
Fix Version/s: 10.2.27, 10.3.18, 10.4.8

Type: Bug Priority: Major
Reporter: Alice Sherepa Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-17187 table doesn't exist in engine after A... Closed
Relates
relates to MDEV-13626 Merge InnoDB test cases from MySQL 5.7 Closed

 Description   

foreign key silently stops working after set foreign_key_checks = 0, drop index and again set foreign_key_checks = 1;

MariaDB [test]> create table t1(f1 int ,primary key(f1))engine=innodb;
 
Query OK, 0 rows affected (0.18 sec)
 
 
 
MariaDB [test]> create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
 
Query OK, 0 rows affected (0.17 sec)
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                                                      |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  KEY `t` (`f2`),
 
  KEY `t1` (`f2`,`f3`),
 
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> alter table t2 drop key t1;
 
Query OK, 0 rows affected (0.08 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> alter table t2 drop key t;
 
ERROR 1553 (HY000): Cannot drop index 't': needed in a foreign key constraint
 
MariaDB [test]> SET foreign_key_checks = 0;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> alter table t2 drop key t;
 
Query OK, 0 rows affected (0.14 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                            |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> insert into t2 values (1,1);
 
Query OK, 1 row affected (0.06 sec)
 
 
 
MariaDB [test]> SET foreign_key_checks = 1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> insert into t2 values (2,2);
 
Query OK, 1 row affected (0.02 sec)
 
 
 
MariaDB [test]> select * from t2;
 
+------+------+
 
| f2   | f3   |
 
+------+------+
 
|    1 |    1 |
 
|    2 |    2 |
 
+------+------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
Empty set (0.00 sec)
 
 
 
MariaDB [test]> alter table t2 add key  t(f2);
 
Query OK, 0 rows affected (0.19 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> insert into t2 values (3,3);
 
Query OK, 1 row affected (0.07 sec)
 
 
 
MariaDB [test]> show create table t2;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                              |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  KEY `t` (`f2`),
 
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t2;
 
+------+------+
 
| f2   | f3   |
 
+------+------+
 
|    1 |    1 |
 
|    2 |    2 |
 
|    3 |    3 |
 
+------+------+
 
3 rows in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t1;
 
Empty set (0.00 sec)

test case from innodb-index-online-fk.test (Mysql 5.7) in MariaDB query
'drop index t on t2' succeeded - should have failed with errno 1553...
https://github.com/MariaDB/server/blob/mdev-13626/mysql-test/suite/innodb/t/innodb-index-online-fk.test (line 494)

create table t1(f1 int,primary key(f1))engine=innodb;
 
create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
 
SET foreign_key_checks=0;
 
--error ER_DROP_INDEX_FK
 
drop index t on t2;
 
drop table t2;
 
drop table t1;



 Comments   
Comment by Marko Mäkelä [ 2017-11-09 ]

Is this really a bug? The

SET foreign_key_checks=0;

tells InnoDB to ignore foreign key constraints.
Therefore, it is kind-of OK to drop the index that InnoDB foreign key processing depends on.

What happens if foreign key checks are re-enabled? Will InnoDB throw error messages? Or crash? What if the server is restarted after successfully dropping the index?

Comment by Alice Sherepa [ 2017-11-09 ]

it looks like bad situation, when visually good table does not work.
mysql is not allowing to drop index, that is used in fk constraint.

Comment by Marko Mäkelä [ 2017-11-09 ]

alice, again, is this really a bug? I would claim that it is not, and MySQL is being inconsistent in refusing the DROP INDEX operation.
Note that with foreign_key_checks=0, MySQL will allow the creation of FOREIGN KEY constraints that refer to non-existing tables. Why should non-existing indexes in existing tables be any different?

Comment by Elena Stepanova [ 2017-11-09 ]

marko,
From what I see, the bug here is not that we allow to drop the key, but what happens after you re-enable foreign_key_checks.
At this point, your table t2 still claims to have the constraint, but you can insert rows which have no matches in the parent table. This shouldn't work:

MariaDB [test]> SET foreign_key_checks = 1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
MariaDB [test]> insert into t2 values (2,2);
 
Query OK, 1 row affected (0.02 sec)

Assuming that we allow to drop the key (which, I agree, is logical), at this point I'd expect Foreign key constraint is incorrectly formed or a similar error.

Further, it goes worse. After the test re-creates the dropped index, the table appears to have a perfectly valid structure foreign-key-wise, but you still can insert orphan rows:

MariaDB [test]> show create table t2;
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                                              |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `f2` int(11) DEFAULT NULL,
 
  `f3` int(11) DEFAULT NULL,
 
  KEY `t` (`f2`),
 
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> insert into t2 values (3,3);
 
Query OK, 1 row affected (0.07 sec)

It should throw the usual error ER_NO_REFERENCED_ROW_2, but it doesn't.

Granted, the effect only lasts till server restart, but it still doesn't look good.

MySQL apparently has chosen a simple way to deal with it, through forbidding to drop the key.

Comment by Marko Mäkelä [ 2019-09-30 ]

MDEV-17187 fixed this:

--source include/have_innodb.inc
 
create table t1(f1 int,primary key(f1))engine=innodb;
 
create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
 
--error ER_DROP_INDEX_FK
 
drop index t on t2;
 
SET foreign_key_checks=0;
 
drop index t on t2;
 
SET foreign_key_checks=1;
 
--error ER_NO_REFERENCED_ROW_2
 
insert into t2 values (3,3);
 
SET foreign_key_checks=0;
 
insert into t2 values (3,3);
 
drop table t2;
 
drop table t1;

With foreign_key_checks enabled, we throw an error for any DML statement that would violate the FOREIGN KEY constraint, or if an underlying index of the constraint is missing.

Generated at Thu Feb 08 08:12:45 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.