MDEV-23805 Make Online DDL to Instant DDL when table is emptybb-10.4-MDEV-23805

- In ha_innobase::prepare_inplace_alter_table(), InnoDB should
check whether the table is empty. If the table is empty then
server should avoid downgrading the MDL after prepare phase.
It is more like instant alter, does change only in dicationary
and metadata.

- Changed few debug test case to make non-empty DDL table

23 files changed, 73 insertions, 13 deletions

diff --git a/mysql-test/suite/gcol/r/virtual_index_drop.result b/mysql-test/suite/gcol/r/virtual_index_drop.result
index 012e61be459..45ee4843b7c 100644
--- a/mysql-test/suite/gcol/r/virtual_index_drop.result
+++ b/mysql-test/suite/gcol/r/virtual_index_drop.result
@@ -25,6 +25,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t1;
 CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB;
+INSERT INTO t1(f1) VALUES(1);
 SET DEBUG_DBUG="+d,create_index_fail";
 SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal";
 ALTER TABLE t1 ADD COLUMN f3 INT AS (f1) VIRTUAL, ADD INDEX(f2, f3);
@@ -33,6 +34,7 @@ SET DEBUG_SYNC="now WAIT_FOR con1_go";
 BEGIN;
 SELECT * FROM t1;
 f1	f2
+1	1
 SET DEBUG_SYNC="now SIGNAL alter_signal";
 connection default;
 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
@@ -47,6 +49,7 @@ t1	CREATE TABLE `t1` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t1;
 CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB;
+INSERT INTO t1(f1) VALUES(1);
 SET DEBUG_DBUG="+d,create_index_fail";
 SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal";
 ALTER TABLE t1 ADD INDEX(f2);
diff --git a/mysql-test/suite/gcol/t/virtual_index_drop.test b/mysql-test/suite/gcol/t/virtual_index_drop.test
index 016832b9e6d..e1ec43f75e5 100644
--- a/mysql-test/suite/gcol/t/virtual_index_drop.test
+++ b/mysql-test/suite/gcol/t/virtual_index_drop.test
@@ -29,6 +29,7 @@ DROP TABLE t1;
 # new_vcol_info in index when rollback of alter happens
 
 CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB;
+INSERT INTO t1(f1) VALUES(1);
 SET DEBUG_DBUG="+d,create_index_fail";
 SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal";
 SEND ALTER TABLE t1 ADD COLUMN f3 INT AS (f1) VIRTUAL, ADD INDEX(f2, f3);
@@ -47,6 +48,7 @@ SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 CREATE TABLE t1(f1 INT, f2 INT AS (f1) VIRTUAL)ENGINE=InnoDB;
+INSERT INTO t1(f1) VALUES(1);
 SET DEBUG_DBUG="+d,create_index_fail";
 SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal";
 send ALTER TABLE t1 ADD INDEX(f2);
diff --git a/mysql-test/suite/innodb/r/alter_candidate_key.result b/mysql-test/suite/innodb/r/alter_candidate_key.result
index 23989e0da5f..79cb225e3b5 100644
--- a/mysql-test/suite/innodb/r/alter_candidate_key.result
+++ b/mysql-test/suite/innodb/r/alter_candidate_key.result
@@ -34,6 +34,7 @@ CREATE TABLE t1(f1 INT, f2 INT,
 PRIMARY KEY(f1, f2),
 UNIQUE INDEX uidx2 (f1, f2),
 UNIQUE INDEX uidx1 (f2))ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 2);
 ALTER TABLE t1 DROP PRIMARY KEY;
 SHOW CREATE TABLE t1;
 Table	Create Table
@@ -66,6 +67,7 @@ test.t1	check	status	OK
 DROP TABLE t1;
 SET SQL_MODE= strict_trans_tables;
 CREATE TABLE t1(a INT UNIQUE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1);
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL dml WAIT_FOR dml_done';
 ALTER TABLE t1 MODIFY COLUMN a INT NOT NULL;
 connection con1;
diff --git a/mysql-test/suite/innodb/r/alter_not_null_debug.result b/mysql-test/suite/innodb/r/alter_not_null_debug.result
index 0c1af03159d..ff77eaf54c5 100644
--- a/mysql-test/suite/innodb/r/alter_not_null_debug.result
+++ b/mysql-test/suite/innodb/r/alter_not_null_debug.result
@@ -80,6 +80,7 @@ SET DEBUG_SYNC='RESET';
 #
 CREATE TABLE t1 (f VARCHAR(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci)
 ENGINE=InnoDB;
+INSERT INTO t1 VALUES('ZERO');
 connection con1;
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
 ALTER TABLE t1 MODIFY f VARCHAR(256) COLLATE latin1_german2_ci NOT NULL;
@@ -96,5 +97,6 @@ ALTER TABLE t1 CHANGE f eins VARCHAR(257) COLLATE latin1_german1_ci NOT NULL,
 ALGORITHM=INSTANT;
 SELECT * FROM t1;
 eins
+ZERO
 one
 DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/r/alter_primary_key.result b/mysql-test/suite/innodb/r/alter_primary_key.result
index afe687871f3..b478691af1f 100644
--- a/mysql-test/suite/innodb/r/alter_primary_key.result
+++ b/mysql-test/suite/innodb/r/alter_primary_key.result
@@ -4,6 +4,7 @@
 #
 CREATE TABLE t0 (pk INT PRIMARY KEY) ENGINE=InnoDB;
 CREATE TABLE t1 (c CHAR(2) NOT NULL) ENGINE=InnoDB;
+INSERT INTO  t1 VALUES('cd');
 connect  con1,localhost,root,,;
 BEGIN;
 INSERT INTO t0 VALUES(1);
@@ -21,6 +22,7 @@ ERROR 23000: Duplicate entry 'a' for key 'PRIMARY'
 SET DEBUG_SYNC='RESET';
 SELECT * FROM t1;
 c
+cd
 ab
 ac
 DROP TABLE t0,t1;
diff --git a/mysql-test/suite/innodb/r/ddl_purge.result b/mysql-test/suite/innodb/r/ddl_purge.result
index 45f4c99e97b..275be16b2d7 100644
--- a/mysql-test/suite/innodb/r/ddl_purge.result
+++ b/mysql-test/suite/innodb/r/ddl_purge.result
@@ -1,5 +1,6 @@
 CREATE TABLE t0 (pk INT PRIMARY KEY) ENGINE=InnoDB;
 CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(100, 100);
 connect  con1,localhost,root,,test;
 BEGIN;
 INSERT INTO t0 SET pk=1;
diff --git a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
index 0716f3da23c..57229d05133 100644
--- a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
+++ b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
@@ -61,7 +61,7 @@ connect  con1,localhost,root,,;
 BEGIN;
 DELETE FROM mysql.innodb_table_stats;
 connect  con2,localhost,root,,;
-SET DEBUG_SYNC='inplace_after_index_build SIGNAL blocked WAIT_FOR ever';
+SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL blocked WAIT_FOR ever';
 ALTER TABLE t1 FORCE;
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR blocked';
diff --git a/mysql-test/suite/innodb/r/innodb-index-debug.result b/mysql-test/suite/innodb/r/innodb-index-debug.result
index daef31d2caa..2eba29e6c28 100644
--- a/mysql-test/suite/innodb/r/innodb-index-debug.result
+++ b/mysql-test/suite/innodb/r/innodb-index-debug.result
@@ -81,6 +81,7 @@ COUNT(k1)	k2	k3
 drop table t1;
 create table t1(k1 int auto_increment primary key,
 k2 char(200),k3 char(200))engine=innodb;
+INSERT INTO t1 VALUES(1, "test", "test");
 SET DEBUG_SYNC= 'row_merge_after_scan
 SIGNAL opened WAIT_FOR flushed';
 ALTER TABLE t1 FORCE, ADD COLUMN k4 int;
@@ -100,6 +101,7 @@ SELECT COUNT(k1),k2,k3 FROM t1 GROUP BY k2,k3;
 COUNT(k1)	k2	k3
 480	aaa	bbb
 480	aaaa	bbbb
+1	test	test
 disconnect con1;
 connection default;
 show create table t1;
@@ -109,7 +111,7 @@ t1	CREATE TABLE `t1` (
   `k2` char(200) DEFAULT NULL,
   `k3` char(200) DEFAULT NULL,
   PRIMARY KEY (`k1`)
-) ENGINE=InnoDB AUTO_INCREMENT=1023 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=latin1
 drop table t1;
 drop table t480;
 #
@@ -117,6 +119,7 @@ drop table t480;
 # in online table rebuild
 #
 CREATE TABLE t1 (j INT UNIQUE, i INT UNIQUE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 2);
 connect  con1,localhost,root,,test;
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built WAIT_FOR log';
 ALTER TABLE t1 DROP j, FORCE;
diff --git a/mysql-test/suite/innodb/r/innodb-index-online.result b/mysql-test/suite/innodb/r/innodb-index-online.result
index 8eebece46b5..f101a63a6ed 100644
--- a/mysql-test/suite/innodb/r/innodb-index-online.result
+++ b/mysql-test/suite/innodb/r/innodb-index-online.result
@@ -509,6 +509,7 @@ DROP TABLE t1;
 # MDEV-13205 assertion !dict_index_is_online_ddl(index) upon ALTER TABLE
 #
 CREATE TABLE t1 (c VARCHAR(64)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('foo');
 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL t1u_created WAIT_FOR dup_done';
 ALTER TABLE t1 ADD UNIQUE(c);
 connection con1;
diff --git a/mysql-test/suite/innodb/r/instant_alter_debug.result b/mysql-test/suite/innodb/r/instant_alter_debug.result
index 359eb1fc384..26db222ca82 100644
--- a/mysql-test/suite/innodb/r/instant_alter_debug.result
+++ b/mysql-test/suite/innodb/r/instant_alter_debug.result
@@ -171,6 +171,7 @@ connect stop_purge,localhost,root;
 START TRANSACTION WITH CONSISTENT SNAPSHOT;
 connect ddl,localhost,root,,test;
 DELETE FROM t1;
+INSERT INTO t1 VALUES(1, 2);
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
 ALTER TABLE t1 FORCE;
 connection default;
@@ -449,6 +450,7 @@ DROP TABLE t1;
 CREATE TABLE t1
 (a INT NOT NULL, b INT, c INT, d INT, e INT, f INT, g INT, h INT, i TEXT)
 ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1, 2, 3, 4, 5, 6, 7, 8, "test");
 ALTER TABLE t1 MODIFY a INT NULL;
 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL alter WAIT_FOR go';
 ALTER TABLE t1 ADD PRIMARY KEY (a);
@@ -462,6 +464,7 @@ connection default;
 disconnect con1;
 SELECT * FROM t1;
 a	b	c	d	e	f	g	h	i
+1	2	3	4	5	6	7	8	test
 DROP TABLE t1;
 SET DEBUG_SYNC=RESET;
 # End of 10.4 tests
diff --git a/mysql-test/suite/innodb/t/alter_candidate_key.test b/mysql-test/suite/innodb/t/alter_candidate_key.test
index 7429cd89a1a..7c8f5e30993 100644
--- a/mysql-test/suite/innodb/t/alter_candidate_key.test
+++ b/mysql-test/suite/innodb/t/alter_candidate_key.test
@@ -24,6 +24,7 @@ CREATE TABLE t1(f1 INT, f2 INT,
 		PRIMARY KEY(f1, f2),
 		UNIQUE INDEX uidx2 (f1, f2),
 		UNIQUE INDEX uidx1 (f2))ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 2);
 ALTER TABLE t1 DROP PRIMARY KEY;
 SHOW CREATE TABLE t1;
 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter
@@ -42,6 +43,7 @@ DROP TABLE t1;
 
 SET SQL_MODE= strict_trans_tables;
 CREATE TABLE t1(a INT UNIQUE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1);
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL dml WAIT_FOR dml_done';
 --send ALTER TABLE t1 MODIFY COLUMN a INT NOT NULL
 connection con1;
diff --git a/mysql-test/suite/innodb/t/alter_not_null_debug.test b/mysql-test/suite/innodb/t/alter_not_null_debug.test
index 7a965fd413a..9c5ba0faff0 100644
--- a/mysql-test/suite/innodb/t/alter_not_null_debug.test
+++ b/mysql-test/suite/innodb/t/alter_not_null_debug.test
@@ -75,7 +75,7 @@ SET DEBUG_SYNC='RESET';
 --echo #
 CREATE TABLE t1 (f VARCHAR(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci)
 ENGINE=InnoDB;
-
+INSERT INTO t1 VALUES('ZERO');
 connection con1;
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
 send ALTER TABLE t1 MODIFY f VARCHAR(256) COLLATE latin1_german2_ci NOT NULL;
diff --git a/mysql-test/suite/innodb/t/alter_primary_key.test b/mysql-test/suite/innodb/t/alter_primary_key.test
index 4edc0cc023e..bf961c14b43 100644
--- a/mysql-test/suite/innodb/t/alter_primary_key.test
+++ b/mysql-test/suite/innodb/t/alter_primary_key.test
@@ -9,6 +9,7 @@
 
 CREATE TABLE t0 (pk INT PRIMARY KEY) ENGINE=InnoDB;
 CREATE TABLE t1 (c CHAR(2) NOT NULL) ENGINE=InnoDB;
+INSERT INTO  t1 VALUES('cd');
 
 connect (con1,localhost,root,,);
 BEGIN;
diff --git a/mysql-test/suite/innodb/t/ddl_purge.test b/mysql-test/suite/innodb/t/ddl_purge.test
index 60d17acead8..e38b68c7bc9 100644
--- a/mysql-test/suite/innodb/t/ddl_purge.test
+++ b/mysql-test/suite/innodb/t/ddl_purge.test
@@ -4,6 +4,7 @@
 
 CREATE TABLE t0 (pk INT PRIMARY KEY) ENGINE=InnoDB;
 CREATE TABLE t1 (pk INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(100, 100);
 
 --connect (con1,localhost,root,,test)
 BEGIN;
diff --git a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
index cbf8ff9e87f..dac176f3b77 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
@@ -80,7 +80,7 @@ BEGIN;
 DELETE FROM mysql.innodb_table_stats;
 
 connect (con2,localhost,root,,);
-SET DEBUG_SYNC='inplace_after_index_build SIGNAL blocked WAIT_FOR ever';
+SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL blocked WAIT_FOR ever';
 send ALTER TABLE t1 FORCE;
 
 connection default;
diff --git a/mysql-test/suite/innodb/t/innodb-index-debug.test b/mysql-test/suite/innodb/t/innodb-index-debug.test
index 2988a89c651..6e31200570b 100644
--- a/mysql-test/suite/innodb/t/innodb-index-debug.test
+++ b/mysql-test/suite/innodb/t/innodb-index-debug.test
@@ -96,6 +96,7 @@ drop table t1;
 # Log file creation failure.
 create table t1(k1 int auto_increment primary key,
 k2 char(200),k3 char(200))engine=innodb;
+INSERT INTO t1 VALUES(1, "test", "test");
 SET DEBUG_SYNC= 'row_merge_after_scan
 SIGNAL opened WAIT_FOR flushed';
 send ALTER TABLE t1 FORCE, ADD COLUMN k4 int;
@@ -122,6 +123,7 @@ drop table t480;
 --echo #
 
 CREATE TABLE t1 (j INT UNIQUE, i INT UNIQUE) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 2);
 --connect (con1,localhost,root,,test)
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL built WAIT_FOR log';
 --send
diff --git a/mysql-test/suite/innodb/t/innodb-index-online.test b/mysql-test/suite/innodb/t/innodb-index-online.test
index 5e21fa896a4..781ff8ee681 100644
--- a/mysql-test/suite/innodb/t/innodb-index-online.test
+++ b/mysql-test/suite/innodb/t/innodb-index-online.test
@@ -477,6 +477,7 @@ DROP TABLE t1;
 --echo # MDEV-13205 assertion !dict_index_is_online_ddl(index) upon ALTER TABLE
 --echo #
 CREATE TABLE t1 (c VARCHAR(64)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('foo');
 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL t1u_created WAIT_FOR dup_done';
 send ALTER TABLE t1 ADD UNIQUE(c);
 
diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test
index 10f7546cc36..8cf2610bbfb 100644
--- a/mysql-test/suite/innodb/t/instant_alter_debug.test
+++ b/mysql-test/suite/innodb/t/instant_alter_debug.test
@@ -186,6 +186,7 @@ connect stop_purge,localhost,root;
 START TRANSACTION WITH CONSISTENT SNAPSHOT;
 connect ddl,localhost,root,,test;
 DELETE FROM t1;
+INSERT INTO t1 VALUES(1, 2);
 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
 send ALTER TABLE t1 FORCE;
 connection default;
@@ -519,7 +520,7 @@ DROP TABLE t1;
 CREATE TABLE t1
 (a INT NOT NULL, b INT, c INT, d INT, e INT, f INT, g INT, h INT, i TEXT)
 ENGINE=InnoDB;
-
+INSERT INTO t1 VALUES(1, 2, 3, 4, 5, 6, 7, 8, "test");
 ALTER TABLE t1 MODIFY a INT NULL;
 
 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL alter WAIT_FOR go';
diff --git a/mysql-test/suite/innodb_fts/r/misc_debug.result b/mysql-test/suite/innodb_fts/r/misc_debug.result
index 9f13822ecda..e252d7f6b11 100644
--- a/mysql-test/suite/innodb_fts/r/misc_debug.result
+++ b/mysql-test/suite/innodb_fts/r/misc_debug.result
@@ -30,6 +30,7 @@ DROP TABLE t2, t1;
 # MDEV-25200 Index count mismatch due to aborted FULLTEXT INDEX
 #
 CREATE TABLE t1(a INT, b TEXT, c TEXT, FULLTEXT INDEX(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1, "test", "test_1");
 connect con1,localhost,root,,test;
 SET DEBUG_DBUG="+d,innodb_OOM_inplace_alter";
 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL s2 WAIT_FOR g2';
@@ -39,6 +40,7 @@ SET DEBUG_SYNC='now WAIT_FOR s2';
 START TRANSACTION;
 SELECT * FROM t1;
 a	b	c
+1	test	test_1
 SET DEBUG_SYNC='now SIGNAL g2';
 connection con1;
 ERROR HY000: Out of memory.
diff --git a/mysql-test/suite/innodb_fts/t/misc_debug.test b/mysql-test/suite/innodb_fts/t/misc_debug.test
index c8542152d4d..4a0edf4ef88 100644
--- a/mysql-test/suite/innodb_fts/t/misc_debug.test
+++ b/mysql-test/suite/innodb_fts/t/misc_debug.test
@@ -59,6 +59,7 @@ DROP TABLE t2, t1;
 --echo # MDEV-25200 Index count mismatch due to aborted FULLTEXT INDEX
 --echo #
 CREATE TABLE t1(a INT, b TEXT, c TEXT, FULLTEXT INDEX(b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1, "test", "test_1");
 connect(con1,localhost,root,,test);
 SET DEBUG_DBUG="+d,innodb_OOM_inplace_alter";
 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL s2 WAIT_FOR g2';
diff --git a/sql/handler.h b/sql/handler.h
index 94ac56c3073..9b44b3fd297 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -2493,6 +2493,9 @@ public:
   /** true when InnoDB should abort the alter when table is not empty */
   bool error_if_not_empty;
 
+  /** True when DDL should avoid downgrading the MDL */
+  bool mdl_exclusive_after_prepare= false;
+
   Alter_inplace_info(HA_CREATE_INFO *create_info_arg,
                      Alter_info *alter_info_arg,
                      KEY *key_info_arg, uint key_count_arg,
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index e46a13ce7a9..4409360cd8f 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -7709,15 +7709,16 @@ static bool mysql_inplace_alter_table(THD *thd,
     lock for prepare phase under LOCK TABLES in the same way as when
     exclusive lock is required for duration of the whole statement.
   */
-  if (inplace_supported == HA_ALTER_INPLACE_EXCLUSIVE_LOCK ||
-      ((inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK ||
+  if (!ha_alter_info->mdl_exclusive_after_prepare &&
+      (inplace_supported == HA_ALTER_INPLACE_EXCLUSIVE_LOCK ||
+       ((inplace_supported == HA_ALTER_INPLACE_COPY_NO_LOCK ||
         inplace_supported == HA_ALTER_INPLACE_COPY_LOCK ||
         inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK ||
         inplace_supported == HA_ALTER_INPLACE_NOCOPY_LOCK ||
         inplace_supported == HA_ALTER_INPLACE_INSTANT) &&
        (thd->locked_tables_mode == LTM_LOCK_TABLES ||
         thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES)) ||
-      alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_EXCLUSIVE)
+      alter_info->requested_lock == Alter_info::ALTER_TABLE_LOCK_EXCLUSIVE))
   {
     if (wait_while_table_is_used(thd, table, HA_EXTRA_FORCE_REOPEN))
       goto cleanup;
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 7ee07f4160e..41871cedfe2 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -1949,10 +1949,15 @@ innobase_fts_check_doc_id_col(
 }
 
 /** Check whether the table is empty.
-@param[in]	table	table to be checked
+@param[in]	table			table to be checked
+@param[in]	ignore_delete_marked	Ignore the delete marked
+					flag record
 @return true if table is empty */
-static bool innobase_table_is_empty(const dict_table_t *table)
+static bool innobase_table_is_empty(const dict_table_t *table,
+				    bool ignore_delete_marked=true)
 {
+  if (!table->space)
+    return false;
   dict_index_t *clust_index= dict_table_get_first_index(table);
   mtr_t mtr;
   btr_pcur_t pcur;
@@ -1991,12 +1996,16 @@ next_page:
   }
 
   rec= page_cur_get_rec(cur);
-  if (rec_get_deleted_flag(rec, dict_table_is_comp(table)));
-  else if (!page_rec_is_supremum(rec))
+  if (rec_get_deleted_flag(rec, dict_table_is_comp(table)))
   {
+    if (ignore_delete_marked)
+      goto scan_leaf;
+non_empty:
     mtr.commit();
     return false;
   }
+  else if (!page_rec_is_supremum(rec))
+    goto non_empty;
   else
   {
     next_page= true;
@@ -6853,6 +6862,7 @@ wrong_column_name:
 		DBUG_ASSERT(num_fts_index <= 1);
 		DBUG_ASSERT(!ctx->online || num_fts_index == 0);
 		DBUG_ASSERT(!ctx->online
+			    || !ha_alter_info->mdl_exclusive_after_prepare
 			    || ctx->add_autoinc == ULINT_UNDEFINED);
 		DBUG_ASSERT(!ctx->online
 			    || !innobase_need_rebuild(ha_alter_info, old_table)
@@ -7666,6 +7676,20 @@ ha_innobase::prepare_inplace_alter_table(
 		DBUG_RETURN(false);
 	}
 
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+	if (table->part_info == NULL) {
+#endif
+	/* Ignore the MDL downgrade when table is empty.
+	This optimization is disabled for partition table. */
+	ha_alter_info->mdl_exclusive_after_prepare =
+		innobase_table_is_empty(m_prebuilt->table, false);
+	if (ha_alter_info->online
+	    && ha_alter_info->mdl_exclusive_after_prepare) {
+		ha_alter_info->online = false;
+	}
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+	}
+#endif
 	indexed_table = m_prebuilt->table;
 
 	/* ALTER TABLE will not implicitly move a table from a single-table
@@ -8450,7 +8474,9 @@ ha_innobase::inplace_alter_table(
 
 	DEBUG_SYNC(m_user_thd, "innodb_inplace_alter_table_enter");
 
-	if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA)) {
+	/* Ignore the inplace alter phase when table is empty */
+	if (!(ha_alter_info->handler_flags & INNOBASE_ALTER_DATA)
+	    || ha_alter_info->mdl_exclusive_after_prepare) {
 ok_exit:
 		DEBUG_SYNC(m_user_thd, "innodb_after_inplace_alter_table");
 		DBUG_RETURN(false);