CREATE TABLE testTable (`key` INT UNSIGNED, version BIGINT UNSIGNED, rowmarker CHAR(3) NOT NULL DEFAULT 'aaa', value MEDIUMBLOB, PRIMARY KEY (`key`, version)) ENGINE=InnoDB;
CREATE TABLE stopper (i INT);
INSERT INTO stopper VALUES (1);
CREATE PROCEDURE lft() BEGIN
DECLARE rnd VARBINARY(1024);
SET rnd = RANDOM_BYTES(1024);
INSERT INTO testTable (`key`, version, value) VALUES (0, 18446744073709551615, LEFT(rnd, 1));
WHILE (SELECT COUNT(*) FROM stopper) DO
UPDATE testTable SET value = LEFT(rnd, RAND()*1023+1) WHERE `key` = 0;
END WHILE;
END |
CREATE PROCEDURE rght() BEGIN
DECLARE rnd VARBINARY(1024);
SET rnd = RANDOM_BYTES(1024);
INSERT INTO testTable (`key`, version, value) VALUES (2, 18446744073709551615, LEFT(rnd, 1));
WHILE (SELECT COUNT(*) FROM stopper) DO
UPDATE testTable SET value = LEFT(rnd, RAND()*1023+1) WHERE `key` = 2;
END WHILE;
END |
CREATE PROCEDURE mdl() BEGIN
DECLARE rnd VARBINARY(1024);
DECLARE v BIGINT UNSIGNED DEFAULT 0;
SET rnd = RANDOM_BYTES(1024);
WHILE (SELECT COUNT(*) FROM stopper) DO
SET v = v + 1;
INSERT INTO testTable (`key`, version, value) VALUES (1, 18446744073709551615 - v, LEFT(rnd, RAND()*1023+1));
IF RAND() <= 0.05 THEN
DELETE from testTable WHERE `key`=1 AND version > 18446744073709551615 - v;
END IF;
END WHILE;
END|
CREATE PROCEDURE ck() BEGIN
DECLARE global_max_seen BIGINT UNSIGNED DEFAULT 0;
foo: WHILE global_max_seen < 5000 DO
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE local_max_seen BIGINT UNSIGNED DEFAULT 0;
DECLARE k INT;
DECLARE u BIGINT UNSIGNED;
DECLARE v BIGINT UNSIGNED;
DECLARE c1 CURSOR FOR select `key`, version FROM testTable WHERE `key` >= 1  AND `key` <= 1 ORDER BY `key` DESC; 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c1;
REPEAT
FETCH c1 INTO k, v;
SET u = 18446744073709551615 - v;
IF u > local_max_seen THEN
SET local_max_seen = u;
END IF;
UNTIL done END REPEAT;
CLOSE c1;
IF local_max_seen < global_max_seen THEN
SELECT "ERROR! local_max_seen < global_max_seen!" AS msg, local_max_seen, global_max_seen;
LEAVE foo;
END IF;
SET global_max_seen = local_max_seen;
END;
END WHILE;
END|
connect  con1,localhost,root,,;
CALL lft();
connect  con2,localhost,root,,;
CALL mdl();
connect  con3,localhost,root,,;
CALL rght();
connect  con4,localhost,root,,;
#
# Check procedure is not supposed to return ERROR.
CALL ck();
#
# Stop activity in other connections
DELETE FROM stopper;
disconnect con4;
connection con1;
disconnect con1;
connection con2;
disconnect con2;
connection con3;
disconnect con3;
connection default;
DROP TABLE stopper;
DROP TABLE testTable;
DROP PROCEDURE lft;
DROP PROCEDURE mdl;
DROP PROCEDURE rght;
DROP PROCEDURE ck;