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

DROP DATABASE drops unrelated FOREIGN KEY constraints

    XMLWordPrintable

    Details

      Description

      After MDEV-25691, the following modification to a test would cause a test failure:

      diff --git a/mysql-test/suite/innodb/t/alter_foreign_crash.test b/mysql-test/suite/innodb/t/alter_foreign_crash.test
      index e2a6c301bf7..b65dfa175c2 100644
      --- a/mysql-test/suite/innodb/t/alter_foreign_crash.test
      +++ b/mysql-test/suite/innodb/t/alter_foreign_crash.test
      @@ -2,6 +2,7 @@
       --source include/have_debug_sync.inc
       # The embedded server does not support restarting.
       --source include/not_embedded.inc
      +--source include/have_case_sensitive_file_system.inc
       
       --echo #
       --echo # Bug #20476395 DICT_LOAD_FOREIGNS() FAILED IN
      @@ -10,6 +11,11 @@
       
       call mtr.add_suppression("InnoDB: Failed to load table");
       
      +CREATE DATABASE Bug;
      +CREATE TABLE Bug.parent(a SERIAL) ENGINE=INNODB;
      +CREATE TABLE Bug.child(a SERIAL, FOREIGN KEY f(a) REFERENCES Bug.parent(a))
      +ENGINE=INNODB;
      +
       create database bug;
       use bug;
       
      @@ -18,6 +24,7 @@ create table child(a serial, foreign key fk (a) references parent(a))engine=inno
       
       insert into parent values(1);
       insert into child values(1);
      +drop database Bug;
       
       connect (con1,localhost,root,,bug);
       SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL s1 WAIT_FOR s2 EXECUTE 2';
      

      CURRENT_TEST: innodb.alter_foreign_crash
      mysqltest: At line 44: query 'drop table parent' succeeded - should have failed with error ER_ROW_IS_REFERENCED_2 (1451)...
      

      The problem is that the primary key of SYS_FOREIGN.ID is in latin1_swedish_ci and not are in a binary collation. Even though table names are in binary collation, the internal name Bug/fk would clash with bug/fk. Hence, the above test creates Buf/f. On DROP DATABASE Bug, also the constraint bug/fk would be removed. The following fixes that:

      diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
      index 4a3be1fb1d8..2b6089c4814 100644
      --- a/storage/innobase/handler/ha_innodb.cc
      +++ b/storage/innobase/handler/ha_innodb.cc
      @@ -1421,8 +1421,9 @@ static void innodb_drop_database(handlerton*, char *path)
           "WHILE 1 = 1 LOOP\n"
           "  FETCH fkf INTO fk;\n"
           "  IF (SQL % NOTFOUND) THEN EXIT; END IF;\n"
      -    "  IF SUBSTR(fk, 0, LENGTH(:db)) <> :db THEN EXIT; END IF;\n"
      -    "  DELETE FROM SYS_FOREIGN_COLS WHERE ID=fk;\n"
      +    "  IF TO_BINARY(SUBSTR(fk, 0, LENGTH(:db)))<>TO_BINARY(:db)"
      +    " THEN EXIT; END IF;\n"
      +    "  DELETE FROM SYS_FOREIGN_COLS WHERE TO_BINARY(ID)=TO_BINARY(fk);\n"
           "  DELETE FROM SYS_FOREIGN WHERE CURRENT OF fkf;\n"
           "END LOOP;\n"
           "CLOSE fkf;\n"
      

      In earlier versions (before MDEV-25691 cleaned up the code in 10.6.1), there is an even more severe bug: not only unrelated constraints but also unrelated tables are being dropped:

      10.5 9bbedcdd590f2c25c79bd9f367a7f54c1fcefdcb

      CURRENT_TEST: innodb.alter_foreign_crash
      mysqltest: At line 42: query 'select * from child' failed: 1146: Table 'bug.child' doesn't exist
      

      That problem probably affects all previous InnoDB versions, so I would not call the milder bug in 10.6 a regression:

      10.2 629449172a5b0a6975663ca1ac420789e00b941d

      CURRENT_TEST: innodb.alter_foreign_crash
      mysqltest: At line 42: query 'select * from child' failed: 1146: Table 'bug.child' doesn't exist
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              marko Marko Mäkelä
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration