|
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.
|
