9

I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db:

mysql> USE my_db;
mysql> DROP TABLE my_user;
mysql> ERROR 1051 (42S02): Unknown table 'my_user'
mysql> CREATE TABLE my_user (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;
mysql> ERROR 1005 (HY000): Can't create table 'my_db.my_user' (errno: -1)

Tried # mysqladmin flush-tables and repeated the steps above but it wasn't helpful. Also, restarted the mysql service, but no good.

Any ideas? Google has failed me so far. Thanks.

Extra info:

mysql> SHOW engine innodb STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
140703 15:15:09 Error in foreign key constraint of table my_db/my_user
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_CFBD431E285FAC6D" FOREIGN KEY ("group_id") REFERENCES "my_group" ("id")
  • 1
    Are you sure you don't have a typo somewhere? You say you're creating table my_user but the error is about my_db.user... – mustaccio Jul 3 '14 at 15:02
  • @mustaccio, yep made a typo when when shortening the table name to my_user (the original has a longer, confusing name). Actually the CREATE TABLE code is generated by the Doctrine ORM library (PHP). – noisebleed Jul 3 '14 at 15:20
  • So, these aren't the actual names, you're just teasing us... – mustaccio Jul 3 '14 at 15:23
  • if you have abandoned record in the InnoDB dictionary it won't let you create table with the same name. Looks like your case, but needs more investigation. Try to put fake my_user.frm and my_user.ibd and drop the table. – akuzminsky Jul 3 '14 at 18:23
  • Does the actual table name have any weird character (not alphanumeric)? Does it start with a digit or a weird character? – ypercubeᵀᴹ Oct 20 '14 at 14:36
5

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

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)

  • #1 Great answer, thank you for the details. #2 Mysqldump(ed), stopped mysqld, removed ibdata1, then restarted but can't get the daemon to start successfully again. Need to understand better what's going on. – noisebleed Jul 3 '14 at 18:35
  • Ok, everything working now. Had to also remove ib_logfile0 and ib_logfile1 (along with ibdata1). After the import I could create the my_user table without any issues. Thank you Rolando! – noisebleed Jul 4 '14 at 13:45
  • I have lost two other tables now. I'm recording every executed query and those tables were not removed using DROP TABLE. Something wrong is going on. – noisebleed Jul 4 '14 at 14:36
4

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.

1

There is one simple way around this, although admittedly, in certain circumstances you may not want to do this. Since this issue stems from an InnoDB internal reference, you can simply create this table with the same name, same columns, only using a different storage engine. I ran into this on a MySQL slave, and even though the master I was replicating from was InnoDB, I re-created this one table with MyISAM and was able to get back up and running. I specifically chose InnoDB for my storage engine on the master, and on some tables, it would be important on the slave as well, but in this case, it had zero impact on this slave for this one table, so it was a quick way to get around this issue. Dropping the whole database would have been a much bigger project.

0

You lost table data, but the record about this table still exists in "mysql/data/ibdata1". The easiest solution is to create this table in some other database and then copy files:

mysql/data/dummy_database/my_user.frm

mysql/data/dummy_database/my_user.ibd

to your own:

mysql/data/yours_database/my_user.frm

mysql/data/yours_database/my_user.ibd

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.