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
onSELECT
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 ;
;
CodePudding user response:
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