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

dict_load_foreign() fails to load the table during alter

    XMLWordPrintable

    Details

      Description

      The following test case repeats the problem:

      --source include/have_innodb.inc
      set foreign_key_checks=0;
      CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 int as (f2) virtual,
                      index(f3), index(f3, f2))engine=innodb;
      ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f3) REFERENCES t1(f3), LOCK=NONE;
      ALTER TABLE t1 DROP IF EXISTS f3;
      SHOW CREATE TABLE t1;
      DROP TABLE t1;
      

      InnoDB shouldn't allow foreign key on virtual column. Final alter drops the virtual column
      and fail to load the index for the table entries in SYS_FOREIGN table.

      Even other test case fails:

      --source include/have_innodb.inc
      set foreign_key_checks=0;
      CREATE TABLE t0(f1 int not null, index(f1))engine=innodb;
      CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 int as (f2) virtual,
                      index(f3), index(f3, f2))engine=innodb;
      ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f3) REFERENCES t0(f1), LOCK=NONE;
      ALTER TABLE t1 DROP IF EXISTS f3;
      SHOW CREATE TABLE t1;
      DROP TABLE t1;
      

      The above test case also aborts the server.

      Error log contain the following:
      2020-08-03 19:09:24 140332217923328 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

        Attachments

          Activity

            People

            Assignee:
            thiru Thirunarayanan Balathandayuthapani
            Reporter:
            thiru Thirunarayanan Balathandayuthapani
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration