5

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

alexP_Keaton
  • 349
  • 5
  • 18
  • 1
    Use `create table if not exists raw_appsites_stage(t int);` – juergen d Nov 17 '14 at 19:45
  • @juergend : I get the exact same error running that command. The issue is not that I'm trying to create that already exists. The issue is that table-name is in limbo, MySQL says no table exists with that name, but also won't let me create a new table with that name. – alexP_Keaton Nov 17 '14 at 19:58
  • what happens if you select * from raw_appsites_stage? You should try drop table if exists raw_appsites_stage – Marshall Tigerus Nov 17 '14 at 20:04
  • @MarshallTigerus: Select * FROM ... = "Table 'apps.raw_appsites_stage' doesn't exist", "Drop table if exists" is fine because MySQL thinks it doesn't exist ... until I try to create it. – alexP_Keaton Nov 17 '14 at 20:09

1 Answers1

3

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

Community
  • 1
  • 1
Riad
  • 3,822
  • 5
  • 28
  • 39
  • 3
    thanks, good info. Unfortunately(?) I am using Innodb so I can't use "repair table", and "check table raw_appsites_stage;" returned "ERROR: Table 'apps.raw_appsites_stage' doesn't exist", and since this is RDS I can't access the filesystem as described in that very detailed link you posted. – alexP_Keaton Nov 18 '14 at 00:09