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

[11 May 2017 12:41] Roel Van de Paar
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);
[11 May 2017 12:41] Roel Van de Paar
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
[11 May 2017 12:46] Roel Van de Paar
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'
[11 May 2017 14:20] MySQL Verification Team
Thank you for the bug report.
[12 May 2017 0:48] Roel Van de Paar
FWIW, I much prefer the way it is done in 5.7.
[12 May 2017 0:49] Roel Van de Paar
On second thoughts, not having a test db would likely be more secure.
[1 Jun 2017 16:29] Dyre Tjeldvoll
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.
[13 Dec 2017 3:06] Paul DuBois
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.