Bug #79588 "select database()" can return database name which was deleted in other session

• Submitted: 10 Dec 2015 8:19
• Modified: 10 Dec 2015 9:33
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.0/5.1/5.5/5.6/5.7
• OS: Any
• CPU Architecture: Any

[10 Dec 2015 8:19] Su Dylan

Description:
Output:
=======
> connection 1
mysql> create database db_for_drop;
Query OK, 1 row affected (0.00 sec)

mysql> use db_for_drop;
Database changed
mysql> select database();
+-------------+
| database()  |
+-------------+
| db_for_drop |
+-------------+
1 row in set (0.00 sec)

> connection 2
mysql> use db_for_drop;
Database changed
mysql> drop database db_for_drop;
Query OK, 0 rows affected (0.01 sec)

> back to previous connection 1
mysql> select database();
+-------------+
| database()  |
+-------------+
| db_for_drop |
+-------------+
1 row in set (0.00 sec)

Problem:
========
After current database is deleted in other session, select database() can still return the already deleted database name.

How to repeat:
> connection 1
create database db_for_drop;
use db_for_drop;

> connection 2
use db_for_drop;
drop database db_for_drop;

> connection 1 (Use the original one)
select database();

Suggested fix:
Select database() should return NULL if the current database doesn't exist any more.

[10 Dec 2015 9:33] MySQL Verification Team

Thank you for the bug report. This behavior exist since 5.0.

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.11 Source distribution PULL: 2015-DEC-05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > create database db_for_drop;
Query OK, 1 row affected (0.02 sec)

mysql 5.7 > use db_for_drop;
Database changed
mysql 5.7 > select database();
+-------------+
| database()  |
+-------------+
| db_for_drop |
+-------------+
1 row in set (0.00 sec)

mysql 5.7 > create table t1 (id int);
ERROR 1049 (42000): Unknown database 'db_for_drop'
mysql 5.7 >