1

I am using mysql innodb engine and during process of creating foreign keys mysql crashed. Now my table doesn't exist and when I try to create it again I am getting error 150. I know that this error has some relations with constraints but I really don't know how to recreate table. I tried to recreate with same fields but nothing. Also, I dropped tables that start with "#sql-*.frm" but also nothing. I really don't know where innodb store this information about broken tables and theirs constraints. Please help with how to recreate table.

Update:

After using SHOW ENGINE INNODB STATUS i got:

LATEST FOREIGN KEY ERROR

160225 11:50:49 Error in foreign key constraint of table mesavis/furnace_report: 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 "furnace_report_ibfk_2" FOREIGN KEY ("FK_treatment") REFERENCES "treatment" ("ID") ON DELETE CASCADE

But when I try to do ALTER TABLE furnace_report DROP FOREIGN KEY furnace_report_ibfk_2; I got that table furnace_report doesn't exist

How to remove those broken constraints such as furnace_report_ibfk_2. They have to be somewhere placed.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Milan Kocic
  • 459
  • 6
  • 20
  • see: http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 – sqrepants Feb 25 '16 at 11:58
  • I cannot believe that no one has solved this yet. I don't want to recreate database from the begin only because one constraint left over somehow. There has to be some special table or some file where i can change and remove this constraint so table can be created again. – Milan Kocic Feb 25 '16 at 14:16

1 Answers1

0

InnoDB monitor can tell you after receiving the above error message what constraint caused the issue. See "LATEST FOREIGN KEY ERROR" section in the sample output in the linked page for an expected output.

Since you mentioned you had .frm files starting with #sql, you may have orphan tables as well in the innodb data dictionary. See mysql's documentation on innodb troubleshooting on how to get rid of such tables.

Shadow
  • 33,525
  • 10
  • 51
  • 64