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.
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.
Verify that your current user has the DROP privilege on the database:
SHOW GRANTS FOR CURRENT_USER;Look for a line like:
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';or at minimum:
GRANT DROP ON mydb.* TO 'user'@'localhost';If you need to grant DROP privileges, run (as a user with GRANT privileges):
GRANT DROP ON mydb.* TO 'user'@'localhost';
FLUSH PRIVILEGES;Replace mydb with your database name and adjust the user and host as needed.
Open connections may hold locks preventing deletion. Check all active processes:
SHOW PROCESSLIST;Look for entries showing your target database in the "db" column. If you find active connections, kill them:
KILL [connection_id];For example, if processlist shows connection ID 42 using your database:
KILL 42;If a connection refuses to die, use KILL CONNECTION with the CONNECTION keyword:
KILL CONNECTION [connection_id];After killing connections, try DROP DATABASE again.
Stop the MySQL server and fix ownership of the database directory:
sudo systemctl stop mysql
# or
sudo service mysql stopThen ensure the mysql user owns the directory:
sudo chown -R mysql:mysql /var/lib/mysql/[database_name]Verify the permissions:
ls -la /var/lib/mysql/ | grep [database_name]You should see output like:
drwx------ 2 mysql mysql 4096 Dec 22 10:30 mydbStart MySQL again:
sudo systemctl start mysql
# or
sudo service mysql startThen attempt DROP DATABASE again.
Stop MySQL and check for non-table files in the database directory:
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:
sudo find /var/lib/mysql/[database_name]/ -type f -exec ls -lh {} \;Remove any non-table files:
sudo rm /var/lib/mysql/[database_name]/suspicious_file.outStart MySQL and retry:
sudo systemctl start mysql
mysql -u root -p
DROP DATABASE [database_name];If the above steps don't work, you can manually remove the directory (after backing up):
# 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.
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:
SHOW ENGINE INNODB STATUS\GLook 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
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions