MySQL Bugs: #29771: The cursor is automatically closed.

Bug #29771	The cursor is automatically closed.
Submitted:	12 Jul 2007 19:50	Modified:	17 Jul 2007 11:25
Reporter:	Jiang Bian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.045	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	cursor

Description:
I am doing some research on Enron email dataset.
http://www.isi.edu/~adibi/Enron/Enron.htm 

The following is my store procedure. Basically it goes through a cursor and insert or update the record table depends on it's a new record or not.

The problem is when ever it runs into the insert statement, it will close the cursor, no matter what the no_more_rows is true or not.

I have no idea, what's wrong here.

PROCEDURE enron.fillmatrix()
BEGIN
  DECLARE no_more_rows BOOLEAN;
  DECLARE num INTEGER;
  DECLARE rm_ID INT;
  DECLARE x2 INT;
  DECLARE m_mid INT(10);
  DECLARE r_type ENUM('TO', 'CC', 'BCC');
  DECLARE m_sender, r_receiver VARCHAR(127);
  DECLARE cur_res CURSOR FOR SELECT r.mid, r.rtype, r.rvalue, m.sender FROM Enron.recipientinfo r LEFT JOIN Enron.message m ON m.mid = r.mid LIMIT 0, 100;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more_rows = TRUE;
  DROP TABLE IF EXISTS `enron`.`recipientmatrix`;
  CREATE TABLE  `enron`.`recipientmatrix` (
    `rmID` int(10) unsigned PRIMARY KEY NOT NULL auto_increment,
    `sender` varchar(127) NOT NULL,
    `reciever` varchar(127) NOT NULL,
    `rtype` enum('TO','CC','BCC') NOT NULL,
    `rmcount` int(10) unsigned NOT NULL DEFAULT 1
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
  OPEN cur_res;
  loop_cur_res: LOOP
    FETCH cur_res INTO m_mid, r_type, r_receiver, m_sender;
    IF no_more_rows THEN
      CLOSE cur_res;
      LEAVE loop_cur_res;
    END IF;
    
      SET rm_ID = 0;
      SELECT rm.rmID INTO rm_ID FROM recipientmatrix rm WHERE rm.sender = m_sender AND rm.reciever = r_receiver AND rm.rtype = r_type;
      IF rm_ID = 0 THEN
        INSERT INTO recipientmatrix (sender, reciever, rtype) VALUES (m_sender, r_receiver, r_type);  
      ELSE        
        UPDATE recipientmatrix rm SET rmcount = rmcount +1 WHERE rm.rmID = rm_ID;
      END IF;
  END LOOP loop_cur_res;
END

How to repeat:
Just run the procedure...
PROCEDURE enron.fillmatrix()
BEGIN
  DECLARE no_more_rows BOOLEAN;
  DECLARE num INTEGER;

  DECLARE rm_ID INT;

  DECLARE x2 INT;

  DECLARE m_mid INT(10);

  DECLARE r_type ENUM('TO', 'CC', 'BCC');

  DECLARE m_sender, r_receiver VARCHAR(127);

  DECLARE cur_res CURSOR FOR SELECT r.mid, r.rtype, r.rvalue, m.sender FROM Enron.recipientinfo r LEFT JOIN Enron.message m ON m.mid = r.mid LIMIT 0, 100;

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more_rows = TRUE;

  DROP TABLE IF EXISTS `enron`.`recipientmatrix`;
  CREATE TABLE  `enron`.`recipientmatrix` (
    `rmID` int(10) unsigned PRIMARY KEY NOT NULL auto_increment,
    `sender` varchar(127) NOT NULL,
    `reciever` varchar(127) NOT NULL,
    `rtype` enum('TO','CC','BCC') NOT NULL,
    `rmcount` int(10) unsigned NOT NULL DEFAULT 1
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

  OPEN cur_res;

  loop_cur_res: LOOP

    FETCH cur_res INTO m_mid, r_type, r_receiver, m_sender;
    
    IF no_more_rows THEN
      CLOSE cur_res;
      LEAVE loop_cur_res;
    END IF;

      
      SET rm_ID = 0;

      SELECT rm.rmID INTO rm_ID FROM recipientmatrix rm WHERE rm.sender = m_sender AND rm.reciever = r_receiver AND rm.rtype = r_type;

      IF rm_ID = 0 THEN
        INSERT INTO recipientmatrix (sender, reciever, rtype) VALUES (m_sender, r_receiver, r_type);  
      ELSE        
        UPDATE recipientmatrix rm SET rmcount = rmcount +1 WHERE rm.rmID = rm_ID;
      END IF;
     
  END LOOP loop_cur_res;

END

Suggested fix:
no idea!!!!!!!!

Thank you for the report.

Please provide output of SHOW CREATE TABLE recipientinfo and SHOW CREATE TABLE message

Here are the table structures of recipientinfo and message tables.

DROP TABLE IF EXISTS `enron`.`message`;
CREATE TABLE  `enron`.`message` (
  `mid` int(10) NOT NULL default '0',
  `sender` varchar(127) NOT NULL default '',
  `date` datetime default NULL,
  `message_id` varchar(127) default NULL,
  `subject` text,
  `body` text,
  `folder` varchar(127) NOT NULL default '',
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `enron`.`message`;
CREATE TABLE  `enron`.`message` (
  `mid` int(10) NOT NULL default '0',
  `sender` varchar(127) NOT NULL default '',
  `date` datetime default NULL,
  `message_id` varchar(127) default NULL,
  `subject` text,
  `body` text,
  `folder` varchar(127) NOT NULL default '',
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thank you for the feedback.

You haven't provided output of SHOW CREATE TABLE recipientinfo. Please, provide it. Also provide output of SHOW TABLE STATUS for both tables.

sorry for the duplicate info. I forgot the recipientinfo table.
Here the table structure of recipientinfo:
DROP TABLE IF EXISTS `enron`.`recipientinfo`;
CREATE TABLE  `enron`.`recipientinfo` (
  `rid` int(10) NOT NULL default '0',
  `mid` int(10) unsigned NOT NULL default '0',
  `rtype` enum('TO','CC','BCC') default NULL,
  `rvalue` varchar(127) default NULL,
  `dater` datetime default NULL,
  PRIMARY KEY  (`rid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here is the result for SHOW TABLE STATUS:
message	MyISAM	10	Dynamic	252759	1924	486526796	281474976710655	2598912	0		2007-07-12 09:18:22	2007-07-12 09:41:53		latin1_swedish_ci		(null)	(null)
recipientinfo	MyISAM	10	Dynamic	2064442	38	79686924	281474976710655	21219328	0		2007-07-12 09:19:32	2007-07-12 09:23:23		latin1_swedish_ci		(null)	(null)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Statement "DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more_rows = TRUE;" works not only for CURSORs, but for regular statements too.