Description:
"UPDATE ST_Traces SET text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) WHERE id = LastId;"
is changing id in the table
CREATE TABLE IF NOT EXISTS ST_Traces (
id TIMESTAMP(6) NOT NULL COMMENT 'Trace date time' PRIMARY KEY,
section VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'To separate parallel traces',
text VARCHAR(1024) NOT NULL COMMENT 'Human meaning of the trace'
) ENGINE=MEMORY;
How to repeat:
DELIMITER $$
DROP PROCEDURE IF EXISTS RecalcRefCount $$
CREATE PROCEDURE RecalcRefCount() NOT DETERMINISTIC
BEGIN
DECLARE LastId TIMESTAMP(6);
DECLARE e INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
SET @ProcedureName = 'RecalcRefCount';
CREATE TABLE IF NOT EXISTS ST_Traces (
id TIMESTAMP(6) NOT NULL COMMENT 'Trace date time' PRIMARY KEY,
section VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'To separate parallel traces',
text VARCHAR(1024) NOT NULL COMMENT 'Human meaning of the trace'
) ENGINE=MEMORY;
INSERT ST_Traces SET section = @ProcedureName, text="Started!";
INSERT ST_Traces SET section = @ProcedureName, text="Calcs BL_Values";
SELECT id INTO LastId from ST_Traces where section = @ProcedureName and text="Calcs BL_Values";
SELECT IFNULL(MIN(id),0), IFNULL(MAX(id),0) INTO @iCnt, @iMax FROM BS.BL_Values;
REPEAT
IF (@iCnt MOD 10000) = 1 THEN
SELECT id from ST_Traces where section = @ProcedureName and text="Calcs BL_Values";
UPDATE ST_Traces SET text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) WHERE id = LastId;
SELECT id from ST_Traces where section = @ProcedureName and text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax);
select @iCnt, LastId, CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax);
#INSERT ST_Traces SET section = @ProcedureName, text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax);
COMMIT;
END IF;
SET @iCnt = @iCnt + 1;
UNTIL @iCnt > @iMax END REPEAT;
COMMIT;
END $$
DELIMITER ;
mysql> CALL TMP_PROKOPIV.RecalcRefCount();
+----------------------------+
| id |
+----------------------------+
| 2015-08-12 11:00:28.603816 |
+----------------------------+
1 row in set (0.04 sec)
+----------------------------+
| id |
+----------------------------+
| 2015-08-12 11:00:28.631726 |
+----------------------------+
1 row in set (0.04 sec)
+-------+----------------------------+-----------------------------------------------+
| @iCnt | LastId | CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) |
+-------+----------------------------+-----------------------------------------------+
| 1 | 2015-08-12 11:00:28.603816 | Calcs BL_Values 1 of 107874076 |
+-------+----------------------------+-----------------------------------------------+
1 row in set (0.04 sec)
Empty set (5.76 sec)
Empty set (5.76 sec)
+-------+----------------------------+-----------------------------------------------+
| @iCnt | LastId | CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) |
+-------+----------------------------+-----------------------------------------------+
| 10001 | 2015-08-12 11:00:28.603816 | Calcs BL_Values 10001 of 107874076 |
+-------+----------------------------+-----------------------------------------------+
1 row in set (5.76 sec)
Description: "UPDATE ST_Traces SET text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) WHERE id = LastId;" is changing id in the table CREATE TABLE IF NOT EXISTS ST_Traces ( id TIMESTAMP(6) NOT NULL COMMENT 'Trace date time' PRIMARY KEY, section VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'To separate parallel traces', text VARCHAR(1024) NOT NULL COMMENT 'Human meaning of the trace' ) ENGINE=MEMORY; How to repeat: DELIMITER $$ DROP PROCEDURE IF EXISTS RecalcRefCount $$ CREATE PROCEDURE RecalcRefCount() NOT DETERMINISTIC BEGIN DECLARE LastId TIMESTAMP(6); DECLARE e INT DEFAULT 0; DECLARE i INT DEFAULT 0; SET @ProcedureName = 'RecalcRefCount'; CREATE TABLE IF NOT EXISTS ST_Traces ( id TIMESTAMP(6) NOT NULL COMMENT 'Trace date time' PRIMARY KEY, section VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'To separate parallel traces', text VARCHAR(1024) NOT NULL COMMENT 'Human meaning of the trace' ) ENGINE=MEMORY; INSERT ST_Traces SET section = @ProcedureName, text="Started!"; INSERT ST_Traces SET section = @ProcedureName, text="Calcs BL_Values"; SELECT id INTO LastId from ST_Traces where section = @ProcedureName and text="Calcs BL_Values"; SELECT IFNULL(MIN(id),0), IFNULL(MAX(id),0) INTO @iCnt, @iMax FROM BS.BL_Values; REPEAT IF (@iCnt MOD 10000) = 1 THEN SELECT id from ST_Traces where section = @ProcedureName and text="Calcs BL_Values"; UPDATE ST_Traces SET text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) WHERE id = LastId; SELECT id from ST_Traces where section = @ProcedureName and text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax); select @iCnt, LastId, CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax); #INSERT ST_Traces SET section = @ProcedureName, text=CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax); COMMIT; END IF; SET @iCnt = @iCnt + 1; UNTIL @iCnt > @iMax END REPEAT; COMMIT; END $$ DELIMITER ; mysql> CALL TMP_PROKOPIV.RecalcRefCount(); +----------------------------+ | id | +----------------------------+ | 2015-08-12 11:00:28.603816 | +----------------------------+ 1 row in set (0.04 sec) +----------------------------+ | id | +----------------------------+ | 2015-08-12 11:00:28.631726 | +----------------------------+ 1 row in set (0.04 sec) +-------+----------------------------+-----------------------------------------------+ | @iCnt | LastId | CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) | +-------+----------------------------+-----------------------------------------------+ | 1 | 2015-08-12 11:00:28.603816 | Calcs BL_Values 1 of 107874076 | +-------+----------------------------+-----------------------------------------------+ 1 row in set (0.04 sec) Empty set (5.76 sec) Empty set (5.76 sec) +-------+----------------------------+-----------------------------------------------+ | @iCnt | LastId | CONCAT("Calcs BL_Values ",@iCnt," of ",@iMax) | +-------+----------------------------+-----------------------------------------------+ | 10001 | 2015-08-12 11:00:28.603816 | Calcs BL_Values 10001 of 107874076 | +-------+----------------------------+-----------------------------------------------+ 1 row in set (5.76 sec)