| Bug #64521 | Premature termination of the cursor | ||
|---|---|---|---|
| Submitted: | 2 Mar 2012 9:41 | Modified: | 6 Mar 2012 16:31 |
| Reporter: | Aleks Pol | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S1 (Critical) |
| Version: | 5.5.21 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cursor | ||
[2 Mar 2012 9:41]
Aleks Pol
[2 Mar 2012 9:49]
Valeriy Kravchuk
Please, provide complete repeatable test case, show the results you get and explain what results you expect.
[2 Mar 2012 11:33]
Aleks Pol
В PROCEDURE Set_Roz_PredmetPotok курсор завершается после вызова FUNCTION `ChekParaRozPotok`
CREATE DEFINER=`root`@`localhost` FUNCTION `ChekParaRozPotok`(Pot int, p int, d int, tt int, s int) RETURNS int(11)
BEGIN
declare a int default 1;
DECLARE del INT DEFAULT 0;
DECLARE iGroupsID INT DEFAULT 0;
DECLARE igod INT;
DECLARE is_end INT DEFAULT 0;
DECLARE cur_potok CURSOR FOR
SELECT tpredmets.GroupID FROM `trozklad`
INNER JOIN `trozpodil` ON (`trozklad`.`RozpodilID` = `trozpodil`.`ID`)
INNER JOIN `tpredmetsinfo` ON (`trozpodil`.`PredmetsInfoID` = `tpredmetsinfo`.`ID`)
INNER JOIN `tpredmets` ON (`tpredmetsinfo`.`PredmetsID` = `tpredmets`.`ID`)
WHERE (tpredmets.`potokID` =pot AND trozklad.`T` =tt AND trozklad.`Ses` =s);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1;
SET is_end = 0;
OPEN cur_potok;
REPEAT
FETCH cur_potok INTO iGroupsID;
IF NOT is_end THEN
if (chekpararoz(iGroupsID, p , d , tt, s , null)=0) or (ChekGr(p, iGroupsid, d, tt, s)=0)
then
set a=0;
end if;
END IF;
UNTIL is_end END REPEAT;
CLOSE cur_potok;
RETURN a ;
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `Set_Roz_PredmetPotok`(IN prindey INT, IN ipotok INT, IN tt INT, IN s INT, IN mPara INT, IN mDey INT)
BEGIN
DECLARE dey INT DEFAULT 1;
DECLARE par INT DEFAULT 1;
DECLARE crpot INT DEFAULT 0;
DECLARE iLoginID INT;
DECLARE iType INT;
DECLARE iGroupsID INT;
DECLARE iName VARCHAR(150);
DECLARE iT INT;
DECLARE iSes INT;
DECLARE nd BOOL DEFAULT FALSE;
DECLARE iPotokID INT;
DECLARE iPodgrupa INT;
DECLARE pr INT;
DECLARE del INT DEFAULT 0;
DECLARE igod INT;
DECLARE is_end INT DEFAULT 0;
DECLARE cur_set CURSOR FOR
SELECT `trozklad`.`ID` FROM
`trozklad`
INNER JOIN `trozpodil`
ON (`trozklad`.`RozpodilID` = `trozpodil`.`ID`)
INNER JOIN `tpredmetsinfo`
ON (`trozpodil`.`PredmetsInfoID` = `tpredmetsinfo`.`ID`)
INNER JOIN `tpredmets`
ON (`tpredmetsinfo`.`PredmetsID` = `tpredmets`.`ID`)
WHERE (tpredmets.`PotokID` =iPotok AND trozklad.den IS NULL AND trozklad.t=tt AND trozklad.ses=s and trozklad.`God`=igod AND trozpodil.`Type`=1)
group by tpredmets.`GroupID`;
DECLARE cur_potok CURSOR FOR
SELECT
tpredmets.`PotokID`
, `tpredmets`.`Name`
, tpredmets.`GroupID`
, tpredmetsinfo.`LoginID`
, `trozklad`.`T`
, `trozklad`.`Type`
, `trozklad`.`Ses`
, `trozklad`.`Podgrupa`
, trozklad.god
FROM `trozklad`
INNER JOIN `trozpodil`
ON (`trozklad`.`RozpodilID` = `trozpodil`.`ID`)
INNER JOIN `tpredmetsinfo`
ON (`trozpodil`.`PredmetsInfoID` = `tpredmetsinfo`.`ID`)
INNER JOIN `tpredmets`
ON (`tpredmetsinfo`.`PredmetsID` = `tpredmets`.`ID`)
WHERE (tpredmets.`PotokID` =ipotok AND trozklad.den IS NULL AND trozklad.t=tt AND trozklad.ses=s);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1;
SET is_end = 0;
OPEN cur_potok;
REPEAT
FETCH cur_potok INTO iPotokID, iName, iGroupsID, iLoginID, iT, iType, iSes, iPodgrupa, igod;
IF NOT is_end THEN
SET par=0;
SET dey=0;
SET nd=FALSE;
REPEAT
SET par=0;
SET dey=dey+1;
REPEAT
SET par=par+1;
IF
chekparlogin(iloginid,4,dey,iT,ises, ipodgrupa)
AND chekpar(iGroupsid,prindey,dey,iT,ises, ipodgrupa)
AND ChekPredmetTypeRoz(iType, iName, iGroupsid,dey,iT,Ises, NULL,mDey)
AND ChekLoginRoz(iloginID,par,dey, iT, iSes )
AND ChekParaRozPotok(iPotok,par,dey, tt,s)
AND chekpararoz(iGroupsid, par, dey, tt, s, NULL)
and ChekNag(iloginId, iGroupsid, dey, tt, s)
AND ChekGr(par, iGroupsid, dey, iT, iSes) # Проверяет свободна ли пара или зарезервирована
THEN
SELECT COUNT( `trozklad`.`ID`)
INTO del
FROM `trozklad`
INNER JOIN `trozpodil` ON (`trozklad`.`RozpodilID` = `trozpodil`.`ID`)
INNER JOIN `tpredmetsinfo` ON (`trozpodil`.`PredmetsInfoID` = `tpredmetsinfo`.`ID`)
INNER JOIN `tpredmets` ON (`tpredmetsinfo`.`PredmetsID` = `tpredmets`.`ID`)
WHERE (tpredmets.`PotokID` =ipotok AND trozklad.den IS NULL AND trozklad.t=tt AND trozklad.ses=s and trozklad.`God`=igod);
if del>0 then
OPEN cur_set;
REPEAT
FETCH cur_set INTO crpot;
UPDATE trozklad SET den=dey, para=par WHERE id =crpot;
set del=del-1;
UNTIL (del=0) END REPEAT;
CLOSE cur_set;
end if;
SET nd=TRUE;
END IF;
UNTIL (nd OR (par=mPara)) END REPEAT;
UNTIL (nd OR (dey=mDey)) END REPEAT;
END IF;
UNTIL is_end END REPEAT;
CLOSE cur_potok;
END
[6 Mar 2012 16:31]
Sveta Smirnova
Thank you for the feedback. This is duplicate of bug #61392 But you actually get warning, not error. As workaround you can set SQL mode to default, "forgiving" state, so execution would not be interrupted: create table t1(f1 int); insert into t1 values(1),(2),(3); create table t2(f1 int); insert into t2 values(1),(2),(3); create function f1() returns int begin declare dummy int; declare t1sum int default 0; declare is_end int default 0; declare cur1 cursor for select f1 from t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1; open cur1; repeat fetch cur1 into dummy; if not is_end then set t1sum = t1sum + dummy; end if; until is_end end repeat; close cur1; return t1sum; end | create procedure p1() begin declare dummy int; declare is_end int default 0; declare cur2 cursor for select f1() from t2; DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1; open cur2; repeat fetch cur2 into dummy; if not is_end then select dummy; end if; until is_end end repeat; close cur2; end | call p1(); dummy 6 dummy 6 dummy 6 Warnings: Error 1329 No data - zero rows fetched, selected, or processed
