How do I fix InnoDB corruption locking a table name from creation (errno: -1) on AWS RDS?

Question

TIP: Do not run ALTER statements in MySQL Workbench for a "Standard TCP/IP over SSH" connection. It is much better to shell into the server and run the ALTER from there. That way, if you lose connection to the server, the ALTER should still finish its job.

I'm trying to create a new table in my database that I tried creating yesterday. The problem is, my internet's been losing connection in micro-dropouts. I believe that one of these blips happened when I was creating the table, and now when I try to create a table with the exact same name:

CREATE TABLE IF NOT EXISTS `adstudio`.`data_feed_param` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

I get this error:

Error Code: 1005. Can't create table 'adstudio.data_feed_param' (errno: -1)

Now previously, I tried creating this table with many other columns, one of which was named input_type, and I had a column with a foreign key relation to input_type:

CREATE TABLE IF NOT EXISTS `adstudio`.`data_feed_param` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ...
  `input_type` TINYINT UNSIGNED NOT NULL DEFAULT '1',
  ...
  PRIMARY KEY (`id`),
  INDEX `fk_input_type_idx` (`input_type` ASC),
  CONSTRAINT `fk_input_type`
    FOREIGN KEY (`input_type`)
    REFERENCES `adstudio`.`input_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

I noticed that this column was bad when Propel failed to regenerate. I dropped the table, changed the column name, and now I cannot add a table with the exact same name with the InnoDB engine.

However, I am able to create a table with the exact same name in MyISAM, insert, and drop it without issue.

How can I fix the database so that I can create the table?

UPDATE 2015/06/01: So I have lost the same table again except under different circumstances. First, I added a prefix to the table name to avoid the aforementioned issue, adstudio.account_data_feed_param.

Secondly, I was making a change to this table instead of dropping it. I ran an ALTER to add a column, but received the message "The MySQL Server has gone away". I did all of this in MySQL Workbench.

Thirdly, I have a many-to-many table that references this one with data populated inside of it. How is this even possible? How is MySQL just arbitrarily dropping my tables?

If I try to access the foreign key definition, I receive the following message:

Error getting DDL for object.
Table 'adstudio.account_data_feed_param' doesn't exist

This is the creation SQL for the table now:

-- -----------------------------------------------------
-- Table `adstudio`.`account_data_feed_param`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `adstudio`.`account_data_feed_param` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `account_id` BIGINT(20) UNSIGNED NOT NULL,
  `name` VARCHAR(64) NOT NULL,
  `default_value` VARCHAR(64) NOT NULL,
  `input_type_id` SMALLINT(5) UNSIGNED NOT NULL,
  `lookups_json` MEDIUMTEXT NOT NULL,
  `enabled` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  `creation_user_id` BIGINT(20) UNSIGNED NOT NULL,
  `creation_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_modified_user_id` BIGINT(20) UNSIGNED NOT NULL,
  `last_modified_date` TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:01',
  `deletion_user_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `deletion_date` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `dfp_account_idx` (`account_id` ASC),
  INDEX `dfp_input_type_idx` (`input_type_id` ASC),
  INDEX `dfp_creation_user_idx` (`creation_user_id` ASC),
  INDEX `dfp_last_modified_user_idx` (`last_modified_user_id` ASC),
  INDEX `dfp_deletion_date_idx` (`deletion_user_id` ASC),
  CONSTRAINT `dfp_account`
    FOREIGN KEY (`account_id`)
    REFERENCES `adstudio`.`account` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `dfp_input_type`
    FOREIGN KEY (`input_type_id`)
    REFERENCES `adstudio`.`input_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `dfp_creation_user`
    FOREIGN KEY (`creation_user_id`)
    REFERENCES `adstudio`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `dfp_last_modified_user`
    FOREIGN KEY (`last_modified_user_id`)
    REFERENCES `adstudio`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `dfp_deletion_date`
    FOREIGN KEY (`deletion_user_id`)
    REFERENCES `adstudio`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8;

And this is the ALTER statement I tried running:

ALTER TABLE `adstudio`.`account_data_feed_param` 
    ADD COLUMN `input_type` VARCHAR(32) NOT NULL DEFAULT 'text' AFTER `input_type_id`;

And trying to create the same table in a pure MySQL command-line interface, I now receive this:

ERROR 1005 (HY000): Can't create table 'adstudio.account_data_feed_param' (errno: -1)

I have postulated that InnoDB

My database is running off of Amazon RDS, so I only have the access that they provide me. Here is the server version I'm running on:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.40-log |
+------------+
1 row in set (0.02 sec)

I really, really don't want to refactor all my code because MySQL won't allow me to recreate my table. That's just dumb. In the meantime, I'm putting this question up for bounty.

Answer 1

DDL in InnoDB is not transactional so it's possible that information in a .frm file and the InnoDB dictionary is different. In your case it looks like the .frm file is missing but there is an orphaned record in the dictionary (well, actually records in few dictionary SYS_* tables).

You can't easily delete a record from the dictionary. You need a respective .frm file so MySQL passes your DROP to InnoDB level. With RDS you can't do it.

But you can DROP whole database. In that case InnoDB will remove all records from the dictionary including the orphaned one.

So, to clean your dictionary I suggest following:

1. Stop all traffic to MySQL, make it read-only
2. Create a temporary database adstudio_tmp
3. RENAME all tables from adstudio to adstudio_tmp
4. DROP DATABASE adstudio. At this point it's empty. The DROP will wipe out all entries in the InnoDB dictionary.
5. RENAME all tables back from adstudio_tmp to adstudio

After this the dictionary should be clean and you'll be able to create your data_feed_param.

I described a similar problem after unsuccessful ALTER TABLE . Check it out for more details.

Answer 2

The doc says:

Error 1005 (ER_CANT_CREATE_TABLE)

If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.

Try a DROP TABLE on this table first, and try re-adding it