hibernate and mysql with create-drop option, console output shows error ... but ddl executed ok; possible bug?

Question

I am using the create-drop option for development, when deploying to mysql database (using hibernate 4) I get the following output:

15:18:07,715 ERROR SchemaExport:426 - HHH000389: Unsuccessful: alter table my_table drop foreign key FKD42DEFE312AC02F1
15:18:07,715 ERROR SchemaExport:427 - Table 'my_db.my_table' doesn't exist

It seems its attempting to alter a table before it has created it. The table and FK are succesfully created. What causes the error message ?

Answer 1

The problem is that:

• if you have two tables A and B and a foreign key reference in A that references B.
• and you want to delete the tables

You need to remove the foreign key constraint first. (except you know the right sequence and have no circular dependencies)

So you normaly remove all constrains first and then drop the tables.

If one table does not exists (because it is new) then you use drop table if exists But unfortunaly my-sql as no statement to remove a forainkey only if the table exists. So it is more a lack of features than a bug.

---

I solved it, but my setup is different. I use hibernate only in validation mode and run the scripts to update the database by hand. The scripts are generated by hibernate org.hibernate.tool.hbm2ddl.SchemaExport. Then I take the generated file, added set foreign_key_checks = 0; in the beginning and set foreign_key_checks = 1; at the end. Then I commented out (add -- at the beginning) every line that match the pattern alter table *. drop foreign key *.;

@See https://stackoverflow.com/a/34038447/280244 for more details