Hibernate & Blobs - please help!!

[Rob Wilson]

Hi all,

I have used the Hibernate forum a few times in the past, but rarely
receive a reply. I currently have an issue where I have configured my
hibernate mapping file to use a 'blob' and have 'Blob' objects in my
entity being persisted.

When persisting Hibernate fails to persist the object with a
DataException, caused by a MySqlTruncation exception.

Based on history I do not think I will get a reply from the Hibernate
forums, so can I please ask the Posse to help!...

http://forum.hibernate.org/viewtopic.php?p=2368427#2368427

(stack)

org.hibernate.exception.DataException: could not insert:
[com.ciderbob.common.Game]
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:
77)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:
43)
at
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:
40)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:
2158)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:
2638)
at
org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:
48)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
at
org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:
298)
at
org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:
181)
at
org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:
107)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:
187)
at
org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:
33)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:
172)
at
org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:
27)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:
70)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.hibernate.context.ThreadLocalSessionContext
$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:
301)
at $Proxy0.save(Unknown Source)
at com.ciderbob.test.TestHibernate$3.doit(TestHibernate.java:186)
at com.ciderbob.dao.HibernateUtil.doCommand(HibernateUtil.java:66)
at com.ciderbob.test.TestHibernate.testBlob(TestHibernate.java:
182)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:164)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:230)
at junit.framework.TestSuite.run(TestSuite.java:225)
at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:
130)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:
38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:
460)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:
673)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:
386)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:
196)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data
too long for column 'largeimage' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2939)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:
1268)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
1541)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
1455)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
1440)
at org.hibernate.id.IdentityGenerator
$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:
73)
at
org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:
33)
... 42 more

[Mac]

The issue isn't hibernate. I think the problem is mysql.
check your mysql setting for "max_allowed_packet". The default
is 1MB I believe. in mysql 3, it's raised to 16M. in Mysql 4.x, it
was altered to the size of ram.

[Christian Catchpole]

Also, unrelated to this issue, but as evidence that blob handling is
treacherous:

There used to be a bug in the Oracle JDBC driver where you could not
insert a BLOB or CLOB larger than 4K. Since the largest varchar in
oracle is 4K, in internal driver mechanism would buffer the data, even
when streamed, and would explode at 4K.

I also remember something about one work around of having to insert an
null blob and then select it for update.

There are all sorts of issues with different databases around
something as simple as "storing a stream of bytes or characters".

Go legacy! :)

[RogerV]

On Nov 4, 6:08 pm, Christian Catchpole <ato...@catchpole.net> wrote:
> Also, unrelated to this issue, but as evidence that blob handling is
> treacherous:
>
> There used to be a bug in the Oracle JDBC driver where you could not
> insert a BLOB or CLOB larger than 4K. Since the largest varchar in
> oracle is 4K, in internal driver mechanism would buffer the data, even
> when streamed, and would explode at 4K.
>
> I also remember something about one work around of having to insert an
> null blob and then select it for update.
>
> There are all sorts of issues with different databases around
> something as simple as "storing a stream of bytes or characters".
>
> Go legacy! :)

I store blobs larger than 4K on Oracle 9i and 10g with no problem. I
did have to code some special hoops for Oracle versus same code that I
got to work for Cloudscape (Derby), though.

[Rob Wilson]

Cheers for you comments, it appears that in MySQL you can have a
mediumblob or largeblob, but in hibernate there's no such type
available.

It appears that the mysql configuration file is the only real answer,
but that dissapoints me.

Thanks for the heads-up on Oracle, but this is for a small website and
do not think I need anything other than MySQL (alternatively
Postgress) for this.

The documentation shows that ~64k is my largest blob size, my test was
using 89k. Probably lucky that I didn't use something smaller
otherwise this could have hit me later.

I am considering using a byte array instead of a blob and then storing
it as a binary type (I think it was called binary), this also means
that the image is available outside of the transaction, but has the
dissadvantage of holding the whole image in memory.

The latest thing I'm looking at is a custom hibernate type, but since
Hibernate 3 they have added many methods to the java interface, I have
yet to find any documentation on how to implement the additional
methods :-(

Any more feedback will be appreciated.

[Mac]

I highly recommend you give postgres a try. Since you are using
hibernate,
trying pgsql is a very trivial thing to do.

[Rob Wilson]

I have used postgres before, but I'm trying to use mysql this time
(partially because it's deployed on my target server already and would
like to not install another db).

MySQL has default the maximum size to 16MB, so I know that this is no
longer a solution to my problem. What I need to do is say
'mediumblob' in hibernate to give me an increase from 64k to 2^24k.

Whilst discussing this with my cousin, we considerd a different
solution altogether and would like some feedback on the design...

First, using a blob has a limitation that it must be used within the
transaction in which it was created, which is not always going to be
possible. As a result of this I am thinking that having an attribute
in my POJO for the 'largeImage' could result in attempted access at
any time - not just at the point of the transaction.

This made my cousin suggest adding a method in the DAO instead - so
the access to the blob can be controlled (makes sense).

This also means that to fix my 'hibernate blob size' constraint, I
could fallback to using a JDBC call instead and access the column
however I want.

Hmm, actually that last step might be wrong; the 'blob' type is
specified in the hibernate mapping file, and this mapping file is used
to create the database... so maybe I should simply change the database
schema manually to use a 'mediumblob' and then maybe hibernate will
play ball?!

I'll try that shortly and update this post, any comments in the
meantime will be appreciated.

Regards,
Rob.

[patrickw]

I'd say that working with a byte array mapped to the Hibernate binary
type is easier that trying to work with blobs. I don't think you have
to do anything special - just map it.

Patrick

[Rob Wilson]

Well, I changed the schema in mysql to use 'mediumblob', and initially
it still didn't work, but then realised that I had left the
hbm2ddl.auto property set to create (which resulted in the table being
re-created as a standard 'blob'), once I fixed that it works!

So, to recap...

Let Hibernate create the database structures, then issue commands to
alter 'blob' types to 'mediumblob'.

So, does anyone know the best way to make hibernate determine whether
there are any tables with 'blob' and alter them programmatically...
perhaps some sort of listener interface called early in the lifecycle?

I may still have the issue of using the blob within a transaction, but
that can be dealt with with the DAO option. Perhaps the POJO can have
some code in the accessor of the largeImage so that if it is null it
throws an exception - but that seems a little heavy handed.

I'll update my post in the hibernate forum to highlight what I've
done, incase it helps others too, linking back to this forum.

Any other comments still very welcome,
Cheers,
Rob.

[Rob Wilson]

Hi Patrick,

I am considering using a byte array because of the restriction that
using a blob must be within the transaction, however since Hibernate 3
there are additional methods that must be implemented in the custom
type interface. I don't suppose you have any material that explains
how these must be implemented?

Cheers,
Rob.

[cichlidguru]

You could always store the value in base64 format in a regular text
field and encode and decode it as necessary. This obviously adds extra
processing, but depending on your situation this may work.

- Kurt

[Jack]

Does your Java Bean currently have a property of type "java.sql.Blob"?

I'd highly recommend against that - and using a byte array, or a
custom type (I can't say what's best in this case, without seeing your
design). I personally do not ever use "java.sql" classes in my
business layer (I realize that java.sql.Date is a java.util.Date, and
Hibernate may try to use it).

[Mike Jones]

On 05/11/2007, Rob Wilson <net...@gmail.com> wrote:
> Let Hibernate create the database structures, then issue commands to
> alter 'blob' types to 'mediumblob'.

You can tell hibernate which column type to use:

<http://www.hibernate.org/119.html#A6>

Maybe a better approach than running alter commands.

[patrickw]

Hey Rob
You don't need to use a custom UserType to use a byte array.

In our app we have a class called BinaryObject which contains a List
of byte arrays. It's slightly more complex than what you're trying to
do, but it allows us to store files of any size in the database by
breaking them down into smaller parts.

The column that contains the binary data (which can be a blob in
oracle, an image in mssql - we support several databases) is just
mapped in Hibernate as type="binary", which is a Hibernate built-in
type.

You should also set the Hibernate system property
hibernate.jdbc.use_streams_for_binary to "true" (see
http://www.hibernate.org/hib_docs/reference/en/html/session-configuration.html).

In answer to your question about implementing custom UserTypes in
Hibernate, perhaps this will help:

public Serializable disassemble( Object value ) throws
HibernateException
{
return (Serializable) value;
}

public Object assemble( Serializable cached, Object value ) throws
HibernateException
{
return cached;
}

public Object replace( Object original, Object target, Object
owner ) throws HibernateException
{
return original;
}

I'd also recommend getting Java Persistence with Hibernate from
Manning if you're doing more than dabbling with Hibernate.

Best wishes,
Patrick

[Christian Catchpole]

Blobs and Clobs are often used to store XML and other such "texty"
data. Depending on what this data is, and if other applications will
ever need to access it, you can save terabytes of storage (and
bandwidth) simply by wrapping a Deflater around the stream. Now you
don't want the format to be an unfortunate legacy mistake (When your
perfect Java App is replaced with XYZ), but Deflate a standard for
just about every platform.

[Rob Wilson]

What an awesome response, far better than on the Hibernate forums
itself (which is a real shame).

Jack, I had decided to use blobs to enable me to move on to the next
item in my to-do list and blobs are working fine (for now). I agree
that I do not long-term want 'blob' in my business layer and may drop
back to a byte array sooner rather than later (when my to-do list has
been reduced!).

Kurt, thanks for the base 64 idea, is there a general java class that
can encode and decode to base 64?

Mike, Many thanks for the heads-up on the hibernate column type
customization link!!

Patrickw, thanks for the custom user type code, is there any
explanation anywhere on what this all means? - I assume these are
default returns unless you need to affect the type in some way? With
your 'binary' example, is this limited to a 64k maximum size? Is the
class serializable for storing the list in the database, or does it
satisfy some sort of interface?

One incidental question, I've noticed that the hbm2ddl can be set to
'update' instead of 'auto' (might have been create?), I've not seen
any documentation on what 'update' actually does... It sounds useful
in the sense that maybe it only creates structures that don't already
exist? Any tips / links?

Thanks all, very much appreciated.

Rob.

[Casper Bang]

> Kurt, thanks for the base 64 idea, is there a general java class that
> can encode and decode to base 64?

There's been a RFE for Sun to make the BASE64 codec in the standard
library public, sadly nothing has happened since it was submitted 8
years ago:
http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=4235519

There's a BASE64 codec support in mail.jar (JEE) if you can carry that
overhead.

I find that the easiest to use, is Roedy Green version:
http://mindprod.com/zips/base6419.zip

/Casper

[Jack]

Here's another one (like all thing in Java, the Base64 CODEC is
encoded is implemented in a hundred different ways, and you get the
luxury of picking one... :) )

http://commons.apache.org/codec/

(I'm a big fan of the Apache Commons libraries)

[Mike Jones]

There's another one here:

http://iharder.sourceforge.net/current/java/base64/

[Todd Costella]

David Herron posts on
java.net(http://weblogs.java.net/blog/robogeek/archive/2007/11/that_ol_j
ava_ja.html) this morning about the origins of the Java Posse intro.
Included in the original post
(http://blogs.sun.com/dcb/entry/java_jingle) is the entire mp3.

Todd

[Ifnu _]

Hi,

> Let Hibernate create the database structures, then issue commands to
> alter 'blob' types to 'mediumblob'.

I also has the right same problem with you :D, and i come up with the
same exact solution, generate the schema and then alter the blob
column with long blob, i use longblob to save uploaded file from user,
the uploaded file mainly are document file such as PDF, doc, odp or
others. I still searching how to tell hibernate to map @Lob (fyi i use
Hibernate-JPA) field to longblon instead of blob.

one question, anyone know how big is Mysql 5 longblob type?

if you come up with slick solution please share here, thanks :D

--
http://www.nagasakti.or.id/roller/ifnu
regards