Description:
If someone codes a simple mistake like forgetting to give a default value to an INT variable, then the resultant infinite loop causes serious problems:
The MySQL server, indeed the user's whole PC, practically locks-up due to the vast amounts of memory used - even after I pressed CTRL-C ("Aborted" client program but mysqld-nt continued to use all the PC's resources). If MySQL Administrator GUI not already running, chances are that the whole mysqld-nt service needs to be stopped, but I failed to do this within the Task Manager ("Access Denied" even though I was logged-on as Administrator). I eventually managed to view the services via the "Computer Management" GUI and stopped the mysqld-nt process from within there (although sometimes I had to try more than once due to “Error 1053: The service did not respond to the start or control request in a timely fashion”). The PC then recovered once the runaway process was stopped.
If the MySQL Administrator GUI IS already running, then one can kill the thread from within this utility - if one can identify the offending thread! (Server connections ALL show a time of 0, so if several queries running at once.... luckily I just had the single query executing, so I highlighted it and pressed "Kill thread" (at this point, Time started to rise as I pressed refresh a few times - it went up as high as 27 whilst state was "freeing items").
How to repeat:
delimiter //
CREATE PROCEDURE test1()
BEGIN
/* DECLARE i INT DEFAULT 0; */
/* Test: create infinite loop by replacing the above
DECLARE statement with the one below (i will be NULL
and therefore can never exit the REPEAT UNTIL loop)
*/
DECLARE i INT;
REPEAT
SET i = i + 1;
UNTIL i >= 10
END REPEAT;
select i;
END;
//
delimiter ;
call test1();
Suggested fix:
I had hoped MySQL would be more robust when dealing with infinite loops - but then I haven't tried this in Oracle, for example. There's an existing bug #6048 regarding out-of-control memory consumption (but not in relation to infinite loops).
Description: If someone codes a simple mistake like forgetting to give a default value to an INT variable, then the resultant infinite loop causes serious problems: The MySQL server, indeed the user's whole PC, practically locks-up due to the vast amounts of memory used - even after I pressed CTRL-C ("Aborted" client program but mysqld-nt continued to use all the PC's resources). If MySQL Administrator GUI not already running, chances are that the whole mysqld-nt service needs to be stopped, but I failed to do this within the Task Manager ("Access Denied" even though I was logged-on as Administrator). I eventually managed to view the services via the "Computer Management" GUI and stopped the mysqld-nt process from within there (although sometimes I had to try more than once due to “Error 1053: The service did not respond to the start or control request in a timely fashion”). The PC then recovered once the runaway process was stopped. If the MySQL Administrator GUI IS already running, then one can kill the thread from within this utility - if one can identify the offending thread! (Server connections ALL show a time of 0, so if several queries running at once.... luckily I just had the single query executing, so I highlighted it and pressed "Kill thread" (at this point, Time started to rise as I pressed refresh a few times - it went up as high as 27 whilst state was "freeing items"). How to repeat: delimiter // CREATE PROCEDURE test1() BEGIN /* DECLARE i INT DEFAULT 0; */ /* Test: create infinite loop by replacing the above DECLARE statement with the one below (i will be NULL and therefore can never exit the REPEAT UNTIL loop) */ DECLARE i INT; REPEAT SET i = i + 1; UNTIL i >= 10 END REPEAT; select i; END; // delimiter ; call test1(); Suggested fix: I had hoped MySQL would be more robust when dealing with infinite loops - but then I haven't tried this in Oracle, for example. There's an existing bug #6048 regarding out-of-control memory consumption (but not in relation to infinite loops).