How to drop orphaned tables in RDS

Question

I have the following two tables that were created due to a sql restart while doing an alter table statement:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

TABLE_ID    NAME    FLAG    N_COLS  SPACE   FILE_FORMAT ROW_FORMAT  ZIP_PAGE_SIZE
1674    avails/#sql-ib1647  1   10  1396    Antelope    Compact 0
1673    avails/#sql-ib1672  1   13  1395    Antelope    Compact 0

How do I drop these two tables? When I do:

DROP TABLE `#sql-ib1647`

I get an error that says Unknown table 'avails.#sql-ib1647'. This is a table in Amazon RDS.

Here is a post on the issue: https://forums.aws.amazon.com/thread.jspa?messageID=570645.

Answer 1

MySQL DOCS:

Prefix the table with #mysql50#this should solve the problem.

DROP TABLE `#sql-ib1647`;
ERROR 1051 (42S02): Unknown table '#sql-ib1647'

Instead prefix the filename with #mysql50#, tis should work:

DROP TABLE `#mysql50##sql-ib1647`;
Query OK, 0 rows affected (0.00 sec)

That's because MySQL and MariaDB encode special characters for the filesystem. The trick here is to prefix the tablename with #mysql50# to prevent the server from escaping the hash mark and hyphen: