I'm unable to get schema export working with either Hsql 1.7.1 or 1.8.0. The errors differ in either case, but it fails with both. See below for sample mapping file and the exact log output from Hibernate. In summary:

With 1.7.1, I get an error, "Unexpected token: GENERATED"

With 1.8.0, I get an error, "Constraint already exists in statement [create table users (..., unique (login), unique (login))]

What do I need to do to get Hibernate and Hsql to work together using hibernate.hbm2ddl.auto=create?

I also tried with hibernate.hbm2ddl.auto=update, which is what I'd rather use, but that fails even sooner with a failure to read metadata followed by a table not found error...

L.

Hibernate version:

3.1

Mapping documents:

Sample:
<hibernate-mapping package="com.foo" default-access="field">
<class name="UserBO" table="users" lazy="true">
<id name="id"><generator class="native"/></id>
<natural-id mutable=" false">
<property name="loginId" column="login" length="32" not-null="true" unique="true"
access="field"/>
</natural-id>

<property name="password" length="32" not-null="true" access="field"/>
<property name="name" length="32" not-null="true" access="field"/>
<property name="email" length="32" not-null="true" access="field"/>
</class>
</hibernate-mapping>

Full stack trace of any exception that occurs:

Name and version of the database you are using:

Hsql 1.7.1 / 1.8.0

The generated SQL (show_sql=true):

With Hsql 1.7.1:

ERROR [main] org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table users (id integer generated by default as identity (start with 1), login varchar(32) not null, password varchar(32) not null, name varchar(32) not null, email varchar(32) not null, primary key (id), unique (login), unique (login))
ERROR [main] org.hibernate.tool.hbm2ddl.SchemaExport - Unexpected token: GENERATED in statement [create table users (id integer generated by default as identity (start with 1), login varchar(32) not null, password varchar(32) not null, name varchar(32) not null, email varchar(32) not null, primary key (id), unique (login), unique (login))]


With Hsql 1.8.0:

ERROR [main] org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table users (id integer generated by default as identity (start with 1), login varchar(32) not null, password varchar(32) not null, name varchar(32) not null, email varchar(32) not null, primary key (id), unique (login), unique (login))
ERROR [main] org.hibernate.tool.hbm2ddl.SchemaExport - Constraint already exists in statement [create table users (id integer generated by default as identity (start with 1), login varchar(32) not null, password varchar(32) not null, name varchar(32) not null, email varchar(32) not null, primary key (id), unique (login), unique (login))]

Try changing the generator value from "native" to increment. I understand that native is an issue for some databases. Also, it looks like there's an extra space in: <natural-id mutable=" false">

The latest release of HSQL broke backward compatibility. The next release of Hibernate will support HSQL 1.8.0.

You could have found a discussion of this by searching JIRA.

This is not true.

I searched the entire hibernate.org site, including the forums, without finding anything relevant (actually, two forum posts on the same problem, but with no replies). Sorry I missed JIRA, I'm new around here. I'll try searching there for the background.

Does this mean I can downgrade to 3.0 to get things working?

Thanks,

L.

No. it means downgrade HSQL. We did not break, they did.

Sorry, I mis-read. OK, so downgrade HSQL to what version, given that neither 1.8.0 nor 1.7.1 are working currently? And how is it HSQL's fault that Hibernate is sending multiple 'unique' constraint clauses for 1.8.0?

L.

1.7.1 works fine.


the trouble is that you have specified the uniqueness constraint twice. you don't need unique=true inside a <natural-id>

Thanks for the tip about the unique constraint in the metadata, I'll fix that. But 1.7.1 is broken too for me (see errors in my original posting). It's possible it's failing because of the duplicate unique constraint that 1.8.1 is complaining about, and just reporting it really really badly though... I'll see if fixing that gets Hsql 1.7.1 to work.

L.