Bug #102442 DROP DATABASE succeeds while the database directory on disk remains
Submitted: 1 Feb 19:00 Modified: 2 Feb 22:11
Reporter: Peiran Song Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:8.0.18 OS:Linux
Assigned to: CPU Architecture:Any

[1 Feb 19:00] Peiran Song
Description:
When the database directory's write permission is removed, the DROP DATABASE command would still succeed. The data dictionary is cleared, however the database directory on disk remains as is. 

In 5.6 and 5.7, the DROP DATABASE command would return an error on permission issue. 

How to repeat:
mysql> create database if not exists test;

shell: $sudo chmod -w <datadir>/test

mysql> drop database test;

shell: $ls -la <datadir>

Suggested fix:
DROP DATABASE should fail with permission error
[2 Feb 10:09] MySQL Verification Team
Hello Peiran,

Thank you for the report and feedback.
I quickly tried to reproduce using latest GA 8.0.23 build but not seeing any issues i.e database directory on disk removed after drop database statement:

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> create database if not exists test;
Query OK, 1 row affected (0.02 sec)

-- file system
ls -l 102442/test
total 0

-- shell terminal

chmod -w 102442/test/
ls -l 102442/test
total 0

-- mysql cli - drop database

mysql> drop database test;
Query OK, 0 rows affected (0.02 sec)

mysql>

-- shell terminal

ls -l 102442/test
ls: cannot access 102442/test: No such file or directory

Could you please confirm with 8.0.23 and report us back if you are still seeing the issue with server build details, OS? Thank you!

regards,
Umesh
[2 Feb 22:11] Peiran Song
Hi Umesh, 

Please try create a table in the database and try again. I missed on mentioning that. 

mysql> create database user;
mysql> use user;
mysql> create table users(a int) engine=innodb;
mysql> drop database user;
Query OK, 1 row affected, 1 warning (0.01 sec) 
mysql> show warnings;
Empty set (0.00 sec)

Btw, 'show warnings' didn't work either.