Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.6
-
None
-
Linux CentOS 7.6
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
- duplicates
-
MDEV-17187 table doesn't exist in engine after ALTER other tables with CONSTRAINTs
- Closed