Bug #23443 user-defined variables can consume too much memory in the server
Submitted: 18 Oct 2006 21:03 Modified: 16 Jan 2007 5:41
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S1 (Critical)
Version:5.0.26, 4.1.22, 5.1.12, 4.0.27, 3.23.55 OS:Any (*)
Assigned to: Tomash Brechko CPU Architecture:Any
Tags: bfsm_2006_11_02, crash, DoS, Memory, OOM, user defined variables

[18 Oct 2006 21:03] Shane Bester
Description:
user-defined variables are useful, but if misused may cause the server to allocate too much memory, and cause a DoS scenario to other users.

run the testcase, which should start failing with errors like:
Out of memory (Needed 21888 bytes)
Out of memory (Needed 21888 bytes)
Out of memory (Needed 21888 bytes)

On my server with 2GB ram, mysqld started swapping and eventually used > 1.8G of memory.

How to repeat:
<?php
$link=mysqli_connect("127.0.0.1","root","") or die("can't connect");
for($i=0;$i<100000;$i++)
{
	mysqli_query($link,"set @var".$i."=repeat('a',100000)") or print(mysqli_error($link) . "\n");
}
mysqli_close($link);
?>

Suggested fix:
As with prepared statements, I think there should be some limits enforced.
[18 Oct 2006 21:18] MySQL Verification Team
May also fail with the following misleading error messages:
You may only use constant expressions with SET
You may only use constant expressions with SET
[18 Oct 2006 22:13] MySQL Verification Team
typical out of memory crash provoked by this bug

Attachment: crash.txt (plain/text, text), 1.94 KiB.

[18 Oct 2006 22:14] MySQL Verification Team
php app that will cause the above crash. simply 'group by' on 5 int's

Attachment: vars.php (application/octet-stream, text), 653 bytes.

[24 Nov 2006 15:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15808

ChangeSet@1.2551, 2006-11-24 18:17:49+03:00, kroki@moonlight.intranet +3 -0
  BUG#23443: user-defined variables can consume too much memory in the
             server
  
  The problem was that when memory was exhausted HEAP engine could crash
  (SQL user variables are stored in it).  Alternatively, if SET was used,
  it could report an error "You may only use constant expressions with SET"
  instead of "Out of memory (Needed NNNNNN bytes)".
  
  The solution is:
   - pass MY_WME to (some) calls to my_malloc() to get correct message.
   - fix heap_write() so that it won't assume that the only possible error
     is HA_ERR_FOUND_DUPP_KEY, and won't initiate key deletion that would
     lead to a crash.
  
  No test case is provided because we can't test out-of-memory behaviour
  in our current test framework.
[1 Dec 2006 13:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16303

ChangeSet@1.2551, 2006-12-01 16:00:05+03:00, kroki@moonlight.intranet +3 -0
  BUG#23443: user-defined variables can consume too much memory in the
             server
  
  The problem was that when memory was exhausted HEAP engine could crash
  (SQL user variables are stored in it).  Alternatively, if SET was used,
  it could report an error "You may only use constant expressions with SET"
  instead of "Out of memory (Needed NNNNNN bytes)".
  
  The solution is:
   - pass MY_WME to (some) calls to my_malloc() to get correct message.
   - fix heap_write() so that the first key is skipped during cleanup
     on ENOMEM because it wasn't inserted and doesn't have to be deleted.
  
  No test case is provided because we can't test out-of-memory behaviour
  in our current test framework.
[1 Dec 2006 15:22] Konstantin Osipov
Bug#24757 "No way to limit memory consumption of some server subsystems" was created to track the feature request to limit the total amount of memory consumed by user variables.
[9 Jan 2007 9:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17765

ChangeSet@1.2198, 2007-01-09 12:24:25+03:00, kroki@moonlight.home +3 -0
  BUG#23443: user-defined variables can consume too much memory in the
             server
  
  The problem was that when memory was exhausted HEAP engine could crash
  (GROUP BY uses HEAP TABLE).  Alternatively, if SET was used, it could
  report an error "You may only use constant expressions with SET" instead
  of "Out of memory (Needed NNNNNN bytes)".
  
  The solution is:
   - pass MY_WME to (some) calls to my_malloc() to get correct message.
   - fix heap_write() so that the first key is skipped during cleanup
     on ENOMEM because it wasn't inserted and doesn't have to be
     deleted.
  
  No test case is provided because we can't test out-of-memory behaviour
  in our current test framework.
[9 Jan 2007 13:04] Tomash Brechko
_Pushed_ to 4.0, and queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[12 Jan 2007 9:45] Konstantin Osipov
4.0 version is 4.0.29
[15 Jan 2007 16:14] Marc ALFF
Pushed earlier to 4.0.29,
Merged into 4.1.23, 5.0.34 and 5.1.15
[16 Jan 2007 5:41] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.0.29, 4.1.23, 5.0.34, aqnd 5.1.15 changelogs.