| Bug #19439 | Client gobbles up all memory on BLOB select | ||
|---|---|---|---|
| Submitted: | 29 Apr 2006 22:49 | Modified: | 13 Jun 2006 13:48 |
| Reporter: | Bill Conlon | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.1.18 | OS: | Linux (Fedora Core 4) |
| Assigned to: | CPU Architecture: | Any | |
[1 May 2006 22:08]
Bill Conlon
Same behavior with 4.1.18-1 #rpm -q mysql-server mysql-server-4.1.18-1.fc4.remi # rpm -q mysql mysql-4.1.18-1.fc4.remi
[1 May 2006 22:10]
Bill Conlon
no change after upgrade to mysql-4.1.18-1.fc4
[2 May 2006 15:01]
MySQL Verification Team
Thank you for the bug report. Could you please test starting the mysql client with the option --quick. Thanks in advance.
[2 May 2006 16:14]
Bill Conlon
--quick seems to work, or at least doesn't gobble memory. It's still writing hyphens to the terminal (I guess to represent binary). My app connects via ODBC. I'll read the docs to see how to set this option, test that way, and report back.
[2 May 2006 16:41]
Bill Conlon
Don't see an option parameter to use --quick from ODBC. My application returns the following error: [MySQL][ODBC 3.51 Driver][mysqld-4.1.18-log]MySQL client run out of memory # rpm -q unixODBC unixODBC-2.2.11-3.FC4.1 # rpm -q MyODBC MyODBC-3.51.09-1
[2 May 2006 17:19]
MySQL Verification Team
Please try the flag: Don't Cache Results. Thanks in advance.
[2 May 2006 18:08]
Bill Conlon
Added to /etc/odbc.ini Option =1048576 This caused my application to consume all RAM (1GB) and had 2GB swap when I killed it.
[30 May 2006 16:25]
MySQL Verification Team
Thank you for the feedback. Besides that option you mentioned try the below option together: http://dev.mysql.com/doc/refman/4.1/en/connection-parameters.html 2097152 Force the use of Forward-only cursor type. In case of applications setting the default static/dynamic cursor type, and one wants the driver to use non-cache result sets, then this option ensures the forward-only cursor behavior.
[12 Jun 2006 17:23]
Bill Conlon
Option = 2097152 works. thanks.
[13 Jun 2006 13:48]
MySQL Verification Team
Thank you for the feedback. I am closing this bug report as not a bug.
Description: mysql> SELECT mime.type, media.filename, media.timestamp, media.size, media.data FROM media_description, mime, media WHERE mime.id = media.mime_type_id AND media.org_id = 301 AND media.id = 230 AND media_description.access_level <= 11;mysql: Out of memory (Needed 6699656 bytes) ERROR 2008 (HY000): MySQL client ran out of memory Some notes. 1. This query ran flawlessly for several years on 3.23. Only appeared with upgrade to: # rpm -q mysql mysql-4.1.16-1.FC4.1 2. This is size dependent. Here is a range of BLOBS, only the four largest are problematic: mysql> SELECT id, size FROM media WHERE size >1000000 ORDER by size ASC; +-----+---------+ | id | size | +-----+---------+ | 52 | 1166071 | | 59 | 1273723 | | 228 | 1275904 | | 225 | 1301894 | | 608 | 1358505 | | 50 | 1986948 | | 57 | 2003508 | | 252 | 2520925 | | 244 | 2520925 | | 611 | 3052673 | | 605 | 3129910 | | 51 | 3367845 | | 60 | 3480311 | | 598 | 4135312 | | 245 | 4224084 | | 231 | 4912128 | | 246 | 4915442 | | 230 | 6699520 | | 31 | 7179054 | +-----+---------+ 19 rows in set (2.51 sec) So somewhere between 4224084 and 4912128 bytes the problem begins. 3. When observing top, mysql memory starts out at 16M and climbs to over 1GB whereupon the error is issued. How to repeat: Here's the table structure: mysql> describe media -> ; +--------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+-------------------+----------------+ | id | int(11) | | PRI | NULL | auto_increment | | mime_type_id | int(11) | YES | | NULL | | | size | int(11) | YES | | NULL | | | data | mediumblob | YES | | NULL | | | timestamp | timestamp | YES | | CURRENT_TIMESTAMP | | | org_id | int(11) | YES | | NULL | | | filename | varchar(127) | YES | | NULL | | | entity_tag | int(11) | | | 0 | | +--------------+--------------+------+-----+-------------------+----------------+ 8 rows in set (0.00 sec) Just put a big file into the data column and try to Select it.