unable to alter table, Table 'xxx/#sql-ib265' already exists

Question

I have a mysql table y in database xxx which I attempted to change compression type before using

    alter table y row_format=compressed key_block_size=8

the process stopped half way. I removed temp file '#sql-ib265.frm and #sql-ib265' in mysql lib directory and restarted the server. However Now when I attempt the alter table y (with the same command above) again I get error.

    ERROR 1050 (42S01) at line 1: Table 'xxx/#sql-ib265' already exists

I can't drop table 'xxx/#sql-ib265' because it can't be found. what should I do?

Edit Solution:

I ended up dropping the old database and recreate the database.

Answer 1

Try to restart mysql client with the --skip-auto-rehash option and try DROP TABLE again.

If above does not work, try this from MySQL Manual:

You have a corrupt innodb data dictionary..

https://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html

Problem with Temporary Table

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-. You can perform SQL statements on tables whose name contains the character “#” if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. To copy or rename a file in the Unix shell, you need to put the file name in double quotation marks if the file name contains “#”.