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

Self referencing foreign key fails with table does not exist in engine

    XMLWordPrintable

    Details

      Description

      A self-referencing foreign key for some reason is only accepted with an extra supporting key. With that in place, a self referencing can be created and then the extra key can be created. Once the MariaDB is restarted though the table is reported as not existing. STurning off foreign key checks makes it appear again, and then all is well, even if FK cjecls are reenabled, until a new restart of MariaDB. The MariaDB Error log reports:
      [Warning] InnoDB: Load table `test`.`fkself` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.

      To reproduce, first run the following script

      DROP TABLE IF EXISTS fkself;
       
      SET foreign_key_checks=0;
       
      CREATE TABLE fkself(
        id INTEGER NOT NULL PRIMARY KEY,
        KEY(id));
       
      ALTER TABLE fkself ADD CONSTRAINT self FOREIGN KEY(id) REFERENCES fkself(id);
      ALTER TABLE fkself DROP KEY id;
       
      INSERT INTO fkself VALUES(1),(2);
      

      Then restart MariaDB and enter again the mysql commandline:

      [MariaDB]> SELECT * FROM fkself;
      ERROR 1932 (42S02): Table 'test.fkself' doesn't exist in engine
      MariaDB> SET foreign_key_checks=0;
      Query OK, 0 rows affected (0.001 sec)
       
      MariaDB> select * from fkself;
      +----+
      | id |
      +----+
      |  1 |
      |  2 |
      +----+
      2 rows in set (0.002 sec)
      MariaDB> SET foreign_key_checks=1;
      Query OK, 0 rows affected (0.000 sec)
      MariaDB> SELECT * FROM fkself;
      +----+
      | id |
      +----+
      |  1 |
      |  2 |
      +----+
      2 rows in set (0.001 sec)
      

      Following this, every restart of MariaDB has the same issue,

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              karlsson Anders Karlsson
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: