1

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.

  • Please show the exact command you ran that generated the error. – RolandoMySQLDBA Dec 23 '14 at 15:23
  • Understanding the InnoDB architecture is important here. Try making another database and then create a table named table1 – Hashir107 Mar 2 '15 at 9:16
1

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

Some worked around this

Others have tried to explain it

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

InnoDB Architecture

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

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.

  • I've seen developers get MySQL 5.1 servers with built-in InnoDB into this state, though exactly how they managed it eluded me, and I don't know whether it's limited to 5.1 or if it's still possible with the "plugin" InnoDB or on later releases. It's not a bad data dictionary entry for a table so much as bad foreign key constraint data that survives some kind of failed operation. I've been able to get the server happy again by converting the visible table "from and to" InnoDB with ALTER TABLE table2 ENGINE=InnoDB, without a restart. – Michael - sqlbot Dec 23 '14 at 18:09

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.