Hi, does anyone know if SchemaUpdate works correctly on PostgreSQL? The Hibernate Reference docs say something about how SchemaUpdate depends heavily on JDBC metadata API. However, the JDBC driver for PostgreSQL supports JDBC 3.0, so shouldn't this be enough for it to work?

When I run the schema update, I get an error message that looks like the one I have below. It's basically that the table already exists, so it doesn't do anything. But it should update the table if the schema has changed right?

Dean


[java] ERROR [main] (SchemaUpdate.java:121) - Error while executing create table unlocker (id INT8 not null, version INT4 not null, author VARCHAR(255), componentClassNameList BYTEA, componentIdList BYTEA, executionTime I
NT8, modified BOOL, name VARCHAR(255), startTime INT8, status INT4, primary key (id))
[java] java.sql.SQLException: ERROR: Relation 'unlocker' already exists
[java] at org.postgresql.core.QueryExecutor.executeV2(QueryExecutor.java:289)
[java] at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:105)
[java] at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
[java] at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:438)
[java] at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
[java] at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:246)
[java] at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:232)
[java] at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:116)
[java] at com.ichg.capsela.tools.HibernateUpdate.main(HibernateUpdate.java:33)

heh, "supports JDBC 3.0" is not quite the same thing as "JDBC metadata actually works".

So how would I debug this issue? Would I need to look into PostgreSQL's driver source to see how they implemented their DatabaseMetadata stuff?

I looked at hibernate's DatabaseMetadata & Configuration.generateSchemaUpdate stuff but didn't see anything that was noticable... it seems like it just gets the metadata from the driver and works with it.

Do you know what sort of databases support the schemaupdate functionality? I know MySQL does... but I need a more powerful database for transactional work....

Dean

I am having problems doing this with mysql, i noticed in the previous reply that you said you knew mysql works. I used the exact following setup (with obvious property values changed) with an oracle 9i database and it worked. The stack trace says that the new attribute can not be found. The stdout says it is trying to create the tables as if it can't find them in the first place. Here is my setup.

JDK 1.4.2
Mysql 4.0.14
Connector/J 3.0.8
Hibernate 2.1 beta 4

[ [ hibernate.hbm.xml file ] ]

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>

<session-factory name="java:comp/env/hibernate/SessionFactory">

<property name="hibernate.dialect">net.sf.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/renegade</property>
<property name="hibernate.connection.username">wdemoss</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.connection.pool_size">5</property>

<property name="hibernate.show_sql">true</property>
<property name="hibernate.hbm2ddl.auto">update</property>

<mapping resource="wfd2/renegade/User.hbm.xml"/>
<mapping resource="wfd2/renegade/Group.hbm.xml"/>

</session-factory>

</hibernate-configuration>

The two mapping files are very simple with a many-to-many between user and group

the exception i get when i try to access these objects after i've added a new property to user follows:

[ [ stack trace ] ]

java.sql.BatchUpdateException: Column not found, message from server: "Unknown column 'phoneNumber' in 'field list'"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1446)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:50)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:116)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2316)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2267)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2211)
at wfd2.renegade.UserTest.testHibernate(UserTest.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)
rethrown as net.sf.hibernate.JDBCException: Could not execute JDBC batch update: Column not found, message from server: "Unknown column 'phoneNumber' in 'field list'"
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:123)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2316)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2267)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2211)
at wfd2.renegade.UserTest.testHibernate(UserTest.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)
Caused by: java.sql.BatchUpdateException: Column not found, message from server: "Unknown column 'phoneNumber' in 'field list'"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1446)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:50)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:116)
... 19 more

the following relevant information is printed out to stdout

[ [ stdout ] ]

java.sql.SQLException: General error, message from server: "Table 'renegade_group' already exists"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1231)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:120)
at net.sf.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:178)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:660)
at wfd2.renegade.UserTest.testHibernate(UserTest.java:16)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)
Oct 7, 2003 8:12:02 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table renegade_user_groups (user_id VARCHAR(32) not null, group_id VARCHAR(32) not null, primary key (user_id, group_id))
java.sql.SQLException: General error, message from server: "Table 'renegade_user_groups' already exists"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1231)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:120)
at net.sf.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:178)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:660)
at wfd2.renegade.UserTest.testHibernate(UserTest.java:16)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)
Oct 7, 2003 8:12:02 PM net.sf.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table renegade_user (uuid VARCHAR(32) not null, name VARCHAR(255), password VARCHAR(255), firstName VARCHAR(255), lastName VARCHAR(255), address VARCHAR(255), phoneNumber VARCHAR(255), surname VARCHAR(255), primary key (uuid))
java.sql.SQLException: General error, message from server: "Table 'renegade_user' already exists"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:928)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1871)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1805)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1231)
at net.sf.hibernate.tool.hbm2ddl.SchemaUpdate.execute(SchemaUpdate.java:120)
at net.sf.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:178)
at net.sf.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:660)
at wfd2.renegade.UserTest.testHibernate(UserTest.java:16)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)

In the previous post i had the property value hibernate.hbm2ddl.auto set to update, if i set it to create or create-delete no exception is thrown, but the schema is recreated not just updated. Maybe that little bit helps your answer?

Thanks in advance
wfd2

I'm a total beginner to this, and I'm trying to use the ANT task for schemaupdate. I have managed to use the ANT task for schemaexport, but the schemaupdate task only writes out a new CREATE TABLE statement, event though the table exists, and the change is the addition of one column. Have I missed some setting or something?

Thanks,
Anders