...and so I'm tempted for the third time to bring up my issue which has to
date resulted in no response: BLOB columns causing various, seemingly random
"out of memory" errors on FreeBSD. Is this problem too low-level for the
list? I see plenty of responses to questions about simple queries, even PHP
code examples -- but it seems that more serious questions warrant no reply.
If nobody can help me, could someone at least point me in the right
direction? I've exhausted all my search options, from list archives to bug
reports (turns out mysqlbug just sends an e-mail to this list, and I've
provided most of that information in my e-mails anyway), and found no
references to this issue.
For consistency, I'll quote my second e-mail:
I'm having an issue with BLOB columns (MEDIUMBLOB and LONGBLOB, more
specifically) in a table that's intended to store files (with filename,
content-type and length columns). Generally, the problem seems to be that
MySQL thinks that it's out of memory when dealing with arbirarily large
values, either during an attempted INSERT/UPDATE (using LOAD_FILE() or the
contents of the file as a string) or during a mysqldump.
The problem has manifested on 2 x86/FreeBSD development boxes running the
ports installation of 3.23.46, as well as an OSX box running 3.23.47. The
exact same operations succeed on our production server, a Sparc/Solaris box
running 3.23.36.
The my.cnf's are identical on all 3 machines: a copy of the my-large.cnf
with a modified max_allowed_packet value of '16M' (which should suit a query
that INSERT's a file at least 7MB in size, with escaped characters, etc.).
The specific errors encountered are:
- when attempting an INSERT INTO binaries (contents) VALUES ([str]), where
[str] is the contents of a file over ~2MB, the INSERT fails with "Out of
memory: (xxxxxxx bytes needed)". Subsequent attempts *sometimes* result in
the "MySQL server has gone away" error. The same errors occur when replacing
[str] with LOAD_FILE([filename]).
- when attempting to dump a database containing values of LONGBLOB columns
over ~1MB, mysqldump fails with the same "Out of memory" error above.
- when attempting to feed a dump into a database using the
"mysql DB < dump.sql" method (or source'ing it from the SQL prompt),
values in the same range cause mysql to die with the error "MySQL server
has gone away".
I should also mention that the errors are pretty erratic: the same operation
often yields different errors on each attempt. A recent mysqldump succeeded
on one FreeBSD box, but the attempt to restore it on the other one failed
("MySQL server has gone away"). Several times, attempts to INSERT large
values have resulted in corrupt tables, or NULL values without any error.
Has anyone else experienced any of these symptoms? Our production server
(which routinely handles files in excess of 5MB) hasn't exhibited this issue
yet, and it's running a practically ancient version of MySQL. An
installation of the same version (3.23.36) on one of the FreeBSD boxes
showed no change. We've checked the physical memory and df on all 3 boxes
that we've experienced the problems, and none of them even come close to
being out of memory or disk space. We even checked safe_mysqld to see if it
calls limit or ulimit... which it doesn't. I'm totally stumped.
> There are some good reasons for wanting to store data directly in the
> database, sometimes.
I'd like to think so, too... but this problem is keeping us from storing
anything larger than 2MB in a column that's supposed to be able to store
16MB worth of data. If we can't make this work, then we'll be forced to
abandon MySQL, and I would really hate to do that!
cheers,
--
shawn allen | [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
