MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

Question

I'm using a MySQL database to store files.

The table I'm using is structured as follows:

+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| AttachmentID | int(11)  | NO   | PRI | NULL    |       |
| Data         | longblob | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+

The INSERT command I'm using is as simple as possible:

INSERT INTO table (AttachmentID, Data) VALUES (attachmentID, attachmentData);

Obviously, where attachmentID is an int, and attachmentData is a large byte array.

Having read several guides on how to store files in a database, I increased the max_allowed_packet setting in the config file to "512M", more than enough for the files that I would actually be inserting.

This worked fine for several files of 30MB - 40MB. However, now that I am inserting larger files in the 90MB+ range, I am recieving an "Out of Memory" exception, with a number of bytes needed equal to the size of the file I tried to insert.

The server is a virtual server, with 4GB allocated and nothing else running that could be interfering.

Why do I get an Out of Memory error for the larger files, and not the smaller? Where is this file-size limitation arising?

I've included my config file below:

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-locking
key_buffer_size = 256M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
max_allowed_packet = 512M

ft_min_word_len=2

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

log-bin=mysql-bin
binlog_format=mixed
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

---

EDIT: I can successfully INSERT a file of size 41,586 KB, but not one of 44,119 KB. The limit is somewhere between them.

Answer 1

I saw an interesting answer to a question about the biggest blob you may have

Here is the statement I saw in ServerFault

innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

I did not see any InnoDB Settings in your my.cnf

I see you disabled the InnoDB Built-in and you plugged in another InnoDB

See if you can adjust innodb_log_file_size and innodb_log_buffer_size to accommodate.

Since you only have 4GB of RAM reserved, try making your InnoDB Log Files Much Bigger

Change innodb_log_file_size to 1G in /etc/my.cnf

1. service mysql stop
2. sleep 30
3. rm -f /var/lib/mysql/in_logfile[01]
4. service mysql start (recreates ib_logfile0 and ib_logfile1)

Give it a Try !!!

Answer 2

This is not the answer per se, but a very good advice for this topic:

Don't store files that big in a database.

Why are they supposed to be stored in a DB anyway? Accessing it via the filesystem is way more efficient to handle and you avoid massive bloat from the tables. If these files are changed often (don't know if this is the case in your scenario), there is also a massive load on the DB-server. Reading the files is also way faster since a webserver+filesystem is there for sending files to a client (or somewhere else).

It's is (IMO) a better practice to just store the path of the file in the database as a reference. If you do this for protecting your files, you should use either .htaccess or block direct file access to your files via the webserver itself and use a wrapper around these file (in PHP, something like ...file.php?file=myfile.pdf so that ACL and other cecks can be performed, reading that you use .NET, I am sure there is something like this available, too) or use other machenisms your server/language provides.

Answer 3

You should also check your front end application/programming language. For example, if you are using PHP, check the php.ini file (or ) for upload_max_filesize and post_max_size. Set these to your desired value: upload_max_filesize = 100M post_max_size = 100M.