错误 1005 (HY000):无法创建表“x"(错误号:-1) [英] ERROR 1005 (HY000): Can't create table "x" (errno: -1)

问题描述

当我尝试在 MySQL 服务器中创建表时出现错误，

I have error coming when i am trying to create tables in MySQL server,

ERROR 1005 (HY000): Can't create table 'bill_period' (errno: -1)

请注意这与ERROR 1005 (HY000): Can't create table (errno: 150)"不同(注意错误号不同).

Please note this is not same as the "ERROR 1005 (HY000): Can't create table (errno: 150)" (Note that error no is different).

即使是简单的创建表也会因此错误而失败，并且未设置和未使用外键.并且大量的磁盘空间是免费的.

even simple create table fails with this error and Foreign keys are not set and not used. And plenty of disk space is free.

有什么想法吗?

出于调试目的，我用一个新表名而不是原来的表名做了同样的查询，第二个似乎没问题，

for debugging purposes, i have done this same query with the a new table name than the what was originally there, seems to be ok for the second one,

mysql> CREATE TABLE IF NOT EXISTS `bill_period` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `day` tinyint(2) unsigned NOT NULL,   PRIMARY KEY (`id`),   KEY `day` (`day`) );                

ERROR 1005 (HY000): Can't create table 'bill_period' (errno: -1)

mysql> CREATE TABLE IF NOT EXISTS `bill_period_1` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `day` tinyint(2) unsigned NOT NULL,   PRIMARY KEY (`id`),   KEY `day` (`day`) );
Query OK, 0 rows affected (0.42 sec)

推荐答案

这是一个老问题，但我今天刚得到这个，并找到了一个解决方案(是的，errno = -1).

This is an old question, but I just got this today, and found a solution (and yes with errno = -1).

我们的表是 InnoDB，每隔几个小时就会创建和销毁一次.我们最近启动了一个从服务器来跟随那个主服务器.我们也使用innodb_file_per_table"选项.

Our table is InnoDB and was created and destroyed every few hours. We had recently started a slave server to follow that master. We also use "innodb_file_per_table" option.

错误只会发生在从站上，而不是在主站上.问题是我们最近几个小时前使用 rsync 将该从站同步到了主站.

The error would occur on the slave only, not on the master. The problem was that we recently synchronized that slave to the master using rsync a few hours prior.

rsync 复制了表文件，但在此期间被 master 删除，因为它是一个临时表.因此，.idb 文件存在于从站中，但不再存在于主站中.

Rsync copied the table file, but then was deleted by the master in the meantime, since it was a temporary table. The .idb file existed therefore in the slave, but no longer in the master.

当主站发送创建表的请求时，从站不能，因为文件已经在那里了.MySQL 不知道有一个表存在，因为元数据说那里没有表.我假设 MySQL 正在尝试创建该文件，但由于它在那里它失败了.

When the master sent a request to create the table, the slave could not as the file was already there. MySQL was unaware that a table existed as the metadata said no table was there. I'm assuming MySQL was trying to create the file, but since it was there it failed.

就我而言，解决方案是删除另一个文件夹中的 .idb 文件(当然首先要备份副本).

之后，数据库将允许我们创建表，然后从站将再次跟随主站

After, that, the database then would allow us to create the table, and the slave would then follow the master again

完成 rsync 的更好方法是使用 --delete 选项删除从主服务器中消失的所有陈旧文件.

A better way to have done that rsync would have been to use a --delete option to remove any stale files that were gone from the master.

无论如何 - 您可能没有我所描述的那种确切情况，但它可能只是一个因某种原因留在那里的陈旧文件.

Anyhow - you might not have that exact situation as what I've described, but it could simply be a stale file that's left there for a reason or another.

这篇关于错误 1005 (HY000):无法创建表“x"(错误号:-1)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！