Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.42
-
Fix Version/s: 5.5.43
-
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
-
Environment: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.
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I believe, you are running on Windows? If so, then it must be mariadb-5.5.42 server (and not mariadb-galera-5.5.42).
As galera version comes out a couple weeks after the non-galera community versions, the fix for MDEV-7672 made
into mariadb-galera-5.5.42 and not mariadb-5.5.42.
Yes I'm running Windows. The binary used for MariaDB installation is mariadb-5.5.42-winx64.msi (SHA1: 28bd539d53f7b45940b192fdfc92e8e31bf06225).
You have error on your SQL. You should first drop a foreign key constraint and then index i.e.
ALTER TABLE `test_item` DROP FOREIGN KEY `fk_test_location`; ALTER TABLE `test_item` DROP INDEX `fk_test_location_idx`;
You have error on your SQL. You should first drop a foreign key constraint and then index
Yes I'm aware of that. The issue is not SQL syntax, but database corruption.
I can still reproduce the issue with MariaDB 5.5.43. 
Can you again explain how your database is corrupted and give clear instructions how to repeat and if that is not feasible provide a database where that happens?
Can you again explain how your database is corrupted and give clear instructions how to repeat
I tried with another clean workstation with MariaDB 5.5.43-winx64. Following instructions above, I can reproduce until errno: 150. However there's a slight difference when I tried to re-create the table: I got error (1005) : Can't create table 'test.test_item' (errno: 121) Duplicate key on write or update. If I run the same CREATE TABLE query again, then it works! So the database is no more corrupt, however it's still possible to make the table disappear when running ALTER TABLE in the wrong order.
Thanks for the report and the test case.
The "crash" part was fixed in
MDEV-7672. I checked that with the given test case the server crashes beforeMDEV-7672fix and does not crash after.It's unclear however why the crash happens on 5.5.42-galera (as reported), while
MDEV-7672is said to be fixed in that version. Maybe the 'fix version' field needs updating.The error on RENAME remains; I'll leave it to Jan Lindström to decide whether it's worth fixing in 5.5.
Both problems also exist in the upstream 5.5.