如何修复损坏的InnoDB锁定从创建一个表名（错误：-1）的AWS RDS？

问题描述

提示：做的没有的一个标准TCP / IP通过SSH跑 ALTER 声明中MySQL的工作台连接。这是更好的壳到服务器并运行 ALTER 从那里。这样，如果你失去连接到服务器时， ALTER 还是应该完成自己的工作。

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)

现在pviously $ P $，我试图创建此表与多个栏目，其中一个被命名为 INPUT_TYPE ，和我有一个列有外键关系 INPUT_TYPE ：

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;

我注意到，这个专栏是不好当的Propel无法再生。我的表中删除，改变了列名，现在我不能完全相同的名称与InnoDB引擎添加表。

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.

然而，我能够创建一个表中的MyISAM，插入完全相同的名字，并把它没有问题。

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?

更新2015年6月1日：所以我失去了同桌的再次只是根据不同的情况。首先，我加了preFIX表名称，以避免上述问题， adstudio.account_data_feed_pa​​ram 。

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.

其次，我正在放弃它的变化来此表来代替。我跑了一个 ALTER 来添加一列，但收到消息MySQL服务器已消失。我做这一切在MySQL的工作台。

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.

第三，我有一个多到多个表引用这其中的与填充它的内部数据。这是怎么可能？如何MySQL的只是随意丢弃我的表？

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

这是创建的SQL表现：

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;

这是 ALTER 语句我试图运行：

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

和尝试创建相同的表在纯MySQL命令行界面，我现在收到这样的：

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)

我已经假定的InnoDB

I have postulated that InnoDB

我的数据库运行过亚马逊RDS的，所以我只能访问他们提供给我。下面是我运行在服务器上的版本：

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)

我真的，真的不想重构我所有的code，因为MySQL不会让我重新创建我的表。这只是愚蠢。在此期间，我把这个问题上升为赏金。

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.

推荐答案

DDL InnoDB的不是事务性的，因此可能是在一个.frm文件和InnoDB的字典信息是不同的。在你的情况下，它看起来像.frm文件丢失，但有一个在字典中的一个孤立的记录（当然，实际上是在一些字典SYS_记录*表）。

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).

您不能轻易删除词典中的记录。你需要一个相应的.frm文件因此MySQL传递您的下降InnoDB的水平。随着RDS你不能做到这一点。

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.

但你可以将整个数据库。在这种情况下的InnoDB将从字典中删除所有记录，包括孤儿之一。

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. 停止所有的流量到MySQL，使其只读
2. 创建一个临时数据库 adstudio_tmp
3. 重命名从 adstudio 所有表 adstudio_tmp
4. DROP DATABASE adstudio 。在这一点上是空的。该 DROP 将清除所有条目InnoDB的字典。
5. 重命名中的所有表从 adstudio_tmp 返回 adstudio

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

在这个字典应该是干净，你就可以创建你的 data_feed_pa​​ram 。

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.

这篇关于如何修复损坏的InnoDB锁定从创建一个表名（错误：-1）的AWS RDS？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！