Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
5.5(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
Consider the following scenario:
--source include/have_innodb.inc
|
|
|
CREATE TABLE t1 (f INT, UNIQUE(f), KEY(f), FOREIGN KEY (f) REFERENCES t1 (f)) ENGINE=InnoDB; |
SET SESSION FOREIGN_KEY_CHECKS= OFF; |
ALTER TABLE t1 DROP KEY f; |
|
|
--source include/restart_mysqld.inc
|
|
|
--error ER_NO_SUCH_TABLE_IN_ENGINE,ER_NO_SUCH_TABLE
|
SELECT * FROM t1; |
SHOW WARNINGS;
|
SET SESSION FOREIGN_KEY_CHECKS= OFF; |
SELECT * FROM t1; |
|
|
# Cleanup
|
DROP TABLE t1; |
The first SELECT when FOREIGN_KEY_CHECKS=ON, fails:
MariaDB [test]> SELECT * FROM t1; |
ERROR 1932 (42S02): Table 'test.t1' doesn't exist in engine |
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+-----------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------+
|
| Error | 1932 | Table 'test.t1' doesn't exist in engine | |
+-------+------+-----------------------------------------+ |
1 row in set (0.000 sec) |
And at the same time produces the warning in the error log:
2019-05-20 16:01:55 9 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
|
It should be the other way round: the message explaining the nature of the problem and suggesting the solution should be the error shown to the user, especially since it only requires a session-level change; while the error about table not existing in the engine is a fairly low-level product which doesn't anyhow help the user, it should go to the error log (if anywhere at all).
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
I'm fine with closing it as a duplicate. Things regarding foreign_keys check change frequently, I don't think there is any point in searching similarities in the current behavior with a 2-year-old bug. If I have similar complaints later, I'll resurrect it or will file a new one.
elenst, do you have an up-to-date example of this, or can we close this as a duplicate of
MDEV-17187?MDEV-17187fixed the test case scenario so that we will allow the table to be opened and accessed even when the underlying index is missing, and we will only refuse INSERT, UPDATE or DELETE on the table.I believe that serg is against adding any storage engine specific error codes and messages. InnoDB probably is the only storage engine that supports foreign keys. Furthermore, my understanding is that most ha_innobase member functions are not even allowed to invoke my_error().