On all complex querys (returning many results and running long) I get the same error after some time: Error Code: 2014. Commands out of sync; you can't run this command now
e.g.:
## Prozedur Droppen
DROP PROCEDURE IF EXISTS ifob.uspUpdateHeatStatsAll;
SET SQL_SAFE_UPDATES = 0;
## Prozedur erstellen
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `uspUpdateHeatStatsAll`()
BEGIN
## Update table
DECLARE _HeatNrDINT int;
DECLARE _count int;
DECLARE _act int;
# Bearbeitungsliste erstellen
DROP TABLE IF EXISTS tmptblColumns;
CREATE TEMPORARY TABLE tmptblColumns (`HeatNrDINT` int) ENGINE = MEMORY;
# Delete old data
# Add columns to workertable
INSERT INTO tmptblColumns (`HeatNrDINT`) SELECT DISTINCT `Input.General.Values.HeatNrDINT` from tblmeasuringvaluesyear;
SET _count = (SELECT count(*) FROM tmptblColumns);
SET _act = 1;
#debug
#SELECT * FROM tmptblColumns;
BEGIN
DECLARE _done BOOL DEFAULT FALSE;
DECLARE column_cursor CURSOR FOR SELECT `HeatNrDINT` FROM `tmptblColumns`;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
#Alte Daten löschen
OPEN column_cursor;
FETCH column_cursor INTO _HeatNrDINT;
read_loop: LOOP
SELECT CONCAT('Bearbeite:',_HeatNrDINT,'(',_act,'/', _count,')') as `Status`;
CALL uspUpdateHeatStats(_HeatNrDINT);
SET _act = _act+1;
FETCH column_cursor INTO _HeatNrDINT;
IF _done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE column_cursor;
# Execute STMT
END;
END$$
DELIMITER ;
#TEST
CALL uspUpdateHeatStatsAll();
SELECT * FROM tblheatdata;
works for 50 loops and then it quits.
