Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Done
-
5.1.67, 5.2.14, 5.3.12, 5.5(EOL)
-
Win 7 Pro SP1 64-bit
-
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.
Attachments
Issue Links
- relates to
-
-
- Closed
-
- links to
Activity
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.
The problem isn't fixed. Look at the previous comment please.