MySQL Bugs: #56744: Stored procedure - 1436 (HY000): Thread stack overrun

Bug #56744	Stored procedure - 1436 (HY000): Thread stack overrun
Submitted:	13 Sep 2010 8:21	Modified:	13 Sep 2010 9:00
Reporter:	Ulf Wendel	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.5-m3-debug	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
Calling a procedure with an output parameter of type varchar(25) causes ERROR 1436 (HY000): Thread stack overrun:  8320 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.

This bug breaks at least two Connector/C++ tests. Does this justify "affects customer" flag? Both tests work fine and pass with MySQL 5.1. 

MySQL 5.5.5-m3 has been build from source using BUILD/compile-pentium-debug. The computer runs on OpenSuSE 11.2.

uname -a
Linux linux-fuxh 2.6.31.12-0.2-desktop #1 SMP PREEMPT 2010-03-16 21:25:39 +0100 x86_64 x86_64 x86_64 GNU/Linux

How to repeat:
mysql> DROP PROCEDURE IF EXISTS p ;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE p(IN ver_in VARCHAR(25), OUT ver_out VARCHAR(25)) BEGIN  SELECT ver_in INTO ver_out; END //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.5.5-m3-debug |
+----------------+
1 row in set (0.00 sec)

mysql> call p('myver', @version);
ERROR 1436 (HY000): Thread stack overrun:  8320 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.

Suggested fix:
Make it like 5.1 :

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP PROCEDURE IF EXISTS p ;
Query OK, 0 rows affected (0.02 sec)

mysql>  delimiter //
mysql>  CREATE PROCEDURE p(IN ver_in VARCHAR(25), OUT ver_out VARCHAR(25)) BEGIN  SELECT ver_in INTO ver_out; END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.45-debug |
+--------------+
1 row in set (0.01 sec)

mysql>  call p('myver', @version);
Query OK, 0 rows affected (0.00 sec)

mysql> select @version;
+----------+
| @version |
+----------+
| myver    |
+----------+
1 row in set (0.00 sec)

Sorry, but I can not repeat this with current mysql-5.5 tree:

openxs@ubuntu:/home2/openxs/dbs/5.5$ bin/mysql --no-defaults -uroot --socket=/tmp/vk.sock test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP PROCEDURE IF EXISTS p ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE p(IN ver_in VARCHAR(25), OUT ver_out VARCHAR(25)) BEGIN  SELECT
    -> ver_in INTO ver_out; END //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.5.7-m3-debug |
+----------------+
1 row in set (0.00 sec)

mysql> call p('myver', @version);
Query OK, 1 row affected (0.01 sec)

mysql> select @version;
+----------+
| @version |
+----------+
| myver    |
+----------+
1 row in set (0.00 sec)

Looks like the bug is already fixed.

Jumping to Ulf's defense here: I can reproduce the same error using mysql-5.5.8 compiled from standard source distribution on Ubuntu Linux 10.04 64-Bit. I used the my-small.cnf config template and modified it slightly by uncommenting out the lines about innodb and adding default_storage_engine=innodb and datadir=/opt/mysql-5.5/data because I installed mysql in /opt/mysql-5.5 for testing purposes.

Comparing the test configuration to /etc/mysql/my.cnf file for that's part of the default 5.1 version that came with Ubuntu 10.04, I see that the "small" config only uses 128K vs. 2M in my regular setup. Maybe 128K is a little too small and the error suggests to up the value to something bigger. But why would the vanilla my-small.cnf suggest such a low thread_stack if it causes problems even with such a minimalistic stored procedure such as Ulf's test case?

A quick update on the topic: the error message went away by setting thread_stack=256K in my.cnf.

thanks -- setting thread_stack=256K in my.cnf allowed sakila-data.sql of http://dev.mysql.com/doc/sakila/en/sakila-installation.html to complete on a new MySQL 5.1.44 XAMPP install on Mac OS X 10.8.3.