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: | |
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
[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.