David542 David542 - 3 months ago 14
MySQL Question

How to drop orphaned tables in RDS

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

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: