CODEWITHSUNDEEP
mysqlblobinnodb
Dan3460
Dan3460

Reputation: 95

MySQL, Saving large blobs

I'm testing an application written in QT that deals with PDFs saved on a database, i was having trouble trying to save anything larger than about 1Mb the application would crash, reading on Goggle end up changing the MAX_ALLOWED_PACKET and let me save blobs.

I plotted several uploads of different size of PDF and i got a number of about 200Kb/sec saving files. Then it came my surprise, checking the data base i realized that anything over around 5Mb would not store. There is no error and it seems that the handshake between the application and MySQL goes ok, as i don't get any errors.

I have some experience with MySQL and Oracle but i have never dealt with Blobs. I read on a post somewhere that i should try to change the value of innodb_log_file_size (i tried 2000000000) but MySQL tells me that it is a read only variable. Could some body help me fix this problem? I'm running MySQL on Ubuntu.

Upvotes: 0

Views: 4024

Answers (2)

Rick James
Rick James

Reputation: 142218

Bill has the immediate answer. But I suggest that you will get bigger and bigger documents, and be hitting one limit after another.

Meanwhile, unless you have a very recent MySQL, changing the innodb_log_file_size is a pain.

If you ever get to 1GB, you will hit the limit for max_allowed_packet. Even if you got past that, then you will hit another hard limit -- 4GB, the maximum size of LONGBLOB or LONGTEXT.

I suggest you either bite the bullet

Plan A: Put documents in the file system, or

Plan B: Chunk the documents into pieces, storing into multiple BLOB rows. This will avoid all limits, even the 4GB limit. But the code will be messy on input and output.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562260

It's not surprising that you got an error, because the default innodb log file size is 48MB (50331648 bytes).

Your innodb log file size must be at least 10x the size of your largest blob that you try to save. In other words, you can save a blob only if it's no larger than 1/10th the log file size. This started being enforced in MySQL 5.6; before that it was recommended in the manual, but not enforced.

You can change the log file size, but it requires restarting the MySQL Server. The steps are documented here: https://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html

P.S. As for the comments about storing images in the database vs. as files on disk, this is a long debate. Some people will make unequivocal statements that it's bad to store images in the database, but there are pros and cons on both sides of the argument. See my answer to Should I use MySQL blob field type?

Upvotes: 1

Related Questions