Saving large files in h2 database

Question

I recently updated the h2 database in my springboot project to 2.1.210.

This induced some problems, most of them I was able to fix myself. The one that I can't fix is about saving large files in the database.

When I try to upload a ~3,5MB file, I get following error:

org.h2.jdbc.JdbcSQLDataException: 
Value too long for column "BINARY VARYING": "504b03040a00000000008a6bd150e8d6354d8303010083030100070000003030302e706e6789504e... (3648495)" [22001-210]

The annotation, which worked flawlessly on h2 1.4.200 looks like this

@Lob
@Type( type = "binary" )
@Column(name = "ZIP", columnDefinition="BLOB")
private byte[] zip;

I don't know if its related, but I also get an error when using

@Type( type = "text" )

The error here is

Precision ("2147483647") must be between "1" and "1048576" inclusive

because whenever I use "text", it gets translated to varchar(2147483647)

This problem should be fixed with version 2.1.212. See https://github.com/h2database/h2database/issues/3457 The strange thing is: With the newer version, my binary type (@Type(type = "org.hibernate.type.BinaryType")) is stored as varbinary (with 1.X versions it was of type binary). — Timz, Jul 20 '22 at 13:22

score 0 · Answer 1 · answered Apr 28 '22 at 16:12

It looks like, since H2 version 2, the org.h2.value.Value.getBytes() method has changed significantly so as to limit the amount that can be held in memory in a byte array.

The H2 manual (data types and large objects) says that the allowed length is from 1 to 1048576 bytes for BINARY VARYING and for anything else ResultSet.getBinaryStream(...) is the preferred method.

I'm not sure how you go about controlling this in HQL or using Hibernate annotations, but changing the data type from byte[] to some kind of stream may encourage the ORM to avoid rs.getBytes(...) and call H2's favoured method instead.

Saving large files in h2 database

1 Answers1