Bug #3052 Rollback in stored procedure doesn't close cursor
Submitted: 3 Mar 2004 2:46 Modified: 24 Apr 2006 7:00
Reporter: Peter Gulutzan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1 OS:Linux (SuSE 8.2)
Assigned to: CPU Architecture:Any
Tags: cursor, rollback

[3 Mar 2004 2:46] Peter Gulutzan
Description:
ROLLBACK doesn't close open cursors. 
 
This applies only for ROLLBACK statements that occur within stored procedures, 
after a cursor is OPENed, before it is CLOSEd. 
 

How to repeat:
 mysql> delimiter // 
 mysql> create table t (s1 int)// 
 Query OK, 0 rows affected (0.26 sec) 
  
 mysql> insert into t values (55)// 
 Query OK, 1 row affected (0.00 sec) 
  
 mysql> create procedure p2 () begin 
  declare v int; 
  declare x cursor for select s1 from t; 
  open x; 
  rollback; 
  fetch x into v; 
  set @x = v; 
  end;// 
 Query OK, 0 rows affected (0.00 sec) 
  
 mysql> call p2()// 
 Query OK, 0 rows affected, 1 warning (0.01 sec) 
  
 mysql> select @x// 
 +------+ 
 | @x   | 
 +------+ 
 | 55   | 
 +------+ 
 1 row in set (0.00 sec) 
  
 It's clear that the FETCH succeeded, even though 
 there was a ROLLBACK just before it. 

Suggested fix:
Nothing immediate is expected. The behaviour is not intentional, 
but apparently we'll have to wait for the new parser.
[15 Mar 2006 13:33] Per-Erik Martin
This can be fixed. Since it will be a change in behaviour, it must be in 5.1 (or later).
At the same time, the behaviour of COMMIT should be fixed the same way. In this case we should add the WITH[OUT] HOLD syntax for DECLARE CURSOR and close all not held cursors on COMMIT.
[22 Aug 2006 15:02] Konstantin Osipov
This is too big to be fixed in 5.0, will be fixed in 5.1
[11 Nov 2017 18:17] Federico Razzoli
Identical behaviour in 8.0.3.