0

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.

tadman
  • 208,517
  • 23
  • 234
  • 262
Dan3460
  • 95
  • 3
  • 13
  • 3
    Why are you storing the files in the DB anyway? https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay – juergen d Aug 29 '17 at 16:56
  • Yeah, files in the database = bad idea.... also can you add your table definition? – Hackerman Aug 29 '17 at 16:58
  • We research the issue of blobs and files inside databases. There are many passionate opinions on both sides and probably the truth is somewhere in the middle. In our case the two most compelling issues were the simplicity of having the whole database contained in one unit and the ease of programming the application as there is no need to keep track of where the files are stored and the mnemonics for keeping track of their names. – Dan3460 Aug 29 '17 at 17:44
  • @Dan3460, Right: backups, data integrity, access privileges, etc. are simpler when you have images within the db. But the db gets a lot bigger! Pros and cons both ways. There's no single right answer for all projects, no matter what some people say. – Bill Karwin Aug 29 '17 at 17:54

2 Answers2

1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the answer, the article talks about changing the my.cfn file. In my setup my.cnf file has only two entries including two directories, one of them has a file called mySql.cnf. The parameter innodb_log_file_size is not in any file that ends on cnf. So i guess i can added. Is that correct? – Dan3460 Aug 29 '17 at 17:39
  • Yes, you can add entries to that file. There are hundreds of options you can put in that file, and any that are absent have defaults. Read https://dev.mysql.com/doc/refman/5.7/en/option-files.html for more information. – Bill Karwin Aug 29 '17 at 17:53
  • 1
    The blob is working, tested it with a 25mb file and stored no problem. Thanks very much. – Dan3460 Aug 29 '17 at 18:29
0

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for the answer Rick. The decision to put the files inside the database was a long and tedious one. It was a lot digging on Google and weighting the many pros and cons positions of a lot of people. This is almost like religion, you are committing a sacrilege if you are in the opposite side. One of the things that played a big role is the size of the documents, the vast majority weighs around 300k, with a handful of exceptions at about 1M and we do not expect this to change. My test was done with a 35M document, just to be in the safe side. – Dan3460 Sep 01 '17 at 21:08