MySQL Bugs: #43375: close cursor problem

Bug #43375	close cursor problem
Submitted:	4 Mar 2009 15:33	Modified:	24 Dec 2009 10:00
Reporter:	Michał Zalewski	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.77	OS:	FreeBSD (7.1-STABLE x64)
Assigned to:		CPU Architecture:	Any
Tags:	close cursor, not found event

Description:
When I use: "close cursor_name;", it hang on "Closing table" in "processlist".
I tested this SP without close cursor and it works correctly.
I use "SELECT FOUND_ROWS() INTO @numRows;" to read row count, because when I use "NOT FOUND" handler it's fired after first "FETCH cursor_name"...

How to repeat:
It's simplified module to choose winner of auction:

tables:

CREATE TABLE auction(id int PRIMARY KEY AUTO_INCREMENT, data_zakonczenia DATETIME, price DECIMAL(10,2), stan tinyint, winner varchar(200) DEFAULT NULL);

CREATE TABLE user(id int PRIMARY KEY AUTO_INCREMENT, login varchar(200));

CREATE TABLE offers(id int PRIMARY KEY AUTO_INCREMENT, user_id int, price DECIMAL(10,2), auction_id int);

Stored procedure:

  DECLARE done INT DEFAULT 0;
  DECLARE auction_id INT;
  DECLARE price DECIMAL(10,2);

  DECLARE cursor_aukcja CURSOR FOR SELECT id, price FROM auction WHERE stan = 1 AND data_zakonczenia > DATE_SUB(now(), INTERVAL 1 HOUR);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cursor_aukcja;

  SELECT FOUND_ROWS() INTO @numRows;

  REPEAT

    SET @numRows = @numRows - 1;

    FETCH cursor_aukcja INTO auction_id, cena;

      SET @auction_id = auction_id;
      SET @price = price;
      SET @winner = null;

      SELECT u.login INTO @winner FROM offers ao
        JOIN user u ON u.id = ao.user_id
        WHERE ao.price = @price
          AND ao.auction_id = @auction_id
      LIMIT 1;

      IF @winner IS NOT NULL THEN
        UPDATE auction SET winner = @winner WHERE id = @auction_id;
      END IF;

  UNTIL @numRows = 0 END REPEAT;

  CLOSE cursor_aukcja; -- without this, it's work correctly

Thank you for the report.

I can not repeat described behavior.

Please add SELECT @numRows; after SELECT FOUND_ROWS() INTO @numRows;, run procedure in you environment and send us how many rows were found.

"SELECT @numRows" return 12 rows, but when I use:

DECLARE done INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

to check cursor have more rows, it set done = 1; after first "FETCH cursor_name;"

Thank you for the feedback.

I still can not repeat the problem. Could you please try with latest version 5.0.89 and if problem still exists attach gdb to the running process during hang, then issue command `bt` to obtain backtrace. Probably we can guess why it hangs from it.

It work correctly on MySQL 5.0.85 (installed from ports on FreeBSD 7.2-STABLE x64)

Thank you for the feedback.

Closed as "Can't repeat"