Bug #107135 “create temporary table like” failed for the same table

• Submitted: 27 Apr 2022 7:24
• Modified: 5 May 2022 8:41
• Reporter: Xlong Li (OCA)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.28, 8.0.29
• OS: Any
• CPU Architecture: Any

[27 Apr 2022 7:24] Xlong Li

Description:
run sql "create temporary table like" failed when the source table is the same one
error is "ERROR 1478 (HY000): InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables."

set file per table off, so that the table can created on sys-tablespace

"create temporary table like" runs fine the first time

"create temporary table like" fails after in-place rebuild of source table

How to repeat:

mysql> set global innodb_file_per_table=off;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> create temporary table t2 like t1;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create temporary table t3 like t1;
ERROR 1478 (HY000): InnoDB: Tablespace `innodb_system` cannot contain TEMPORARY tables.

[27 Apr 2022 8:09] MySQL Verification Team

Hello Xlong Li,

Thank you for the report and test case.

regards,
Umesh

[5 May 2022 8:18] huahua xu

Hi, Xlong Li:

You may have a good idea to fix the bug, After reading this commit: https://github.com/mysql/mysql-server/commit/18dc75bdecb1b0598d8dbc8ba9bfd4b418032209

[5 May 2022 8:30] huahua xu

InnoDB has specified that: 
All temporary tables are created in a session temporary tablespace. TABLESPACE=%s option is deprecated and will be removed in a future release

so, the tablespace option should not be kept when target is temporary table.

diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index b60844a71ac..e70f12238e6 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -10810,9 +10810,10 @@ bool mysql_create_like_table(THD *thd, TABLE_LIST *table, TABLE_LIST *src_table,

   /*
     Keep tablespace, only if it was specified explicitly in CREATE
-    TABLE when source table was created.
+    TABLE when source table was created and target object is not temporary table.
   */
-  if (src_table_obj && !src_table_obj->is_explicit_tablespace()) {
+  if ((create_info->options & HA_LEX_CREATE_TMP_TABLE)
+      || (src_table_obj && !src_table_obj->is_explicit_tablespace())) {
     local_create_info.tablespace = nullptr;
   }

[5 May 2022 8:41] Xlong Li

@huahua xu

thanks a lot for your suggestions

it's very reasonable that session level explicit temporary tablespace is discard