wood burning stoves 2.0*
The moose likes JDBC and the fly likes Storing Files larger than 1 GB in MySQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Storing Files larger than 1 GB in MySQL" Watch "Storing Files larger than 1 GB in MySQL" New topic

Storing Files larger than 1 GB in MySQL

Rajesh k Kumar

Joined: Jan 21, 2010
Posts: 3

I've to store a binary file into database larger than max_allowed_packet size say e.g. 2 GB. How to do that? I hope you'll know that if you try the jdbc blob way you get an error saying that max_packet_size exceeded.
-- Is there a way I can open a stream to a blob into a table's row and write data chunk ( < max_packet_size) by chunk?
-- Or Any other you can suggest?

Note: Please avoid posting increasing the max_allowed_packet size. What is I want to store a file larger than the max value of max_allowed_packet . Also even if I have to store a file of say 200 mb and if I have to hold the 200 mb in some memory object, jvm will run out of memory sooner or later.

Thanks in advance,
Peter Johnson

Joined: May 14, 2008
Posts: 5823

Rajesh, welcome to Java Ranch!

Quoting from the MySQL documentation: (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_allowed_packet)

Command-Line Format --max_allowed_packet=#
Config-File Format max_allowed_packet
Option Sets Variable Yes, max_allowed_packet
Variable Name max_allowed_packet
Variable Scope Both
Dynamic Variable Yes
Permitted Values
Type numeric
Default 1048576
Range 1024-1073741824

The maximum size of one packet or any generated/intermediate string.

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

You might have to break your object/file up into pieces and store as multiple bolbs.

JBoss In Action
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704

I'm in the camp that believes files should *NEVER* be stored in a database... but even if I was in the other camp, storing files greater than 1 GB in a database? That's a really bad idea... really bad.

My Blog: Down Home Country Coding with Scott Selikoff
Rajesh k Kumar

Joined: Jan 21, 2010
Posts: 3
Thanks Scott and Peter,

Why I want to store these files in DB?

-- Ours is a product which would work in a clustered Environmnt ad the processing of a file can be delegated to any node? So a file mighthve been receivd by a node and posts a JMS notification for processing. These file sizes can be from 500 kb to 1 GB. I want to store them into DB so that they are available to EJBs on any node and take advntge of HA featues of Ap server.

-- Even ifI increase the size of max packet, i woul still need to hold the whole bytes into RAM which will definately not be a good idea.


Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 30370

But then you have an extra network transfer of a 1 GB file. This takes a significant amount of time. I recommend you revisit this design. Maybe route the jobs to the server where the file actually exists. Or use a SAN/network drive so all the app servers can access the file.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704

Databases are designed to handle thousands (often millions+) of records so the ability of any one record containing 1 GB would mean you could potentially have hundreds (or thousands) of records each with 1 gigabyte. I seriously doubt your system is designed to handle that kind of load. Do not store large files in the database... its a really bad idea.
Rajesh k Kumar

Joined: Jan 21, 2010
Posts: 3
Thanks for you views,

I agree on the point that large files should not be stored into DB.

So let me drop this hypothetical angle of strorng a 1GB file.

What about the second problem that I have where in if you want to store a 30 MB file (for example), you must hold it in the memory before you could write it into a MySQL server. Do we have any solutions there?

I agree. Here's the link: http://aspose.com/file-tools
subject: Storing Files larger than 1 GB in MySQL