MySQL Bugs: #45445: cannot execute procedures with thread

Bug #45445	cannot execute procedures with thread_stack set to 128k
Submitted:	11 Jun 2009 9:37	Modified:	9 Jan 2015 14:38
Reporter:	Domas Mituzas	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1, (5.0 - lower impact)	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When executing SPs, check_stack_overrun asks for following margins (for safety):

5.0: 96000
5.1: 128000

This stack 'reservation' is very very close to 128k thread_stack limit, endorsed by us, and few other distributions too. 

We do have thread_stack=128k example in support-files/my-small.cnf, and it has been recursively picked up by others.

So, the bug can be interpreted in few different ways:

1) Our SP code thread stack reservation code is way too aggressive
2) Our my.cnf examples are too conservative
3) Lower thread_stack boundaries are too low. 

The code in question:
(gdb) 
#2  0x000e1fa6 in check_stack_overrun (thd=0x101b200, margin=128000, buf=0xb0249eb4 "") at sql_parse.cc:5560
5559	    my_message(ER_STACK_OVERRUN_NEED_MORE, ebuff, MYF(ME_FATALERROR));
(gdb) 
#3  0x00277773 in sp_head::execute (this=0x10a1e10, thd=0x101b200) at sp_head.cc:1088
1088	  if (check_stack_overrun(thd, 8 * STACK_MIN_SIZE, (uchar*)&old_packet))

This can also be seen as regression over 5.0 - it didn't hit it that acutely, needed some more acrobatics. 

This is common issue, do note forum posts at:
http://forums.mysql.com/read.php?98,256564,256564

How to repeat:
5.1 reproduction is way easier than 5.0:

1. start mysqld --thread_stack=128k
2. CREATE PROCEDURE xx () SELECT 1;
3. CALL xx();

Suggested fix:
Depending on interpretation of this bug, I suggest one of following

1. making stack reservation less aggressive
2. removing thread_stack=128k from my.cnf example and establishing lower bound for thread_stack somewhere around 192k.

google marker, the error once hit looks like this:

ERROR 1436 (HY000): Thread stack overrun:  4656 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld -O thread_stack=#' to specify a bigger stack.

If you get this error on HP-UX 11.23 (IA64) you may be affected by bug #42213 that was fixed in versions 5.0.85, 5.1.38, 5.4.4.

If on other platforms please note that our minimum recommended stack size is 192k for 32 bit versions and 256k for 64 bit versions. Please use great care and test thoroughly with your workload if you believe it is necessary to use smaller values than these.

Dmitry, 
please change the default in my-small.cnf to 192k:

kostja@ibbur:~/work/5.5-runtime/support-files$ grep thread_stack *.cnf
my-innodb-heavy-4G.cnf:thread_stack = 192K
my-small.cnf:thread_stack = 128K

You're welcome to come up with a more reasonable formula to use 
in sp_head.cc, but I don't know what it could be.

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

3483 Dmitry Shulga	2010-08-18
      Fixed bug #45445 - cannot execute procedures with thread_stack
      set to 128k
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun.

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

3507 Dmitry Shulga	2010-09-20
      Fixed bug #45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value 2 * STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun.

Bug#54138 was closed as a duplicate of this bug.

The test case rpl.rpl_row_sp011 is marked as disabled because of BUG#54138, which is a duplicate of this one. So please re-enable rpl.rpl_row_sp011 when you fix this bug.

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

3519 Dmitry Shulga	2010-10-06
      Fixed bug #45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ mysql-test/collections/default.experimental
        Re-enabled test rpl.rpl_row_sp011.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value 2 * STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun.

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

3523 Dmitry Shulga	2010-10-06
      Fixed bug #45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ mysql-test/collections/default.experimental
        Re-enabled test rpl.rpl_row_sp011.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value 2 * STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun.

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

3157 Dmitry Shulga	2010-10-07
      Fixed bug#45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ mysql-test/collections/default.experimental
        Re-enabled test rpl.rpl_row_sp011*.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value 2 * STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as a second argument value
        in call to check_stack_overrun.

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

3238 Dmitry Shulga	2010-10-12
      A follow up for the patch for Bug#45445, increase stack size
      to fix an sp-error.test failure on Sun Sparc 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/120554

3265 Dmitry Shulga	2010-10-12 [merge]
      A follow up for the patch for Bug#45445, increase stack size
      to fix an sp-error.test failure on Sun Sparc system.

The patch was reverted.

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

3523 Dmitry Shulga	2010-10-19
      Fixed bug#45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ mysql-test/collections/default.experimental
        Re-enabled test rpl.rpl_row_sp011.
     @ sql/sp.cc
        Added checking for stack overrun at functions db_load_routine/sp_find_routine.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun. Added checking for stack overrun
        at functions sp_lex_keeper::reset_lex_and_exec_core/sp_instr_stmt::execute.
     @ sql/sql_parse.cc
        check_stack_overrun modified: allocate buffer for error message
        at heap instead of stack.
        parse_sql modified: added call to check_stack_overrun() before
        parsing of sql statement.

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

3169 Dmitry Shulga	2010-10-20
      Fixed bug#45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ sql/sp.cc
        Added checking for stack overrun at functions
        db_load_routine/sp_find_routine.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun. Added checking for stack overrun
        at functions sp_lex_keeper::reset_lex_and_exec_core/sp_instr_stmt::execute.
     @ sql/sql_parse.cc
        check_stack_overrun modified: allocate buffer for error message
        at heap instead of stack.
        parse_sql modified: added call to check_stack_overrun() before
        parsing of sql statement.

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

3170 Dmitry Shulga	2010-10-21
      Fixed bug#45445 - cannot execute procedures with thread_stack
      set to 128k.
     @ sql/sp.cc
        Added checking for stack overrun at functions
        db_load_routine/sp_find_routine.
     @ sql/sp_head.cc
        sp_head::execute() modified: pass constant value STACK_MIN_SIZE
        instead of 8 * STACK_MIN_SIZE  as second argument value
        in call to check_stack_overrun. Added checking for stack overrun
        at functions sp_lex_keeper::reset_lex_and_exec_core/sp_instr_stmt::execute.
     @ sql/sql_parse.cc
        check_stack_overrun modified: allocate buffer for error message
        at heap instead of stack.
        parse_sql modified: added call to check_stack_overrun() before
        parsing of sql statement.

Pushed to mysql-5.5-runtime.

Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)

Noted in 5.5.7 changelog.

Stored procedures were too aggressive in checking for stack space and 
triggered stack overrun errors too easily.

Setting Staale as lead.
Removing "disabled" tag since actually no test is disabled due to this bug.

Per Alik:

"
WRT 45445: it was pushed for the upcoming 5.5.7, but then reverted.
Then, it was pushed for the upcoming 5.5.7, but again reverted.
So, 45445 is not fixed neither in 5.5.7 nor in 5.5.8.
"

I have removed the 5.5.7 changelog entry.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (version source revid:alexander.nozdrin@oracle.com-20101216173826-ze3y5h450sksotrh) (merge vers: 5.6.1) (pib:23)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)

Note that a fix is not pushed to 5.5.9 either.

BUG#47791 is a duplicate of this bug.