Details

    • Sprint:
      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

            Activity

              People

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                sam.choobs Sam Grandjean
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: