Error Dropping Database (Can't rmdir '.test\', errno: 17)

Question

Basically, I was taught on how to create a root password using the "mysqladmin -u root -p password" command, this was done all through the windows command editor. Now, the next process was to display the default databases (info. schema, mysql and test) which was achieved by using "SHOW DATABASES;"

But according to the book, we had to delete the redundant test file and the following error pops up:

Error Dropping Database (Can't rmdir '.test\', errno: 17)

The command put to use was DROP DATABASE test;

I am using MYSQL and PHPMYADMIN. Any help on how to drop the file with no errors?

Abshire

Does the MySQL user definitely have ownership of the relevant data directory? – Brindle

Update. I created a new database from PHPMYADMIN and used the command in the command editor, it worked without popping the error this time. Edit: Middaparka, I really haven't touched that part yet. I don't know, by default, what are the permission given. – Abshire

I guess the new MySQL versions really does not delete the database TEST instead try deleting the folder "TEST" in your mysql directory/bin, its just another solution for this to add on to your learning process..... – Emu

possible duplicate of how to drop database – Rothermere

Answer 1

A database is represented by a directory under the data directory (usually /var/lib/mysql), and the directory is intended for storage of table data.

The DROP DATABASE statement will remove all table files and then remove the directory that represented the database. It will not, however, remove non-table files, whereby making it not possible to remove the directory.

MySQL displays an error message when it cannot remove the directory

you can really drop the database manually by removing any remaining files in the database directory and then the directory itself.

Deborahdeborath

Thanks, Shakti Singh. I dropped the table of 'test' by visiting PHPMYADMIN, but I really wanted to do it through the command editor. I guess I tried deleting the same file again and again, so the error. I created a new DB and entered the DROP DATABASE command, it worked! – Abshire

Note that you can really only remove the directory yourself if the database only have MyISAM tables – Garges

In my case I had to logout and then login again, to see the database gone. – Alexandrine

Where exactly are the files`? – Carola

I have created the files in the database directory. My issue resolved after removing these files. – Scrubby

I ran into this, and in my case, I had previously exported query results into a csv OUTFILE. Unbeknownst to me, MySQL had retained this file in the same directory as its table data. Removing this allowed me to drop the database completely. – Agnosia

Answer 2

I ran into this same issue on a new install of mysql 5.5 on a mac. I tried to drop the test schema and got an errno 17 message. errno 17 is the error returned by some posix os functions indicating that a file exists where it should not. In the data directory, I found a strange file ".empty":

sh-3.2# ls -la data/test
total 0
drwxr-xr-x   3 _mysql  wheel  102 Apr 15 12:36 .
drwxr-xr-x  11 _mysql  wheel  374 Apr 15 12:28 ..
-rw-r--r--   1 _mysql  wheel    0 Mar 31 10:19 .empty

Once I rm'd the .empty file, the drop database command succeeded.

I don't know where the .empty file came from; as noted, this was a new mysql install. Perhaps something went wrong in the install process.

Exclamation

See MySQL Bug #62443. This is a MySQL bug introduced around 5.5.11 because of a limitation in cmake. As you said, a workaround is to remove data\test\.empty – Leash

in windows for me it was: C:\wamp\bin\mysql\mysql5.5.24\data\test\ – Admixture

Answer 3

Go to the datadir* for your mysql installation and rm the databases manually. It can be

/usr/local/var/mysql

Then,

rm -R <Your DB name>

Notes:

* To check datadir for your installation,

vim the mysql.server file and find it there.

Eanes

I'm running linux mint with mysql 5.5.34-0ubuntu0.13.04.1 and they store it under /var/lib/mysql. I'm sure it's different for different distros. – Molina

Thank you for letting me know where the directory was!! – Sphere

Works for me :) – Helaine

worked for me try rm -R databasename then reload the phpmyadmin – Deaconess

on ubuntu 22.04 no /usr/local/var/mysql dir – Entirety

Answer 4

For phpmyadmin, go to xampp\mysql\data and simply delete the database folder. Worked for me !!

Busybody

Answer 5

If it is XAMPP, Do the following:

cd /opt/lampp/var/mysql;
sudo su;
rm -rf test;

Hope this helps.

Footless

+1 This works well on mac, if you are using a mounted XAMPP on it. open the terminal through the application (mounted xampp app) and follow the guide above. – Trihedron

This Helped Me Again :) – Trihedron

Answer 6

I just ran into this problem with WAMP and the phpMyAdmin that comes with it. To remove the database and make the error go away. I went into C:\wamp\bin\mysql\mysql5.5.24\data\ and deleted the folder for the database in question.

Then I refreshed the page at phpMyAdmin, and the database was gone.

Espinoza

Answer 7

Just to add to the responses so far: In Windows 10, the data files are kept here...

C:\ProgramData\MySQL\MySQL Server [m.n]\data

There will be directories for each schema in your database. You will want to go inside the schema you are trying to drop, manually delete any superfluous files then try the drop command again.

Any workbench changes against a database will get stored in this location. For example, I had this error following a reverse engineering exercise on one of my databases and saving the changes which get stored in a .mwb file in this location.

Reject

Answer 8

mysql -s -N -username -p information_schema -e 'SELECT Variable_Value FROM GLOBAL_VARIABLES WHERE Variable_Name = "datadir"'

The command will select the value only from MySQL's internal information_schema database and disables the tabular output and column headers.

Output on Linux [mine result]:

/var/lib/mysql

or

mysql> select @@datadir;

on MYSQL CLI

and then

cd /var/lib/mysql && rm -rf test/NOTEMPTY

change path based on your result

Sideward

Answer 9

I my case the problem was a residual dump file in the database directory. That file was probably generated during a backup script test I had been working on. Once I manually deleted the file I could then drop the database.

Tew

Answer 10

You can delete the data in the sql directory.

For me i was using AMPPS on windows 10. I went to the AMPPs installtion directory. for me it was :

D:\Program Files (x86)\Ampps\mysql\data

because i have it installed on my secondary drive.

Then refresh your SQL client and you will see it's gone.

Reo

Answer 11

You may need to check two things.

1- Database Foleder's permission The database you wants to delete must have the same owner as mysql process has.

2- Directory Must be empty Goto the mysql data directory and verify that directory is empty

After that connect your mysql cli and run drop database command again.

Gegenschein

Answer 12

Go through this and remove corresponding cache files in selected db then after you can drop your database

First find Your MySQL Data Directory Containing Your selected DB

Linux

• Open up MySQL's configuration file: less /etc/my.cnf

• Search for the term "datadir": /datadir

• If it exists, it will highlight a line that reads: datadir = [path]

• You can also manually look for that line. It typically would be found under a section heading of [mysqld] but it does not necessarily have to be found there.

• If that line does not exist, then MySQL will default to: /var/lib/mysql.

Windows 1. Open up MySQL's configuration file into Notepad: my.ini

The my.ini will be located in the MySQL program folder, which would be wherever it got installed. If you did not install MySQL, then use the Windows "search" feature to look for my.ini. You could also manually search for it by browsing to [drive]:\Program Files\MySQL\MySQL Server 5.5.

2. Do a search in Notepad to find the term "datadir".

3. If it exists, it will highlight a line that reads: datadir = [path]

4. You can also manually look for that line. It typically would be found under a section heading of [mysqld] but it does not necessarily have to be found there.

5. If that line does not exist, then you'll probably find it under [drive]:\ProgramData\MySQL\MySQL Server 5.5\data.

NOTE: The "ProgramData" folder may be hidden. You may have to type the explicit path into Windows Explore

Threepence

Answer 13

In my case I didn't see any tables under my database on phpMyAdmin I am using Wamp server but when I checked the directory under C:\wamp\bin\mysql\mysql5.6.12\data I found this employed.ibd when I deleted this file manually I was able to drop the database from phpMyAdmin smoothly without any problems.

Lasky

Answer 14

I had the same issue (mysql 5.6 on mac) with 'can't rmdir..'-errors when dropping databases. Leaving an empty database directory not possible to get rid of. Thanks @Framework and @Beel for the solutions.

I first deleted the db directory from the Terminal in (/Applications/XAMPP/xamppfiles/var/mysql).

For further db drops, I also deleted the empty test file. In my case the file was called NOTEMPTY but still containing 0:

sudo ls -al test
total 0
drwxrwx---   3 _mysql  _mysql  102 Mar 26 16:50 .
drwxrwxr-x  18 _mysql  _mysql  612 Apr  7 13:34 ..
-rw-rw----   1 _mysql  _mysql    0 Jun 26  2013 NOTEMPTY

Chmod first and then

sudo rm -rf test/NOTEMPTY

No problems dropping databases after that

Dihydric

Answer 15

just go to data directory in my case path is "wamp\bin\mysql\mysql5.6.17\data" here you will see all databases folder just delete this your database folder the db will automatically drooped :)

Simplehearted

Answer 16

I ran into this problem, and when I checked out the database directory, there were a number of exp files (the ibd and frm files had been removed). Listing the files to look at their attributes (since the owner already had rw privileges on the files)

lsattr *.exp
-------------e-- foo.exp
-------------e-- bar.exp

Man page says

       The  'e'  attribute  indicates that the file is using extents for mapping the blocks on disk.
       It may not be removed using chattr(1).

You actually can chattr -e these files, but mysql still won't let you drop the database. Removing the files with rm, however, allows the database to be dropped cleanly.

Idocrase

Answer 17

It happens because you may have copied /var/lib/mysql folder's database folder from another server to your server. But you haven't copied these files: /var/lib/mysql/ib_buffer_pool /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile1 /var/lib/mysql/ibtmp1 So you can create a new db and tables you are able to drop them but you can't drop the databases that you have copied from another server and you are also not able to explore the same database that you copied from another server. So I also copied that files : ib_buffer_pool,ibdata1,ib_logfile0,ib_logfile1,ibtmp1 after that everything worked.

Ecdysis

Answer 18

Anybody coming from Manjaro/Arch, installing XAMPP with PHP8 and then downgrading to 7.4 will get this error. To fix, simply go to /opt/lampp/var/mysql/ with Thunar as root user, and delete the database file with the same database name in question and database should be gone. You can get the database name with PhpMyAdmin.

Rayborn

Answer 19

I faced the same problem, I was unable to delete a database, it was giving the same error:

I am using xampp for my work, so I went to the folder that I installed xampp, inside xampp folder find mysql folder, inside mysql folder find data folder and finally inside data find the database that you are looking for and open your database folder and delete everything.

And at the end go to your phpmyadmin and delete specific database.

Suellen