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