Solving ‘Lob not found’ exception in H2 Database for a Spring Boot application

This is a rather short article about an error that we had during the development phase using a test H2 Database.

Context

We have a microservices-based application generated with the help of JHipster which, during development phase, uses an H2 Database to store dummy data.

In one of the microservices we have an invalidation service for the JWT tokens. That is, when a user has his rights elevated (he gets new roles) we need to regenerate the token and invalidate the previous one.

For this to work, we save the tokens in a table. The length of the token is unknown and can become quite large, depending on the information that is stored inside. Therefore, a VARCHAR would not be suitable. Instead, we have created a table with a CLOB type to hold the token.

Implementation

Below is a snippet containing the declaration of the table in Liquidbase structure:

        <createTable tableName="invalid_token">
            <column name="id" type="bigint" autoIncrement="${autoIncrement}">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="token" type="clob">
                <constraints nullable="false" />
            </column>                                                
        </createTable>

 

On Java side, the Entity looks like this

@Entity
@Table(name = "invalid_token")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class InvalidToken implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;
    
    @NotNull
    @Column(name = "token", columnDefinition="CLOB NOT NULL")
    @Lob
    private String token;    
    
//Getters and setters omitted for brevity
}

As you can see, the CLOB column is actually a simple String in Java land but annotated with @Lob.

Inside the Spring Boot application, there is a filter which, on every request, checks this table in the database to see if the token is invalid or not.

And everything seems to be working. Until some point.

 

Problems at the horizon

We noticed that if no request is done to the application for a particular amount of time, usually around 5 minutes, the application will crash with the following exception:

org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
.........
Caused by: org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: Lob not found: 1/-2"; SQL statement:
select invalidtok0_.id as id1_6_, invalidtok0_.token as token2_6_ from invalid_token invalidtok0_ where invalidtok0_.token=? [50000-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.message.DbException.convert(DbException.java:307)
    at org.h2.command.Command.executeQuery(Command.java:216)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:114)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy256.executeQuery(Unknown Source)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 133 common frames omitted
Caused by: java.lang.RuntimeException: Lob not found: 1/-2
    at org.h2.message.DbException.throwInternalError(DbException.java:254)
    at org.h2.store.LobStorageMap.getInputStream(LobStorageMap.java:285)
    at org.h2.value.ValueLobDb.getInputStream(ValueLobDb.java:397)
    at org.h2.value.ValueLobDb.getReader(ValueLobDb.java:377)
    at org.h2.value.ValueLobDb.getString(ValueLobDb.java:292)
    at org.h2.value.ValueLobDb.compareSecure(ValueLobDb.java:361)
    at org.h2.value.Value.compareTo(Value.java:1160)
    at org.h2.engine.Database.areEqual(Database.java:371)
    at org.h2.command.dml.Query.sameResultAsLast(Query.java:302)
    at org.h2.command.dml.Query.query(Query.java:359)
    at org.h2.command.dml.Query.query(Query.java:333)
    at org.h2.command.CommandContainer.query(CommandContainer.java:114)
    at org.h2.command.Command.executeQuery(Command.java:202)
    ... 141 common frames omitted

This happens even with an empty table, so not even with a LOB value inside.

Solution

After some troubleshooting and googling I realised that this is not an implementation issue but rather an issue with the underlying H2 mechanism so I begun tinkering with the flags.

If you don’t know, in H2 Database, you can specify configuration flags in the connection URL.

For a spring boot application, this is configured in the application.properties (or .yml) under the property spring.datasource.url. Initially, my connection URL was something like this:

spring.datasource.url=jdbc:h2:file:./target/h2db/db/kpdgateway;DB_CLOSE_DELAY=-1;

In turns out that for a while now H2 has a new storage engine called MvStore. In newer versions this is the default. It can be disabled with a flag mv_store=false.

Because we use H2 database only in devlopment, we don’t actually care if we use a legacy structure or not, so we simply need to append this flag to the connection URL and we are good to go.

spring.datasource.url=jdbc:h2:file:./target/h2db/db/kpdgateway;DB_CLOSE_DELAY=-1;mv_store=false

I am not sure to which version of H2 this applies, so for completness my version is “1.4.197”.

The version can be queried with

SELECT H2VERSION() FROM DUAL

 

This solved our problems with the LOB missing error.