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-13 13:04:53-05:00, cbell@mysql_cab. +3 -0
  BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
  This patch allows user-defined variables to be replicated if used in SF/Triggers.

  mysql-test/r/rpl_user_variables2.result@stripped, 2006-12-13 12:54:42-05:00, cbell@mysql_cab.
+51 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch allows user-defined variables to be replicated if used in SF/Triggers.
    

  mysql-test/r/rpl_user_variables2.result@stripped, 2006-12-13 12:54:42-05:00, cbell@mysql_cab.
+0 -0

  mysql-test/t/rpl_user_variables2.test@stripped, 2006-12-13 12:54:43-05:00, cbell@mysql_cab.
+45 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch allows user-defined variables to be replicated if used in SF/Triggers.
    

  mysql-test/t/rpl_user_variables2.test@stripped, 2006-12-13 12:54:43-05:00, cbell@mysql_cab.
+0 -0

  sql/item_func.cc@stripped, 2006-12-13 12:54:42-05:00, cbell@mysql_cab. +14 -1
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch allows user-defined variables to be replicated if used in SF/Triggers.

# 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-13 13:05:19 -05:00
+++ 1.341/sql/item_func.cc	2006-12-13 13:05:19 -05:00
@@ -4117,7 +4117,20 @@ 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)))
+  /*
+    BUG#20141 - User-defined variables are not replicated properly 
+                for SF/Triggers in SBR mode 
+    Write the @var value to the binary log for every query that has 
+    user-defined variables, not just update queries. Adding SQLCOM_END
+    identifies SP/Triggers that have user-defined variables. This allows 
+    users to use @vars inside sp and triggers.
+
+    Note: This is not necessary for RBR because when variables are used 
+    in other statements such as insert, the RBR replicates the data 
+    after the substitution.
+  */
+  if (!(opt_bin_log && 
+       (is_update_query(sql_command) || (sql_command == SQLCOM_END))))
   {
     *out_entry= var_entry;
     return 0;
--- New file ---
+++ mysql-test/r/rpl_user_variables2.result	06/12/13 12:54:42
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
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;

--- New file ---
+++ mysql-test/t/rpl_user_variables2.test	06/12/13 12:54:43
source include/master-slave.inc;
# User-defined variables are not replicated properly 
# for SF/Triggers in SBR mode
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;