MySQL Bugs: #86282: Create database/directory inconsistency

Bug #86282	Create database/directory inconsistency
Submitted:	11 May 2017 12:41	Modified:	13 Dec 2017 3:06
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This is a bit of caveat in 8.0 that would be great to see fixed. 

In earlier versions, one could create a database by simple adding a directory;
mkdir /data/test

This is still - in part - possible in the new version, and trying to issue a CREATE DATABASE test; after executing the mkdir /data/test produces;

mysql> source /home/roel/percona-qa/plugins_80.sql
ERROR 1007 (HY000): Can't create database 'test'; database exists

However, if the database has been created by creating a directory, then;

mysql> CREATE TABLE t1 (id INT);
ERROR 1049 (42000): Unknown database 'test'

Which conflicts with the ERROR 1007 above.

How to repeat:
mkdir /data/test
CREATE DATABASE test;
CREATE TABLE t1 (id INT);

You can also do other fun things with this like;

mysql> drop database test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
mysql> create database test;
ERROR 1007 (HY000): Can't create database 'test'; database exists

Bit more fun

mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> system rmdir data/test
mysql> drop database test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
mysql> create database test;
ERROR 1062 (23000): Duplicate entry '1-test' for key 'catalog_id'

Thank you for the bug report.

FWIW, I much prefer the way it is done in 5.7.

On second thoughts, not having a test db would likely be more secure.

Posted by developer:
 
You cannot create a schema (database) by doing an mkdir inside the datadir in 8.0.

And AFAICT, the examples here show exactly that. Having said that though, I think it would be worth looking into:

- Why CREATE SCHEMA/DATABASE fails with ERROR 1007 (HY000): Can't create database 'test'; database exists,  when a directory has been created in datadir. This is IMHO wrong. A more appropriate error message would be something like 'Cannot create schema (database) because the required file system path <datadir>/<dbname> is in use'

- Why DROP SCHEMA/DATABASE fails with ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist,  when the schema/database directory has been removed. This is also wrong. A more appropriate error message would be something like 'Cannot drop schema/database because the required file system path <datadir>/<dbname> does not exist (or alternatively allow the drop to go through with a warning).

- The fact that we get error ERROR 1062 (23000): Duplicate entry '1-test' for key 'catalog_id'  when trying to create a schema which exists in the DD, but for which the directory has been removed. (It happens because the create is not intercepted before we try to insert a new record into the DD which violates a constraint).

In general, we should make all existence checks for schemas against the DD and only report errors/warnings as appropriate when the file system does not match what is in the DD.

Posted by developer:
 
Fixed in 8.0.4, 9.0.0.

Schema creation and removal operations could fail due to checking for
schema directories under the data directory rather than checking the
data dictionary.