I've encountered a similar problem which seems to stem from a "larger"
problem of MySQL thinking it's out of memory. It manifests itself in several
ways:
- when INSERT'ing a row into a table which stores binary data in a LONGBLOB
column, I get one of several "out of memory" errors ("Out of memory (needed
xxxxxxx bytes)" or "error 12 from table handler"). This seems to occurr
regardless of whether I create an INSERT query with the contents of the
LONGBLOB column as a string or using LOAD_FILE().
- when attempting to dump the database after successfully inserting large
rows, I get an "Out of memory" error.
- when the INSERT does succeed (no errors), the resulting value of the
LONGBLOB column is NULL.
- sometimes, the table that stores these files has become corrupt simply by
attempting to insert a new row (mysql then complains that "binaries.MYD"
does not exist, which turns out to be true).
These problems only occurr when the file I'm attempting to INSERT is about
3MB or greater in size. They also manifest regardless of whether I'm
connected locally or not (so I know it's not the mysql client on my personal
machine). My my.cnf is a copy of the my-large.cnf packaged with the
distribution (3.23.46 source), with max_allowed_packets changed to 16MB to
allow queries of a length required for relatively large files.
The server in question is a development box running FreeBSD with 512MB RAM,
with ample disk space on the /var partition (where mysql's datadir resides).
What's also strange is that this problem has only surfaced now. We run an
older version of a content management system that handles INSERT's with
files in excess of 10MB on our development server (a Sun box running an
almost ancient 3.23.36, with the *exact* same my.cnf). Our newer CMS handles
the uploads the same way -- an INSERT using the contents of the file as a
string. Since we're planning on scaling to a separate database server once
we get more machines, we can't rely on LOAD_DATA() (which doesn't seem to
solve the problem anyway).
I'd appreciate any insight anyone may have into this issue.
--
shawn allen | [EMAIL PROTECTED]
| 415 577 3961
| aim: shawnpallen
---------------------------------------------------------------------
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
