Bug #15089 BLOB data should not remain in memory
Submitted: 21 Nov 2005 4:24 Modified: 20 May 2009 7:32
Reporter: Gili Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:3.1.10, 4.1, 5.0, 5.1, 6.0 bzr OS:Any (Linux, Windows XP sp2)
Assigned to: CPU Architecture:Any
Tags: affects_connectors

[21 Nov 2005 4:24] Gili 
Description:
Currently com.mysql.Blob stores the entire BLOB data in memory at all times. This defeats the entire purpose of BLOB which is made to hold huge amounts of data.

I need this issue fixed as soon as possible because my application makes heavy use of BLOBs and quickly runs out of memory.

Expected behavior: BLOB methods should retrieve data sub-sets from the server on-demand and not keep a local copy around (nor the entire BLOB value, since often only a small subset is actually requested). If you want a local cache, feel free to use SoftReference for that purpose.

If you have a workaround in mind I am all ears :)

How to repeat:
Loads Blobs from DB, one at a time, modify them slightly, and write them back into the DB.

The problem is that I want to have an Image object in memory without necessarily having its data (blob) in memory for the same lifetime.

Due to the evils of Hibernate, I cannot always unload the enclosing Image from memory when I want to.

Suggested fix:
Use SoftReferences for local cache. Only retrieve requested subsets of BLOB, not entire value.
[21 Nov 2005 21:34] Mark Matthews 
This is a limitation with the server, neither the MySQL network protocol, nor the server itself allow a client to retrieve a BLOB piecemeal in an _efficient_ fashion.

(The JDBC driver can do this if "emulateLocators" is set to true in the configuration properties, but this still requires the _server_ to load the ENTIRE LOB for each request of a "chunk" of it from the input stream returned to the application by the JDBC driver).
[20 May 2009 7:38] Sveta Smirnova 
This partially fixed in patch for bug #38002.
[18 Nov 2009 9:19] Sveta Smirnova 
Bug #48085 was marked as duplicate of this one.
[21 Apr 2010 6:46] Sveta Smirnova 
Bug #47623 was marked as duplicate of this one.
[26 Nov 2011 19:05] Gili T. 
Mark and Sveta,

What is the updated status now that bug #38002 was fixed? Does the server still load the entire LOB each time a chunk of it is requested by the JDBC driver?
[26 Nov 2011 20:37] Mark Matthews 
That fix is orthogonal to the issue. MySQL can still only load entire blobs to satisfy requests for portions of them. It is a limitation imposed by the storage engine API, requests for partial column values are not supported.
[26 Nov 2011 21:07] Gili T. 
Okay. I filed RFE #63441