Can't create table, but table doesn't exist

InnoDB Architecture

InnoDB Architecture

ANALYSIS

  • Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table.
  • You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped.
  • Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ... because mysqld thinks it is OK to create the table but then defers to the storage engine. InnoDB says "I already have the tablespace_id of my_user registered".

This sequence of events can be proved if you create the table using MyISAM. mysqld will allow it. Once you switch to InnoDB, it goes right back to the data dictionary, which is faulty on that one entry.

I have two suggestions

SUGGESTION #1

Don't create the table with that name anymore. Use a different table name

CREATE TABLE my_usertable (id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255), group_id VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

This will result in you changing the table name in your application code

SUGGESTION #2

  • mysqldump all the data, triggers and stored procedures
  • backup ibdata1
  • delete ibdata1
  • reload the mysqldump
  • Detailed steps from my StackOverflow post

I have dealt with this problem before in my post InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)


Just to add my solution as I had a similar problem.

TL;DR

  • Recreate the table with the same foreign key specification but with a different name as previously held by the table .
  • Drop the resulting table (will also drop original orphan foreign key)
  • Recreate table with original or no foreign key

Detail

I ran into the nasty situation where an ALTER TABLE statement failed due to a foreign key not being dropped earlier. This led to some inconsistencies in the InnoDB data dictionary (probably due to http://bugs.mysql.com/bug.php?id=58215).

Related question here: https://stackoverflow.com/questions/16857451/error-in-foreign-key-constraint-on-a-droped-table

mysql> ALTER TABLE `visits` CHANGE COLUMN `variation_visitor_id` `variation_visitor_id` INT(11) NOT NULL  ;

Error on rename of './db/#sql-482c_8448f' to './db/visits' (errno: 150)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint of table db/visits:
 FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html 
for correct foreign key definitippon.

As I couldn't recover the #sql-482c_8448f table to visits, I decided to reimport it from a backup done just before the alter. However this failed. On investigation:

  • The constraint had been removed from INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.STATISTICS
  • But the constraint was still visible in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
  • The table didn't exist so I couldn't drop the foreign key
  • I couldn't create the table without errors

SQL/Errors

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE ID='db/fk_visits_variations_visitors1';

+-----------------------------------+-----------+------------------------+--------+------+
| ID                                | FOR_NAME  | REF_NAME               | N_COLS | TYPE |
+-----------------------------------+-----------+------------------------+--------+------+
| db/fk_visits_variations_visitors1 | db/visits | db/variations_visitors |      1 |   48 |
+-----------------------------------+-----------+------------------------+--------+------+

Trying to recreate the table without the foreign key caused ane error 150

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

ERROR 1005 (HY000) at line 26: Can't create table 'db.visits' (errno: 150)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint of table db/visits:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
  CONSTRAINT "fk_visits_variations_visitors1" FOREIGN KEY ("variation_visitor_id") REFERENCES "variations_visitors" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION

Trying to create it with caused a error 121

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
  KEY `fk_visits_variations_visitors1` (`variation_visitor_id`),
  CONSTRAINT `fk_visits_variations_visitors1` FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

ERROR 1005 (HY000) at line 26: Can't create table 'db.visits' (errno: 121)

mysql> SHOW ENGINE INNODB STATUS;

Error in foreign key constraint creation for table `db`.`visits`.
A foreign key constraint of name `db`.`fk_visits_variations_visitors1`
already exists. (Note that internally InnoDB adds 'databasename'
in front of the user-defined constraint name.)
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
> the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.

Eventually I used a new foreign key name. I wasn't expecting this to work but it allowed the table to be created.

mysql>

SET UNIQUE_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `visits` (
  `id` INT(11) NOT NULL AUTO_INCREMENT  ,
  `variation_visitor_id` INT(11) NOT NULL  ,
  PRIMARY KEY (`id`),
  KEY `fk_visits_variations_visitors2` (`variation_visitor_id`),
  CONSTRAINT `fk_visits_variations_visitors2` FOREIGN KEY (`variation_visitor_id`) REFERENCES `variations_visitors` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Simply dropping the table after that removed the errant record in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN, allowing an import with the original foreign key name.

Tags:

Mysql

Innodb

Recent Posts