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: | |
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
[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.