Bug #72686 Drop database doesn't remove temp-table residing in that db

• Submitted: 20 May 2014 3:11
• Modified: 20 May 2014 7:05
• Reporter: Krunal Bauskar
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.5
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[20 May 2014 3:11] Krunal Bauskar

Description:
- Drop database fails to remove the temporary table that resides in that db.

How to repeat:
mysql> use test;
Database changed
mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table t (i int) engine =innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
|       20 | #sql7730_1_0 |      4 |     8 | FALSE                | FALSE         |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.00 sec)

mysql> drop database t2;
Query OK, 0 rows affected (2.49 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
|       20 | #sql7730_1_0 |      4 |     8 | FALSE                | FALSE         |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.01 sec)

Suggested fix:
- Drop database scan SYS_TABLES to generate list of tables residing in that db but temporary tables entry is not made to SYS_TABLES and so the failure.

[20 May 2014 5:38] Krunal Bauskar

Updated TCs: (Initial tc continued to use test though the bug still remain valid.)

mysql> use test;
Database changed
mysql> create database t2;
Query OK, 1 row affected (0.00 sec)

mysql> use t2;
Database changed
mysql> create temporary table t (i int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
|       20 | #sql3098_1_0 |      4 |     8 | FALSE                | FALSE         |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.00 sec)

mysql> drop database t2;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from information_schema.innodb_temp_table_info;
+----------+--------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME         | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+--------------+--------+-------+----------------------+---------------+
|       20 | #sql3098_1_0 |      4 |     8 | FALSE                | FALSE         |
+----------+--------------+--------+-------+----------------------+---------------+
1 row in set (0.01 sec)

[20 May 2014 5:51] MySQL Verification Team

Hello Krunal,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

[20 May 2014 7:06] Krunal Bauskar

Following bug is tracking the issue.

http://bugs.mysql.com/bug.php?id=30099