Bug #24854 Mixing Searched Case with Simple Case inside Stored Procedure crashes Mysqld
Submitted: 6 Dec 2006 14:39 Modified: 16 Jan 2007 6:40
Reporter: Stefan Haubold Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.32-BK, 5.0.27 OS:Linux (Linux, Debian)
Assigned to: Marc ALFF CPU Architecture:Any
Tags: Mysqld Crash, Searched Case, Signal 11, Simple Case, stored procedure

[6 Dec 2006 14:39] Stefan Haubold
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;
[6 Dec 2006 14:40] Stefan Haubold
Syslog Output

Attachment: bugTest-syslog.txt (text/plain), 8.41 KiB.

[6 Dec 2006 16: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 16: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 21:51] Marc ALFF
See related bug#19194
[12 Dec 2006 0: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 1:17] Marc ALFF
Pushed to 5.1-runtime
[15 Jan 2007 16:08] Marc ALFF
Merged into 5.0.34 and 5.1.15
[16 Jan 2007 6: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.
[11 Jun 2011 7:08] sameer joshi
I am facing this issue now in the latest version of MySQL.(MySQL version 5.5.13 )
i think this bug is reopened in the latest release. can anybody check it n ow.
[9 Nov 2011 12:24] rudi vos
I am having problems with the case stament also. (version()= 5.5.13-log)

Have tried the sample above and it executed successfully.
However get the following then trying to execute a query with case statement 

NOTE : Only fails on the second time that I run the query (first time executes successfully), I have tried this multiple times and fails every second time. Causes entire mysql to crash)
Have tested some other case statements and then execute successfully 

Extract From MYSQL error log
----------------------------

111109 13:04:13 - 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=268435456
read_buffer_size=1048576
max_used_connections=10
max_threads=151
thread_count=6
connection_count=6
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 573110 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0xb328e60
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...
stack_bottom = 0x4a5e90d0 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x39)[0x7cb619]
/usr/sbin/mysqld(handle_segfault+0x379)[0x4fde09]
/lib64/libpthread.so.0[0x388d00eb10]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0xb24b468): SELECT P.ID, HEX(P.ID) GUID,
       P.Level TypeName,
       CASE P.Level
           WHEN 1 THEN PT.PackL1Name
           WHEN 2 THEN PT.PackL2Name
           WHEN 3 THEN PT.PackL3Name
           WHEN 4 THEN PT.PackL4Name
           WHEN 5 THEN PT.PackL5Name
           WHEN 6 THEN PT.PackL6Name
           WHEN 7 THEN PT.PackL7Name
           WHEN 8 THEN PT.PackL8Name
           WHEN 9 THEN PT.PackL9Name
           ELSE 'N/A'
       END TypeDescr, P.Value RecValue,
       SBM.CreatedDT
FROM stockbatchmove_packaging SBM
JOIN packaging P on (SBM.Packaging = P.ID)
JOIN productionorder PO on (P.ProductionOrder = PO.ID)
JOIN producttemplate PT on (PO.ProductTemplate = PT.ID)
WHERE SBM.StockBatchMove =  NAME_CONST('pID',_binary'TR\0 ¥zƒ  á\nÀë œÂ' COLLATE 'binary')

Connection ID (thread ID): 18
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
111109 13:04:13 mysqld_safe Number of processes running now: 0
111109 13:04:13 mysqld_safe mysqld restarted
111109 13:04:13 [Note] Plugin 'FEDERATED' is disabled.
111109 13:04:13 InnoDB: The InnoDB memory heap is disabled
111109 13:04:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111109 13:04:13 InnoDB: Compressed tables use zlib 1.2.3
111109 13:04:13 InnoDB: Using Linux native AIO
111109 13:04:13 InnoDB: Initializing buffer pool, size = 128.0M
111109 13:04:13 InnoDB: Completed initialization of buffer pool
111109 13:04:13 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
111109 13:04:13  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
[9 Nov 2011 12:35] MySQL Verification Team
rudi, it's not suprising 5.5.13 has a problem like this.  you should upgrade to current version, which is 5.5.17.  there was since another CASE related bug fixed (this Bug #24854 is fixed for years already).
[9 Nov 2011 12:59] sameer joshi
rudi,
i got the solution for this issue in my case. Just check the character sets and collation for the tables and columns involved in CASE statements are the same or different. If different this issue occurs.Make it same the issue will be solved.