Internal HSQL database complains about privileges

Question

I'm setting up a standalone Java service with an in-process, in-memory HSQL database.

Persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
 version="2.0">

 <persistence-unit name="manager">

 <class>tr.silvercar.data.entities.User</class>
 <properties>
 <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />
 <property name="javax.persistence.jdbc.user" value="sa" />
 <property name="javax.persistence.jdbc.password" value="" />
 <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:testdb" />

 <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
 <property name="hibernate.max_fetch_depth" value="3" />

 <!-- cache configuration -->
<!-- 
 <property name="hibernate.ejb.classcache.org.hibernate.ejb.test.Item"
  value="read-write" />
 <property
  name="hibernate.ejb.collectioncache.org.hibernate.ejb.test.Item.distributors"
  value="read-write, RegionName" />
 -->
 </properties>

 </persistence-unit>

</persistence>

Code

  emf = Persistence.createEntityManagerFactory("manager");

  User newUser = new User();
  newUser.setName("Testgebruiker");
  newUser.setCredits(100);

  System.out.println("Inserting user");
  EntityManager em = emf.createEntityManager();
  em.persist(newUser);
  em.close();

  System.out.println("Getting user");
  em = emf.createEntityManager();
  User u = (User) em.createQuery("SELECT u FROM User u").getSingleResult();
  em.close();
  System.out.println(u);

It would seem to me that since the database is in memory, and Hibernate should generate tables, that I don't need to do anything else. However, upon calling getSingleResult I get the exception:

org.hsqldb.HsqlException: user lacks privilege or object not found: USER

Answer 1

You need to use Hibernate 3.5.6 or later, together with HSQLDB version 2.2.x or later. Otherwise, older Hibernte jars work with HSQLDB 1.8.x. The name of the table is not a problem. I have developed the dialect and run the Hibernate tests for this version, but Pascal knows a lot more about Hibernate usage than I do and has helped a lot of people here.

Answer 2

I run into the same issue and none of solutions provided (really) helped me (and there are quite a few more posts here on Stackoverflow which are closely related), but I finally figured it out. Therefore I thought I share my findings (sorry for the slightly lengthy post):

In my case I converted some existing UnitTests using a MySQL database to HSQLDB so that the external dependency can be removed. This all looks kind of easy if you look at descriptions like: http://eskatos.wordpress.com/2007/10/15/unit-test-jpa-entities-with-in-memory-database/ But it turned out to be a bit more tricky.

I experimented with

1. different versions (as suggested above),
2. the create and ifexists parameters (see: http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html),
3. specifying different user credentials (username="sa", password="" is correct),
4. specifying update, create and create-drop as hibernate.hbm2ddl.auto (see http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html),
5. using different types of DataSources: c3p0, dbcp, ...
6. ...

But none of those really made any difference (the errors differed btw.). Apart from the user lacks privilege or object not found error message, the most informative one I could get was the following: SQL Error: -20, SQLState: IM001 (which translates to "Driver does not support this function") And even more explicitly I found this in the logs: [2012-09-24 14:50:45,796] ERROR main::org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(144) | This function is not supported

So something clearly was broken. It turns out that biggest issue turns out to be a combination of two things: 1. Me not properly looking through the log output, since it actually contained the clue 2. An erroneous annotation

Regarding 1:

The issue was that the output contains a huge amount of lines that looked like the following (which can apparently be ignored, and there is even a ticket for H2):

[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: alter table SimpleSubscription drop constraint FKAD76A00F168752B2
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | user lacks privilege or object not found: PUBLIC.SIMPLESUBSCRIPTION
Hibernate: alter table SimpleSubscriptionChange drop constraint FKB3B8189FFC3506ED
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: alter table SimpleSubscriptionChange drop constraint FKB3B8189FFC3506ED
[2012-09-25 10:07:13,453] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | user lacks privilege or object not found: PUBLIC.SIMPLESUBSCRIPTIONCHANGE
Hibernate: alter table SimpleSubscriptionChange_languages drop constraint FK5A45F07BFC21A8E6

Regarding 2:

Between all those lines were hidden the following lines, which actually gives away what the error is:

[2012-09-25 10:07:13,468] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(426) | HHH000389: Unsuccessful: create table Rule (id bigint generated by default as identity (start with 1), creationTime timestamp not null, deleted BIT not null, lastUpdateTime timestamp, version integer not null, fetcher varchar(255), hash integer not null, primary key (id), unique (id))
[2012-09-25 10:07:13,468] ERROR main::org.hibernate.tool.hbm2ddl.SchemaExport.perform(427) | a UNIQUE constraint already exists on the set of columns in statement [create table Rule (id bigint generated by default as identity (start with 1), creationTime timestamp not null, deleted BIT not null, lastUpdateTime timestamp, version integer not null, fetcher varchar(255), hash integer not null, primary key (id), unique (id))]

So the issue is that the BaseEntity that is defined has an erroneous annotation for the id:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(nullable = false, unique = true)
private Long id;

The field is already identified as an ID (i.e. as a primary key), and therefore can not have a unique annotation (and also the nullable is kind of superfluous). Changing this to:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private Long id;

and everything works fine :-)