How to fix InnoDB corruption by blocking table name from creation (errno: -1) on AWS RDS?

Question

TIP . Do not run ALTER statements in MySQL Workbench to connect "Standard TCP / IP over SSH". It is much better to copy to the server and run ALTER from there. Thus, if you lose the connection to the server, ALTER should shut down.

I am trying to create a new table in my database that I was trying to create yesterday. The problem is that my internet has lost touch with micro-dropouts. I believe that one of these triggers occurred when I created the table, and now when I try to create a table with the 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 I tried to create this table with many other columns, one of which was named input_type , and I had a column with an external 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 could not recover. I reset the table, changed the column name, and now I can not add the table with the same name with the InnoDB engine.

However , I can create a table with the same name in MyISAM, insert and delete it without problems.

How can I fix the database to create a table?

UPDATE 2015/06/01: Thus, I lost the same table again , but under different circumstances. First, I added a prefix to the table name to avoid the above problem, adstudio.account_data_feed_param .

Secondly, I made changes to this table, instead of dropping it. I ran ALTER to add a column, but received the message "MySQL server is gone." I did all this in MySQL Workbench.

Thirdly, I have a many-to-many table that references this data in it . How is this even possible? How does MySQL just randomly delete my tables?

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

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

Now this is creating SQL for the table:

 -- ----------------------------------------------------- -- 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:

 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 clean MySQL command line interface, I now get the following:

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

I assumed that InnoDB

My database is disconnected from Amazon RDS, so I have the access they give me. Here is the version of the server 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 reorganize all my code, because MySQL will not let me recreate my table. This is just stupid. In the meantime, I ask this question for generosity.

Answer 1

InnoDB DDL is not transactional, so information in the .frm file and in the InnoDB dictionary are possible. In your case, it looks as if the .frm file is missing, but there is a lost entry in the dictionary (well, actually, entries in several tables of the SYS_ * dictionary).

You cannot easily delete an entry from the dictionary. You need the appropriate .frm file, so MySQL transfers your DROP to the InnoDB level. With RDS you cannot do this.

But you can DROP the entire database. In this case, InnoDB will delete all entries from the dictionary, including orphans.

So, to clear the dictionary, I suggest the following:

• Stop all traffic to MySQL, make it read-only
• Create temporary adstudio_tmp database
• RENAME all tables from adstudio to adstudio_tmp
• DROP DATABASE adstudio . At this moment it is empty. DROP destroy all entries in the InnoDB dictionary.
• RENAME all tables back from adstudio_tmp to adstudio

After that, the dictionary should be clean, and you can create your data_feed_param .

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

Answer 2

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. 

First try the DROP TABLE in this table and try re-adding it

Answer 3

If your script is correct, check the directory in mysql / data / {your database name} for files with the name of your table. Delete everything (move them in case you need them to further resolve problems). After that, create a table with a name and one column to check if this resolved the problem.

Answer 4

After upgrading from 5.5 to 5.6, I now get a new error trying to create the same tables:

 Error Code: 1813. Tablespace for table '`adstudio`.`account_data_feed_param`' exists. Please DISCARD the tablespace before IMPORT. 

I assume the statement:

 DROP TABLESPACE `data_feed_param`; 

Now I will fix my problem without having RENAME my tables, but, unfortunately, we cannot get the CREATE TABLESPACE privilege for the user of our database.