Bug #38448 size limit option for mysql_store_result()
Submitted: 30 Jul 2008 8:46 Modified: 30 Jul 2008 8:52
Reporter: Hartmut Holzgraefe Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S4 (Feature request)
Version:5.0, 5.1, all OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: D5 (Feature request)

[30 Jul 2008 8:46] Hartmut Holzgraefe
Description:
As mysql_store_result() fetches the complete result set from the server and stores it client side before providing result rows to the client application
it may exceed the available client memory on very large result sets.

Right now the only thing mysql_store_result() handles is a malloc() failure in which case it will return CR_OUT_OF_MEMORY

Unless memory limits are in effect for the client process (e.g. via ulimit settings) mysql_store_result() will at least temporarily allocate all available memory on the system before finally failing with CR_OUT_OR_MEMORY in the end.

While it temporarily allocates all this memory it might cause trouble for other applications (or other threads within the application) trying to allocate memory at the same time and in the worst case it will even cause an OOM kill of the application on Linux

ulimit settings help to protect other processes from this and prevent OOM kills of the process. They do not help with memory allocation contention within a multithreaded client application though and need to be set before starting the actual application so requiring a wrapper script to start the application with proper setting.

How to repeat:
Create a really big result set, e.g. by doing a full cartesian product on two large tables with big TEXT or BLOB data fields in them.

Best do this on a 32bit Linux system with a result set exceeding 3GB

Suggested fix:
Provide a client side option to limit the max. allocation size available to mysql_store_result() and let the call fail when it reaches the limit
( but still let it consume the remaining rows so that more queries are possible on the connection without running into out-of-sync errors next)

This way a client application can set sensible limits for mysql_store_result() itself and can be sure that these are in effect (opposed to the ulimit workaround)
[4 Nov 2008 16:03] Hartmut Holzgraefe
Another idea besides simply failing when the preconfigured amount of memory is exceeded: store the client side result set in temporary disk file instead of main memory when it exceeds a certain configurable threshold and serve actual application row fetch request from there ... a mechanism similar to tmp_table_size on the server side ...
[2 Oct 2009 12:47] Bernd Ocklin
reminds me off

1. BUG#36701 - joins on BLOB tables do not have their buffers released fast enough
2. BUG#47572 - releases BLOB memory more often in long-running transactions
3. BUG#47573 - BLOB buffer may not be freed correctly
4. BUG#47574 - BLOB buffer calculation could be wrong in scan operations

can you check and possibly simply close?
[12 Oct 2009 14:26] Frazer Clement
Regarding the recently closed Blob bugs - they affect memory usage in the Server, not the mysql client.