Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.42
-
Win 2012R2 SP1 64-bit, also tested with Win 7 Pro SP1 64-bit
Description
It is possible to corrupt a database by just modifying some foreign keys. Furthermore, this can also crash the whole service if the database is not fixed immediately after the corruption.
This applies to MariaDB 5.5.42-winx64, but not in the latest stable 10.0.17-winx64.
The queries are generated by Navicat when using "Structure Synchronization".
How to reproduce
This problem is reproducible all the time with the aforementioned MariaDB version. I managed to reproduce it on two different machines (one server and one workstation with fresh MariaDB installation).
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-35a4_6' to '.\test\test_item' (errno: 150).
Content of .err log file:
150320 11:05:29 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.
|
150320 11:05:29 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.
|
Service crash
At this point the table is not listed anymore with SHOW TABLES. So I try 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 2003 - Can't connect to MySQL server on 'localhost' (10038) or 2003 - Can't connect to MySQL server on 'localhost' (10061 "Unknown error") after a few seconds.
Content of .err log file:
150320 11:23:03 InnoDB: The InnoDB memory heap is disabled
|
150320 11:23:03 InnoDB: Mutexes and rw_locks use Windows interlocked functions
|
150320 11:23:03 InnoDB: Compressed tables use zlib 1.2.3
|
150320 11:23:03 InnoDB: Initializing buffer pool, size = 128.0M
|
150320 11:23:03 InnoDB: Completed initialization of buffer pool
|
150320 11:23:03 InnoDB: highest supported file format is Barracuda.
|
InnoDB: The log sequence number in ibdata files does not match
|
InnoDB: the log sequence number in the ib_logfiles!
|
150320 11:23:03 InnoDB: Database was not shut down normally!
|
InnoDB: Starting crash recovery.
|
InnoDB: Reading tablespace information from the .ibd files...
|
InnoDB: Restoring possible half-written data pages from the doublewrite
|
InnoDB: buffer...
|
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
|
InnoDB: in total 14 row operations to undo
|
InnoDB: Trx id counter is 500
|
150320 11:23:03 InnoDB: Rolling back trx with id 33D, 14 rows to undo
|
InnoDB: Dropping table with id 22 in recovery if it exists
|
InnoDB: Error: trying to load index PRIMARY for table test/test_item
|
InnoDB: but the index tree has been freed!
|
Now the whole service is unusable, to repair I need to do stop/reinstall/start/restore backup.
