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
-
-
- Closed
-
Activity
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
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.
Isn’t this a duplicate of
MDEV-17187, which was fixed in 10.4.8?