CODEWITHSUNDEEP
mysql
mahen3d
mahen3d

Reputation: 7734

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

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)

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.

Any ideas ?

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)

Upvotes: 3

Views: 1824

Answers (1)

Pierre-Olivier Benoit
Pierre-Olivier Benoit

Reputation: 149

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

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.

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

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.

In my case, the solution was to delete the .idb file in another folder (of course with a backup copy first).

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

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.

Upvotes: 1

Related Questions