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.

1 files changed, 58 insertions, 1 deletions

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;