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

[12 Oct 2004 17:14] Peter Gulutzan
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)//
[12 Oct 2004 21:33] Miguel Solorzano
I can confirm strange behavior on my Slackware box, it not reboots but some
application was closed like XChat and the Konsole shells.
[22 Oct 2004 8:17] Per-Erik Martin
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.)
[18 May 2005 9:31] 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/internals/25015
[23 May 2005 10:12] Per-Erik Martin
The first patch had to be reverted, trying out a new approach...
[23 May 2005 22:07] 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/internals/25195