Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is ignored

An INSERT query log event is preceeded by an INSERT_ID intvar event if the
INSERT allocates a new auto_increment value. But if we ignore the INSERT
due to --replicate-ignore-table or similar, then the INSERT_ID event is
still executed, and the set value of INSERT_ID lingers around in the
slave sql thread THD object indefinitely until the next INSERT that
happens to need allocation of a new auto_increment value.

Normally this does not cause problems as such following INSERT would
normally come with its own INSERT_ID event. In this bug, the user had
a trigger on the slave which was missing on the master, and this
trigger had an INSERT which could be affected. In any case, it seems
better to not leave a stray INSERT_ID hanging around in the sql thread
THD indefinitely.

Note that events can also be skipped from apply_event_and_update_pos();
however it is not possible in that code to skip the INSERT without also
skipping the INSERT_ID event.

2 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/suite/rpl/r/rpl_auto_increment.result b/mysql-test/suite/rpl/r/rpl_auto_increment.result
index 831e9b5c8b5..9fcd9bbd54c 100644
--- a/mysql-test/suite/rpl/r/rpl_auto_increment.result
+++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result
@@ -312,3 +312,32 @@ Comparing tables master:test.t2 and slave:test.t2
 DROP TABLE t1;
 DROP TABLE t2;
 SET SQL_MODE='';
+CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam;
+CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+CREATE TABLE test.slave_only(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+INSERT INTO slave_only VALUES(NULL);
+CREATE TRIGGER t1_update AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO slave_only VALUES(NULL);
+INSERT INTO t_ignored1 VALUES(NULL);
+INSERT INTO t1 VALUES('s');
+UPDATE t1 SET s='s1';
+SELECT * FROM t1;
+s
+s1
+CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+STOP SLAVE;
+SET GLOBAL sql_slave_skip_counter = 2;
+START SLAVE;
+INSERT INTO t_ignored2 VALUES(NULL);
+UPDATE t1 SET s='s2';
+SELECT * FROM t1;
+s
+s2
+SHOW TABLES LIKE 't\_ignored_';
+Tables_in_test (t\_ignored_)
+t_ignored2
+SELECT * FROM t_ignored2;
+id
+DROP TABLE slave_only;
+DROP TABLE t1;
+DROP TABLE t_ignored1;
+DROP TABLE t_ignored2;
diff --git a/mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt b/mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt
new file mode 100644
index 00000000000..79ed6f96a4a
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt
@@ -0,0 +1 @@
+--replicate-ignore-table=test.t_ignored1