MDEV-23805 Make Online DDL to Instant DDL when table is emptybb-10.7-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

27 files changed, 89 insertions, 22 deletions

diff --git a/mysql-test/main/innodb_mysql_sync.result b/mysql-test/main/innodb_mysql_sync.result
index 46ed7d43830..9b85efe2c8e 100644
--- a/mysql-test/main/innodb_mysql_sync.result
+++ b/mysql-test/main/innodb_mysql_sync.result
@@ -131,6 +131,7 @@ connection default;
 DROP DATABASE db1;
 # Test 2: Primary index (implicit), should block writes.
 CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
+INSERT INTO t1 VALUES(1, 1);
 SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
 # Sending:
 ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED;
@@ -139,8 +140,9 @@ SET DEBUG_SYNC= "now WAIT_FOR manage";
 USE test;
 SELECT * FROM t1;
 a	b
+1	1
 # Sending:
-UPDATE t1 SET a=NULL;
+UPDATE t1 SET a=2;
 connection con2;
 # Waiting for SELECT to be blocked by the metadata lock on t1
 SET DEBUG_SYNC= "now SIGNAL query";
@@ -158,15 +160,16 @@ connection con1;
 SET DEBUG_SYNC= "now WAIT_FOR manage";
 SELECT * FROM t1;
 a	b
+2	1
 # Sending:
-UPDATE t1 SET a=NULL;
+UPDATE t1 SET a=3;
 connection con2;
 # Waiting for SELECT to be blocked by the metadata lock on t1
 SET DEBUG_SYNC= "now SIGNAL query";
 connection default;
 # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a)
 connection con1;
-# Reaping: UPDATE t1 SET a=NULL
+# Reaping: UPDATE t1 SET a=3
 # Test 4: Secondary unique index, should not block reads.
 connection default;
 SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
@@ -176,6 +179,7 @@ connection con1;
 SET DEBUG_SYNC= "now WAIT_FOR manage";
 SELECT * FROM t1;
 a	b
+3	1
 SET DEBUG_SYNC= "now SIGNAL query";
 connection default;
 # Reaping: ALTER TABLE t1 ADD UNIQUE (b)
diff --git a/mysql-test/main/innodb_mysql_sync.test b/mysql-test/main/innodb_mysql_sync.test
index 4026080c4b4..56e4da34031 100644
--- a/mysql-test/main/innodb_mysql_sync.test
+++ b/mysql-test/main/innodb_mysql_sync.test
@@ -176,6 +176,7 @@ DROP DATABASE db1;
 --echo # Test 2: Primary index (implicit), should block writes.
 
 CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb;
+INSERT INTO t1 VALUES(1, 1);
 SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query";
 --echo # Sending:
 --send ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED
@@ -185,13 +186,13 @@ SET DEBUG_SYNC= "now WAIT_FOR manage";
 USE test;
 SELECT * FROM t1;
 --echo # Sending:
---send UPDATE t1 SET a=NULL
+--send UPDATE t1 SET a=2
 
 connection con2;
 --echo # Waiting for SELECT to be blocked by the metadata lock on t1
 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
   WHERE state= 'Waiting for table metadata lock'
-  AND info='UPDATE t1 SET a=NULL';
+  AND info='UPDATE t1 SET a=2';
 --source include/wait_condition.inc
 SET DEBUG_SYNC= "now SIGNAL query";
 
@@ -215,13 +216,13 @@ connection con1;
 SET DEBUG_SYNC= "now WAIT_FOR manage";
 SELECT * FROM t1;
 --echo # Sending:
---send UPDATE t1 SET a=NULL
+--send UPDATE t1 SET a=3
 
 connection con2;
 --echo # Waiting for SELECT to be blocked by the metadata lock on t1
 let $wait_condition= SELECT COUNT(*)= 1 FROM information_schema.processlist
   WHERE state= 'Waiting for table metadata lock'
-  AND info='UPDATE t1 SET a=NULL';
+  AND info='UPDATE t1 SET a=3';
 --source include/wait_condition.inc
 SET DEBUG_SYNC= "now SIGNAL query";
 
@@ -230,7 +231,7 @@ connection default;
 --reap
 
 connection con1;
---echo # Reaping: UPDATE t1 SET a=NULL
+--echo # Reaping: UPDATE t1 SET a=3
 --reap
 
 --echo # Test 4: Secondary unique index, should not block reads.
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 b0b56047abc..ec171e34102 100644
--- a/mysql-test/suite/innodb/r/alter_candidate_key.result
+++ b/mysql-test/suite/innodb/r/alter_candidate_key.result
@@ -43,6 +43,7 @@ t1	CREATE TABLE `t1` (
   UNIQUE KEY `uidx2` (`f1`,`f2`),
   UNIQUE KEY `uidx1` (`f2`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES(2, 2);
 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter
 		  SIGNAL conc_dml WAIT_FOR go_ahead';
 ALTER TABLE t1 CHANGE COLUMN f1 f11 INT, ALGORITHM=INPLACE;
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..3bb5e06f372 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 a1d96de24ca..6ed220a3c84 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);
 INSERT INTO t0 VALUES(100);
 connect  con1,localhost,root,,test;
 BEGIN;
diff --git a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
index a64b2339a39..845dbacbada 100644
--- a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
+++ b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result
@@ -55,7 +55,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 8a1091266a9..f6b23eea41a 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 6ad5c20ffed..31974dec1eb 100644
--- a/mysql-test/suite/innodb/r/innodb-index-online.result
+++ b/mysql-test/suite/innodb/r/innodb-index-online.result
@@ -493,6 +493,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;
@@ -509,6 +510,7 @@ ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1 (c);
 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
 DROP TABLE t2,t1;
 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 2);
 connect  con1,localhost,root,,;
 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL created WAIT_FOR inserted';
 ALTER TABLE t1 ADD INDEX(b);
@@ -523,6 +525,7 @@ disconnect con1;
 connection default;
 SELECT * FROM t1;
 a	b
+2	2
 CHECK TABLE t1;
 Table	Op	Msg_type	Msg_text
 test.t1	check	status	OK
diff --git a/mysql-test/suite/innodb/r/innodb-table-online.result b/mysql-test/suite/innodb/r/innodb-table-online.result
index 8f06576f2ed..68742e909a2 100644
--- a/mysql-test/suite/innodb/r/innodb-table-online.result
+++ b/mysql-test/suite/innodb/r/innodb-table-online.result
@@ -434,6 +434,7 @@ t1	CREATE TABLE `t1` (
 SET GLOBAL innodb_monitor_disable = module_ddl;
 DROP TABLE t1;
 CREATE TABLE t1 (a INT PRIMARY KEY, b blob) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(2, 'b');
 connection con1;
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL created WAIT_FOR ins';
 ALTER TABLE t1 FORCE;
@@ -448,6 +449,7 @@ disconnect con1;
 connection default;
 SELECT * FROM t1;
 a	b
+2	b
 DROP TABLE t1;
 SET DEBUG_SYNC = 'RESET';
 SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
diff --git a/mysql-test/suite/innodb/t/alter_candidate_key.test b/mysql-test/suite/innodb/t/alter_candidate_key.test
index 979d8fa4fee..afbace78f20 100644
--- a/mysql-test/suite/innodb/t/alter_candidate_key.test
+++ b/mysql-test/suite/innodb/t/alter_candidate_key.test
@@ -26,6 +26,7 @@ CREATE TABLE t1(f1 INT, f2 INT,
 		UNIQUE INDEX uidx1 (f2))ENGINE=InnoDB;
 ALTER TABLE t1 DROP PRIMARY KEY;
 SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES(2, 2);
 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter
 		  SIGNAL conc_dml WAIT_FOR go_ahead';
 --send ALTER TABLE t1 CHANGE COLUMN f1 f11 INT, ALGORITHM=INPLACE
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..f753bec1dc1 100644
--- a/mysql-test/suite/innodb/t/alter_not_null_debug.test
+++ b/mysql-test/suite/innodb/t/alter_not_null_debug.test
@@ -75,6 +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';
diff --git a/mysql-test/suite/innodb/t/alter_primary_key.test b/mysql-test/suite/innodb/t/alter_primary_key.test
index 4edc0cc023e..6aa88663d9b 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 678cb597c03..1baabb49e06 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);
 # MDEV-515 takes X-lock on the table for the first insert.
 # So concurrent insert won't happen on the table
 INSERT INTO t0 VALUES(100);
diff --git a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
index 769ac8fa4bc..ab6e1586897 100644
--- a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
+++ b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test
@@ -79,7 +79,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 9083dc80720..204bdfe5540 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 08711aa1a5d..580131d23d4 100644
--- a/mysql-test/suite/innodb/t/innodb-index-online.test
+++ b/mysql-test/suite/innodb/t/innodb-index-online.test
@@ -475,6 +475,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);
 
@@ -497,7 +498,7 @@ ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1 (c);
 DROP TABLE t2,t1;
 
 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
-
+INSERT INTO t1 VALUES(2, 2);
 connect (con1,localhost,root,,);
 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL created WAIT_FOR inserted';
 send ALTER TABLE t1 ADD INDEX(b);
diff --git a/mysql-test/suite/innodb/t/innodb-table-online.test b/mysql-test/suite/innodb/t/innodb-table-online.test
index 9cf1c871dcb..424879e6e49 100644
--- a/mysql-test/suite/innodb/t/innodb-table-online.test
+++ b/mysql-test/suite/innodb/t/innodb-table-online.test
@@ -395,7 +395,7 @@ SET GLOBAL innodb_monitor_disable = module_ddl;
 DROP TABLE t1;
 
 CREATE TABLE t1 (a INT PRIMARY KEY, b blob) ENGINE=InnoDB;
-
+INSERT INTO t1 VALUES(2, 'b');
 connection con1;
 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL created WAIT_FOR ins';
 send ALTER TABLE t1 FORCE;
diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test
index b553dc3ad74..9b174a1974b 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 d012fa5a9c8..2a2afacb052 100644
--- a/mysql-test/suite/innodb_fts/r/misc_debug.result
+++ b/mysql-test/suite/innodb_fts/r/misc_debug.result
@@ -31,6 +31,7 @@ SET SESSION debug_dbug=@saved_debug_dbug;
 # 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';
@@ -40,6 +41,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 bbf3afe69e5..84da1320264 100644
--- a/mysql-test/suite/innodb_fts/t/misc_debug.test
+++ b/mysql-test/suite/innodb_fts/t/misc_debug.test
@@ -60,6 +60,7 @@ SET SESSION debug_dbug=@saved_debug_dbug;
 --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.cc b/sql/handler.cc
index 57992d98c04..90b155f11d8 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -5268,7 +5268,8 @@ Alter_inplace_info::Alter_inplace_info(HA_CREATE_INFO *create_info_arg,
     rename_keys(current_thd->mem_root),
     modified_part_info(modified_part_info_arg),
     ignore(ignore_arg),
-    error_if_not_empty(error_non_empty)
+    error_if_not_empty(error_non_empty),
+    no_downgrade_if_empty(false)
   {}
 
 void Alter_inplace_info::report_unsupported_error(const char *not_supported,
diff --git a/sql/handler.h b/sql/handler.h
index 3303b8fe9e0..57b9f87fedd 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -2625,6 +2625,9 @@ public:
   /** true when InnoDB should abort the alter when table is not empty */
   const bool error_if_not_empty;
 
+  /** True when InnoDB should avoid downgrading the MDL */
+  bool no_downgrade_if_empty;
+
   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 7a7a56c1336..448d2dc1ac3 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -7472,6 +7472,7 @@ static bool mysql_inplace_alter_table(THD *thd,
        inplace_supported == HA_ALTER_INPLACE_NOCOPY_NO_LOCK) &&
       !(thd->locked_tables_mode == LTM_LOCK_TABLES ||
         thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES) &&
+      !ha_alter_info->no_downgrade_if_empty &&
       (alter_info->requested_lock != Alter_info::ALTER_TABLE_LOCK_EXCLUSIVE))
   {
     /* If storage engine or user requested shared lock downgrade to SNW. */
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index e4e84521e8d..7da4c1b344e 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -1914,10 +1914,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_flag	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_flag=true)
 {
+  if (!table->space)
+    return false;
   dict_index_t *clust_index= dict_table_get_first_index(table);
   mtr_t mtr;
   btr_pcur_t pcur;
@@ -1955,12 +1960,17 @@ 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)))
   {
-    mtr.commit();
-    return false;
+    if (!ignore_delete_flag)
+    {
+non_empty:
+      mtr.commit();
+      return false;
+    }
   }
+  else if (!page_rec_is_supremum(rec))
+    goto non_empty;
   else
   {
     next_page= true;
@@ -6753,6 +6763,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->no_downgrade_if_empty
 			    || ctx->add_autoinc == ULINT_UNDEFINED);
 		DBUG_ASSERT(!ctx->online
 			    || !innobase_need_rebuild(ha_alter_info, old_table)
@@ -7516,6 +7527,7 @@ ha_innobase::prepare_inplace_alter_table(
 	bool		add_fts_idx		= false;
 	dict_s_col_list*s_cols			= NULL;
 	mem_heap_t*	s_heap			= NULL;
+	bool		is_partition_exist	= false;
 
 	DBUG_ENTER("prepare_inplace_alter_table");
 	DBUG_ASSERT(!ha_alter_info->handler_ctx);
@@ -7547,6 +7559,19 @@ ha_innobase::prepare_inplace_alter_table(
 		DBUG_ASSERT(!m_prebuilt->trx->dict_operation_lock_mode);
 		DBUG_RETURN(false);
 	}
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+	is_partition_exist = table->part_info != NULL;
+#endif
+	/* Ignore the MDL downgrade when table is empty.
+	This optimization is disabled for partition table. */
+	if (!is_partition_exist) {
+		ha_alter_info->no_downgrade_if_empty =
+			innobase_table_is_empty(m_prebuilt->table, false);
+		if (ha_alter_info->online) {
+			ha_alter_info->online=
+				!ha_alter_info->no_downgrade_if_empty;
+		}
+	}
 
 	indexed_table = m_prebuilt->table;
 
@@ -8344,7 +8369,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->no_downgrade_if_empty) {
 ok_exit:
 		DEBUG_SYNC(m_user_thd, "innodb_after_inplace_alter_table");
 		DBUG_RETURN(false);