MySQL Bugs: #6048: Stored procedure causes operating system reboot

Bug #6048	Stored procedure causes operating system reboot
Submitted:	12 Oct 2004 17:14	Modified:	24 May 2005 9:25
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.2-alpha-debug	OS:	Linux (SuSE 8.2)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
I have a stored procedure which does arithmetic only. I call it, passing a value which is 
used to indicate the number of times that a loop within the procedure must be repeated. 
Calling it with small values causes no harm, but when I pass 10,000,000 there is a five 
minute pass, then the operating system (SuSE 8.2) reboots. I suppose that with other 
operating systems the effect might be less harmful, but my advice is: save everything you 
are doing before trying to repeat this. 
 

How to repeat:
mysql> CREATE PROCEDURE p4 (parameter1 int) begin declare v1,v2 decimal(16,12); 
declare v3,v4 decimal(16,0); declare v5 int; set v1 = 1; set v2 = 2; set v3 = 1 * 
parameter1; set v4 = 2 * parameter1; set v5 = 0; while v5 < parameter1 do set v1 = v1 + 1 
/ parameter1; set v2 = v2 - 1 / parameter1; set v3 = v3 + 1; set v4 = v4 - 1; set v5 = v5 + 
1; end while; select v1, v2, v3, v4, v5, v3 * (1 / parameter1), v4 * (1 / parameter1); end// 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> call p4(10)// 
+------+------+----+----+----+-----------------------+-----------------------+ 
| v1   | v2   | v3 | v4 | v5 | v3 * (1 / parameter1) | v4 * (1 / parameter1) | 
+------+------+----+----+----+-----------------------+-----------------------+ 
| 2.00 | 1.00 | 20 | 10 | 10 |                  2.00 |                  1.00 | 
+------+------+----+----+----+-----------------------+-----------------------+ 
1 row in set (0.27 sec) 
 
Query OK, 0 rows affected (0.27 sec) 
 
mysql> call p4(1000)// 
+------+------+------+------+------+-----------------------+-----------------------+ 
| v1   | v2   | v3   | v4   | v5   | v3 * (1 / parameter1) | v4 * (1 / parameter1) | 
+------+------+------+------+------+-----------------------+-----------------------+ 
| 2.00 | 1.00 | 2000 | 1000 | 1000 |                  2.00 |                  1.00 | 
+------+------+------+------+------+-----------------------+-----------------------+ 
1 row in set (0.01 sec) 
 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> call p4(100000)// 
+------+------+--------+--------+--------+-----------------------+-----------------------+ 
| v1   | v2   | v3     | v4     | v5     | v3 * (1 / parameter1) | v4 * (1 / parameter1) | 
+------+------+--------+--------+--------+-----------------------+-----------------------+ 
| 2.00 | 1.00 | 200000 | 100000 | 100000 |                  2.00 |                  1.00 | 
+------+------+--------+--------+--------+-----------------------+-----------------------+ 
1 row in set (0.94 sec) 
 
Query OK, 0 rows affected (0.94 sec) 
 
mysql> call p4(1000000)// 
+------+------+---------+---------+---------+-----------------------+-----------------------+ 
| v1   | v2   | v3      | v4      | v5      | v3 * (1 / parameter1) | v4 * (1 / parameter1) | 
+------+------+---------+---------+---------+-----------------------+-----------------------+ 
| 2.00 | 1.00 | 2000000 | 1000000 | 1000000 |                  2.00 |                  1.00 | 
+------+------+---------+---------+---------+-----------------------+-----------------------+ 
1 row in set (15.56 sec) 
 
Query OK, 0 rows affected (15.59 sec) 
 
mysql> call p4(10000000)//

I can confirm strange behavior on my Slackware box, it not reboots but some
application was closed like XChat and the Konsole shells.

The reason is known. (It's consuming memory during expression evaluation which is not released until the end of execution).
Whether it actually causes a reboot depends on the operating system's ability to gracefully handle the "out of memory" condition. (On my old redhat 7.3 it just killed
the offending mysqld processes.)

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/internals/25015

The first patch had to be reverted, trying out a new approach...

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/internals/25195