How can I insert huge BLOBs into a MySQL database (InnoDB)?
Fields of type LONGBLOB support data sizes of up to 4 GB according to the MySQL manual. But how does data of such a huge size get into the database?
I tried to use
INSERT INTO table (bindata) VALUES ( LOAD_FILE('c:/tmp/hugefile') );
which fails if the size of hugefile is bigger than about 500 MB. I have set max_allowed_packet
to an appropriate size; the value of innodb_buffer_pool_size
doesn't seem to have an influence.
My server machine runs Windows Server 2003 and has 2 GB RAM. I'm using MySQL 5.0.74-enterprise-nt.
BLOBs are cached in memory, that's why you will have 3 copies of a BLOB as you are inserting it into a database.
Your 500 MB BLOB occupies 1,500 MB in RAM, which seems to hit your memory limit.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With