[Commits] Rev 2982: Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is ignored in http://bazaar.launchpad.net/~maria-captains/maria/5.1

knielsen at knielsen-hq.org knielsen at knielsen-hq.org
Tue Nov 30 10:57:13 EET 2010


At http://bazaar.launchpad.net/~maria-captains/maria/5.1

------------------------------------------------------------
revno: 2982
revision-id: knielsen at knielsen-hq.org-20101130085710-lu60yg658w6zcbfk
parent: monty at mysql.com-20101130072533-vcnoikaq9ttohrdr
committer: knielsen at knielsen-hq.org
branch nick: work-5.1-bug54201
timestamp: Tue 2010-11-30 09:57:10 +0100
message:
  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.
=== modified file 'mysql-test/extra/rpl_tests/rpl_auto_increment.test'
--- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test	2009-12-03 11:19:05 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test	2010-11-30 08:57:10 +0000
@@ -241,3 +241,59 @@ DROP TABLE t1;
 DROP TABLE t2;
 SET SQL_MODE='';
 sync_slave_with_master;
+
+#
+# Bug#54201: "SET INSERT_ID" event must be ignored if corresponding event is
+# ignored.
+#
+connection master;
+
+CREATE TABLE t1(s VARCHAR(10)) ENGINE=myisam;
+# -slave.opt has --replicate-ignore-table=test.t_ignored1
+CREATE TABLE t_ignored1(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+
+connection slave;
+
+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);
+
+connection master;
+
+INSERT INTO t_ignored1 VALUES(NULL);
+INSERT INTO t1 VALUES('s');
+UPDATE t1 SET s='s1';
+
+# With Bug#54201, slave stops with duplicate key error here du to trigger
+# using the insert_id from insert on master into t1_ignored1
+sync_slave_with_master;
+connection slave;
+SELECT * FROM t1;
+
+connection master;
+CREATE TABLE t_ignored2(id INT AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+sync_slave_with_master;
+
+connection slave;
+STOP SLAVE;
+# Ignore the next INSERT into t_ignored2 and the INSERT_ID event just before it.
+SET GLOBAL sql_slave_skip_counter = 2;
+START SLAVE;
+
+connection master;
+INSERT INTO t_ignored2 VALUES(NULL);
+UPDATE t1 SET s='s2';
+sync_slave_with_master;
+
+connection slave;
+SELECT * FROM t1;
+SHOW TABLES LIKE 't\_ignored_';
+SELECT * FROM t_ignored2;
+DROP TABLE slave_only;
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t_ignored1;
+DROP TABLE t_ignored2;
+
+sync_slave_with_master;
\ No newline at end of file

=== modified file 'mysql-test/suite/rpl/r/rpl_auto_increment.result'
--- a/mysql-test/suite/rpl/r/rpl_auto_increment.result	2009-12-03 11:19:05 +0000
+++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result	2010-11-30 08:57:10 +0000
@@ -312,3 +312,32 @@ Comparing tables master:test.t2 and slav
 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;

=== added file 'mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt'
--- a/mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_auto_increment-slave.opt	2010-11-30 08:57:10 +0000
@@ -0,0 +1 @@
+--replicate-ignore-table=test.t_ignored1

=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc	2010-11-24 22:57:34 +0000
+++ b/sql/log_event.cc	2010-11-30 08:57:10 +0000
@@ -3307,6 +3307,19 @@ START SLAVE; . Query: '%s'", expected_er
     /* If the query was not ignored, it is printed to the general log */
     if (!thd->is_error() || thd->main_da.sql_errno() != ER_SLAVE_IGNORED_TABLE)
       general_log_write(thd, COM_QUERY, thd->query(), thd->query_length());
+    else
+    {
+      /*
+        Bug#54201: If we skip an INSERT query that uses auto_increment, then we
+        should reset any @@INSERT_ID set by an Intvar_log_event associated with
+        the query; otherwise the @@INSERT_ID will linger until the next INSERT
+        that uses auto_increment and may affect extra triggers on the slave etc.
+
+        We reset INSERT_ID unconditionally; it is probably cheaper than
+        checking if it is necessary.
+      */
+      thd->auto_inc_intervals_forced.empty();
+    }
 
 compare_errors:
 



More information about the commits mailing list