MySQL Bugs: #8077: Stored Procedure crash mysql if Insertin rows are more then 2300

Bug #8077	Stored Procedure crash mysql if Insertin rows are more then 2300
Submitted:	21 Jan 2005 19:35	Modified:	21 May 2005 12:35
Reporter:	Ritesh Athwale	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S1 (Critical)
Version:	5.0.2 Alpha Standard	OS:	Linux (LINUX)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
HI I have created stored procedure to select the row from one table and insert into another table with avg ing the rows value.
I use stored procedure with cursor to accomplish this task.
After Inserting certain number ( 2378 ) of rows into table, mysql gets hung.

I have no Idea how do I overcome this problem.
I have tried to increase key_buffer_size through MySQL Administrator tool. I played lot with different sizes and values for buffer and memeory.

Modifying buffer size didnt increase the rows insertion.

After inserting 2378 rows mysql crashes.

Help will be appreciated..

Thanks

- 

How to repeat:
Create the table that has more then 3000 rows.
Create another similar table with same structure.

Create PRocedure test with 
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v1 INT;
DECLARE t1 DATETIME;
DECLARE c CURSOR for Select timestamp,c1 from table  //this should return more then 3000 rows
DECLARE CONTINUE HANDLER FOR NOT FOUND' SET done = 1;
OPEN c;
FETCH c INTO t1,v1;
WHILE done = 0 DO
INSET INTO table1 (timestamp,c11) SELECT t1, c1 from table where timestamp=t1;
FETCH c INTO t1,v1;
END WHILE;
CLOSE c;
END

DELIMITER $$

DROP PROCEDURE IF EXISTS `avb`.`sp_das_stat_avg`$$
CREATE PROCEDURE `avb`.`sp_das_stat_avg`()
BEGIN
  DECLARE avg_interval INT DEFAULT 30;
  DECLARE done INT DEFAULT 0;
  DECLARE c1,c2,c4,c11,c12,c14,c21,c22,c24 INT;
  DECLARE c5,c15,c25 CHAR;
  DECLARE c3,c13,c23 DATETIME;
  DECLARE das_all_site CURSOR FOR SELECT site_ckey,dev_ckey,timestamp,days,data_interval
                    FROM das_stat WHERE site_ckey=1
                                           AND  dev_ckey=1
                                           AND  data_interval='D'
                                           ORDER BY timestamp;
  DECLARE das_all_dev CURSOR FOR SELECT site_ckey,dev_ckey,timestamp,days,data_interval
                    FROM das_stat WHERE site_ckey!=1
                                           AND  dev_ckey=1
                                           AND  data_interval='D'
                                           ORDER BY timestamp;
  DECLARE das_site_dev CURSOR FOR SELECT site_ckey,dev_ckey,timestamp,days,data_interval
                    FROM das_stat WHERE site_ckey!=1
                                           AND  dev_ckey!=1
                                           AND  data_interval='D'
                                           ORDER BY timestamp;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN das_all_site;
  OPEN das_all_dev;
  OPEN das_site_dev;
        FETCH das_all_site INTO c1,c2,c3,c4,c5;
        WHILE done = 0 DO
            INSERT INTO das_stat_avg (site_ckey,dev_ckey,timestamp,days,data_interval,successMBeanSweepCount, unsuccessMBeanSweepCount, TotalCFindSucceeded, TotalCFindFailed, TotalCMoveSucceeded, TotalCMoveFailed, TotalCStoreSucceeded, TotalCStoreFailed, TotalStoreCommitSucceeded, TotalStoreCommitFailed, TotalMPPSSucceeded, TotalMPPSFailed, TotalUngracefulShutdowns, RebootCounter, Downtime )
                                            SELECT c1,c2,c3,c4,c5,avg(successMBeanSweepCount), avg(unsuccessMBeanSweepCount), avg(TotalCFindSucceeded),avg(TotalCFindFailed), avg(TotalCMoveSucceeded), avg(TotalCMoveFailed), avg(TotalCStoreSucceeded), avg(TotalCStoreFailed), avg(TotalStoreCommitSucceeded), avg(TotalStoreCommitFailed), avg(TotalMPPSSucceeded), avg(TotalMPPSFailed), avg(TotalUngracefulShutdowns), avg(RebootCounter), avg(Downtime)
                                            FROM das_stat
                                            WHERE timestamp BETWEEN DATE_SUB(c3, INTERVAL avg_interval DAY) AND c3
                                            AND  site_ckey=1
                                            AND  dev_ckey=1
                                            AND  data_interval='D'
                                            GROUP BY dev_ckey;

            FETCH das_all_site INTO c1,c2,c3,c4,c5;
        END WHILE;

        SET done = 0;
        FETCH das_all_dev INTO c11,c12,c13,c14,c15;
        WHILE done = 0 DO
            INSERT INTO das_stat_avg (site_ckey,dev_ckey,timestamp,days,data_interval,successMBeanSweepCount, unsuccessMBeanSweepCount, TotalCFindSucceeded, TotalCFindFailed, TotalCMoveSucceeded, TotalCMoveFailed, TotalCStoreSucceeded, TotalCStoreFailed, TotalStoreCommitSucceeded, TotalStoreCommitFailed, TotalMPPSSucceeded, TotalMPPSFailed, TotalUngracefulShutdowns, RebootCounter, Downtime )
                                            SELECT c11,c12,c13,c14,c15,avg(successMBeanSweepCount), avg(unsuccessMBeanSweepCount), avg(TotalCFindSucceeded),avg(TotalCFindFailed), avg(TotalCMoveSucceeded), avg(TotalCMoveFailed), avg(TotalCStoreSucceeded), avg(TotalCStoreFailed), avg(TotalStoreCommitSucceeded), avg(TotalStoreCommitFailed), avg(TotalMPPSSucceeded), avg(TotalMPPSFailed), avg(TotalUngracefulShutdowns), avg(RebootCounter), avg(Downtime)
                                            FROM das_stat
                                            WHERE timestamp BETWEEN DATE_SUB(c13, INTERVAL avg_interval DAY) AND c13
                                            AND  site_ckey=c11
                                            AND  dev_ckey=1
                                            AND  data_interval='D'
                                            GROUP BY dev_ckey;
        FETCH das_all_dev INTO c11,c12,c13,c14,c15;
        END WHILE;

        SET done = 0;
        FETCH das_site_dev INTO c21,c22,c23,c24,c25;
        WHILE done = 0 DO
             INSERT INTO das_stat_avg (site_ckey,dev_ckey,timestamp,days,data_interval,successMBeanSweepCount, unsuccessMBeanSweepCount, TotalCFindSucceeded, TotalCFindFailed, TotalCMoveSucceeded, TotalCMoveFailed, TotalCStoreSucceeded, TotalCStoreFailed, TotalStoreCommitSucceeded, TotalStoreCommitFailed, TotalMPPSSucceeded, TotalMPPSFailed, TotalUngracefulShutdowns, RebootCounter, Downtime )
                                            SELECT c21,c22,c23,c24,c25,avg(successMBeanSweepCount), avg(unsuccessMBeanSweepCount), avg(TotalCFindSucceeded),avg(TotalCFindFailed), avg(TotalCMoveSucceeded), avg(TotalCMoveFailed), avg(TotalCStoreSucceeded), avg(TotalCStoreFailed), avg(TotalStoreCommitSucceeded), avg(TotalStoreCommitFailed), avg(TotalMPPSSucceeded), avg(TotalMPPSFailed), avg(TotalUngracefulShutdowns), avg(RebootCounter), avg(Downtime)
                                            FROM das_stat
                                            WHERE timestamp BETWEEN DATE_SUB(c23, INTERVAL avg_interval DAY) AND c23
                                            AND  site_ckey=c21
                                            AND  dev_ckey=c22
                                            AND  data_interval='D'
                                            GROUP BY dev_ckey;
            FETCH das_site_dev INTO c21,c22,c23,c24,c25;
        END WHILE;
   CLOSE das_all_site;
   CLOSE das_all_dev;
   CLOSE das_site_dev;
END$$

DELIMITER ;

mysql crashes in 3rd cursor after inserting 2378 rows..

This is the single most annoying bug I have with MySQL stored procedures.
If I put action statements in a loop, memory gets used up at super high speed and then MySQL server either crashes or hangs. 
It would be great if you could fix this bug as this is a MAJOR showstopper for me. 
Thanks,
Gabor
PS: Ritesh, have you tried running "top" in another console while running your stored procedure?

If the crash is occurring because of the loop rather than because of the inserting, then this is probably a variation of bug#6048.

Please attach table definitions and test data.

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".