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

Don't know what is happening there with this in memory database... :S Maybe [fredt](http://stackoverflow.com/users/361828/fredt) will show up with a good explanation. Just in case, what version of HSQLDB are you using? — Pascal Thivent, Sep 27 '10 at 20:42

fredt · Accepted Answer · 2013-04-12T14:55:26.693

15

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.

edited Apr 12 '13 at 14:55

answered Sep 27 '10 at 23:22

fredt

24,044
3
40
61

Interesting, I wasn't aware of these requirements (restrictions?) on versions. +1 – Pascal Thivent Sep 27 '10 at 23:37
1

I came across this question after getting the same error after adding a new column to an entity that clashed with an SQL reserved word - `WHEN` in this case. The entity properties are not listed in the question but in my case, adding an annotation with escaping (backticks) solved the issue: ``@Column( name = "`when`" )``. Maybe the column name just sucks :-) – penfold Sep 12 '11 at 00:15
Does hibernate currently supports HSQLDB 2.2.8? – Bas Mar 16 '12 at 09:54
Latest Hibernate supports HSQLDB 2.2.8. USER can be used as a table name, but when it is used as a column name, it must be quoted, otherwise it returns the value of CURRENT_USER. – fredt Mar 16 '12 at 10:18

score 13 · Answer 2 · edited May 23 '17 at 12:25

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

different versions (as suggested above),
the create and ifexists parameters (see: http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html),
specifying different user credentials (username="sa", password="" is correct),
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),
using different types of DataSources: c3p0, dbcp, ...
...

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 :-)

The issue of the erroneous id annotation for the id also helped in my case. Thanks a lot for sharing. SQLserver seams to be less strict on erroneous (or redundant) annotations... — Mathias G., Dec 16 '13 at 15:36

score 4 · Answer 3 · answered Jul 29 '13 at 06:11

4

I ran into a similar problem, but in my case the problem occurred because of column definitions. I used MySQL definitions in this way:

@Id
@GeneratedValue
@Column(columnDefinition = "INT(11)")
private long id;

This seems to not be supported by HSQL, and I changed the definition to this:

@Id
@GeneratedValue
@Column(columnDefinition = "INTEGER")
private long id;

And then the tests worked again.

answered Jul 29 '13 at 06:11

Stig Runar Vangen

161
2
5

Thanks !! its worked for me ...Actually i used @Column(columnDefinition = "text") – Darshan Oct 14 '15 at 11:42

score 1 · Answer 4 · answered Dec 06 '10 at 03:45

As per the above post, I was using Hibernate 3.5.6 together with HSQLDB 2.0.1 but it was still throwing the error. I also corrected the url but of no help. Finally changed HQSL jar to 1.8.1 version from http://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_1_8_1/hsqldb_1_8_1_3.zip/download and it worked

score 0 · Answer 5 · edited Oct 20 '12 at 06:47

When I had this error, I realised that I was using the wrong provider class in persistence.xml

For Hibernate it should be

<provider>org.hibernate.ejb.HibernatePersistence</provider>

And for EclipseLink it should be

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

You should also note that different names are used in persistence.xml and when creating Persistence.createEntityManagerFactory("name")

score 0 · Answer 6 · answered Jun 18 '13 at 21:21

Try explicitly specifying the SCHEMA name for the table/sequence being referenced.

I just faced a similar issue where in my sequence was not being recognized and then I realized that in my Hibernate @SequenceGenerator annotation I didn't prefix the schema to the sequence ref.

call next value for EXAMPLE_SCHEMA.TEST_SEQ

upog · Answer 7 · 2010-09-28T14:18:09.240

0

I too have faced the same error. It got resolved when i gave the absolute path of script file in "connection.url".

< property name="connection.url">jdbc:hsqldb:file:C:\Hibernate-example\database\mydb;shutdown=true < /property>

edited Sep 28 '10 at 14:18

answered Sep 28 '10 at 13:55

upog

4,965
8
42
81

Thank you, but I'm using a database stored in memory, not in a file. – Bart van Heukelom Sep 28 '10 at 14:56

score 0 · Answer 8 · answered Apr 06 '17 at 15:27

0

HSQL does not use the longtext but the LONGVARCHAR type. Changing it solved a similar issue for me, where it complained about required privileges.

answered Apr 06 '17 at 15:27

Alexander Pacha

9,187
3
68
108

Internal HSQL database complains about privileges

8 Answers8

Linked