MySQL Bugs: #3052: Rollback in stored procedure doesn't close cursor

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

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.

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.

This is too big to be fixed in 5.0, will be fixed in 5.1

Identical behaviour in 8.0.3.

作为一名留学生，适应国外的学术环境可能是一项艰巨的任务。学术要求往往比预期的要高，尤其是在写作方面。许多留学生发现，在繁忙的课程安排中，很难找到足够的时间来完成所有的学术任务。再加上英语不是母语的挑战，很多学生感到压力重重，无法达到预期的学术标准。在这样的情况下，留学生作业代写 http://www.pnstudy.com/ 服务成为了许多留学生的救命稻草。

Iron Tv offer a wide range of channels and content, catering to diverse viewer preferences. From live sports events to international news broadcasts https://irontvpro.live

Pure Tv offer a wide range of channels and content, catering to diverse viewer preferences. Merci https://pureiptvabo.com/