Bug #8756 Cursors: Commiting a transaction does not close the open cursor

• Submitted: 23 Feb 2005 23:27
• Modified: 24 Feb 2005 12:52
• Reporter: Disha
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.2
• OS: Windows (Windows 2003)
• CPU Architecture: Any

[23 Feb 2005 23:27] Disha

Description:
If we turn-off autocommit, start a transaction and then within transaction open a cursor, fetch data, insert it into another table and then COMMIT the transaction. The open cursor does not get closed on commit.

How to repeat:
Test Setup 
1. Create database say 'Test' 
2. Create table 'Employees' in database 'Test' with fields say 'name','lname','dob','sal'
3. Populate Employees table with 10-11 records
    
Repro Steps:

1. Create the following procedure    
       CREATE PROCEDURE SP1( )
	begin
        DECLARE done INT DEFAULT 0;
        DECLARE count integer default 0;
        DECLARE newName char(20);
        DECLARE newlname char(20);
        DECLARE newdob char(20);
        DECLARE newsal integer;
	DECLARE cur1 CURSOR FOR SELECT name,lname,dob,sal FROM Employees;
        SET AUTOCOMMIT=0;
	Start transaction;
        Open cur1;
            BEGIN
                        set count = 10;
                        while count <> 1 do
                                    FETCH cur1 INTO newName,newlname,newdob,newsal;
				    Insert into Temp1 values (newName,newlname,newdob,newsal);
                                    set count = count - 1;
			End While;
            END;
        FETCH cur1 INTO newName,newlname,newdob,newsal;
	Insert into Temp1 values (newName,newlname,newdob,1001);
	END//

 2. Excute the above procedure
	call SP1();

Expected Result
The procedure creation should fail at the last FETCH statement as the cursor has already been closed by COMMIT.

Actual Result
The procedure executes without any errors and inserts 10 new rows in the Temp1 table.

[24 Feb 2005 9:31] Disha

Updated the synopsis

[24 Feb 2005 12:52] Alexander Keremidarski

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Current 5.0 from BK tree
ChangeSet@1.1892, 2005-02-23 20:22:51+03:00, [e-mail]

gives the desired result:

mysql> call SP1 ()//
ERROR 1329 (02000): No data to FETCH