MDEV-13407 innodb.drop_table_background failed in buildbot with "Tablespace for table exists"bb-10.2-ext-marko

The InnoDB background DROP TABLE queue is something that we should
really remove, but are unable to until we remove dict_operation_lock
so that DDL and DML operations can be combined in a single transaction.

Because the queue is not persistent, it is not crash-safe. We should
in some way ensure that the deferred-dropped tables will be dropped
after server restart.

The existence of two separate transactions complicates the error handling
of CREATE TABLE...SELECT. We should really not break locks in DROP TABLE.

Our solution to these problems is to rename the table to a temporary
name, and to drop such-named tables on InnoDB startup. Also, the
queue will use table IDs instead of names from now on.

check-testcase.test: Ignore #sql-ib*.ibd files, because tables may enter
the background DROP TABLE queue shortly before the test finishes.

innodb.drop_table_background: Test CREATE...SELECT and the creation of
tables whose file name starts with #sql-ib.

innodb.alter_crash: Adjust the recovery, now that the #sql-ib tables
will be dropped on InnoDB startup.

row_mysql_drop_garbage_tables(): New function, to drop all #sql-ib tables
on InnoDB startup.

row_mysql_drop_t::table_id: Replaces table_name.

row_drop_table_for_mysql_in_background(): Remove an unnecessary and
misplaced call to log_buffer_flush_to_disk(). (The call should have been
after the transaction commit. We do not care about flushing the redo log
here, because the table would be dropped again at server startup.)

If server shutdown has been initiated, empty the list without actually
dropping the tables. They will be dropped again on startup.

Remove the entry from the list before attempting drop, and re-add the
entry in case of failure. In this way, the table should eventually be
dropped.

row_drop_table_for_mysql(): Do not call lock_remove_all_on_table().
Instead, if locks exist, defer the DROP TABLE until they do not exist.
If the table name does not start with #sql-ib, rename it to that prefix
before adding it to the background DROP TABLE queue.

5 files changed, 67 insertions, 57 deletions

diff --git a/mysql-test/include/check-testcase.test b/mysql-test/include/check-testcase.test
index a282201857e..4ca53989d06 100644
--- a/mysql-test/include/check-testcase.test
+++ b/mysql-test/include/check-testcase.test
@@ -82,7 +82,10 @@ call mtr.check_testcase();
 
 let $datadir=`select @@datadir`;
 list_files $datadir mysql_upgrade_info;
-list_files $datadir/test #sql*;
+list_files_write_file $datadir.tempfiles.txt $datadir/test #sql*;
+--replace_regex /#sql-ib[0-9a-f]+-[0-9a-f]+\.ibd\n//
+cat_file $datadir.tempfiles.txt;
+remove_file $datadir.tempfiles.txt;
 list_files $datadir/mysql #sql*;
 
 --enable_query_log
diff --git a/mysql-test/suite/innodb/r/alter_crash.result b/mysql-test/suite/innodb/r/alter_crash.result
index 8de02cc5fbd..df1645a4ef6 100644
--- a/mysql-test/suite/innodb/r/alter_crash.result
+++ b/mysql-test/suite/innodb/r/alter_crash.result
@@ -44,10 +44,9 @@ SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';
 ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
 ERROR HY000: Lost connection to MySQL server during query
 # Restart mysqld after the crash and reconnect.
-# Manual *.frm recovery begin.
-# Manual recovery end
-FLUSH TABLES;
-# Drop the orphaned original table.
+SELECT * FROM information_schema.innodb_sys_tables
+WHERE table_id = ID;
+TABLE_ID	NAME	FLAG	N_COLS	SPACE	FILE_FORMAT	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
 # Files in datadir after manual recovery.
 t1.frm
 t1.ibd
@@ -83,11 +82,9 @@ SET DEBUG_DBUG='+d,innodb_alter_commit_crash_before_commit';
 ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
 ERROR HY000: Lost connection to MySQL server during query
 # Startup the server after the crash
-# Read and remember the temporary table name
-# Manual *.frm recovery begin. The dictionary was not updated
-# and the files were not renamed. The rebuilt table
-# was left behind on purpose, to faciliate data recovery.
-# Manual recovery end
+SELECT * FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test/#sql-ib%';
+TABLE_ID	NAME	FLAG	N_COLS	SPACE	FILE_FORMAT	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
 # Drop the orphaned rebuilt table.
 SHOW TABLES;
 Tables_in_test
@@ -123,10 +120,10 @@ SET DEBUG_DBUG='+d,innodb_alter_commit_crash_after_commit';
 ALTER TABLE t1 ADD INDEX (b), CHANGE c d int, ALGORITHM=INPLACE;
 ERROR HY000: Lost connection to MySQL server during query
 # Restart mysqld after the crash and reconnect.
-# Manual *.frm recovery begin.
-# Manual recovery end
+SELECT * FROM information_schema.innodb_sys_tables
+WHERE table_id = ID;
+TABLE_ID	NAME	FLAG	N_COLS	SPACE	FILE_FORMAT	ROW_FORMAT	ZIP_PAGE_SIZE	SPACE_TYPE
 FLUSH TABLES;
-# Drop the orphaned original table.
 # Files in datadir after manual recovery.
 t1.frm
 t1.ibd
diff --git a/mysql-test/suite/innodb/r/drop_table_background.result b/mysql-test/suite/innodb/r/drop_table_background.result
index a6f5672ba7f..e74bcd5e780 100644
--- a/mysql-test/suite/innodb/r/drop_table_background.result
+++ b/mysql-test/suite/innodb/r/drop_table_background.result
@@ -3,7 +3,22 @@ KEY(c1), KEY(c2), KEY(c2,c1),
 KEY(c3), KEY(c3,c1), KEY(c3,c2), KEY(c3,c2,c1),
 KEY(c4), KEY(c4,c1), KEY(c4,c2), KEY(c4,c2,c1),
 KEY(c4,c3), KEY(c4,c3,c1), KEY(c4,c3,c2), KEY(c4,c3,c2,c1)) ENGINE=InnoDB;
+CREATE TABLE `#mysql50##sql-ib-foo`(a SERIAL) ENGINE=InnoDB;
+INSERT INTO t (c1) VALUES (1),(2),(1);
 SET DEBUG_DBUG='+d,row_drop_table_add_to_background';
+CREATE TABLE target (PRIMARY KEY(c1)) ENGINE=InnoDB SELECT * FROM t;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from target;
+ERROR 42S02: Table 'test.target' doesn't exist
 DROP TABLE t;
 CREATE TABLE t (a INT) ENGINE=InnoDB;
 DROP TABLE t;
+DROP TABLE target;
+ERROR 42S02: Unknown table 'test.target'
+CREATE TABLE target (a INT) ENGINE=InnoDB;
+DROP TABLE target;
+SELECT * FROM `#mysql50##sql-ib-foo`;
+ERROR 42S02: Table 'test.#mysql50##sql-ib-foo' doesn't exist in engine
+DROP TABLE `#mysql50##sql-ib-foo`;
+Warnings:
+Warning	1932	Table 'test.#mysql50##sql-ib-foo' doesn't exist in engine
diff --git a/mysql-test/suite/innodb/t/alter_crash.test b/mysql-test/suite/innodb/t/alter_crash.test
index b4fdfd2f2d5..c4ee895d192 100644
--- a/mysql-test/suite/innodb/t/alter_crash.test
+++ b/mysql-test/suite/innodb/t/alter_crash.test
@@ -75,28 +75,22 @@ ALTER TABLE t1 ADD PRIMARY KEY (f2, f1);
 --echo # Restart mysqld after the crash and reconnect.
 --source include/start_mysqld.inc
 
-let $temp_table_name = `SELECT SUBSTR(name, 6)
-	FROM information_schema.innodb_sys_tables
-	WHERE table_id = $orig_table_id`;
-
---echo # Manual *.frm recovery begin.
-
---move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm
-
+let TABLENAME_INC= $MYSQLTEST_VARDIR/tmp/tablename.inc;
 perl;
-my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm";
-my $t1_frm = "$ENV{'datadir'}/test/t1.frm";
-rename($frm_file[0], $t1_frm);
+die unless open OUT, ">$ENV{TABLENAME_INC}";
+chdir "$ENV{'datadir'}/test";
+my @frm_file = map { substr($_, 0, -4) } glob "#sql-*.frm";
+print OUT 'let $tablename=', $frm_file[0], ';';
+close OUT or die;
 EOF
+source $TABLENAME_INC;
+remove_file $TABLENAME_INC;
 
---echo # Manual recovery end
-
-FLUSH TABLES;
+--replace_result $orig_table_id ID
+eval SELECT * FROM information_schema.innodb_sys_tables
+WHERE table_id = $orig_table_id;
 
---echo # Drop the orphaned original table.
---disable_query_log
-eval DROP TABLE `#mysql50#$temp_table_name`;
---enable_query_log
+move_file $datadir/test/$tablename.frm $datadir/test/t1.frm;
 
 --echo # Files in datadir after manual recovery.
 --list_files $MYSQLD_DATADIR/test
@@ -134,16 +128,9 @@ ALTER TABLE t2 ADD PRIMARY KEY (f2, f1);
 --echo # Startup the server after the crash
 --source include/start_mysqld.inc
 
---echo # Read and remember the temporary table name
-let $temp_table_name = `SELECT SUBSTRING(name,6)
-	FROM information_schema.innodb_sys_tables
-	WHERE name LIKE "test/#sql-ib$orig_table_id%"`;
-
---echo # Manual *.frm recovery begin. The dictionary was not updated
---echo # and the files were not renamed. The rebuilt table
---echo # was left behind on purpose, to faciliate data recovery.
+SELECT * FROM information_schema.innodb_sys_tables
+WHERE name LIKE 'test/#sql-ib%';
 
-let TABLENAME_INC= $MYSQLTEST_VARDIR/tmp/tablename.inc;
 perl;
 die unless open OUT, ">$ENV{TABLENAME_INC}";
 chdir "$ENV{'datadir'}/test";
@@ -154,8 +141,6 @@ EOF
 source $TABLENAME_INC;
 remove_file $TABLENAME_INC;
 
---echo # Manual recovery end
-
 --echo # Drop the orphaned rebuilt table.
 --disable_query_log
 eval DROP TABLE `#mysql50#$tablename`;
@@ -198,28 +183,24 @@ ALTER TABLE t1 ADD INDEX (b), CHANGE c d int, ALGORITHM=INPLACE;
 --echo # Restart mysqld after the crash and reconnect.
 --source include/start_mysqld.inc
 
-let $temp_table_name = `SELECT SUBSTR(name, 6)
-	FROM information_schema.innodb_sys_tables
-	WHERE table_id = $orig_table_id`;
-
---echo # Manual *.frm recovery begin.
---move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm
+--replace_result $orig_table_id ID
+eval SELECT * FROM information_schema.innodb_sys_tables
+WHERE table_id = $orig_table_id;
 
 perl;
-my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm";
-my $t1_frm = "$ENV{'datadir'}/test/t1.frm";
-rename($frm_file[0], $t1_frm);
+die unless open OUT, ">$ENV{TABLENAME_INC}";
+chdir "$ENV{'datadir'}/test";
+my @frm_file = map { substr($_, 0, -4) } glob "#sql-*.frm";
+print OUT 'let $tablename=', $frm_file[0], ';';
+close OUT or die;
 EOF
 
---echo # Manual recovery end
+source $TABLENAME_INC;
+remove_file $TABLENAME_INC;
+move_file $datadir/test/$tablename.frm $datadir/test/t1.frm;
 
 FLUSH TABLES;
 
---echo # Drop the orphaned original table.
---disable_query_log
-eval DROP TABLE `#mysql50#$temp_table_name`;
---enable_query_log
-
 --echo # Files in datadir after manual recovery.
 --list_files $MYSQLD_DATADIR/test
 
diff --git a/mysql-test/suite/innodb/t/drop_table_background.test b/mysql-test/suite/innodb/t/drop_table_background.test
index 0f596dec574..8d82bea9675 100644
--- a/mysql-test/suite/innodb/t/drop_table_background.test
+++ b/mysql-test/suite/innodb/t/drop_table_background.test
@@ -9,6 +9,9 @@ KEY(c3), KEY(c3,c1), KEY(c3,c2), KEY(c3,c2,c1),
 KEY(c4), KEY(c4,c1), KEY(c4,c2), KEY(c4,c2,c1),
 KEY(c4,c3), KEY(c4,c3,c1), KEY(c4,c3,c2), KEY(c4,c3,c2,c1)) ENGINE=InnoDB;
 
+CREATE TABLE `#mysql50##sql-ib-foo`(a SERIAL) ENGINE=InnoDB;
+INSERT INTO t (c1) VALUES (1),(2),(1);
+
 let $n= 10;
 
 SET DEBUG_DBUG='+d,row_drop_table_add_to_background';
@@ -24,7 +27,18 @@ while ($i) {
   dec $i;
 }
 --enable_query_log
+--error ER_DUP_ENTRY
+CREATE TABLE target (PRIMARY KEY(c1)) ENGINE=InnoDB SELECT * FROM t;
+--error ER_NO_SUCH_TABLE
+SELECT * from target;
 DROP TABLE t;
 --source include/restart_mysqld.inc
 CREATE TABLE t (a INT) ENGINE=InnoDB;
 DROP TABLE t;
+--error ER_BAD_TABLE_ERROR
+DROP TABLE target;
+CREATE TABLE target (a INT) ENGINE=InnoDB;
+DROP TABLE target;
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+SELECT * FROM `#mysql50##sql-ib-foo`;
+DROP TABLE `#mysql50##sql-ib-foo`;