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

17 files changed, 28 insertions, 6 deletions

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';