MySQL Bugs: #66526: Cursor's only works the first time at mysql5.5.x (but works fine at mysql5.1.x)

Bug #66526	Cursor's only works the first time at mysql5.5.x (but works fine at mysql5.1.x)
Submitted:	24 Aug 2012 10:33	Modified:	27 Sep 2012 13:52
Reporter:	Meix Luck	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CURSOR BROKEN, CURSOR PROBLEM

Description:
Note: This bug not happens at mysql 5.1.x and olders.

In all my functions with cursors, when i execute the function, my cursor only run the first time at mysql5.5.x. In mysql5.1.x the same function works fine.

How to repeat:
One example:

CREATE FUNCTION `example_function`() RETURNS INT(11)
BEGIN

 DECLARE my_value INT DEFAULT 0;
 DECLARE cursor_example CURSOR FOR 
 SELECT data from mytable;/*table with 100 rows*/
 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cursor_example;
 net_loop: LOOP
 FETCH cursor_example INTO my_value;

 IF done THEN
	 LEAVE net_loop;
  END IF;
 
  INSERT INTO temporal_table (text) VALUES (my_value);

 END LOOP net_loop;
 CLOSE cursor_example; 
 RETURN 1;
 END$$

DELIMITER ;

executing this function at mysql5.1 fill temporal_table with 100 rows. Executing this function at mysql5.5.x fill temporal_table ONLY with the first row of mytable

Suggested fix:
Maybe a mysql5.5 bug?

Looks like your example is incomplete (done variable is not defined, I do not see table definitions etc). When I completed it I've got no problem with 5.5.28:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

createWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.28-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table mytable(data int);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into mytable values(1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table temporal_table(text int);
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter //
mysql> CREATE FUNCTION `example_function`() RETURNS INT(11)
    -> BEGIN
    -> 
    ->  DECLARE my_value INT DEFAULT 0;
    ->  DECLARE done INT DEFAULT 0;
    ->  DECLARE cursor_example CURSOR FOR 
    ->  SELECT data from mytable;/*table with 100 rows*/
    ->  
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -> 
    -> OPEN cursor_example;
    ->  net_loop: LOOP
    ->  FETCH cursor_example INTO my_value;
    -> 
    ->  IF done THEN
    ->  LEAVE net_loop;
    ->   END IF;
    ->  
    ->   INSERT INTO temporal_table (text) VALUES (my_value);
    -> 
    ->  END LOOP net_loop;
    ->  CLOSE cursor_example; 
    ->  RETURN 1;
    ->  END//
Query OK, 0 rows affected (0.06 sec)

mysql> select * from mytable;
    -> //
+------+
| data |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.04 sec)

mysql> select * from temporal_table;
    -> //
Empty set (0.00 sec)

mysql> select example_function() from dual;
    -> //
+--------------------+
| example_function() |
+--------------------+
|                  1 |
+--------------------+
1 row in set, 1 warning (0.09 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Error
   Code: 1329
Message: No data - zero rows fetched, selected, or processed
1 row in set (0.00 sec)

mysql> select * from temporal_table;
    -> //
+------+
| text |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Hi again.

That is only a simple example. You are right, that example works fine.

I was testering my real functions and the reason is: if you call a function inside the cursor that have a empty select, the cursor die.

Example:

the main function:

DROP FUNCTION IF EXISTS `example_function`$$
CREATE FUNCTION example_function() RETURNS INT(11)
BEGIN

 DECLARE my_value INT DEFAULT 0;
 DECLARE done INT DEFAULT 0;
 DECLARE existe_veces INT DEFAULT 0;
 
 DECLARE cursor_example CURSOR FOR SELECT mydata FROM mytable;/*table with 100 rows*/ 
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 OPEN cursor_example;
 net_loop: LOOP
 FETCH cursor_example INTO my_value;

 IF done THEN
	 LEAVE net_loop;
  END IF;

/*HERE is the problem, the next function have a empty select*/
	SET existe_veces=other_function(1);
  INSERT INTO temporal_table (texto) VALUES (existe_veces);

 END LOOP net_loop;
 CLOSE cursor_example; 
 RETURN 1;
 END$$

DELIMITER ;

the other_function:

DELIMITER $$

DROP FUNCTION IF EXISTS `other_function`$$

CREATE FUNCTION `other_function`(int_value INT) RETURNS INT(11)
BEGIN

 DECLARE veces INT DEFAULT -1;
/*THIS SELECT dont return any value*/
 SELECT VALUE INTO veces FROM empty_table;

 RETURN veces;
 END$$

DELIMITER ;

then, when you execute the main function, only does the first result of the cursor

Hi again,

This only happens at 5.5.X, the same example at 5.1.x works fine

Do you have error handler in that function? Read http://dev.mysql.com/doc/refman/5.5/en/condition-handling.html:

"Before MySQL 5.6.3, if a statement that generates a warning or error causes a condition handler to be invoked, the handler may not clear the diagnostic area. This might lead to the appearance that the handler was not invoked. The following discussion demonstrates the issue and provides a workaround."

Looks like you have to apply one of the workarounds mentioned there.

Hi again.

Thanks again for your reply.

I knew it, i though that the reason could be a warning, but in the example, the function looks like not have any warning/error:

select other_function(1);
/* 0 rows affected, 1 rows found. Duration for 1 query: 0,000 sec. */

IMHO you need a handler in a BEGIN ... END block around this statement in your functions (with a workaround to clear warnings in handler, as that manual page describes):

 SELECT VALUE INTO veces FROM empty_table;

to make it work reliably in any case.

Hi again,

thanks Valeriy, im going to try it :)

Best regards;

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".