quote

domenica, dicembre 28, 2008

BLOB type in MySQL

I had this exception when I was trying to insert a big image in a BLOB column using MySQL.

java.sql.BatchUpdateException: Data truncation: Data too long for column 'picture' at row 1


The problem didn't happen with small images, i.e. with the size of 2,3Kb. After spent some time to investigate about the problem, I understood that the reason is due to the BLOB type in MySQL whose maximum size is 64k. Therefore if you want to store big binary data in a MySQL database you need to use another blob type as MEDIUMBLOB or LONGBLOB.
If you are using JPA annotations to define your tables you need to specify the length of your BLOB attribute using the @Column annotation.
Here is what I have defined for the picture attribute used in jugevents application.

@Lob
@Basic(fetch = FetchType.LAZY)
@Column(length=1048576)
public byte[] getPicture() {
return picture;
}

In this way the type of the column corresponding to the picture attribute will be MEDIUMBLOB instead of BLOB and you won't get that exception

4 commenti:

Unknown ha detto...

There is no need to write @Basic(fetch = FetchType.LAZY) because this is default.

BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB)

MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes, 16MB)

LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes, 4GB)

enricoGi ha detto...

Thanks Shervin, to be honest with you, I am not an expert in JPA annotation, I only use JPA in the open source project jugevents (www.jugevents.org) which I collaborate. Thanks for your comment.

bunyawat ha detto...

Thank you,
this post help me out

Anonimo ha detto...

Thank you.