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 cbell. When cbell 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, 2007-01-10 16:34:15-05:00, cbell@mysql_cab. +3 -0
  BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
  User-defined variables used inside of SF/SP/Triggers are not replicated. This can
  cause errors in data if the user-defined variables are used in generating data.
  This patch permits user-defined variables to be replicated if they are used in
  SF/SP/Triggers. This permits the slave to produce the same data as the master
  when user-defined variables are used within SF/SP/Triggers.
  
  This patch also fixes BUG#14914 and BUG#25167.

  mysql-test/r/rpl_user_variables.result@stripped, 2007-01-10 16:34:11-05:00, cbell@mysql_cab.
+72 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch modifies the test to ensure the test covers the use of user-defined
    variables in SF/SP/Triggers.

  mysql-test/t/rpl_user_variables.test@stripped, 2007-01-10 16:34:11-05:00, cbell@mysql_cab.
+66 -0
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    This patch modifies the test to ensure the test covers the use of user-defined
    variables in SF/SP/Triggers.

  sql/item_func.cc@stripped, 2007-01-10 16:34:12-05:00, cbell@mysql_cab. +37 -1
    BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR
mode.
    User-defined variables used inside of SF/SP/Triggers are not replicated. This can
    cause errors in data if the user-defined variables are used in generating data.
    This patch permits user-defined variables to be replicated if they are used in
    SF/SP/Triggers. This permits the slave to produce the same data as the master
    when user-defined variables are used within SF/SP/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	2007-01-10 16:34:34 -05:00
+++ 1.341/sql/item_func.cc	2007-01-10 16:34:34 -05:00
@@ -4117,10 +4117,46 @@ 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)))
+  /*
+    Log accesses to user-defined variables for any statements used in
+    sp/trigger. The @var must be added to the list thd->user_var_events 
+    to generate replication events. This is identified by thd->in_sub_stmt.
+  */
+  if (!(opt_bin_log && 
+       (is_update_query(sql_command) || thd->in_sub_stmt)))
   {
     *out_entry= var_entry;
     return 0;
+  }
+  /*
+    If the execution is in a SP/Func/Trigger (in_sub_stmt != 0),
+    check to see if the @var has already been used. If it has, don't
+    overwrite any @var SET commands that are used inside the
+    SP/Func/Trigger. This is necessary because the evaluation of the
+    @vars created in this function are evaluatd on the slave before 
+    the execution of the SP/Func/Trigger.
+  */
+  if (thd->in_sub_stmt)
+  {
+    bool found = false;
+    if (thd->user_var_events.elements)
+    {
+      BINLOG_USER_VAR_EVENT *uve;
+      for (uint i= 0; i < thd->user_var_events.elements; i++)
+      {
+        get_dynamic(&thd->user_var_events,(gptr) &uve, i);
+        if (stricmp(uve->user_var_event->name.str, var_entry->name.str) == 0)
+        {
+          found = true;
+          break;
+        }
+      }
+    }
+    if (found) //if already used, don't overwrite
+    {
+      *out_entry= var_entry;
+      return 0;
+    }
   }
 
   if (!var_entry)

--- 1.19/mysql-test/r/rpl_user_variables.result	2007-01-10 16:34:34 -05:00
+++ 1.20/mysql-test/r/rpl_user_variables.result	2007-01-10 16:34:34 -05:00
@@ -80,4 +80,76 @@ 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;
+CREATE TABLE t1 (i int);
+CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var);
+set @var = 1;
+INSERT INTO t1 VALUES (square());
+set @var = 2;
+INSERT INTO t1 VALUES (square());
+set @var = 3;
+INSERT INTO t1 VALUES (square());
+set @var = 4;
+INSERT INTO t1 VALUES (square());
+set @var = 5;
+INSERT INTO t1 VALUES (square());
+SELECT * FROM t1;
+i
+1
+4
+9
+16
+25
+SELECT * FROM t1;
+i
+1
+4
+9
+16
+25
+DROP TABLE t1;
 stop slave;

--- 1.18/mysql-test/t/rpl_user_variables.test	2007-01-10 16:34:34 -05:00
+++ 1.19/mysql-test/t/rpl_user_variables.test	2007-01-10 16:34:34 -05:00
@@ -53,5 +53,71 @@ 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;
+# Now test stored functions
+CREATE TABLE t1 (i int);
+CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var);
+set @var = 1;
+INSERT INTO t1 VALUES (square());
+set @var = 2;
+INSERT INTO t1 VALUES (square());
+set @var = 3;
+INSERT INTO t1 VALUES (square());
+set @var = 4;
+INSERT INTO t1 VALUES (square());
+set @var = 5;
+INSERT INTO t1 VALUES (square());
+SELECT * FROM t1;
+sync_slave_with_master;
+connection slave;
+SELECT * FROM t1;
+connection master;
+DROP TABLE t1;
 sync_slave_with_master;
 stop slave;