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

Table disappear after modifying FK

Details

    • 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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7809 Crash after modifying FK on version 5.5

          • Major - Major loss of function.
          • Closed
          links to

          Web Link Bug #68148 drop index on a foreign key column leads to missing table

          Activity

            People

              jplindst Jan Lindström (Inactive)
              sam.choobs Sam Grandjean
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.