| Bug #24854 | Mixing Searched Case with Simple Case inside Stored Procedure crashes Mysqld | ||
|---|---|---|---|
| Submitted: | 6 Dec 2006 15:39 | Modified: | 16 Jan 2007 7:40 |
| Reporter: | Stefan Haubold | ||
| Status: | Closed | ||
| Category: | Server | Severity: | S1 (Critical) |
| Version: | 5.0.32-BK, 5.0.27 | OS: | Linux (Linux, Debian) |
| Assigned to: | Marc Alff | Target Version: | |
| Tags: | Signal 11, Mysqld Crash, Searched Case, Simple Case, stored procedure | ||
[6 Dec 2006 15:40]
Stefan Haubold
Syslog Output
Attachment: bugTest-syslog.txt (text/plain), 8.41 KiB.
[6 Dec 2006 17:07]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.32-BK on Linux:
mysql> delimiter //
mysql> CREATE PROCEDURE `bugTest` (
-> pTestString1 varchar(50),
-> pTestString2 varchar(50)
-> )
-> BEGIN
-> DECLARE dummy VARCHAR(50);
-> -- Searched Case Statement
-> CASE
-> WHEN pTestString1 = 'hallo' THEN
-> -- Simple Case Statement
-> CASE pTestString2
-> WHEN '1' THEN
-> SET dummy = 1;
-> WHEN '2' THEN
-> SET dummy = 2;
-> END CASE;
-> WHEN pTestString1 = 'hallo2' THEN
-> SET dummy = 3;
-> ELSE
-> SET dummy = 4;
-> END CASE;
-> SELECT dummy;
-> END//
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> CALL bugTest('hallo1','1');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
Number of processes running now: 0
061206 17:32:45 mysqld restarted
mysql> select version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: test
+--------------+
| version() |
+--------------+
| 5.0.32-debug |
+--------------+
1 row in set (0.21 sec)
[6 Dec 2006 17:10]
Valeriy Kravchuk
Resolved stack trace: openxs@suse:~/dbs/5.0> bin/resolve_stack_dump -s /tmp/mysqld50.sym -n 24854.st ack 0x81d96b0 handle_segfault + 412 0x8145778 _ZN16Item_sp_variable10fix_fieldsEP3THDPP4Item + 44 0x81665b1 _ZN9Item_func10fix_fieldsEP3THDPP4Item + 223 0x833b6dc _Z20sp_prepare_func_itemP3THDPP4Item + 128 0x834136a _ZN20sp_instr_jump_if_not9exec_coreEP3THDPj + 24 0x83409c3 _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 385 0x834134d _ZN20sp_instr_jump_if_not7executeEP3THDPj + 151 0x833d851 _ZN7sp_head7executeEP3THD + 1157 0x833ed6b _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 1259 0x81f6567 _Z21mysql_execute_commandP3THD + 20591 0x81f956b _Z11mysql_parseP3THDPcj + 475 0x81efdb9 _Z16dispatch_command19enum_server_commandP3THDPcj + 1951 0x81ef60a _Z10do_commandP3THD + 526 0x81ee7f6 handle_one_connection + 982 0x40050aa7 _end + 932067271 0x40247c2e _end + 934127950
[8 Dec 2006 22:51]
Marc Alff
See related bug#19194
[12 Dec 2006 1:00]
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/16807 ChangeSet@1.2286, 2006-12-11 16:59:02-07:00, malff@weblab.(none) +4 -0 Bug#19194 (Right recursion in parser for CASE causes excessive stack usage, limitation) Bug#24854 (Mixing Searched Case with Simple Case inside Stored Procedure crashes Mysqld) Implemented code review (19194) comments
[13 Dec 2006 2:17]
Marc Alff
Pushed to 5.1-runtime
[15 Jan 2007 17:08]
Marc Alff
Merged into 5.0.34 and 5.1.15
[16 Jan 2007 7:40]
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 5.0.34 and 5.1.15 changelogs.
Description: I've run in a serious problem with stored Procedures. I had written a stored procedure which restarted mysql if it was executed whith some special params. It took me a half day to figure out what caused the Crash. I added a Testprocedure whicht does a restart on a Debian running Mysql 5.0.27 and on a differen Debian Server with Mysql 5.0.24. On Both system an execute of this procedure causes a Signal 11 restart of the Mysql Server. After the restart everything, the Server comes up again and works as if nothing had happend. The crash only occurs if NOT the first case matches. So if you call the Test Procedure whith 'hallo' as first param, it works. Running it with 'hallo1' mysql crashes. If you Change the first Case from a Searched Case Statement to a Simple Case Statement or Change the inner Case accordingly, everything works. I think the syntax of the Case Statements is completly valid. So this shouldn't be a problem. If there is an error with the syntax, mysql shouldn't restart anyway. I'll add the logging output from syslog after the bug is opened How to repeat: Create the following Procedure in your Database Server. Then Run it like this: CALL bugTest('hallo1','1'); DELIMITER $$ CREATE PROCEDURE `bugTest` ( pTestString1 varchar(50), pTestString2 varchar(50) ) BEGIN DECLARE dummy VARCHAR(50); -- Searched Case Statement CASE WHEN pTestString1 = 'hallo' THEN -- Simple Case Statement CASE pTestString2 WHEN '1' THEN SET dummy = 1; WHEN '2' THEN SET dummy = 2; END CASE; WHEN pTestString1 = 'hallo2' THEN SET dummy = 3; ELSE SET dummy = 4; END CASE; SELECT dummy; END$$ DELIMITER ; Suggested fix: For Now, just use the same Case Type inside another Case , don't mix both. Worked for me. For example, changing the Test Case like this, fixes the problem: CASE WHEN pTestString1 = 'hallo' THEN -- Simple Case Statement CASE WHEN pTestString2 = '1' THEN SET dummy = 1; WHEN pTestString2 = '2' THEN SET dummy = 2; END CASE; WHEN pTestString1 = 'hallo2' THEN SET dummy = 3; ELSE SET dummy = 4; END CASE;