How to fix ERROR 1009: Error dropping database (can't delete) in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 154 views

MySQL Error 1009 occurs when the DROP DATABASE command fails because the server cannot delete the database directory from the file system, usually due to permission issues, active connections, or leftover files.

Quick fix

Fixes 1009

SHOW GRANTS FOR CURRENT_USER;
  1. Check your DROP DATABASE privileges
  2. Check for active connections to the database
  3. Fix directory permissions
  4. Remove leftover files from the database directory
mysql — error
SHOW GRANTS FOR CURRENT_USER;
ERROR 1009: Error dropping database (can't delete)

What this error means

MySQL Error 1009 (ER_DB_DROP_DELETE) signals a file system-level failure when attempting to drop a database. While MySQL successfully removes all tables associated with the database, it cannot delete the underlying directory. This happens because the operating system refuses to remove a non-empty directory or because the MySQL process lacks sufficient permissions to perform the deletion. The error includes an errno value that provides more details. For example, errno: 39 means "Directory not empty," indicating files remain in the directory that MySQL cannot or doesn't know how to remove. Other common errno values include 13 (Permission denied) and 2 (No such file or directory). This is fundamentally a file system access issue, not a database corruption problem. The tables are successfully dropped from the database metadata, but the directory removal fails at the operating system level.

Typical symptoms

  • DROP DATABASE command fails with "ERROR 1009: Error dropping database (can't delete)"
  • Database tables are successfully dropped but the database entry persists
  • Database directory remains on disk after failed DROP DATABASE
  • Error message includes errno: 39 (directory not empty), errno: 13 (permission denied), or similar
  • Issue occurs consistently when attempting to drop specific databases
  • Other database operations work normally but dropping fails

Common causes

  1. File permission issues: The MySQL process (typically running as the "mysql" user) lacks write or delete permissions on the database directory
  2. Hidden or leftover files in the directory: Non-table files remain (e.g., .out files created by SELECT INTO OUTFILE, temporary backup files, or OS-specific hidden files)
  3. Active connections to the database: Open sessions or long-running queries hold locks on .ibd or .frm files
  4. Backup tools or other processes: External tools may have created locks or open file handles in the database directory
  5. Incorrect directory ownership: After manual file operations or deployment scripts, the directory owner is not the MySQL user
  6. File system restrictions: SELinux, AppArmor, antivirus software, or Windows NTFS permissions prevent removal
  7. Disk issues or file system corruption: Underlying storage problems prevent directory deletion

How to fix "ERROR 1009: Error dropping database (can't delete)"

1Check your DROP DATABASE privileges

Verify that your current user has the DROP privilege on the database:

sql
SHOW GRANTS FOR CURRENT_USER;

Look for a line like:

bash
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';

or at minimum:

bash
GRANT DROP ON mydb.* TO 'user'@'localhost';

If you need to grant DROP privileges, run (as a user with GRANT privileges):

sql
GRANT DROP ON mydb.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Replace mydb with your database name and adjust the user and host as needed.

2Check for active connections to the database

Open connections may hold locks preventing deletion. Check all active processes:

sql
SHOW PROCESSLIST;

Look for entries showing your target database in the "db" column. If you find active connections, kill them:

sql
KILL [connection_id];

For example, if processlist shows connection ID 42 using your database:

sql
KILL 42;

If a connection refuses to die, use KILL CONNECTION with the CONNECTION keyword:

sql
KILL CONNECTION [connection_id];

After killing connections, try DROP DATABASE again.

3Fix directory permissions

Stop the MySQL server and fix ownership of the database directory:

bash
sudo systemctl stop mysql
# or
sudo service mysql stop

Then ensure the mysql user owns the directory:

bash
sudo chown -R mysql:mysql /var/lib/mysql/[database_name]

Verify the permissions:

bash
ls -la /var/lib/mysql/ | grep [database_name]

You should see output like:

bash
drwx------  2 mysql mysql 4096 Dec 22 10:30 mydb

Start MySQL again:

bash
sudo systemctl start mysql
# or
sudo service mysql start

Then attempt DROP DATABASE again.

4Remove leftover files from the database directory

Stop MySQL and check for non-table files in the database directory:

bash
sudo systemctl stop mysql
sudo ls -la /var/lib/mysql/[database_name]/

Look for unexpected files like:

- .out files (from SELECT INTO OUTFILE commands)
- Temporary files (.* hidden files)
- Backup files
- Lock files

List all files to identify suspicious ones:

bash
sudo find /var/lib/mysql/[database_name]/ -type f -exec ls -lh {} \;

Remove any non-table files:

bash
sudo rm /var/lib/mysql/[database_name]/suspicious_file.out

Start MySQL and retry:

bash
sudo systemctl start mysql
mysql -u root -p
DROP DATABASE [database_name];
5Manually remove the database directory as a last resort

If the above steps don't work, you can manually remove the directory (after backing up):

bash
# Create a backup first
sudo cp -r /var/lib/mysql/[database_name] /backup/[database_name]_backup

# Stop MySQL
sudo systemctl stop mysql

# Remove the directory
sudo rm -rf /var/lib/mysql/[database_name]/

# Start MySQL
sudo systemctl start mysql

# Clean up metadata
mysql -u root -p
DROP DATABASE IF EXISTS [database_name];

The IF EXISTS clause prevents an error if the database doesn't exist in the metadata. This will complete the removal even if the directory deletion failed earlier.

Advanced notes

Understanding errno values:
- errno: 39 (ENOTEMPTY) means the directory is not empty; hidden files or locks are preventing deletion
- errno: 13 (EACCES) means permission denied; the MySQL process cannot write to or delete from the directory
- errno: 2 (ENOENT) means file not found; the directory was already deleted but metadata remains

Windows-specific troubleshooting:
On Windows, Error 1009 usually indicates that MySQL cannot remove the directory because files are still open. Check:
1. Grant "Modify" NTFS permissions to the account listed in Services → MySQL "Log On As"
2. Close any text editors or tools that have files open in the database directory
3. Disable temporarily antivirus scanning of the MySQL data directory
4. Ensure no third-party backup tools have handles on the files

Linux security module issues:
If running SELinux or AppArmor, MySQL may be denied access to delete directory entries even with correct file ownership:
- SELinux: Verify the security context with ls -Z /var/lib/mysql/[database_name]/ and adjust if needed
- AppArmor: Check /etc/apparmor.d/usr.sbin.mysqld allows write access to data directory

InnoDB-specific considerations:
For InnoDB tables, check the status to ensure no long-running transactions are holding locks:

sql
SHOW ENGINE INNODB STATUS\G

Look for "ACTIVE TRANSACTIONS" section. Kill any transactions preventing the drop if safe to do so.

Prevention:
- Always close client connections before dropping databases
- Avoid using SELECT INTO OUTFILE; use mysqldump for exports instead
- Maintain consistent file permissions with regular audits
- Run MySQL with a dedicated user account with appropriate permissions
- Use transactions and connection pooling in applications to avoid orphaned connections

Frequently asked questions

What does "1009" mean?
MySQL Error 1009 (ER_DB_DROP_DELETE) signals a file system-level failure when attempting to drop a database. While MySQL successfully removes all tables associated with the database, it cannot delete the underlying directory. This happens because the operating system refuses to remove a non-empty directory or because the MySQL process lacks sufficient permissions to perform the deletion.
How do I fix "1009"?
MySQL Error 1009 occurs when the DROP DATABASE command fails because the server cannot delete the database directory from the file system, usually due to permission issues, active connections, or leftover files.
What causes "1009"?
File permission issues: The MySQL process (typically running as the "mysql" user) lacks write or delete permissions on the database directory Hidden or leftover files in the directory: Non-table files remain (e.g., .out files created by SELECT INTO OUTFILE, temporary backup files, or OS-specific hidden files) Active connections to the database: Open sessions or long-running queries hold locks on .ibd or .frm files
Is the "1009" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading