Win 7 Pro SP1 64-bit

It's an upstream bug https://bugs.mysql.com/bug.php?id=68148 which was fixed in 5.6.12 and thus in 10.0 before GA. Do you think there is a critical need to backport the fix into 5.5?

Our company is still on 5.5 branch. We think losing data by sending invalid queries should not happen at all. Since 5.5 branch is supported until 2017, backporting the fix is a necessity.

jplindst,

Please consider if it's possible to backport the fix from 5.6/10.0 to 5.5.
Please also not that the fix is not perfect – it seems that the table still goes away if a user happens to restart the server after dropping the index; so, if you improve it on the way, all the better.

In MySQL 5.6 this was fixed on:

commit b21b9953a710e8dfdd133eba942b1767fc0f5acd
Author: bin.x.su@oracle.com <>
Date: Thu Apr 18 09:46:45 2013 +0800

Bug#16208542 DROP INDEX ON A FOREIGN KEY COLUMN LEADS TO MISSING TABLE

== Analysis ==
The bug is caused by the reason that dict_load_foreigns() will return error
when it couldn't find a equivalent fk index, and dict_load_table() who get
the error will return NULL to indicate there is something wrong with the
table. This happens no matter which value(1/0) is set to FOREIGN_KEY_CHECKS.

== Solution ==
Allow user to open the table with missing fk indexes when
FOREIGN_KEY_CHECKS=0. When the table is opened, user has to recreate
the missing indexes to fulfill the fk constraints. After that the table
can be open in a normal way.

A new enum type in dict_err_ignore_t called DICT_ERR_IGNORE_FK_NOKEY
is defined, with which dict_load_foreigns() will always load all the
fk constraints and fk indexes ignoring those missing. User can decide
which indexes should be created according to the table definition with
all the fk constraints.

An error message can be found when open a table with missing fk indexes.
I think using ib_logf in dict_load_table() to print the error message is
an easy way.

rb#2308 is approved by Marko

In my understanding the port to MySQL 5.5 has been done in:

commit 7b66df16a1ca00084ee6336aaf50d32f914e625c
Author: bin.x.su@oracle.com <>
Date: Tue Jun 25 09:42:54 2013 +0800

Bug 16876388 - PLEASE BACKPORT BUG#16208542 TO 5.5

Straight forward backport.

Approved by Jimmy, rb#2656

Both referenced numbers are not open to public but based on dates both should be already merged to MariaDB, notable is that both fixes do not contain test cases, thus I will create one to rest is the issue really fixed.

I did not see any disappearing tables while testing. Remember that optimize table is same as alter table ... engine=innodb; and you should not run that when foreign_key_checks=0 and table is still missing required index for fk. If you do execute optimize table, table name will be changed to internal name (see from data directory the name). You can get back that table with foreign_key_checks=0 and rename table.

jplindst,

MariaDB [test]> 

MariaDB [test]> ALTER TABLE `test_item` DROP INDEX `fk_test_person_idx`;

Query OK, 0 rows affected (0.48 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> 

MariaDB [test]> ALTER TABLE `test_item` DROP FOREIGN KEY `fk_test_location`;

ERROR 1025 (HY000): Error on rename of './test/#sql-29fc_2' to './test/test_item' (errno: 150)

MariaDB [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| test_location  |

| test_person    |

+----------------+

2 rows in set (0.01 sec)

This is a disappearance of the table. test_item was there, and then it was not.
A user cannot possibly know the internals, such as "optimize is the same as...", "you should not run", and especially "table name will be changed to internal name".
If a user cannot run optimize, it must be denied by the server. If a user runs an allowed command and it causes visible data loss, it's a problem.

Idea here is that you normally run your ALTER and other commands with FOREIGN_KEY_CHECKS=1 i.e. InnoDB will automatically make sure that you do not break table and database consistency. If user sets FOREIGN_KEY_CHECKS=0, it is indication that you know what you are doing. While this setting is disabled you may ALTER the table so that table is not consistent. This is by design, there are several clauses about this on documentation (and I do agree it is not exhaustive). If you make table not consistent e.g. by dropping the foreign key index, table is removed from the InnoDB tablespace cache. This is done because if you then set FOREIGN_KEY_CHECKS=1, this setting does not really immediately do any consistency checking. Thus, if table would be on tablespace cache, you could e.g. INSERT data to it and your database could be broken. So, if you do try to INSERT InnoDB tries to open that table, not found from cache so it is tried to load from data dictionary. Now that foreign key checks is enabled the table load will fail.

I could change the server to refuse to drop foreign key index even when FOREIGN_KEY_CHECKS=0 (and I actually would prefer it to be like that, so that user must drop first the foreign key constraint and then index if needed) but that would make MariaDB and MySQL behave differently and (actual change is not big) not sure if that change is safe for GA product (current applications could already use this feature).

Thank you for your input, Jan. As stated by Elena, it would be nice not needing to know MySQL internals to handle this issue, but your explanation makes lot of sense.

For those wondering how to recover table, you can use RENAME statement. Eg:

RENAME TABLE `#mysql50##sql2-5108-4` TO `test_item`;

However this requires ALTER, DROP, CREATE, and INSERT privileges, and also knowing the table name on filesystem.

Mysql bug https://bugs.mysql.com/bug.php?id=68148 can be reproduced in Mariadb 10.0.28 and 10.1.19.
I used Mysql Sandbox in the following example, but also I can do the same in production environments.

And then after restart of Mariadb:

In Mysql 5.7.16 the bug isn't reproducible because the server blocks the index dropping:

The problem isn't fixed. Look at the previous comment please.

This looks little bit different compared to original report as now you have added server restart. I will review current MySQL behavior and see if we can follow their solution. My original comment holds also here, set foreign_key_checks=0 should allow user temporally break foreign key constraints and if you do then it is user's responsibility to fix the situation as server does not enough information to always fix it.

You can use following to restore table in above case:

SET FOREIGN_KEY_CHECKS=0;

ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`);

SHOW CREATE TABLE `main`;

No need to know any internals.

Verified what MariaDB and MySQL do

• MySQL 5.5.54 and MariaDB 5.5.54 (MariaDB just provides better warning) as expected.
• MySQL 5.6.35 and MariaDB 10.0.29 (different error on show create table main 1146 vs 1932, difference compared to 5.5 allows dropping fk) as expected.
• MySQL 5.6.35 and MariaDB 10.1.20 (same as above) as expected.
• MySQL 5.7.17 and MariaDB 10.2.3 (10.2 as expected) but MySQL 5.7: ALTER TABLE `test_item` DROP INDEX `fk_test_person_idx`' failed: 1553: Cannot drop index 'fk_test_person_idx': needed in a foreign key constraint)

I do not see here any reason to fix the current way in 5.5, 10.0, 10.1. Currently, MariaDB 10.2 does behave differently compared to MySQL 5.7, but in my opinion user should have a choice to drop a index needed in a foreign key constraint while foreign_key_checks=0. This is because setting foreign_key_checks=0 is indication that user want's to temporally do operations that can make foreign key constraints not consistent. If user has not fixed them consistent after setting foreign_key_checks=1 server does print errors on error log and there is a way to fix the situation. Thus, in my opinion works-as-designed.