From aab5c557cfdf4a155e5eab69d0979bcb4b26c710 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 3 Aug 2018 17:39:38 +0300 Subject: MDEV-16830 Crash in ALTER TABLE DROP FOREIGN KEY ha_innobase::inplace_alter_table(): Do nothing if INNOBASE_ALTER_INSTANT flags (such as DROP FOREIGN KEY) was present. Also, use ALTER_OPTIONS instead of the alias ALTER_CHANGE_CREATE_OPTION. This bug was caused by MDEV-11369, MDEV-13134 or related work. --- mysql-test/suite/innodb/r/instant_alter.result | 59 +++++++++++++++++++++++++- mysql-test/suite/innodb/t/instant_alter.test | 21 +++++++++ 2 files changed, 79 insertions(+), 1 deletion(-) (limited to 'mysql-test/suite/innodb') diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result index a70a3d077e0..bb15b665d49 100644 --- a/mysql-test/suite/innodb/r/instant_alter.result +++ b/mysql-test/suite/innodb/r/instant_alter.result @@ -446,6 +446,25 @@ ALTER TABLE t1 ADD COLUMN c INT; UPDATE t1 SET c = 1; UPDATE t1 SET c = 2; DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a)) +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET b=1; +ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1; +ALTER TABLE t2 ADD INDEX(a); +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY +SET foreign_key_checks=0; +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a), +ALGORITHM=INSTANT; +SET foreign_key_checks=1; +ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk; +ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1; +DROP TABLE t2, t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -838,6 +857,25 @@ ALTER TABLE t1 ADD COLUMN c INT; UPDATE t1 SET c = 1; UPDATE t1 SET c = 2; DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT; +CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a)) +ENGINE=InnoDB ROW_FORMAT=COMPACT; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET b=1; +ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1; +ALTER TABLE t2 ADD INDEX(a); +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY +SET foreign_key_checks=0; +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a), +ALGORITHM=INSTANT; +SET foreign_key_checks=1; +ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk; +ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1; +DROP TABLE t2, t1; CREATE TABLE t1 (id INT PRIMARY KEY, c2 INT UNIQUE, c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'), @@ -1230,10 +1268,29 @@ ALTER TABLE t1 ADD COLUMN c INT; UPDATE t1 SET c = 1; UPDATE t1 SET c = 2; DROP TABLE t1; +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a)) +ENGINE=InnoDB ROW_FORMAT=DYNAMIC; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET b=1; +ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1; +ALTER TABLE t2 ADD INDEX(a); +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY +SET foreign_key_checks=0; +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a), +ALGORITHM=INSTANT; +SET foreign_key_checks=1; +ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk; +ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1; +DROP TABLE t2, t1; disconnect analyze; SELECT variable_value-@old_instant instants FROM information_schema.global_status WHERE variable_name = 'innodb_instant_alter_column'; instants -39 +45 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency; diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index c414b92f713..18c07bdac73 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -319,6 +319,27 @@ UPDATE t1 SET c = 1; UPDATE t1 SET c = 2; DROP TABLE t1; +# MDEV-16830 Crash in ALTER TABLE DROP FOREIGN KEY +eval CREATE TABLE t1 (a INT PRIMARY KEY) $engine; +eval CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a)) +$engine; +INSERT INTO t1 SET a=1; +INSERT INTO t2 SET b=1; +ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1; +ALTER TABLE t2 ADD INDEX(a); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +SET foreign_key_checks=0; +ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a), +ALGORITHM=INSTANT; +ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a), +ALGORITHM=INSTANT; +SET foreign_key_checks=1; +ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk; +ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1; +DROP TABLE t2, t1; + dec $format; } disconnect analyze; -- cgit v1.2.1