mysql - MySQL 上所有复杂查询的错误(2014。命令超出查询)

Question

在所有复杂的查询中(返回很多结果并运行很长时间)一段时间后我得到了同样的错误:错误代码:2014。命令不同步；你现在不能运行这个命令

例如:

## 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;

工作 50 个循环然后退出。

Answer 1

我也遇到过这个问题。 MySQL Workbench 中的最大结果集数 defaults to 50 . 50 后会崩溃，因为之前的结果还没有取回。