Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Done
-
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5
-
Fix Version/s: 5.5.46
-
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
-
Labels:
-
Environment:Win 7 Pro SP1 64-bit
-
Sprint:10.1.8-4
Description
A table can disappear when running ALTER queries on it.
This was tested with MariaDB 5.5.45-winx64, but not in 10.0.21-winx64.
How to reproduce
This problem is reproducible all the time with the aforementioned MariaDB version.
Initial situation
This is the database state before problem. I've removed non-relevant structure.
DROP DATABASE IF EXISTS `test`; CREATE DATABASE `test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; USE `test`; CREATE TABLE `test_location` ( `idlocation` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`idlocation`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; CREATE TABLE `test_person` ( `idperson` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`idperson`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; CREATE TABLE `test_item` ( `iditem` int(10) unsigned NOT NULL AUTO_INCREMENT, `idlocation` int(10) unsigned DEFAULT NULL, `idperson` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`iditem`), KEY `fk_test_location_idx` (`idlocation`) USING BTREE, KEY `fk_test_person_idx` (`idperson`) USING BTREE, CONSTRAINT `fk_test_location` FOREIGN KEY (`idlocation`) REFERENCES `test_location` (`idlocation`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_test_person` FOREIGN KEY (`idperson`) REFERENCES `test_person` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
Database corruption
Then I run these commands to alter the database structure.
Note: Both ALTER TABLE statements must be run in order to trigger the problem.
USE `test`; SET FOREIGN_KEY_CHECKS=0; ALTER TABLE `test_item` DROP INDEX `fk_test_person_idx`; ALTER TABLE `test_item` DROP FOREIGN KEY `fk_test_location`;
This will issue error 1025 - Error on rename of '.\test#sql-5108_4' to '.\test\test_item' (errno: 150).
Content of .err log file:
150925 15:05:08 InnoDB: Error: in ALTER TABLE `test`.`test_item` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition. 150925 15:05:08 InnoDB: Error: in ALTER TABLE `test`.`test_item` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition.
At this point the table is not listed anymore with SHOW TABLES. When trying to recreate it:
USE `test`; SET FOREIGN_KEY_CHECKS=0; CREATE TABLE `test_item` ( `iditem` int(10) unsigned NOT NULL AUTO_INCREMENT, `idlocation` int(10) unsigned DEFAULT NULL, `idperson` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`iditem`), KEY `fk_test_location_idx` (`idlocation`) USING BTREE, KEY `fk_test_person_idx` (`idperson`) USING BTREE, CONSTRAINT `fk_test_location` FOREIGN KEY (`idlocation`) REFERENCES `test_location` (`idlocation`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_test_person` FOREIGN KEY (`idperson`) REFERENCES `test_person` (`idperson`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
This will issue error 1005 - Can't create table 'test.test_item' (errno: 121).
"Fix"
To be able to recreate the table, the service must be restarted. After restart, CREATE TABLE statement work.
However, all data is lost.
I know the queries are run in wrong order, but losing a whole table and its data is not acceptable when running "invalid" SQL.
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.
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.
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.
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?