January 18, 2011

Hibernate hbm2ddl won't create schema before creating tables

Situation

I have a local H2 in memory database for integration tests and an Oracle db for production. I do not control the Oracle DB model. The in memory H2 database is created automatically by adding

<prop key="hibernate.hbm2ddl.auto">update</prop>

to hibernate properties in AnnotationSessionFactoryBean. The definition of the entity stored in DB points to a schema

@Entity
@Table(name = "business_operations", schema = "sowa")
public class BusinessOperation {
...


The problem

When creating the H2 database, Hibernate won't create the schema before creating tables. As a result it will show errors when trying to create the tables in non existing schema and fail in any query (queries will be run with sowa.business_operations).

2011-01-18 15:13:30,884 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Running hbm2ddl schema update
2011-01-18 15:13:30,885 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - fetching database metadata
2011-01-18 15:13:30,915 INFO [org.hibernate.tool.hbm2ddl.SchemaUpdate] - updating schema
2011-01-18 15:13:30,927 INFO [org.hibernate.tool.hbm2ddl.DatabaseMetadata] - table not found: business_operations
2011-01-18 15:13:30,941 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - Unsuccessful: create table sowa.business_operations 
2011-01-18 15:13:30,942 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - 
Schema "SOWA" not found;

Turns out this bug is reported and open since 2006: link.


The solution

The solution to this problem is to create the schema before hibernate's hbm2ddl turns on. That would be easy with H2 if we could tell H2 to initialize itself like this:

database.url=jdbc:h2:mem:;INIT=RUNSCRIPT FROM 'src/main/resources/scripts/create.sql';

All seems nice, except H2 RUNSCRIPT FROM command doesn't work with relative resources as you may expect. Fortunatelly INIT allows us to give any commands, not just point to a script, so this little change will solve the problem:

database.url=jdbc:h2:mem:;INIT=create schema IF NOT EXISTS sowa 

Yeah, I know it's obvious and simple stupid, but looking at all the questions on all the mailing lists in google I may have just saved a little bit of somebody's time.

6 comments:

  1. I've been following the HHH-1853 issue for the last two years (sic!) Unfortunately we'll have to stick with this nice workaround You provided as this ticket has just been rejected and postponed to 4.0 future :-(.

    ReplyDelete
  2. Wow, this just saved us a ton of time and headache.

    Seriously, thanks for posting it!

    ReplyDelete
  3. I think this might be better and more Entity-oriented http://stackoverflow.com/questions/2645255/how-to-use-hibernate-schemaupdate-class-with-a-jpa-persistence-xml

    ReplyDelete
  4. This didn't quite produce the full solution for me (using H2 1.3.163 and Hibernate 3.5.5-Final). In order for the schema to be created and for the schema to still remain in existence when the tests run, I had to setup the connection string as follows: jdbc:h2:mem:[schema-name];DB_CLOSE_DELAY=-1;INIT=CREATE SCHEMA IF NOT EXISTS [schema-name] - This is because H2 will drop the database from memory as soon as the connection is dropped (after the hbm2ddl process is complete). This may be different if you have Hibernate setup to keep connections open (I'm not sure), but with our configuration, the database ceased to exist after the hbm2ddl process had run.

    ReplyDelete
  5. I'm getting this error: (org.hibernate.tool.hbm2ddl.SchemaUpdate:235) [execute] - HHH000388: Unsuccessful: create table...
    I'm using MS SQL 2008 R2 database. The best way to fix this issue is using these database properties:
    database.maxPoolSize=10
    database.minPoolSize=1
    database.dialect=org.hibernate.dialect.SQLServer2008Dialect
    database.driverClassName=net.sourceforge.jtds.jdbc.Driver
    Good luck.

    ReplyDelete