--disable_abort_on_error --disable_warnings DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; --enable_warnings CREATE TABLE t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT); CREATE TABLE t2 (f1 BIGINT); INSERT INTO t1 VALUES(0,0,0); INSERT INTO t2 VALUES(5),(6),(7); delimiter |; eval CREATE TRIGGER trg BEFORE INSERT ON t1 FOR EACH ROW BEGIN SET @counter = 1; SET @my_max1 = 0, @my_max2 = 0; # The problem disappears, when the next line is deleted. SELECT MAX(f1), MAX(f2) INTO @my_max1,@my_max2 FROM t1; SET new.f1 = @my_max1 + @counter, new.f2 = @my_max2 + @counter; # We generated a replacement value, so @my_max is already used. # Increment the number to be added next time. SET @counter = @counter + 1; END| delimiter ;| SELECT * FROM t1; # harmless - INSERT SELECT one record INSERT INTO t1 (f1,f2,f3) SELECT f1,f1,f1 FROM t2 WHERE f1 = 5; SELECT * FROM t1; # # bad - INSERT SELECT two records INSERT INTO t1 (f1,f2,f3) SELECT f1,f1,f1 FROM t2 WHERE f1 > 5; SELECT * FROM t1; # # harmless - INSERT two records INSERT INTO t1 (f1,f2,f3) VALUES(8,8,8),(9,9,9); SELECT * FROM t1; DROP TRIGGER trg;