CODEWITHSUNDEEP
javaspring-bootspring-data-jpahsqldb
user74416
user74416

Reputation: 161

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

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?

Upvotes: 4

Views: 12901

Answers (3)

Vladyslav Yemelianov
Vladyslav Yemelianov

Reputation: 21

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>

Upvotes: 1

Hank
Hank

Reputation: 1338

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

Upvotes: 2

user74416
user74416

Reputation: 161

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.

Upvotes: 10

Related Questions