Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14335

foreign key silently becomes broken

    XMLWordPrintable

    Details

      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;
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              alice Alice Sherepa
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: