Spring data JPA with HSQLDB: user lacks privilege or object not found

Question

I'm trying to setup a simple Database with an article which may contains comments with Spring Data JPA. However as soon as I enable the comment relation on the article Hibernate throws an exception:

o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
[...]
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: PUBLIC.ARTICLE_COMMENTS
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.4.1.jar:2.4.1]
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.9.jar:na]
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.9.jar:na]
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
... 157 common frames omitted

My article class looks as follows:

@Entity
public class Article {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String name;
    private String abstractText;
    private String author;
    private Date createdAt;
    private int visibility;
    private int likes;
    private int views;
    private int commentCount;
    @OneToMany(cascade=CascadeType.ALL)
    private List<Comment> comments;
    // getters and setters omitted
}

And the Comment:

@Entity
public class Comment {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private int id;
    private String commentText;
    private Date createdAt;
}

And the pom contains dependencies to HSQLDB, PostgreSQL, Spring Boot Starter Data JPA and Spring Boot Starter Web:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

When I remove the relation to the Comment List, it works. Any suggestions?

Look like joining problem. If you will add @JoinTable anotation into your article class private List comments. Its work. You can check this example https://www.codejava.net/frameworks/hibernate/hibernate-one-to-many-association-on-join-table-annotations-example — Dhiren, Oct 29 '18 at 20:27
How do you create your database schema? You need to add the missing table to that. — Jens Schauder, Oct 30 '18 at 05:30
Adding the @JoinTable Annotation didn't help either, still same error. I let Hibernate generate the tables (spring.jpa.hibernate.ddl-auto=create-drop in application.properties). — user74416, Oct 30 '18 at 07:12
You could try the fix described in https://stackoverflow.com/a/20698339/4663544 — Jeba Ranganathan, Oct 03 '22 at 11:53

score 10 · Accepted Answer · answered Oct 31 '18 at 05:41

Seems like the spring.jpa.hibernate.ddl-auto property were the problem. Spring defaults it to create-drop for in-memory databases. However, in the current Spring Boot or HSQLDB or Hibernate version (or maybe the combination?) this seems to cause problems. After changing the value to update, it just works fine.

score 2 · Answer 2 · answered Jan 22 '19 at 08:24

The exception you saw is just a WARNING, and it really doesn't mean anything broken. If you use show-sql: true options, you will see this warning is actually cause by alter table comment DROP CONSTRAINT XXXXX, which is part of Hibernate creation DDL, and hsqldb doesn't have any table when this query executed.

Changing to ddl-auto: update has its side-effect. import.sql won't work with this option.

To avoid this WARNING, you could add these lines to your application.yaml:

logging:
  level:
    org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl: ERROR

score 1 · Answer 3 · edited Apr 07 '21 at 08:40

1

The issue for me was in dependency I provided. I have a column @Column(nullable = false, columnDefinition = "text") and old dependency doesn't support this column definition. That's why I had to change

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <scope>test</scope>
</dependency>

to this

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

edited Apr 07 '21 at 08:40

Syscall

19,327
10
37
52

answered Apr 07 '21 at 08:32

Vladyslav Yemelianov

21
1
3

2

It is absolutely fantastic how people can be this far away from the understanding of the simple things really going on. Even in a primitive cases like this one. Absotutely insane... How did you manage to go this far from understanding? – Jul 07 '21 at 18:02
1

It is different databases – Alexander.Iljushkin Oct 27 '21 at 06:31

Spring data JPA with HSQLDB: user lacks privilege or object not found

3 Answers3