Unable to delete a MySQL InnoDB table

Question

I am using MySQL 5.1.56

I have a DB with about 70 tables and I have an issue with a particular corrupted table e.g. Table_X

When I try to access the table mysql> select * from Table_x; ERROR 1105 (HY000): Failed to read from the .par file

I am unable to add partitions to the table.

When I try to drop the table I see the below errors. mysql> drop table Table_X; ERROR 1051 (42S02): Unknown table 'Table_X'

The create query gives the error: ERROR 1050 (42S01): Table 'Table_X' already exists.

In my DB files locations, I can see the corresponding Table_X.frm, Table_X.ibd and Table_X.par files. But in addition, I also see a file '#sql-Table_X.frm' in the location.

When I check the 'Tables' table in the information_Schema DB, the Engine value is NULL for this particular table, where as it should have been InnoDb. The table seems to be corrupted somehow.

I tried the Flush-tables command,but that did not help as well. I am still unable to drop and recreate the table.

I do not wish to take a backup of this particulate table, but I need to preserve other tables of the database. Is there any way, I can just recreate this individual table without having to restore the entire Database.

Answer 1

You can try to repair the table with the following statement :

REPAIR TABLE Table_X;

Take care, such query can induce a loss of data, but since you are trying to DROP the table, I guess this is not a matter for you.

You could also try to use the FRM file as a source for your table with

REPAIR TABLE Table_X USE_FRM;

PS : Can you give us the result of the following query ?

CHECK TABLE Table_X;

References :

https://www.tutorialspoint.com/mysql/mysql_repair_table_statement.htm https://www.tutorialspoint.com/mysql/mysql_check_table_statement.htm