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

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_buffe

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

• service mysql stop

• sleep 30

• rm -f /var/lib/mysql/in_logfile[01]

• service mysql start (recreates ib_logfile0 and ib_logfile1)

Give it a Try !!!

StackExchange Database Administrators Q#2273, answer score: 3