Bug #17306 Stored procedures, cursors, loop forever

• Submitted: 10 Feb 2006 16:12
• Modified: 19 Feb 2006 16:04
• Reporter: Nuno Soares
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.18
• OS: Windows (Windows)
• CPU Architecture: Any

[10 Feb 2006 16:12] Nuno Soares

Description:
-> Cursors do not "copy" values to a temp table while they are processed and this may cause infinitive loops if we insert values in the table that we are fetching values.

-> In other cases MySQL Server service stops when i'm calling stored procedures that calls other stored procedures;

How to repeat:
delimiter $$
drop table if exists t1$$
drop table if exists t2$$
drop procedure if exists xSP1$$
drop procedure if exists xSP2$$
drop procedure if exists xSP3$$

create table t1 (t1c1 int not null)$$
insert into t1 (t1c1) values (1)$$
insert into t1 (t1c1) values (2)$$

create table t2 (t2c1 int not null)$$
insert into t2 (t2c1) values (3)$$
insert into t2 (t2c1) values (4)$$

create procedure xSP1()
begin
   declare _t1c1 int;
   declare _cursorDone int default 0;
   declare sp1_cursor cursor for
      select t1c1 from t1;
   declare continue handler for SQLSTATE '02000' set _cursorDone = 1;

   open sp1_cursor;
   repeat
      fetch sp1_cursor into _t1c1;
      if (_cursorDone = 0) then
        call xSP2(_t1c1);
      end if;
   until _cursorDone end repeat;

   close sp1_cursor;
end$$

create procedure xSP2(a int)
begin
   update t2 set t2c1 = t2c1 + a;
   call xSP3(a);
end$$

create procedure xSP3(colT1C1 int)
begin
   declare _x int;
   declare _cursorDone int default 0;
   declare sp3_cursor cursor for
      select t1c1 from t1;
   declare continue handler for SQLSTATE '02000' set _cursorDone = 1;

   open sp3_cursor;
   repeat
      fetch sp3_cursor into _x;
      if (_cursorDone = 0) then
        insert into t1
          select ifnull(max(t1c1), 0) + 1
            from t1;
      end if;
   until _cursorDone end repeat;

   close sp3_cursor;
end$$

delimiter;

call xSP1();

Suggested fix:
I suggest that cursors should have various types. STATIC, DYNAMIC, ... like SQL Server.

[19 Feb 2006 16:04] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I see no problem in your test case:

mysql> create table t1 (t1c1 int not null)$$
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 (t1c1) values (1)$$
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 (t1c1) values (2)$$
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 (t2c1 int not null)$$
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 (t2c1) values (3)$$
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 (t2c1) values (4)$$
Query OK, 1 row affected (0.00 sec)

mysql> create procedure xSP1()
    -> begin
    ->    declare _t1c1 int;
    ->    declare _cursorDone int default 0;
    ->    declare sp1_cursor cursor for
    ->       select t1c1 from t1;
    ->    declare continue handler for SQLSTATE '02000' set _cursorDone = 1;
    ->
    ->    open sp1_cursor;
    ->    repeat
    ->       fetch sp1_cursor into _t1c1;
    ->       if (_cursorDone = 0) then
    ->         call xSP2(_t1c1);
    ->       end if;
    ->    until _cursorDone end repeat;
    ->
    ->    close sp1_cursor;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> create procedure xSP2(a int)
    -> begin
    ->    update t2 set t2c1 = t2c1 + a;
    ->    call xSP3(a);
    -> end$$
Query OK, 0 rows affected (0.00 sec)

mysql> create procedure xSP3(colT1C1 int)
    -> begin
    ->    declare _x int;
    ->    declare _cursorDone int default 0;
    ->    declare sp3_cursor cursor for
    ->       select t1c1 from t1;
    ->    declare continue handler for SQLSTATE '02000' set _cursorDone = 1;
    ->
    ->    open sp3_cursor;
    ->    repeat
    ->       fetch sp3_cursor into _x;
    ->       if (_cursorDone = 0) then
    ->         insert into t1
    ->           select ifnull(max(t1c1), 0) + 1
    ->             from t1;
    ->       end if;
    ->    until _cursorDone end repeat;
    ->
    ->    close sp3_cursor;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call xSP1();
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+------+
| t1c1 |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
+------+
8 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)