MySQL: Can't create table: errno -1

Question

I'm having a weird issue with MySQL, I can't create a table. The table used to exist, it's part of a job that loads to a staging table every day, then truncates that table at the end. After months, the table disappeared (maybe someone dropped it, doesn't matter) and I went to rebuild it but I get this error:

Level   Code    Message
Error   1005    Can't create table 'apps.raw_appsites_stage' (errno: -1)

I can't drop the table or rebuild it. But if change the table name at all I can build it.
So it seems the table got dropped but the namespace is still reserved or stuck in limbo on the back-end.

Here's the ddl that doesn't work:

create table raw_appsites_stage(t int);

But this ddl does work:

create table raw_appsites_stage_1(t int);

I'm using an RDS instance so I don't have much access to the server itself. Here's the version info:

Variable_name   Value
innodb_version  5.5.40
protocol_version    10
slave_type_conversions  
version 5.5.40-log
version_comment Source distribution
version_compile_machine x86_64
version_compile_os  Linux

How can I see where this name is being held onto and why it won't let me drop or create a table with this name?

UPDATE I tried a rename on that table and got a new error I've never seen before:

Can't find file: './apps/raw_appsites_stage.frm' (errno: 2)

So it's definitely something funky on the back-end, but this being and RDS instance I don't have access to the machine OS. Anyone know of a fix for this? DO I need to wait on AWS support?

Thanks

Answer 1

Ok, if you are using InnoDB as engine. Somehow, you lost the raw_appsites_stage.frm and raw_appsites_stage.ibd files. The data dictionary still has an entry for that table.

Now you can't drop table as mysqld will look for .frm file first.

you can either try check and repair table raw_appsites_stage (if not InnoDB)

or, backup ibdata1 by mysqldump then delete ibdata1 and restore. Details Here