In-depth Analysis of MySQL Database Drop Failures: Understanding and Resolving Errno 13, 17, and 39

Introduction

In MySQL database management, executing the DROP DATABASE command may encounter various error codes, such as Errno 13, 17, or 39. These errors typically indicate system-level obstacles during the removal of the database directory, rather than simple SQL syntax issues. Based on common technical Q&A data, this paper delves into the causes of these errors and provides a complete guide from quick fixes to fundamental solutions. By understanding the mechanisms behind these errors, database administrators can handle similar problems more effectively, avoiding data loss or security risks.

Overview of Error Codes

When MySQL attempts to drop a database, it tries to remove the corresponding data directory. If this process fails, the system returns error codes, with Errno 13, 17, and 39 being the most common. Errno 13 is often related to permission issues, Errno 17 indicates file existence conflicts, and Errno 39 signals a non-empty directory. Each error corresponds to different underlying causes, requiring targeted troubleshooting methods.

Quick Fix Methods

In some urgent situations, a quick database drop may be necessary, but caution is advised. First, locate the data directory using: SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';. Then, stop the MySQL service (e.g., service mysql stop on Linux, NET STOP MYSQL57 on Windows). Next, manually delete the corresponding database directory, restart the MySQL service, and re-execute the DROP DATABASE command. However, note that this method may bypass the error cause; it is recommended to read subsequent sections to understand potential risks before proceeding.

Errno 13: Permission Issues Analysis

Errno 13 errors usually stem from the MySQL process lacking write permissions on the parent directory. On Linux systems, use ls -la /path/to/data/dir/ and ls -la /path/to/data/dir/mydb to check directory permissions and ownership. Ensure the MySQL user (e.g., mysql) has appropriate access rights. Additionally, security frameworks like AppArmor or SELinux can cause permission conflicts, even if file permissions are correct. For example, if the MySQL data directory does not match AppArmor configurations, access may be blocked. Check system logs (e.g., /var/log/syslog) for security violation records, or attempt to execute touch testfile && rm testfile as the MySQL user to test access capability.

A common mistake is using chmod 777 to "fix" permission issues. This actually opens full directory access, leading to severe security risks such as data breaches or malware injection. Under normal circumstances, avoid such operations and instead resolve the root cause by adjusting ownership or security policies.

Errno 39: Directory Not Empty Error

Errno 39 indicates that the directory contains hidden files or other files unknown to MySQL, preventing deletion. For instance, in the example problem, the directory db/mydb contains files HIS_STAT.MYD and HIS_STAT.MYI, which may be residual table files. Solutions include manually deleting these files or moving them out of the directory, then retrying the DROP DATABASE command. Use the ls -a command to view hidden files and ensure the directory is completely empty.

Errno 17: File Existence Conflicts

Errno 17 errors are often caused by MySQL files present in the directory, which may have been created via commands like SELECT ... INTO OUTFILE 'filename';. For example, in MySQL 5.6, if filename does not specify a path, the file is created in the current database's data directory, leading to drop failures. The following code demonstrates this issue:

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE testdb;
Database changed
mysql> SELECT version() INTO OUTFILE 'tempfile';
Query OK, 1 row affected (0.00 sec)

mysql> DROP DATABASE testdb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './testdb/', errno: 17)

After deleting tempfile, the DROP DATABASE command can execute successfully. Moreover, Errno 17 may indicate security threats, such as malware injected via vulnerabilities into the data directory as PHP files. If unusual files (e.g., with .php extensions) are found, use the file command to inspect or perform virus scans, rather than deleting them directly, to avoid overlooking potential attacks.

Another common cause is file duplication from cross-platform migrations. For instance, when copying MySQL data between Linux and Windows, differences in filesystem case sensitivity can produce duplicate files (e.g., table.MYI and table.myi). MySQL might only recognize one, causing drop failures. In such cases, manually deleting outdated or case-mismatched files can resolve the issue.

Supplementary Case: Impact of lower_case_table_names Parameter

Based on supplementary answers, the lower_case_table_names parameter setting can cause Errno 39 errors. When this parameter is enabled, if the database contains uppercase table names, drop operations may fail. Solutions include adjusting the parameter setting or renaming tables to match case rules. This highlights the importance of considering platform compatibility when configuring MySQL.

Data Directory Location Methods

Accurately locating the data directory is key to resolving drop errors. This can be done by: checking MySQL configuration files (e.g., /etc/my.cnf on Linux or my.ini on Windows) for the datadir setting under the [mysqld] section. Alternatively, execute in the MySQL client: SHOW VARIABLES WHERE Variable_name LIKE '%datadir%';. For example, output might show /var/lib/mysql/ as the data directory path.

Prevention and Best Practices

To avoid errors when dropping databases, consider the following measures: regularly monitor the data directory for anomalous files; specify full paths when using SELECT INTO OUTFILE; standardize file naming conventions before cross-platform migrations; configure appropriate security policies (e.g., AppArmor or SELinux) to match MySQL data locations. Additionally, backing up critical data and verifying directory status before executing drop operations can reduce accidental risks.

Conclusion

MySQL database drop failure errors (Errno 13, 17, 39) often reflect system-level permission, file, or directory issues. By deeply analyzing error codes, combining specific cases and solutions, database administrators can effectively diagnose and fix these problems. Key steps include checking permissions, cleaning up residual files, troubleshooting security framework conflicts, and avoiding dangerous commands like chmod 777. Adhering to best practices, such as correctly locating the data directory and preventing file conflicts, can enhance the efficiency and security of database management. In complex scenarios, referring to official documentation and community resources aids in further optimizing handling processes.