Bug #37228 Sever crashes when creating stored procedure with more than 10 IF/ELSEIF
Submitted: 5 Jun 2008 15:27 Modified: 12 Sep 2008 15:53
Reporter: Alexander Rubin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.51+, 5.1.23+ OS:Any
Assigned to: Marc ALFF CPU Architecture:Any
Tags: elseif, stored procedure

[5 Jun 2008 15:27] Alexander Rubin 
Description:
When creating stored procedure with more than 10 IF/ELSEIF server crashes on 64bit and syntax error occurs on 32bit

Creating SP with many ELSEIF (see bellow for an example)

64bit:
...
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    ->
    -> end if;
    ->
    -> END $$

delimiter ;

select 1;ERROR 2013 (HY000): Lost connection to MySQL server during query

In error log:

080605 17:19:31 - 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=4294967296
read_buffer_size=131072
max_used_connections=1
max_threads=400
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5068513 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x4874b10
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...
Cannot determine thread, fp=0x450691b8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x4874b10 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html
and follow instructions on how to resolve the stack trace.
Resolved stack trace is much more helpful in diagnosing the
problem, so please do resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x48cc810 = CREATE procedure testlongproc (        IN p_input INTEGER UNSIGNED,
        OUT p_output INTEGER
)
BEGIN

if (p_input>1) then set p_output = 1;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_inpu
thd->thread_id=1
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.

32bit:
...
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    ->
    -> end if;
    ->
    -> END $$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that                                                                                             corresponds to your MySQL server version for the right syntax to use near ';
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000' at line 20

MySQL versions 5.0.36, 5.0.45 are NOT AFFECTED:

5.0.45:
...
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    -> ELSEIF ( p_input < 83000000 ) then set p_output = 23;
    ->
    -> end if;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

How to repeat:
DELIMITER $$

DROP procedure if exists testlongproc $$

CREATE procedure testlongproc (        IN p_input INTEGER UNSIGNED,
        OUT p_output INTEGER
)
BEGIN

if (p_input>1) then set p_output = 1; 
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;
ELSEIF ( p_input < 83000000 ) then set p_output = 23;

end if;

END $$

delimiter ;

select 1;

Suggested fix:
fix mysql, so it will not crash or issue syntax error
[5 Jun 2008 15:35] Alexander Nozdrin 
Just to make to more clear.
This one fails:
---------------------------------------------
DELIMITER |

DROP PROCEDURE IF EXISTS p1|

CREATE PROCEDURE p1 ()
BEGIN
  DECLARE v INT DEFAULT 123;

  IF (v > 1) THEN SET v = 1; 
  ELSEIF (v < 83000000) THEN SET v = 20;
  ELSEIF (v < 83000000) THEN SET v = 21;
  ELSEIF (v < 83000000) THEN SET v = 22;
  ELSEIF (v < 83000000) THEN SET v = 23;
  ELSEIF (v < 83000000) THEN SET v = 24;
  ELSEIF (v < 83000000) THEN SET v = 25;
  ELSEIF (v < 83000000) THEN SET v = 26;
  ELSEIF (v < 83000000) THEN SET v = 27;
  ELSEIF (v < 83000000) THEN SET v = 28;
  ELSEIF (v < 83000000) THEN SET v = 29;
  END IF;
END|
---------------------------------------------

This one works:
---------------------------------------------
DELIMITER |

DROP PROCEDURE IF EXISTS p1|

CREATE PROCEDURE p1 ()
BEGIN
  DECLARE v INT DEFAULT 123;

  IF (v > 1) THEN SET v = 1; 
  ELSEIF (v < 83000000) THEN SET v = 20;
  ELSEIF (v < 83000000) THEN SET v = 21;
  ELSEIF (v < 83000000) THEN SET v = 22;
  ELSEIF (v < 83000000) THEN SET v = 23;
  ELSEIF (v < 83000000) THEN SET v = 24;
  ELSEIF (v < 83000000) THEN SET v = 25;
  ELSEIF (v < 83000000) THEN SET v = 26;
  ELSEIF (v < 83000000) THEN SET v = 27;
  ELSEIF (v < 83000000) THEN SET v = 28;
  END IF;
END|
---------------------------------------------
[5 Jun 2008 15:37] Alexander Nozdrin 
Valgrind errors

Attachment: qqq.txt (text/plain), 19.76 KiB.
[9 Jun 2008 9:57] MySQL Verification Team 
maybe bug #37269 is a duplicate of this but it didn't use IF ELSE too much so I am not sure.  There are some more testcases attached there, that appear to have same stack trace in the crash.
[9 Jun 2008 15:03] Marc ALFF 
See related
Bug#35577 CREATE PROCEDURE causes either crash or syntax error depending on build
[23 Jul 2008 0:26] Paul DuBois 
Noted in 5.1.28 changelog.

Freeing of an internal parser stack during parsing of complex stored
programs caused a server crash.

Setting report to Need Doc Info pending push into 5.0.x/6.0.x.
[23 Jul 2008 15:41] Paul DuBois 
Noted in 5.0.68 changelog.

Setting report to Need Doc Info pending push of fix into 6.0.x.
[24 Jul 2008 17:46] Paul DuBois 
Noted in 6.0.7 changelog.