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: Bugs System CPU Architecture:Any

[21 Jan 2005 19:35] Ritesh Athwale
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
[21 Jan 2005 22:25] Ritesh Athwale
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..
[22 Mar 2005 6:01] Gabor Bereczki
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?
[22 Mar 2005 15:21] Peter Gulutzan
If the crash is occurring because of the loop rather than because of the inserting, then this is probably a variation of bug#6048.
[21 Apr 2005 12:35] Per-Erik Martin
Please attach table definitions and test data.
[21 May 2005 23:00] Bugs System
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".