MySQL Error Message : ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./db’, errno: 39)

This is an article written to describe on how to solve the error generated in MySQL Database Server. The error itself is an error specifically triggered upon dropping a database which in this context is executed through a command typed in MySQL Command Console. The error specifically mentioned in the title of this article “ERROR 1010 (HY000): Error drop database (can’t rmdir ‘./db’, errno:39)”. Where the ‘./db’ in the error message is referring to the name of the database which is going to be dropped.

The error is shown in detail through the execution of the following command :

root@hostname:/etc/mysql/conf.d# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2017, 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> drop database mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb', errno: 39)
mysql> 

The error above happened at the time when the space storage of the database server is exhausted. At that time, there was an execution of restoring a database named mydb with a specific file. But the process suddenly stopped as shown in the article titled ‘MySQL Error Message : ERROR 1114 (HY000) at line 4032: The table ‘table’ is full’ in this link.

Because of the failure on restoring the database which result on several table created but unfinished, the step taken normally involving on removing the already created table. But as shown in the above process on removing the already created table which is done by dropping the database actually ends as a failure.

So, how to resolve the problem so that the incomplete process of restoring the database which only consists of several unused tables can be cleaned and restart over the restore process ?. Do the following steps :

1. Find the folder represents the database. In MySQL Database Server, it is usually located in ‘/var/lib/mysql’. Go inside the folder represents the database and remove the contents as shown below :

root@hostname:/etc/mysql/conf.d# cd /var/lib/mysql/mydb/
root@hostname:/var/lib/mysql/mydb# mv -vf * /home/user/temporary/
'xx_table_one.ibd' -> '/home/user/temporary/xx_table_one.ibd'
'xx_table_two.ibd' -> '/home/user/temporary/xx_table_two.ibd'
root@hostname:/var/lib/mysql/mydb#

2. Access to MySQL Database Server’s command console and execute the query command for dropping the database as shown below :

root@hostname:/var/lib/mysql/mydb# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)
Copyright (c) 2000, 2017, 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> drop database mydb;
Query OK, 0 rows affected (0,00 sec)
mysql>

There is a chance that the file represented each tables from the database is corrupted and it didn’t represent the integrity of the tables since the database restore process is actually failed. So, remove the files and drop the database as shown above.