MySQL: cannot create table: errno -1

Question

I had a strange problem with MySQL, I cannot create a table. The table used for existence is part of the task, which is loaded into the staging table every day, and then truncates this table at the end. After several months, the table disappeared (maybe someone left it, it doesn’t matter), and I went to restore it, but I get this error:

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

I cannot abandon the table or rebuild it. But if I change the name of the table, I can build it.
Thus, the table seems to have fallen, but the namespace is still reserved or stuck in limbo on the rear panel.

Here's the ddl that doesn't work:

 create table raw_appsites_stage(t int); 

But this ddl really works:

 create table raw_appsites_stage_1(t int); 

I am using an RDS instance, so I do not have much access to the server itself. Here is the version information:

 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 held, and why will it not allow me to refuse or create a table with this name?

UPDATE I tried to rename this table and got a new error that I had never seen before:

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

So this is definitely something funky on the back-end, but this creature and RDS instance I don't have access to the machine OS. Does anyone know about this? Do I have to wait for AWS support?

thanks

Answer 1

Well, if you use InnoDB as an engine. Somehow you lost the raw_appsites_stage.frm and raw_appsites_stage.ibd . The data dictionary still contains an entry for this table.

Now you cannot drop the table as mysqld will first search for the .frm file.

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

or, backup ibdata1 to mysqldump , then delete ibdata1 and restore. Read more here