Sometimes, the only thing that we want is to drop a database(not in PRODUCTION) and recreate it from scratch, especially when we are in the process of automating the deployment of our MySQL databases, for instance using liquibase. Therefore, it is not uncommon in an initial testing process to wish to drop and recreate our tested database.
Recently, I found the following error message after to try execute a simple and rarely applied DROP DATABASE statement:
[crayon-6740859ccec3e701622904/]
The reasons behind this error could be many, in my case, it was a problem related to an existing connection that was using the target database, in which case the showprocess list is your best friend, once that you have killed the process which was pointing out to the DB, the next step consists in check the data directory, the easy way is inside MySQL execute the following command:
[crayon-6740859ccec46252109123/]
Once that you check into the datadir, you need to verify that exists a folder with the same name than the database that you want to drop, localice and inside of this folder check that not exists any file with the extensions:
.BAK.DAT.HSH.MRG.MYD.MYI.cfg.db.ibd.ndb
As the official MySQL documentation says: “If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed”. So, you only need to be sure of having enough permission for removing any file which has the extensions mentioned above and remove it manually, to do this you only need to and issue the DROP DATABASE statement again.
I hope this simple trick would be useful, especially in lower environment and during testing automation process.