Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7809

Crash after modifying FK on version 5.5

    XMLWordPrintable

Details

    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.

      Attachments

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              sam.choobs Sam Grandjean
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.