Quirky database object creation stories

[bgardner]

I've been having some odd behavior with database objects getting created for domain objects. Recently I needed to re-create a table with the right constraints, so I changed my dbcreate in DataSource.groovy from "update" to "create-drop" just long enough to rebuild my table.

All was well. I put dbcreate back to "update" and started run-app, and immediately started getting errors on creation of things like the hibernate-sequence. I had seen that before, so I stopped the RunApp and started it up again (yes, this had worked before). Now the table I had recreated is gone, and I have a pile of errors :

2010-08-04 10:36:20,085 [main] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table appquest_applications add id number(19,0)
2010-08-04 10:36:20,085 [main] ERROR hbm2ddl.SchemaUpdate  - ORA-00942: table or view does not exist

2010-08-04 10:36:20,085 [main] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: alter table appquest_applications add version number(19,0)
2010-08-04 10:36:20,085 [main] ERROR hbm2ddl.SchemaUpdate  - ORA-00942: table or view does not exist

and so on for a while...

Ok, so I put it back to create-drop to see if things could get built, and I continue to get these errors, some on dependencies (foreign key things) and others on simple object creation.

I can handle blowing everything away and starting over - is that the recommended approach here?

Brian

[burtbeckwith]

create-drop drops when the app shuts down cleanly. It runs the drop commands and add commands at startup, and drop commands at shutdown.

You could use just 'create' which just does the drop/add at startup.

But I would just run 'grails schema-export' script and use the ddl from target/ddl.sql to manually update the database. At some point after early development you'll probably want to use a migration tool like Liquibase - there's a plugin for that, http://grails.org/plugin/liquibase

[bgardner]

Burt -

Changing it to "create", which solved my problem on the tables, but I still get an error for the hibernate_sequence creation.

2010-08-04 13:13:41,011 [main] ERROR hbm2ddl.SchemaExport  - Unsuccessful: create sequence hibernate_sequence
2010-08-04 13:13:41,011 [main] ERROR hbm2ddl.SchemaExport  - ORA-00955: name is already used by an existing object

It doesn't keep things from working, but all log entries with the word ERROR are a little disconcerting.

Brian