bk commit into 5.1 tree (cbell:1.2362) BUG#20141

[cbell]

Below is the list of changes that have just been committed into a local
5.1 repository of Chuck. When Chuck does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2006-12-14 11:21:28-05:00, cbell@mysql_cab. +3 -0
  BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
  This patch modifies the get_var_with_binlog to allow the capture and binlogging of the 
  user-defined variables (@vars) when included in SF/Triggers in an 'if (@var)' operation.

  mysql-test/r/rpl_user_variables.result@stripped, 2006-12-14 11:21:20-05:00, cbell@mysql_cab.
+45 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch adds a test to verify that the @vars are replicated when included
    in SF/Triggers in an 'if (@var)' operation.

  mysql-test/t/rpl_user_variables.test@stripped, 2006-12-14 11:21:21-05:00, cbell@mysql_cab.
+47 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch adds a test to verify that the @vars are replicated when included
    in SF/Triggers in an 'if (@var)' operation.

  sql/item_func.cc@stripped, 2006-12-14 11:21:21-05:00, cbell@mysql_cab. +7 -1
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch modifies the get_var_with_binlog to allow the capture and binlogging of the

    user-defined variables (@vars) when included in SF/Triggers in an 'if (@var)'
operation.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	cbell
# Host:	mysql_cab.
# Root:	C:/source/c++/mysql-5.1_BUG_20141

--- 1.340/sql/item_func.cc	2006-12-14 11:21:50 -05:00
+++ 1.341/sql/item_func.cc	2006-12-14 11:21:50 -05:00
@@ -4117,7 +4117,13 @@ int get_var_with_binlog(THD *thd, enum_s
   user_var_entry *var_entry;
   var_entry= get_variable(&thd->user_vars, name, 0);
 
-  if (!(opt_bin_log && is_update_query(sql_command)))
+  /*
+    If an `if (@var)' operator (not a query) of sp/trigger is used, the 
+    @var must be added to the list thd->user_var_events to generate 
+    replication events. This is identified by sql_command == SQLCOM_END.
+  */
+  if (!(opt_bin_log && 
+       (is_update_query(sql_command) || (sql_command == SQLCOM_END))))
   {
     *out_entry= var_entry;
     return 0;

--- 1.19/mysql-test/r/rpl_user_variables.result	2006-12-14 11:21:51 -05:00
+++ 1.20/mysql-test/r/rpl_user_variables.result	2006-12-14 11:21:51 -05:00
@@ -80,4 +80,49 @@ abc\def
 This is a test
 insert into t1 select * FROM (select @var1 union  select @var2) AS t2;
 drop table t1;
+CREATE TABLE t20 (a varchar(20));
+CREATE TABLE t21 (a varchar(20));
+CREATE PROCEDURE test.insert()
+BEGIN
+IF (@VAR)
+THEN
+insert into test.t20 VALUES ('SP_TRUE');
+ELSE
+insert into test.t20 VALUES ('SP_FALSE');
+END IF;
+END|
+CREATE TRIGGER test.insert_bi BEFORE INSERT
+ON test.t20 FOR EACH ROW
+BEGIN
+IF (@VAR)
+THEN
+insert into test.t21 VALUES ('TRIG_TRUE');
+ELSE
+insert into test.t21 VALUES ('TRIG_FALSE');
+END IF;
+END|
+stop slave;
+start slave;
+SET @VAR=0;
+call test.insert();
+SET @VAR=1;
+call test.insert();
+select * from t20;
+a
+SP_FALSE
+SP_TRUE
+select * from t21;
+a
+TRIG_FALSE
+TRIG_TRUE
+select * from t20;
+a
+SP_FALSE
+SP_TRUE
+select * from t21;
+a
+TRIG_FALSE
+TRIG_TRUE
+drop table t20;
+drop table t21;
 stop slave;

--- 1.18/mysql-test/t/rpl_user_variables.test	2006-12-14 11:21:51 -05:00
+++ 1.19/mysql-test/t/rpl_user_variables.test	2006-12-14 11:21:51 -05:00
@@ -53,5 +53,52 @@ SELECT * FROM t1 ORDER BY n;
 connection master;
 insert into t1 select * FROM (select @var1 union  select @var2) AS t2;
 drop table t1;
+
+#
+# BUG#20141
+# The following test ensures that if user-defined variables are used in SF/Triggers
+# that they are replicated correctly. This test should be run in both SBR and RBR
+# modes.
+#
+CREATE TABLE t20 (a varchar(20));
+CREATE TABLE t21 (a varchar(20));
+delimiter |;
+CREATE PROCEDURE test.insert()
+BEGIN
+  IF (@VAR)
+  THEN
+      insert into test.t20 VALUES ('SP_TRUE');
+  ELSE
+      insert into test.t20 VALUES ('SP_FALSE');
+  END IF;
+END|
+CREATE TRIGGER test.insert_bi BEFORE INSERT
+    ON test.t20 FOR EACH ROW
+    BEGIN
+      IF (@VAR)
+      THEN
+       insert into test.t21 VALUES ('TRIG_TRUE');
+      ELSE
+       insert into test.t21 VALUES ('TRIG_FALSE');
+      END IF;
+    END|
+delimiter ;|
+sync_slave_with_master;
+stop slave;
+start slave;
+connection master;
+SET @VAR=0;
+call test.insert();
+SET @VAR=1;
+call test.insert();
+select * from t20;
+select * from t21;
+sync_slave_with_master;
+connection slave;
+select * from t20;
+select * from t21;
+connection master;
+drop table t20;
+drop table t21;
 sync_slave_with_master;
 stop slave;