1

Using Grails 2.4.4, and have ported my domain classes from 2.2.0.

There's a problem I am facing w.r.t "create-drop" config of DataSource, using MySQL as the datasource.

Whenever I issue a grails stop-app command, out of total 35 tables, 22 tables are left in the schema.

After enabling debug mode for Hibernate classes, and at the end of the stop-app process, it was generating drop table if exists <tablename> for all 35 tables, but no error/confirmation was there in the logs whether the drop table succeeded or not.

The tables left are having FK associations, and they need to be removed in specific order. With same Domain class structure, I never had this problem with earlier (2.2.0) version of grails.

Right now I am manually dropping-creating everytime before run-app as it's causing trouble with the BootStrap data.

Any pointer to debug this issue or a usecase for when this can happen, shall be appreciated.

v1p
  • 133
  • 1
  • 11
  • 1
    You should have DDL statements dropping the foreign keys before dropping the tables, e.g. `alter table tablename drop foreign key FK_hrogx8ddq6cptuh5ru8uycn6s`. Run `grails schema-export` and look at `target/ddl.sql` to see the SQL that Hibernate generates. – Burt Beckwith Jan 19 '15 at 00:11
  • Thanks @BurtBeckwith. I ran `grails schema-export` and generated the file, and the starting 35 lines are `drop table if exists ` . There's no `alter table tablename drop foreign key ` in the file. – v1p Jan 19 '15 at 00:43
  • This is how the file is structured : first `drop table if exists ` , then `create table ` , then `alter table add UK constraints` & creation of indices, in the last there are `alter table add FK constrains` – v1p Jan 19 '15 at 00:47
  • My bad I am extremely sorry, I better stop working late night at a stretch. I had put this file `ImprovedMySQLDialect` to get rid of [this](http://stackoverflow.com/questions/23858953/grails-2-4-and-hibernate4-errors-with-run-app) a few days back. so I had unknowingly barred the drop of FKs. So to get rid of this other problem I need to implement a better solution. shall look into overriding the dropConstraints() method. Thanks @BurtBeckwith for pointing me in right direction. – v1p Jan 19 '15 at 00:54

1 Answers1

0

For my case setting FK-checks to 0 for MySQL (v5.5.25) solved this, although I am not entirely sure if I am supposed to SET FOREIGN_KEY_CHECKS=0 at all.

If anyone has a better solution, please do share.

EDIT

The problem was faced due to this. Lesson learnt - Thou shalt not copy-paste random code mindlessly ~:-/

ANSWER

Thanks Burt.

If DB is behaving erratically w.r.t ddl operations. Always check ddl.sql generated by grails schema-report, which should ideally have following structure

alter table <Table> drop constraint <Constraint>
...

drop table if exists <Table>
...

create table <Table>(...)
...

create index <Index> ...   --(if any)
...

alter table <Table> add constraint <Constraint>
....
Community
  • 1
  • 1
v1p
  • 133
  • 1
  • 11
  • where did you set this `SET FOREIGN_KEY_CHECKS=0`? Can you please elaborate? – Chetan Feb 09 '15 at 17:54
  • @Chetan : `SET FOREIGN_KEY_CHECKS=0` was manually set in the mysql console session, where I was dropping all the tables manually. – v1p Feb 10 '15 at 09:17
  • but the actual problem was in a custom MySQL5InnoDBDialect class that I wrote, with overridden method `public boolean dropConstraints()` returning `false` – v1p Feb 10 '15 at 09:21