MySQL(Cursors/stored procedures): Error Code: 2013. Lost connection to MySQL server during query 6000.015 sec

Question

Platform: MySQL Workbench 8.0 CE SQL: 5.5.57

I'm having some troubles with a stored procedure. I keep getting the same Error 2013 Lost Connection when i call the stored procedure.

Things i've tried:

• Increasing the DBMS connection read timeout interval (60 - 6000 seconds): Error Code: 2013. Lost connection to MySQL server during query during-query
• Indexing my tables
• Limiting cursor to LIMIT 5
• Exiting loop after five iterations with if i <= x then leave iterate_personalstamm; end if; set i = i + 1;
• Using WHERE on SELECT

Thanks in advance for trying to help me. Following the stored procedure:

USE `personaldb`;
DROP procedure IF EXISTS `austritt_telintern`;

USE `personaldb`;
DROP procedure IF EXISTS `personaldb`.`austritt_telintern`;
;

DELIMITER $$
USE `personaldb`$$
CREATE DEFINER=`root`@`hidden` PROCEDURE 
`austritt_telintern`()
begin
declare i int default 1;
declare x int default 5;

# cursor for personalDB.personalstamm
declare c_finished int default 0;
declare c_persnr int;
declare c_austritt date;
declare CUR_personalstamm cursor for select persnr, austritt from 
personalDB.personalstamm where austritt is not null;

declare continue handler for not found set c_finished = 1;

create index idx_personalstamm_persnr on `personalstamm`(persnr);
create index idx_telefon_persnr on `telefon`(persnr);

open CUR_personalstamm;
    iterate_personalstamm: loop
        fetch CUR_personalstamm into c_persnr, c_austritt;
        if c_finished = 1 then
            leave iterate_personalstamm;
        end if;
        update personalDB.telefon set telefon.gueltig_bis = c_austritt
        where telefon.persnr = c_persnr and telefon.gueltig_bis is null;
        if i <= x then leave iterate_personalstamm;
        end if;
        set i = i + 1;
    end loop iterate_personalstamm;
close CUR_personalstamm;
end$$

DELIMITER ;
;

Answer 1

The actual select/insert code looks straightforward and should not take that much time.

Do following:

• Create the index outside the procedure. Indexes are created once whereas a procedure can be called many times
• Do not serialize the queries when you do not need to. Use a join in a update instead of a cursor

SQL:

UPDATE telefon
  JOIN personalstamm ON personalstamm.persnr = telefon.persnr
SET telefon.gueltig_bis = personalstamm.austritt
WHERE telefon.gueltig_bis is null