Infinite loop of mysql

Question

Infinite loop of mysql

Navigation

#1 by (1 votes)

1

I have to take out a stored procedure, to simplify fractions (divide by the same numerator and denominator number). However, when I run the script it stays running for a while and the MySQL connection is cut off.

My code is as follows:

delimiter //
create procedure simpfrac(IN denom int, IN numerad int)
    begin
        declare contador int;
        set contador = 0;
        -- Creamos un bucle en el cual usamos un contador para ir simplificando las fracciones
        incremento: loop
            set contador = contador+1;
            if (denom % contador = 0) and  (contador < denom) and (numerad % contador = 0) and (contador < numerad)
                then
                    begin
                        set denom = denom / contador;
                        set numerad = numerad / contador;
                    end;
            else if (numerad = denom)
                then
                    begin
                        select 'El resultado es 1, no hace falta simplificar';
                        leave incremento;
                end;
            else if (denom < numerad) and (contador = denom)
                then
                    leave incremento;
            else if (denom > numerad) and (contador = numerad)
                then
                    leave incremento;
            end if;
            end if;
            end if;
            end if;
        end loop incremento;
        end;// 
        delimiter ;

mysql sql loops stored-procedure

asked by ras212 29.11.2017 в 00:25

source

1 answer

Add arrows to carousel Wait for retrofit to answer a call?

score 1 · Accepted Answer

Solution

You must correct the following block:

        if (denom % contador = 0) and  (contador < denom) and (numerad % contador = 0) and (contador < numerad)
            then
                begin
                    set denom = denom / contador;
                    set numerad = numerad / contador;
                end;

... and replace it with (note the 2 changes: contador > 1 and set contador = 0; ):

        if contador > 1 and (denom % contador = 0) and  (contador < denom) and (numerad % contador = 0) and (contador < numerad)
            then
                begin
                    set denom = denom / contador;
                    set numerad = numerad / contador;
                    set contador = 0;
                end;

Explanation

Indeed, you have an infinite loop. The reason is that if you enter the condition where you simplify the fraction:

begin
    set denom = denom / contador;
    set numerad = numerad / contador;
end;

... you need to reset variable contador before continuing to iterate in the loop. Otherwise, it is possible that by reducing denom and numerad , that both variables now have values smaller than contador . And if this happens, it means that the 2 other conditions contador = denom and contador = numerad will never become true as contador keeps growing.

And anyway, logically, once you have reduced the fraction once, you need to restart the search for simplifications with contador to zero:

begin
    set denom = denom / contador;
    set numerad = numerad / contador;
    set contador = 0;
end;

But that's not all. There is another correction you need. Otherwise you'll still have an infinite loop.

When contador = 1 , the condition:

if (denom % contador = 0) and  (contador < denom) and (numerad % contador = 0) and (contador < numerad)

... will often evaluate true , because module 1 ( n % 1 ) of any number is always equal to zero. But it does not make sense to simplify the fraction if contador = 1 , because dividing any number by 1 does not simplify anything.

So the second correction needed is to add a condition so that you avoid trying to simplify the fraction when contador = 1 :

if contador > 1 and (denom % contador = 0) and  (contador < denom) and (numerad % contador = 0) and (contador < numerad)

Actually, I could suggest many improvements in the design of your algorithm, but I just identify the basic problem you have with your infinite loop.