Hi there,

Is it possible to store a binary content of around 200 MBytes into MySQL ?

Theorically, there is the BLOB data type (and variation such as
LARGEBLOB), but in pratice I don't see how it works.

You are limited by the 'max_allowed_packet' and even more by the memory
of the client in the first place.

There is no way to stream that ?

I'm using Hibernate framework upon MySQL, but I can't find anything
useful there neither.

Is that so that just nobody use BLOB to store more than 16 MB ? I know
that filesystem is better for doing that kind of stuff, but it requires
more pain for developpement, deployement and maintenance since the data
are not store in a unique place (the DB), and the dump file are not
exhaustive anymore.

Any advice or pointer ?

--
Hugo

Hugo wrote:
I've gone up to 64Mytes blob data..

I had to mess with some of the system tunables. Probably PHP and apache
ones, but not IIRC mysql ones.




I know
Not sure what the problem is.

LOAD FILE will load a BLOB any size you want really..and I am dead
against loading binary data by an update or insert statement..there are
potential gotchas there..I chose not to face..:-)

On 1 Oct, 15:49, The Natural Philosopher <t... (AT) invalid (DOT) invalid> wrote:
There is no LOAD FILE in MySQL.
There is LOAD DATA INFILE...
And there is the LOAD_FILE() function but that is used in an INSERT or
UPDATE statement, which you said you are against???
So what do you actually mean?

Captain Paralytic wrote:
I mean you don't want to be using insert etc with a
set mblob='sdfuyasd672340935nklwefner89rv7\\@"0';-P`¬`#~ h9'

rather than a

LOAD_FILE('/tmp/myblob.dat')

Hugo <hugo (AT) nospam (DOT) invalid> wrote:
It is. But it does not make much sense.

The MySQL API is very ... basic when it comes to blob handling.
You can only read or write blobs as a whole. And in any case both
server and client need a buffer that holds an entire row.

Example: inserting a 200M blob into an InnoDB table

- the client builds a network buffer of 200M+x length and fills it
- the buffer is sent to the server and allocates another 200M there
- the blob is copied over to database pages in the InnoDB buffer pool
- the blob is copied to the redo log buffer (will require multiple
flushes of the log buffer to disk)

All this could happen in multiple active connections concurrently.

max_allowed_packet is the configurable limit for the communication
buffer. Can be as much as 4G.

No.

OK, the prepared statement API has a function to send a blob in pieces.
But this does only work for writes (INSERT, UPDATE). And again data is
"collected" in a buffer in the server.

File servers have been invented.


XL

Axel Schwenke wrote:
Not true, you can read SUBSTRINGs of blob fields.

SELECT SUBSTRING(data,$start,$length) FROM blob_data WHERE id = 1000 LIMIT 1

works just fine.


--
Norman
Registered Linux user #461062
-Have you been to www.mysql.com yet?-