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

Question

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

• mustaccio: 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...
• noisebleed: @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).
• mustaccio: So, these aren't the actual names, you're just teasing us...
• akuzminsky: 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.
• ypercubeᵀᴹ: Does the actual table name have any weird character (not alphanumeric)? Does it start with a digit or a weird character?

Answer 1

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)

• noisebleed: #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: 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: 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.
• Gigih Aji Ibrahim: thanks. have same exact problem. 1 table in my database missing and I cannot restore it from backup because I can't create the table.

Answer 2

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.

• Mauro Molinari: Your comment was very helpful, it happened the same thing to me and following your steps seemed to work... but WARNING: with my version of MySQL (Percona Server 5.5.62), when I got to the point in which I recreated the foreign key with the original name in my table, MySQL server crashed completely and left that table in an unusable state after restart. So I was forced to dump the whole schema and recreate it from scratch (with another name), then dropping the old schema, to get out of this problem completely.

Answer 3

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.

Answer 4

What worked for me was:

• first move the .frm and .ibd files to another dir, e.g. /tmp/tablebackup *
• now extracting the table structure from the .frm file using mysqlfrm from Oracle's mysql-utitilies** (because I had no other copy/backup of the structure) e.g.: /usr/bin/mysqlfrm --diagnostic /tmp/tablebackup/MyTable.frm
• create a new table with the original table's structure but with a different name (e.g. let's say the table with the issue is MyTable then I now created a table MyTableB with the original table's structure)
• next rename the table to the original name from within mysql, e.g.: RENAME TABLE `MyTableB` TO `MyTable`; (note that this only works if you do not have innodb_force_recovery set in your my.cnf)
• now in mysql run: ALTER TABLE `MyTable` DISCARD TABLESPACE;
• then copy the original .ibd file (only the .ibd file, not the .frm file) back into the mysql database dir where it was originally moved from (there shouldn't be an existing .ibd file at this moment because that gets remove by the DISCARD TABLESPACE command)
• and now run ALTER TABLE `MyTable` IMPORT TABLESPACE;

*I restarted mysql after this step but not sure this is required

**mysql-utilities may require installing mysql-connector-python first

Answer 5

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