H2 DB & Binaries > 1MB

[patrick-werner]

Describe the bug
When using hapi with H2 DB with Binaries > 1MB size i get
Caused by: org.h2.jdbc.JdbcSQLDataException: Value too long for column "BINARY VARYING": "1f8b08000000000000ff447bc79283cab6e5bfdc291d81773dc37b0f0234c37b233cf4cf3775ce7b... (1095716)" [22001-210]

To Reproduce
Steps to reproduce the behavior:
Load a FHIR Package with a (Terminology) Resource > 1MB OR: Post a Binary > 1MB to hapi

Expected behavior
Binary will be persisted without any errors (like it works with postgres)
This seems to be caused by trying to persist a binary as byte[], which is restricted to max 1 MB in H2 version 2. This should be persisted as BLOB or CLOB.

Environment (please complete the following information):

• HAPI FHIR Version 6.0.1
• H2 DB

[tadgh]

Are you referring to specifically the HFJ_RES_VER table's myResource field?

	@Column(name = "RES_TEXT", length = Integer.MAX_VALUE - 1, nullable = true)
	@Lob()
	@OptimisticLock(excluded = true)
	private byte[] myResource;

I just had a peek and it looks like H2 is indeed storing this data as a BLOB:

Is it this field its complaining about?

[patrick-werner]

I think so, i had problems with IntelliJs H2 browser, but checked it myself with the H2 Jar, and you are right!
Which is odd, i posted the complete stack trace here: https://pastebin.com/nMt6Vr29

the interesting part is:

Caused by: org.h2.jdbc.JdbcSQLDataException: Value too long for column "BINARY VARYING": "1f8b08000000000000ff447bc79283cab6e5bfdc291d81773dc37b0f0234c37b233cf4cf3775ce7b... (1095716)" [22001-210]
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:525)
    at org.h2.message.DbException.getJdbcSQLException (DbException.java:496)
    at org.h2.message.DbException.get (DbException.java:227)
    at org.h2.message.DbException.getValueTooLongException (DbException.java:341)
    at org.h2.value.ValueVarbinary.<init> (ValueVarbinary.java:34)
    at org.h2.value.ValueVarbinary.getNoCopy (ValueVarbinary.java:65)
    at org.h2.value.ValueVarbinary.get (ValueVarbinary.java:51)
    at org.h2.jdbc.JdbcPreparedStatement.setBytes (JdbcPreparedStatement.java:1001)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setBytes (HikariProxyPreparedStatement.java)
    at org.hibernate.type.descriptor.sql.BlobTypeDescriptor$3$1.doBind (BlobTypeDescriptor.java:113)
    at org.hibernate.type.descriptor.sql.BlobTypeDescriptor$2$1.doBind (BlobTypeDescriptor.java:86)
    at org.hibernate.type.descriptor.sql.BasicBinder.bind (BasicBinder.java:73)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet (AbstractStandardBasicType.java:276)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet (AbstractStandardBasicType.java:271)
    at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet (AbstractSingleColumnStandardBasicType.java:39)
    at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate (AbstractEntityPersister.java:3073)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert (AbstractEntityPersister.java:3372)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert (AbstractEntityPersister.java:3908)
    at org.hibernate.action.internal.EntityInsertAction.execute (EntityInsertAction.java:107)
    at org.hibernate.engine.spi.ActionQueue.executeActions (ActionQueue.java:604)
    at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1 (ActionQueue.java:478)
    at java.util.LinkedHashMap.forEach (LinkedHashMap.java:721)
    at org.hibernate.engine.spi.ActionQueue.executeActions (ActionQueue.java:475)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions (AbstractFlushingEventListener.java:344)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush (DefaultFlushEventListener.java:40)
    at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener (EventListenerGroupImpl.java:107)
    at org.hibernate.internal.SessionImpl.doFlush (SessionImpl.java:1402)
    at org.hibernate.internal.SessionImpl.managedFlush (SessionImpl.java:493)
    at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion (SessionImpl.java:3285)
    at org.hibernate.internal.SessionImpl.beforeTransactionCompletion (SessionImpl.java:2420)
    at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion (JdbcCoordinatorImpl.java:449)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback (JdbcResourceLocalTransactionCoordinatorImpl.java:183)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300 (JdbcResourceLocalTransactionCoordinatorImpl.java:40)
    at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit (JdbcResourceLocalTransactionCoordinatorImpl.java:281)
    at org.hibernate.engine.transaction.internal.TransactionImpl.commit (TransactionImpl.java:101)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit (JpaTransactionManager.java:562)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit (AbstractPlatformTransactionManager.java:743)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit (AbstractPlatformTransactionManager.java:711)
    at org.springframework.transaction.support.TransactionTemplate.execute (TransactionTemplate.java:152)
    at ca.uhn.fhir.jpa.packages.JpaPackageCache.addPackageToCache (JpaPackageCache.java:250)
    at ca.uhn.fhir.jpa.packages.JpaPackageCache.installPackage (JpaPackageCache.java:466)

ValueVarbinary.java:34does a check against the max String size, which is 1MB (i think).

[tadgh]

heh, so while investigating this, I stumbled across this: h2database/h2database#3457 and this: hapifhir/hapi-fhir-jpaserver-starter#328

Long story short, looks like bumping H2 will fix this as they've rolled the changes into 2.1.212.

I would love to replicate this though instead of just blind-bumping the dependency. Could you possibly pastebin me the sample Binary Resource you are using if that works?

[patrick-werner]

Just try to load an IG, like IPS in the application.yaml, e.g.: https://gist.github.com/patrick-werner/1092e094c4026b4345467bb9db1ba3cf#file-application-yaml

I tried it with H2 2.1.212, and it just works. Will create a PR.

[tadgh]

Thanks Patrick! Merged and added an attribution and changelog 👍🏻