[MDEV-20723] Self referencing foreign key fails with table does not exist in engine Created: 2019-10-02  Updated: 2020-09-28  Resolved: 2019-10-07

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

Type: Bug Priority: Major
Reporter: Anders Karlsson Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux CentOS 7.6

Issue Links:
Duplicate
duplicates MDEV-17187 table doesn't exist in engine after A... Closed

 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,

 Comments   
Comment by Marko Mäkelä [ 2019-10-02 ]

Isn’t this a duplicate of MDEV-17187, which was fixed in 10.4.8?

Comment by Anders Karlsson [ 2019-10-02 ]

The looks like a duplicate and I don't see the issue in 10.4.8. But the
description is rather different, whereas 17187 talks about several
tables with constraints, 20723 on the other hand is dead simple to
reproduce with just a single table with one column. If you determine
that these have the smae cause (and hence the same fix) I am OK with
closing it as duplicate. I did look at 17187 and deemed it to be
different enough to look into it.

/Karlsson

–
Anders Karlsson, Principal Sales Engineer
MariaDB| t: +46 708-608-121 | Skype: drdatabase

Comment by Elena Stepanova [ 2019-10-06 ]

Be it the exact same problem or not, I can confirm that it disappeared from 10.2 tree after the same commit 

commit 9de2e60d7491fcf3cd1f20a4be715ef0bedc316f e279c0076d59fdc840931b3bfa497b38893adf93
 
Author:     Marko Mäkelä <marko.makela@mariadb.com>
 
AuthorDate: Wed Aug 21 11:38:17 2019 +0300
 
Commit:     Marko Mäkelä <marko.makela@mariadb.com>
 
CommitDate: Wed Aug 21 11:38:33 2019 +0300
 
 
 
    MDEV-17187 table doesn't exist in engine after ALTER of FOREIGN KEY

marko, I'm assigning it to you in case there is anything else you want to do with this report (e.g. add a test case, or whatever). Otherwise, please close.

Generated at Thu Feb 08 09:01:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.