MDEV-20370 Crash after OPTIMIZE TABLE on TEMPORARY TABLE

Temporary tables are typically short-lived, and temporary tables
are assumed to be accessed only by the thread that is handling
the owning connection. Hence, they must not be subject to
defragmenting.

ha_innobase::optimize(): Do not add temporary tables to
the defragment_table() queue.

3 files changed, 32 insertions, 21 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
index d10727b95b4..07c96e76213 100644
--- a/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
+++ b/mysql-test/suite/innodb/r/innodb_defrag_concurrent.result
@@ -1,7 +1,7 @@
-DROP TABLE if exists t1;
-select @@global.innodb_stats_persistent;
-@@global.innodb_stats_persistent
-0
+SET @n_pages= @@GLOBAL.innodb_defragment_n_pages;
+SET @accuracy= @@GLOBAL.innodb_defragment_stats_accuracy;
+SET @sp= @@GLOBAL.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent = 0;
 set global innodb_defragment_stats_accuracy = 80;
 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 b VARCHAR(256),
@@ -18,6 +18,14 @@ connect  con3,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
 connect  con4,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
 connection default;
 SET @@global.innodb_defragment_n_pages = 20;
+CREATE TEMPORARY TABLE tt (a INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+INSERT INTO tt SELECT 0 FROM seq_1_to_180;
+INSERT INTO tt SELECT 5 FROM seq_1_to_160;
+INSERT INTO tt SELECT 1 FROM seq_1_to_1000;
+OPTIMIZE TABLE tt;
+Table	Op	Msg_type	Msg_text
+test.tt	optimize	note	Table does not support optimize, doing recreate + analyze instead
+test.tt	optimize	status	OK
 select count(*) from t1;
 count(*)
 20000
@@ -89,3 +97,6 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like
 count(stat_value) > 0
 1
 drop table t1;
+SET GLOBAL innodb_defragment_n_pages = @n_pages;
+SET GLOBAL innodb_defragment_stats_accuracy = @accuracy;
+SET GLOBAL innodb_stats_persistent = @sp;
diff --git a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
index bbcd72f1a3a..1e4e14eb7c6 100644
--- a/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
+++ b/mysql-test/suite/innodb/t/innodb_defrag_concurrent.test
@@ -2,17 +2,13 @@
 --source include/big_test.inc
 --source include/not_valgrind.inc
 --source include/not_embedded.inc
+--source include/have_sequence.inc
 
---disable_warnings
-DROP TABLE if exists t1;
---enable_warnings
+SET @n_pages= @@GLOBAL.innodb_defragment_n_pages;
+SET @accuracy= @@GLOBAL.innodb_defragment_stats_accuracy;
+SET @sp= @@GLOBAL.innodb_stats_persistent;
 
---disable_query_log
-let $innodb_defragment_n_pages_orig=`select @@innodb_defragment_n_pages`;
-let $innodb_defragment_stats_accuracy_orig=`select @@innodb_defragment_stats_accuracy`;
---enable_query_log
-
-select @@global.innodb_stats_persistent;
+SET GLOBAL innodb_stats_persistent = 0;
 set global innodb_defragment_stats_accuracy = 80;
 
 # Create table.
@@ -46,6 +42,12 @@ connection default;
 
 SET @@global.innodb_defragment_n_pages = 20;
 
+CREATE TEMPORARY TABLE tt (a INT, KEY(a)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
+INSERT INTO tt SELECT 0 FROM seq_1_to_180;
+INSERT INTO tt SELECT 5 FROM seq_1_to_160;
+INSERT INTO tt SELECT 1 FROM seq_1_to_1000;
+OPTIMIZE TABLE tt;
+
 let $data_size = 20000;
 let $delete_size = 2000;
 
@@ -60,7 +62,7 @@ while ($i)
 }
 --enable_query_log
 
-select count(*) from t1; 
+select count(*) from t1;
 select count(*) from t1 force index (second);
 select count(*) from t1 force index (third);
 
@@ -75,7 +77,7 @@ while ($size)
 }
 --enable_query_log
 
-select count(*) from t1; 
+select count(*) from t1;
 select count(*) from t1 force index (second);
 select count(*) from t1 force index (third);
 
@@ -136,7 +138,6 @@ select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like
 drop table t1;
 
 # reset system
---disable_query_log
-EVAL SET GLOBAL innodb_defragment_n_pages = $innodb_defragment_n_pages_orig;
-EVAL SET GLOBAL innodb_defragment_stats_accuracy = $innodb_defragment_stats_accuracy_orig;
---enable_query_log
+SET GLOBAL innodb_defragment_n_pages = @n_pages;
+SET GLOBAL innodb_defragment_stats_accuracy = @accuracy;
+SET GLOBAL innodb_stats_persistent = @sp;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 0338e2b682a..044d407f7e7 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -14903,8 +14903,7 @@ ha_innobase::optimize(
 	calls to OPTIMIZE, which is undesirable. */
 	bool try_alter = true;
 
-	/* TODO: Defragment is disabled for now */
-	if (srv_defragment) {
+	if (!m_prebuilt->table->is_temporary() && srv_defragment) {
 		int err;
 
 		err = defragment_table(m_prebuilt->table->name.m_name, NULL, false);