Db migrate - Hibernate/JPA - hbm2ddl - diff tools...

Question

Hello

I use - EJB3 / JPA (hibernate) - MySQL 5

I have to set up a system to help database migrations. Tried to use LiquiBase but it seems not enough mature yet to be used with Hibernate.

What i would like to do is: - I have version 1.0 of the application in production - I have version 2.0 of the application developped and tested - I want to update the application database in production without loosing data

Actually i would like to use the new persistence.xml and the production database to be able to generate the "delta" between the old database and the new. I would like to be able to retrieve the sql code that is executed when hbm2ddl is on "update" mode. This sql code will be modified (drop+create=rename etc...) to avoid loosing data, that could happen with hbm2ddl.auto=update.

I guess it is possible because hibernate does it when deploying new version with hbm2ddl.auto=update. But i want to be able to do it on an Ant task with "hibernatetool".

I don't find many informations about this on internet so i wonder if someone already done something like that here and could help me.

I've done the following:

<hibernatetool destdir="${dist}">
      <!--
      <jdbcconfiguration propertyfile="hibtest.properties"></jdbcconfiguration>
      -->
      <jpaconfiguration persistenceunit="server-pu" />
      <classpath>
       <fileset dir="${core.lib.server}" includes="*.jar" />
       <fileset dir="${core.lib.runtime}" includes="*.jar" />
       <fileset dir="${core.lib.build}" includes="*.jar" />
       <pathelement location="${core.class}" />
       <pathelement location="${core.etc}" />
      </classpath>
      <hbm2ddl outputfilename="schema-delta.sql" format="true"
       export="false" update="true" />
</hibernatetool>

I don't really know how to do, i was able to get the creation sql file, but i just want the delta. I have to put update="true" ? Can i use the jpaconfig with persistence unit outside of application server (the db settings are set on JNDI ressource on Glassfish). (tried and it just can't find the db)

I tried also with or setting properties of db in persistence.xml instead of using JNDI ressource.

I got the following error

BUILD FAILED
/home/slorber/workspace/build/build.xml:899: org.hibernate.exception.JDBCConnectionException: Getting database metadata
    at org.hibernate.tool.ant.HibernateToolTask.reportException(HibernateToolTask.java:226)
    at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:189)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1298)
    at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.eclipse.ant.internal.ui.antsupport.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1181)
    at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.run(InternalAntRunner.java:423)
    at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.main(InternalAntRunner.java:137)
Caused by: org.hibernate.exception.JDBCConnectionException: Getting database metadata
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:64)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.caseForSearch(AbstractMetaDataDialect.java:163)
    at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getTables(JDBCMetaDataDialect.java:22)
    at org.hibernate.cfg.reveng.JDBCReader.processTables(JDBCReader.java:476)
    at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:74)
    at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:860)
    at org.hibernate.cfg.JDBCBinder.readDatabaseSchema(JDBCBinder.java:115)
    at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:88)
    at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:42)
    at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:81)
    at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
    at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
    at org.hibernate.tool.ant.Hbm2DDLExporterTask.createExporter(Hbm2DDLExporterTask.java:51)
    at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
    at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
    ... 15 more
Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql:3306//localhost/db
    at java.sql.DriverManager.getConnection(DriverManager.java:602)
    at java.sql.DriverManager.getConnection(DriverManager.java:154)
    at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getConnection(AbstractMetaDataDialect.java:122)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:61)
    ... 29 more
--- Nested Exception ---
org.hibernate.exception.JDBCConnectionException: Getting database metadata
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:64)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.caseForSearch(AbstractMetaDataDialect.java:163)
    at org.hibernate.cfg.reveng.dialect.JDBCMetaDataDialect.getTables(JDBCMetaDataDialect.java:22)
    at org.hibernate.cfg.reveng.JDBCReader.processTables(JDBCReader.java:476)
    at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:74)
    at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:860)
    at org.hibernate.cfg.JDBCBinder.readDatabaseSchema(JDBCBinder.java:115)
    at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:88)
    at org.hibernate.cfg.JDBCMetaDataConfiguration.readFromJDBC(JDBCMetaDataConfiguration.java:42)
    at org.hibernate.tool.ant.JDBCConfigurationTask.doConfiguration(JDBCConfigurationTask.java:81)
    at org.hibernate.tool.ant.ConfigurationTask.getConfiguration(ConfigurationTask.java:55)
    at org.hibernate.tool.ant.HibernateToolTask.getConfiguration(HibernateToolTask.java:302)
    at org.hibernate.tool.ant.Hbm2DDLExporterTask.createExporter(Hbm2DDLExporterTask.java:51)
    at org.hibernate.tool.ant.ExporterTask.execute(ExporterTask.java:39)
    at org.hibernate.tool.ant.HibernateToolTask.execute(HibernateToolTask.java:186)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1298)
    at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.eclipse.ant.internal.ui.antsupport.EclipseDefaultExecutor.executeTargets(EclipseDefaultExecutor.java:32)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1181)
    at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.run(InternalAntRunner.java:423)
    at org.eclipse.ant.internal.ui.antsupport.InternalAntRunner.main(InternalAntRunner.java:137)
Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql:3306//localhost/db
    at java.sql.DriverManager.getConnection(DriverManager.java:602)
    at java.sql.DriverManager.getConnection(DriverManager.java:154)
    at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getConnection(AbstractMetaDataDialect.java:122)
    at org.hibernate.cfg.reveng.dialect.AbstractMetaDataDialect.getMetaData(AbstractMetaDataDialect.java:61)
    ... 29 more

I use mysql and my jdbc properties are: hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect hibernate.connection.driver_class=com.mysql.jdbc.Driver hibernate.connection.url=jdbc:mysql:3306//localhost/db hibernate.connection.username=root hibernate.connection.password=root hibernate.connection.show_sql=true

My jdbc mysql driver is in ${core.lib.server}: mysql-connector-java-5.1.7-bin.jar (also tried to put in classpath )

Also added it to the run configuration classpath of Ant (eclipse plugin).

So my questions are: - Have i done something wrong to do what i want? - Is it the way you would do to db migrate with an hibernate framework? (and what else would you do if not writing all db changes in hand-made sql files?)

Answer 1

Hbm2ddl does have an API you can call on your own. If the existing ant tasks aren't doing what you want, you can always make the hbm2ddl calls yourself, possibly even passing hibernate an overloaded jdbc driver that logs execute statements rather than actually executing them or overloading the hbm2ddl execute methods.

I would also add that you should look at using liquibase and creating xml files manually over any sort of diffs. You recognize that you will need to validate that the generated sql is correct, it is just as easy to create it one step at a time as you develop, and then you KNOW it is right.

We use hibernate, and rather than using the liquibase hibernate integration, we update our hibernate mappings, run our integration tests to see that they fail, create the necessary liquibase changeset (add table, add column, rename column, etc.), then re-run the tests and see them pass. Building up the changelogs step by step works well with the regular rhythm of development without resorting to diffs for anything beyond sanity checks.