Why Can\'t I Drop a MySQL Table That \'Already Exists\'?

MySQL Table Enigma: Existing Yet Nonexistent

Facing the paradoxical error of "table already exists" during table creation or alteration, yet encountering "unknown table" during drop attempts, users have been left puzzled and unable to resolve the issue.

Investigating the Anomaly

Examination of the database reveals an absence of the table in 'show tables,' and 'describe' reports that the table doesn't exist. Surprisingly, no .frm file can be found, and attempts to create the table using "create table if not exists" fail. To make matters worse, dropping the database results in MySQL crashes.

Possible Resolution

After analyzing various unsuccessful attempts, the underlying cause seems to reside in missing table files. In the data directory, an orphaned .frm file without the corresponding data file, or vice versa, can cause this issue. If using innodb_file_per_table, ensure both .frm and .ibd files exist for the affected table. For MYISAM, the expected files are .frm, .MYI, and .MYD.

Resolving the Enigma

To resolve the issue, manually delete the orphaned file from the data directory. This should allow successful creation or deletion of the affected table and resolve the database crashing problem.

The above is the detailed content of Why Can\'t I Drop a MySQL Table That \'Already Exists\'?. For more information, please follow other related articles on the PHP Chinese website!