#1025 - Error on rename of table errno: 150 : Table was deleted while tried to assign foreign key

Question

When I tried to assign foreign key to a table named mydb.table1 it was deleted from my database, I could not understand the reason and tried to move on. For this tried to create same table with create SQL but it says

#1005 - Can't create table 'mydb.table1' (errno: 150) 

Now I created same schema table with table name table2, this was successfully created but as my application code is written for table1, I need to rename this table to table1 again on which gives me following error.

#1025 - Error on rename of table errno: 150 

Also tried to run alter table query with set foreign_key_checks = 0. But same error.

While searching I came accross below SQL,

SHOW INNODB ENGINE STATUS;

Which displays following message

141223 11:30:10 Error in foreign key constraint of table myahd/table1:
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_key9" FOREIGN KEY ("updated_by") REFERENCES "users" ("userid") ON DELETE NO ACTION ON UPDATE NO ACTION

While there is no such table listed in my database with name table1. Nor any such key is found with fk_key9.

May be I can delete this table1's idb or frm file, but unable to locate it. Or may be there is some other solution.

Thanks in advance.

Answer 1

You have a weird situation, but it has happened others.

Some worked around this

• (Bug?) InnoDB MySQL error 1025, errno 150 Foreign Key
• What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

Others have tried to explain it

• MySQL's ERROR 1025 explained
• SQLSTATE[HY000]: General error: 1025 Error on rename of

I think the problem stems from the InnoDB Architecture (Vadim Tkachencko's Picture)

See the data dictionary at the top of ibdata1 ? Although the physical file (table1.ibd) to the InnoDB table is gone, the data dictionary still thinks the table exists.

I have addressed this data dictionary issue before

• Jul 03, 2014 : Can't create table, but table doesn't exist
• Jun 09, 2014 : temp table (#sql-7a87_230c32.ibd along with its .frm) still exists on slave

You may need to mysqldump the database and load it into a new server (as suggested by the OP since it was unresolved but worked around), or look for a tool that can flush out the bad data dictionary entry.