MySQL Bugs: #31153: calling stored procedure crashes server if available memory is low

Bug #31153	calling stored procedure crashes server if available memory is low
Submitted:	23 Sep 2007 9:01	Modified:	21 Dec 2007 16:54
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1, 5.0	OS:	Any
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any
Tags:	bfsm_2007_10_18, crash, sp cache

Description:
This is a followup to bug #24757 "No way to limit memory consumption of some server subsystems."

When mysql gets low on memory the server doesn't handle it gracefully in all cases:

070923 10:28:14 [Note] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: ready for connections.
Version: '5.0.48-enterprise-gpl-debug'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Enterprise Server - Debug (GPL)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 [ERROR] /home/sbester/server/5.0/mysql-enterprise-gpl-5.0.48-linux-i686-glibc23/bin/mysqld: Out of memory (Needed 129036 bytes)
070923 10:33:21 - mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=1048576000
read_buffer_size=131072
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1241599 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8b55530
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x808407bc, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x81901c5 handle_segfault + 427
0x811117b st_lex::st_lex() + 11
0x82b0111 sp_head::reset_lex(THD*) + 135
0x81b2659 MYSQLparse(void*) + 7489
0x82b69dd db_load_routine(THD*, int, sp_name*, sp_head**, unsigned long, char const*, char const*, char const*, st_sp_chistics&, char const*, long long, long long) + 607
0x82b66f9 db_find_routine(THD*, int, sp_name*, sp_head**) + 955
0x82b93cf sp_cache_routines_and_add_tables_aux(THD*, st_lex*, Sroutine_hash_entry*, bool, bool*) + 279
0x82b9551 sp_cache_routines_and_add_tables(THD*, st_lex*, bool, bool*) + 29
0x81ca6ba open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int) + 226
0x81cad28 open_and_lock_tables(THD*, TABLE_LIST*) + 66
0x81aa717 mysql_execute_command(THD*) + 17745
0x81acfe2 mysql_parse(THD*, char const*, unsigned int, char const**) + 334
0x81a5075 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1663
0x81a49ea do_command(THD*) + 442
0x81a3ca4 handle_one_connection + 812
0x40045aa7 _end + 933199639
0x40176c2e _end + 934449310
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8b69548 = call `p45403`()
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

How to repeat:
create many stored procedures in a single connection, without calling drop procedure..

this behaviour is know, and can be found here: http://forge.mysql.com/wiki/MySQL_Internals_Stored_Programs#Stored_Program_Caches

run the attached .php script to create 100K sp's.

Suggested fix:
1) fix the crash in this bug.
2) see the ultimate solution in bug #24757

start mysqld with --key_buffer_size=1000M to give it a headstart in memory usage. run testcase.

Attachment: bug31153_testcase.php (application/octet-stream, text), 1.86 KiB.

progress report: I get an error in sp_head::reset_lex trying to use the overloaded new-operator to get a st_lex-object. For some reason the constructor is executed even though malloc is returning null.

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/36038

ChangeSet@1.2536, 2007-10-22 15:30:52+02:00, thek@adventure.(none) +8 -0
  Bug #31153 calling stored procedure crashes server if available memory is low
  
  When the server was out of memory it crashed because of invalid memory access.
  
  This patch adds detection for failed memory allocations and have the server
  output a proper error message.

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/36656

ChangeSet@1.2536, 2007-10-30 15:29:30+01:00, thek@adventure.(none) +9 -0
  Bug #31153 calling stored procedure crashes server if available memory is low
  
  When the server was out of memory it crashed because of invalid memory access.
  
  This patch adds detection for failed memory allocations and make the server
  output a proper error message.

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/38063

ChangeSet@1.2536, 2007-11-19 15:39:17+01:00, thek@adventure.(none) +7 -0
  Bug #31153 calling stored procedure crashes server if available memory is low
  
  When the server was out of memory it crashed because of invalid memory access.
  
  This patch adds detection for failed memory allocations and make the server
  output a proper error message.

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/38080

ChangeSet@1.2536, 2007-11-19 17:59:44+01:00, thek@adventure.(none) +7 -0
  Bug #31153 calling stored procedure crashes server if available memory is low
  
  When the server was out of memory it crashed because of invalid memory access.
  
  This patch adds detection for failed memory allocations and make the server
  output a proper error message.

Patch revisited to fix:
1. Don't generate warnings in push_warnings
2. Don't crash because of double free on vio-object.
3. Cover more execution paths which might be affected by this test case.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

The server crashed in the parser when running out of memory. Memory
handling in the parser has been improved to gracefully return an
error when out-of-memory conditions occur in the parser.