| Bug #15579 | Loop inside Loop into Cursor Loop crash | ||
|---|---|---|---|
| Submitted: | 8 Dec 2005 9:46 | Modified: | 20 Jan 2006 19:08 |
| Reporter: | Michael Maréchal | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | Mysql 5.0.16 with WampServer 1.5 | OS: | Windows (Windows XP Pro) |
| Assigned to: | CPU Architecture: | Any | |
[8 Dec 2005 18:38]
Valeriy Kravchuk
Thank you for a problem report. Can you, please, send the SHOW CREATE TABLE results for the real tables used in you procedure, as well as some data for them. You may upload them as private. Or, even better, can you demonstrate the problem on a simple table with 2 rows, used in the outer "cursor loop"?
[9 Dec 2005 11:41]
Michael Maréchal
Here is the file with creation and insertion scripts u request !
[20 Dec 2005 19:08]
Valeriy Kravchuk
I've got the same behaviour you described in 5.0.16. But we need a simpler test case without your private data. So, please, try to NOT use SELECT... INTO... in your inner loops (just replace them with some SELECT 'debug text') and check, how many times the outer loop will be executed. Test this, and you original case on 5.0.17, now officially released, if you can.
[21 Jan 2006 0: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".
Description: I got a code that s supposed to bind invoices with commissions that are distributed to a hierarchy of agents who sell telecoms products. I use triggers and procedures... First i create the commission's row into table Comm_Direct with a trigger (after client inserted) > OK Twice i calculate the amount of the commission the agents can get > OK Third I Have to distribute many different percents of the commission to the agents 's hierarchy. Here comes the problem... The distribution calculations are ok and i can distribute the percents to the hierachy in the right way. The problem is that i got many commissions to distribute ... so i use imbricated loops (the main is the cursor loop) to distribute all of them to the agents... But the procedure go trough the main (cursor) loop only once and then stop without crashing (hum..is this the right word ?) but without going trough all comm_direct table's rows too... Code Example: DECLARE done INT DEFAULT O; DECLARE cur1 CURSOR FOR SELECT IDCommDirect,AgentID,.. From Comm_Direct; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; REPEAT FECTH cur1 INTO IDCom,AgentID,... IF NOT done THEN hierarchy_loop: IF ... THEN -- usual Sql statements -- ELSE Comm: LOOP -- usual Sql statements -- IF ... THEN LEAVE Comm; END IF; END LOOP; END IF; IF ... THEN Comm2: LOOP -- usual Sql statements -- IF ... THEN LEAVE Comm2; END IF; END LOOP; END IF; END LOOP; END IF; UNTIL done END REPEAT; So the procedure go trough the main loop, the second (hierarchy), the third, make all the calculations and the insertions i need right but stop before (re)starting the same operations for other comm_direct table's rows ... I tried all types of loops (LOOP, WHILE, REPEAT, ... with mysql cursor or with my test code)... none of them work right in this case ... How to repeat: Not sure ... Try to imbricate some loops (first one with a cursor) with basic select and insert code ... Suggested fix: The only solution i found is to move the main loop into my php code and to give the next IDCommDirect as a param each time i call the proc ... This replace the cursor loop and seems to work ...