Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.2.11, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
CentOS Linux release 7.5.1804 (Core)
Description
After ALTER of several tables (few of them had CONSTRAINs to a problematic table) a table 'vanished' from server.
|
|
MariaDB [DBname]> select count(*) from transaction;
|
ERROR 1932 (42S02): Table 'DBname.transaction' doesn't exist in engine
|
|
it is however visible in table inventory (show tables).
During ALTER statements foreign_key_checks was set to OFF.
If I disable foreign_key_checks table appears again and it is visible after enabling foreign_key_checks. It seems this affect 'global' variable. After the server restart the table is 'invisible' again (with the same error as described above).
MariaDB [DBname]> select count(*) from transaction;
|
ERROR 1932 (42S02): Table 'DBname.transaction' doesn't exist in engine
|
MariaDB [DBname]> set foreign_key_checks=0;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [DBname]> select count(*) from transaction;
|
+-----------+
|
| count(*) |
|
+-----------+
|
| 244715434 |
|
+-----------+
|
1 row in set (2 min 0.19 sec)
|
|
|
MariaDB [DBname]> set foreign_key_checks=1;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [DBname]> select count(*) from transaction;
|
+-----------+
|
| count(*) |
|
+-----------+
|
| 244715434 |
|
+-----------+
|
1 row in set (2 min 1.28 sec)
|
|
|
MariaDB [DBname]> Bye
|
[root@host ~]# mysql DBname
|
|
|
|
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 151
|
Server version: 10.2.11-MariaDB-log MariaDB Server
|
|
|
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [DBname]> select count(*) from transaction;
|
+-----------+
|
| count(*) |
|
+-----------+
|
| 244715435 |
|
+-----------+
|
1 row in set (1 min 59.65 sec)
|
|
|
MariaDB [DBname]>
|
|
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
- links to
Activity
The observation in the Description is explained by the fact that the function ha_innobase::open() allows the table to be opened when foreign_key_checks=0:
/* Check whether FOREIGN_KEY_CHECKS is set to 0. If so, the table |
can be opened even if some FK indexes are missing. If not, the table
|
can't be opened in the same situation */
|
if (thd_test_options(thd, OPTION_NO_FOREIGN_KEY_CHECKS)) { |
ignore_err = DICT_ERR_IGNORE_FK_NOKEY;
|
}
|
I will try to remove this special case, and implement the check for missing indexes when DML operations would access the indexes for enforcing FOREIGN KEY constraints.
I am working on a fix that replaces some use of DICT_ERR_IGNORE_NONE with DICT_ERR_IGNORE_FK_NOKEY, so that most of the time, we would allow access to tables even when some underlying indexes for FOREIGN KEY are missing.
All existing tests pass with that, but I think that I must write an additional test where DML would encounter the missing index when trying to enforce a FOREIGN KEY constraint.
It turns out that the function row_ins_check_foreign_constraint(), which gets invoked when deleting from the parent table, was silently ignoring the constraint when the index is missing from the child table (the one with the CONSTRAINT…FOREIGN KEY…REFERENCES clause). Even after fixing that, I was still having the problem that an INSERT into the child table is being accepted.
The higher-level code seems to ignore any FOREIGN KEY constraints that are not backed by indexes in the referencing table are being ignored altogether.
I fixed it so that such DELETE and INSERT will be rejected with foreign_key_checks=1 and accepted with foreign_key_checks=0.
It turns out that the MySQL 5.7.5 fix of removing a condition from ha_innobase::prepare_inplace_alter_table() was omitted when the InnoDB changes from MySQL 5.7.9 were applied to MariaDB 10.2.2.
I think that it is best to remove the check from ha_innobase::open() and adjust the other code accordingly. If we fail to do that, users who have already dropped the indexes that are needed for enforcing FOREIGN KEY constraints would seem to have no way to regain access to the table.