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.

Iptv Century It's very interesting, I realized you can fix this https://iptvcentury.com

HORLOLUJO RELOJES REPLICAS SUIZOS SUPER CLON Luxury Replica Watches
 http://horlolujo.com

Net iptv It's very interesting, I realized you can fix this only when you truly want  to use it in usa. I am looking forward to the https://netiptv.store

Iptv Uk It's very interesting, I realized you can fix this only when you truly want  to use it in uk . I am looking forward to the next Discount http://tivilitopia.com

German iptv  It's very interesting, I realized you can fix this only when you truly want to use it in Germany https://iptevia.com

IPTV Nederland https://Darktv.co

IPTV Nederland services represent a major IPTV KOPEN https://Darktv.co/home

Discover the Best IPTV UK Provider: Say goodbye to costly cables and endless buffering! https://xtremehd-uk.uk

Troubleshoot AI Apps  https://www.troubleshoot.dev/

Latest Merch Deals  https://latestmerch.com/

Remote Software Jobs https://programable.com/

attorney jobs  https://attorney.work/

authentic jerseys store   https://authenticjerseysstore.com/

Best IPTV Providers IS the Best iptv subscription service in the worldwide https://bestiptvproviders.net