[MDEV-8845] Table disappear after modifying FK - Jira

XML

Word

Printable

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.

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;

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

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.

relates to

MDEV-7809 Crash after modifying FK on version 5.5

links to

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