MDEV-20590 Introduce a file format constraint to ALTER TABLE

If a table is altered using the MDEV-11369/MDEV-15562/MDEV-13134
ALGORITHM=INSTANT, it can force the table to use a non-canonical
format:

* A hidden metadata record at the start of the clustered index
is used to store each column's DEFAULT value. This makes it possible
to add new columns that have default values without rebuilding the table.

* Starting with MDEV-15562 in MariaDB Server 10.4, a BLOB in the
hidden metadata record is used to store column mappings. This makes
it possible to drop or reorder columns without rebuilding the table.
This also makes it possible to add columns to any position or drop
columns from any position in the table without rebuilding the table.

If a column is dropped without rebuilding the table, old records
will contain garbage in that column's former position, and new records
will be written with NULL values, empty strings, or dummy values.

This is generally not a problem. However, there may be cases where
users may want to avoid putting a table into this format.
For example, users may want to ensure that future UPDATE operations
after an ADD COLUMN will be performed in-place, to reduce write
amplification. (Instantly added columns are essentially always
variable-length.) Users might also want to avoid bugs similar to
MDEV-19916, or they may want to be able to export tables to
older versions of the server.

We will introduce the option innodb_instant_alter_column_allowed,
with the following values:

* never (0): Do not allow instant add/drop/reorder,
to maintain format compatibility with MariaDB 10.x and MySQL 5.x.
If the table (or partition) is not in the canonical format, then
any ALTER TABLE (even one that does not involve instant column
operations) will force a table rebuild.

* add_last (1, default in 10.3): Store a hidden metadata record that
allows columns to be appended to the table instantly (MDEV-11369).
In 10.4 or later, if the table (or partition) is not in this format,
then any ALTER TABLE (even one that does not involve column changes)
will force a table rebuild.

Starting with 10.4:

* add_drop_reorder (2, default): Like 'add_last', but allow the
metadata record to store a column map, to support instant
add/drop/reorder of columns (MDEV-15562).

8 files changed, 244 insertions, 54 deletions

diff --git a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
index cb65d12b09f..e333e77aa1e 100644
--- a/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
+++ b/mysql-test/suite/innodb/r/instant_alter,4k.rdiff
@@ -1,6 +1,6 @@
 --- instant_alter.result
 +++ instant_alter,4k.result
-@@ -241,7 +241,7 @@
+@@ -243,7 +243,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -9,7 +9,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -251,7 +251,7 @@
+@@ -253,7 +253,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -18,7 +18,7 @@
  connection default;
  BEGIN;
  UPDATE t2 SET d1 = repeat(id, 200);
-@@ -262,7 +262,7 @@
+@@ -264,7 +264,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -27,7 +27,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -272,7 +272,7 @@
+@@ -274,7 +274,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -36,7 +36,7 @@
  connection default;
  ALTER TABLE t2 DROP p;
  affected rows: 0
-@@ -320,8 +320,14 @@
+@@ -322,8 +322,14 @@
  affected rows: 0
  info: Records: 0  Duplicates: 0  Warnings: 0
  ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
@@ -52,7 +52,7 @@
  INSERT INTO t3 SET id=4;
  ERROR HY000: Field 'c2' doesn't have a default value
  INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
-@@ -334,7 +340,9 @@
+@@ -336,7 +342,9 @@
  ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
  CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
  affected rows: 4
@@ -63,7 +63,7 @@
  INSERT INTO t3 SET id=5, c2=9;
  Warnings:
  Note	1265	Data truncated for column 'c7' at row 1
-@@ -348,7 +356,9 @@
+@@ -350,7 +358,9 @@
  5	9	POLYGON((1 1,2 2,3 3,1 1))	1970-01-01 03:00:42	1970-01-01 03:00:42	NULL	03:00:42	1970-01-01	ääH	binary line of business
  ALTER TABLE t3 DROP c3, DROP c7;
  affected rows: 0
@@ -74,7 +74,7 @@
  SELECT * FROM t3;
  id	c2	c4	c5	c6	c8	phrase	b
  1	1	1970-01-01 03:00:42	1970-01-01 03:00:42	NULL	1970-01-01	The quick brown fox jumps over the lazy dog	
-@@ -376,6 +386,8 @@
+@@ -378,6 +388,8 @@
  (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
  p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
  ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
@@ -83,7 +83,7 @@
  BEGIN;
  INSERT INTO big
  SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
-@@ -393,13 +405,15 @@
+@@ -395,13 +407,15 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -101,7 +101,7 @@
  CHECKSUM TABLE big;
  Table	Checksum
  test.big	1705165209
-@@ -416,7 +430,7 @@
+@@ -418,7 +432,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -110,7 +110,7 @@
  connection default;
  ROLLBACK;
  CHECKSUM TABLE big;
-@@ -429,7 +443,7 @@
+@@ -431,7 +445,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -119,16 +119,7 @@
  connection default;
  InnoDB		0 transactions not purged
  DROP TABLE t1,t2,t3,t4,big;
-@@ -515,6 +529,8 @@
- CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
- ALTER TABLE t1 ADD d TEXT;
- ALTER TABLE t1 ADD PRIMARY KEY (a,b);
-+Warnings:
-+Warning	139	Row size too large (> 1979). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
- ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
- DROP TABLE t1;
- CREATE TABLE t1
-@@ -703,7 +719,7 @@
+@@ -730,7 +744,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -137,7 +128,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -713,7 +729,7 @@
+@@ -740,7 +754,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -146,7 +137,7 @@
  connection default;
  BEGIN;
  UPDATE t2 SET d1 = repeat(id, 200);
-@@ -724,7 +740,7 @@
+@@ -751,7 +765,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -155,7 +146,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -734,7 +750,7 @@
+@@ -761,7 +775,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -164,7 +155,7 @@
  connection default;
  ALTER TABLE t2 DROP p;
  affected rows: 0
-@@ -783,7 +799,9 @@
+@@ -810,7 +824,9 @@
  info: Records: 0  Duplicates: 0  Warnings: 0
  ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
  affected rows: 0
@@ -175,7 +166,7 @@
  INSERT INTO t3 SET id=4;
  ERROR HY000: Field 'c2' doesn't have a default value
  INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
-@@ -796,7 +814,9 @@
+@@ -823,7 +839,9 @@
  ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
  CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
  affected rows: 4
@@ -186,7 +177,7 @@
  INSERT INTO t3 SET id=5, c2=9;
  Warnings:
  Note	1265	Data truncated for column 'c7' at row 1
-@@ -810,7 +830,9 @@
+@@ -837,7 +855,9 @@
  5	9	POLYGON((1 1,2 2,3 3,1 1))	1970-01-01 03:00:42	1970-01-01 03:00:42	NULL	03:00:42	1970-01-01	ääH	binary line of business
  ALTER TABLE t3 DROP c3, DROP c7;
  affected rows: 0
@@ -197,7 +188,7 @@
  SELECT * FROM t3;
  id	c2	c4	c5	c6	c8	phrase	b
  1	1	1970-01-01 03:00:42	1970-01-01 03:00:42	NULL	1970-01-01	The quick brown fox jumps over the lazy dog	
-@@ -838,6 +860,8 @@
+@@ -865,6 +885,8 @@
  (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
  p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
  ENGINE=InnoDB ROW_FORMAT=COMPACT;
@@ -206,7 +197,7 @@
  BEGIN;
  INSERT INTO big
  SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
-@@ -855,13 +879,15 @@
+@@ -882,13 +904,15 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -224,7 +215,7 @@
  CHECKSUM TABLE big;
  Table	Checksum
  test.big	1705165209
-@@ -878,7 +904,7 @@
+@@ -905,7 +929,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -233,7 +224,7 @@
  connection default;
  ROLLBACK;
  CHECKSUM TABLE big;
-@@ -891,7 +917,7 @@
+@@ -918,7 +942,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -242,16 +233,7 @@
  connection default;
  InnoDB		0 transactions not purged
  DROP TABLE t1,t2,t3,t4,big;
-@@ -977,6 +1003,8 @@
- CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
- ALTER TABLE t1 ADD d TEXT;
- ALTER TABLE t1 ADD PRIMARY KEY (a,b);
-+Warnings:
-+Warning	139	Row size too large (> 1982). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
- ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
- DROP TABLE t1;
- CREATE TABLE t1
-@@ -1165,7 +1193,7 @@
+@@ -1217,7 +1241,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -260,7 +242,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -1175,7 +1203,7 @@
+@@ -1227,7 +1251,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -269,7 +251,7 @@
  connection default;
  BEGIN;
  UPDATE t2 SET d1 = repeat(id, 200);
-@@ -1186,7 +1214,7 @@
+@@ -1238,7 +1262,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -278,7 +260,7 @@
  connection default;
  ROLLBACK;
  connection analyze;
-@@ -1196,7 +1224,7 @@
+@@ -1248,7 +1272,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/t2';
  clust_index_size
@@ -287,7 +269,7 @@
  connection default;
  ALTER TABLE t2 DROP p;
  affected rows: 0
-@@ -1317,7 +1345,7 @@
+@@ -1369,7 +1393,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -296,7 +278,7 @@
  connection default;
  ALTER TABLE big ADD COLUMN
  (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde',
-@@ -1340,7 +1368,7 @@
+@@ -1392,7 +1416,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -305,7 +287,7 @@
  connection default;
  ROLLBACK;
  CHECKSUM TABLE big;
-@@ -1353,7 +1381,7 @@
+@@ -1405,7 +1429,7 @@
  SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
  WHERE name = 'test/big';
  clust_index_size
@@ -314,10 +296,11 @@
  connection default;
  InnoDB		0 transactions not purged
  DROP TABLE t1,t2,t3,t4,big;
-@@ -1446,5 +1474,5 @@
+@@ -1523,6 +1547,6 @@
  FROM information_schema.global_status
  WHERE variable_name = 'innodb_instant_alter_column';
  instants
--57
-+58
+-60
++61
  SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
+ SET GLOBAL innodb_file_format = @saved_format;
diff --git a/mysql-test/suite/innodb/r/instant_alter.result b/mysql-test/suite/innodb/r/instant_alter.result
index aadb7021887..1a818531756 100644
--- a/mysql-test/suite/innodb/r/instant_alter.result
+++ b/mysql-test/suite/innodb/r/instant_alter.result
@@ -1,6 +1,8 @@
 #
 # MDEV-11369: Instant ADD COLUMN for InnoDB
 #
+SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
 call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is");
 CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT;
 ALTER TABLE t ADD e INT, ROW_FORMAT=COMPRESSED;
@@ -519,6 +521,22 @@ ALTER TABLE t1 ADD PRIMARY KEY (b,a);
 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
 DROP TABLE t1;
 SET innodb_strict_mode = OFF;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+INSERT INTO t1 SET a=42;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+ALTER TABLE t1 MODIFY a INT DEFAULT 0;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
+DROP TABLE t1;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
 CREATE TABLE t1
 (id INT PRIMARY KEY, c2 INT UNIQUE,
 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
@@ -983,6 +1001,22 @@ ALTER TABLE t1 ADD PRIMARY KEY (b,a);
 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
 DROP TABLE t1;
 SET innodb_strict_mode = OFF;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
+INSERT INTO t1 SET a=42;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+ALTER TABLE t1 MODIFY a INT DEFAULT 0;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
+DROP TABLE t1;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
 CREATE TABLE t1
 (id INT PRIMARY KEY, c2 INT UNIQUE,
 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
@@ -1447,10 +1481,27 @@ ALTER TABLE t1 ADD PRIMARY KEY (b,a);
 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
 DROP TABLE t1;
 SET innodb_strict_mode = OFF;
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
+INSERT INTO t1 SET a=42;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
+ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: innodb_instant_alter_column_allowed=never. Try ALGORITHM=INPLACE
+ALTER TABLE t1 MODIFY a INT DEFAULT 0;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
+DROP TABLE t1;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
 disconnect analyze;
 SELECT variable_value-@old_instant instants
 FROM information_schema.global_status
 WHERE variable_name = 'innodb_instant_alter_column';
 instants
-57
+60
 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
+SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance;
diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test
index be79d7437c3..6d5c4e96d26 100644
--- a/mysql-test/suite/innodb/t/instant_alter.test
+++ b/mysql-test/suite/innodb/t/instant_alter.test
@@ -4,6 +4,9 @@
 --echo # MDEV-11369: Instant ADD COLUMN for InnoDB
 --echo #
 
+SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+
 call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is");
 
 let $format= `SELECT CASE WHEN @@GLOBAL.innodb_page_size>16384
@@ -408,6 +411,24 @@ ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
 DROP TABLE t1;
 SET innodb_strict_mode = OFF;
 
+# MDEV-20950 innodb_instant_alter_column_allowed
+eval CREATE TABLE t1 (a INT PRIMARY KEY) $engine;
+INSERT INTO t1 SET a=42;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
+SET GLOBAL innodb_instant_alter_column_allowed = never;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
+--enable_info
+ALTER TABLE t1 MODIFY a INT DEFAULT 0;
+--disable_info
+ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
+DROP TABLE t1;
+SET GLOBAL innodb_instant_alter_column_allowed = add_last;
+
 dec $format;
 let $redundant_4k= 0;
 }
@@ -416,3 +437,4 @@ SELECT variable_value-@old_instant instants
 FROM information_schema.global_status
 WHERE variable_name = 'innodb_instant_alter_column';
 SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
+SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance;
diff --git a/mysql-test/suite/sys_vars/r/innodb_instant_alter_column_allowed_basic.result b/mysql-test/suite/sys_vars/r/innodb_instant_alter_column_allowed_basic.result
new file mode 100644
index 00000000000..657fbcbbe67
--- /dev/null
+++ b/mysql-test/suite/sys_vars/r/innodb_instant_alter_column_allowed_basic.result
@@ -0,0 +1,45 @@
+SET @start_global_value = @@global.innodb_instant_alter_column_allowed;
+SET GLOBAL innodb_instant_alter_column_allowed=never;
+select @@session.innodb_instant_alter_column_allowed;
+ERROR HY000: Variable 'innodb_instant_alter_column_allowed' is a GLOBAL variable
+show global variables like 'innodb_instant_alter_column_allowed';
+Variable_name	Value
+innodb_instant_alter_column_allowed	never
+show session variables like 'innodb_instant_alter_column_allowed';
+Variable_name	Value
+innodb_instant_alter_column_allowed	never
+SELECT * FROM information_schema.global_variables
+WHERE variable_name='innodb_instant_alter_column_allowed';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_INSTANT_ALTER_COLUMN_ALLOWED	never
+SELECT * FROM information_schema.session_variables
+WHERE variable_name='innodb_instant_alter_column_allowed';
+VARIABLE_NAME	VARIABLE_VALUE
+INNODB_INSTANT_ALTER_COLUMN_ALLOWED	never
+set global innodb_instant_alter_column_allowed=no;
+ERROR 42000: Variable 'innodb_instant_alter_column_allowed' can't be set to the value of 'no'
+set global innodb_instant_alter_column_allowed=1.1;
+ERROR 42000: Incorrect argument type to variable 'innodb_instant_alter_column_allowed'
+set global innodb_instant_alter_column_allowed=-1;
+ERROR 42000: Variable 'innodb_instant_alter_column_allowed' can't be set to the value of '-1'
+select @@global.innodb_instant_alter_column_allowed;
+@@global.innodb_instant_alter_column_allowed
+never
+set global innodb_instant_alter_column_allowed=2;
+ERROR 42000: Variable 'innodb_instant_alter_column_allowed' can't be set to the value of '2'
+select @@global.innodb_instant_alter_column_allowed;
+@@global.innodb_instant_alter_column_allowed
+never
+set global innodb_instant_alter_column_allowed=1;
+select @@global.innodb_instant_alter_column_allowed;
+@@global.innodb_instant_alter_column_allowed
+add_last
+set global innodb_instant_alter_column_allowed=0;
+select @@global.innodb_instant_alter_column_allowed;
+@@global.innodb_instant_alter_column_allowed
+never
+set global innodb_instant_alter_column_allowed=default;
+select @@global.innodb_instant_alter_column_allowed;
+@@global.innodb_instant_alter_column_allowed
+add_last
+SET GLOBAL innodb_instant_alter_column_allowed = @start_global_value;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 8d39f6c14b6..fe57528fa13 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1137,6 +1137,18 @@ NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	OFF,ON
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	INNODB_INSTANT_ALTER_COLUMN_ALLOWED
+SESSION_VALUE	NULL
+DEFAULT_VALUE	add_last
+VARIABLE_SCOPE	GLOBAL
+VARIABLE_TYPE	ENUM
+VARIABLE_COMMENT	File format constraint for ALTER TABLE
+NUMERIC_MIN_VALUE	NULL
+NUMERIC_MAX_VALUE	NULL
+NUMERIC_BLOCK_SIZE	NULL
+ENUM_VALUE_LIST	never,add_last
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_IO_CAPACITY
 SESSION_VALUE	NULL
 DEFAULT_VALUE	200
diff --git a/mysql-test/suite/sys_vars/t/innodb_instant_alter_column_allowed_basic.test b/mysql-test/suite/sys_vars/t/innodb_instant_alter_column_allowed_basic.test
new file mode 100644
index 00000000000..4ff84b42a3a
--- /dev/null
+++ b/mysql-test/suite/sys_vars/t/innodb_instant_alter_column_allowed_basic.test
@@ -0,0 +1,32 @@
+--source include/have_innodb.inc
+
+SET @start_global_value = @@global.innodb_instant_alter_column_allowed;
+SET GLOBAL innodb_instant_alter_column_allowed=never;
+
+--error ER_INCORRECT_GLOBAL_LOCAL_VAR
+select @@session.innodb_instant_alter_column_allowed;
+show global variables like 'innodb_instant_alter_column_allowed';
+show session variables like 'innodb_instant_alter_column_allowed';
+SELECT * FROM information_schema.global_variables
+WHERE variable_name='innodb_instant_alter_column_allowed';
+SELECT * FROM information_schema.session_variables
+WHERE variable_name='innodb_instant_alter_column_allowed';
+
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_instant_alter_column_allowed=no;
+--error ER_WRONG_TYPE_FOR_VAR
+set global innodb_instant_alter_column_allowed=1.1;
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_instant_alter_column_allowed=-1;
+select @@global.innodb_instant_alter_column_allowed;
+--error ER_WRONG_VALUE_FOR_VAR
+set global innodb_instant_alter_column_allowed=2;
+select @@global.innodb_instant_alter_column_allowed;
+set global innodb_instant_alter_column_allowed=1;
+select @@global.innodb_instant_alter_column_allowed;
+set global innodb_instant_alter_column_allowed=0;
+select @@global.innodb_instant_alter_column_allowed;
+set global innodb_instant_alter_column_allowed=default;
+select @@global.innodb_instant_alter_column_allowed;
+
+SET GLOBAL innodb_instant_alter_column_allowed = @start_global_value;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 63267a3c5cb..773026dbbd1 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -213,9 +213,6 @@ static char* innodb_large_prefix;
 stopword table to be used */
 static char*	innobase_server_stopword_table;
 
-/* Below we have boolean-valued start-up parameters, and their default
-values */
-
 static my_bool	innobase_use_atomic_writes;
 static my_bool	innobase_use_checksums;
 static my_bool	innobase_locks_unsafe_for_binlog;
@@ -239,6 +236,9 @@ my_bool innodb_evict_tables_on_commit_debug;
 extern my_bool srv_scrub_force_testing;
 #endif
 
+/** File format constraint for ALTER TABLE */
+ulong innodb_instant_alter_column_allowed;
+
 /** Note we cannot use rec_format_enum because we do not allow
 COMPRESSED row format for innodb_default_row_format option. */
 enum default_row_format_enum {
@@ -474,6 +474,21 @@ static TYPELIB innodb_change_buffering_typelib = {
 	NULL
 };
 
+/** Allowed values of innodb_instant_alter_column_allowed */
+const char* innodb_instant_alter_column_allowed_names[] = {
+	"never", /* compatible with MariaDB 5.5 to 10.2 */
+	"add_last",/* allow instant ADD COLUMN */
+	NullS
+};
+
+/** Enumeration of innodb_instant_alter_column_allowed */
+static TYPELIB innodb_instant_alter_column_allowed_typelib = {
+	array_elements(innodb_instant_alter_column_allowed_names) - 1,
+	"innodb_instant_alter_column_allowed_typelib",
+	innodb_instant_alter_column_allowed_names,
+	NULL
+};
+
 /** Retrieve the FTS Relevance Ranking result for doc with doc_id
 of m_prebuilt->fts_doc_id
 @param[in,out]	fts_hdl	FTS handler
@@ -19200,6 +19215,12 @@ static MYSQL_SYSVAR_BOOL(stats_include_delete_marked,
   "Include delete marked records when calculating persistent statistics",
   NULL, NULL, FALSE);
 
+static MYSQL_SYSVAR_ENUM(instant_alter_column_allowed,
+			 innodb_instant_alter_column_allowed,
+  PLUGIN_VAR_RQCMDARG,
+  "File format constraint for ALTER TABLE", NULL, NULL, 1/*add_last*/,
+  &innodb_instant_alter_column_allowed_typelib);
+
 static MYSQL_SYSVAR_ULONG(io_capacity, srv_io_capacity,
   PLUGIN_VAR_RQCMDARG,
   "Number of IOPs the server can do. Tunes the background IO rate",
@@ -19316,6 +19337,7 @@ static MYSQL_SYSVAR_ENUM(flush_method, innodb_flush_method,
 static MYSQL_SYSVAR_STR(file_format, innodb_file_format,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Deprecated parameter with no effect.", NULL, NULL, NULL);
+
 static MYSQL_SYSVAR_STR(large_prefix, innodb_large_prefix,
   PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY,
   "Deprecated parameter with no effect.", NULL, NULL, NULL);
@@ -20482,6 +20504,7 @@ static struct st_mysql_sys_var* innobase_system_variables[]= {
   MYSQL_SYSVAR(random_read_ahead),
   MYSQL_SYSVAR(read_ahead_threshold),
   MYSQL_SYSVAR(read_only),
+  MYSQL_SYSVAR(instant_alter_column_allowed),
   MYSQL_SYSVAR(io_capacity),
   MYSQL_SYSVAR(io_capacity_max),
   MYSQL_SYSVAR(page_cleaners),
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 50f6596cbad..be6bc669349 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -59,6 +59,8 @@ Smart ALTER TABLE
 #include "span.h"
 
 using st_::span;
+/** File format constraint for ALTER TABLE */
+extern ulong innodb_instant_alter_column_allowed;
 
 static const char *MSG_UNSUPPORTED_ALTER_ONLINE_ON_VIRTUAL_COLUMN=
 			"INPLACE ADD or DROP of virtual columns cannot be "
@@ -954,6 +956,26 @@ ha_innobase::check_if_supported_inplace_alter(
 
 	const char* reason_rebuild = NULL;
 
+	switch (innodb_instant_alter_column_allowed) {
+	case 0: /* never */
+		if ((ha_alter_info->handler_flags
+		     & ALTER_ADD_STORED_BASE_COLUMN)
+		    || m_prebuilt->table->is_instant()) {
+			reason_rebuild =
+				"innodb_instant_alter_column_allowed=never";
+			if (ha_alter_info->handler_flags
+			    & ALTER_RECREATE_TABLE) {
+				reason_rebuild = NULL;
+			} else {
+				ha_alter_info->handler_flags
+					|= ALTER_RECREATE_TABLE;
+				ha_alter_info->unsupported_reason
+					= reason_rebuild;
+			}
+		}
+		break;
+	}
+
 	switch (ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE) {
 	case ALTER_OPTIONS:
 		if (alter_options_need_rebuild(ha_alter_info, table)) {