Below is the list of changes that have just been committed into a local
5.0 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-23 16:46:30-05:00, cbell@mysql_cab_desk. +7 -0
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
User-defined variables used inside of SF/Triggers are not replicated if they are not
involved in update queries. This can cause errors in data if the user-defined variables
are later used in generating data.
The patch modifies the code to permit the logging of user-defined variables when they
are used in SF/Triggers if they appear in the execution of a SF/Trigger. This is
accomplished in the get_var_with_binlog() method in item_func.cc by adding a condition
that identifies when a SF/Trigger is executing (thd->in_sub_stmt). If this is true,
the additional code added to the mysql_bin_log.start_union_events can be used to properly
add the replication of the user-defined variable if it is being used and not already
used (replicated).
Together these changes permit the slave to produce the same data as the master
when user-defined variables are used within SF/Triggers.
Note: Only stored procedures called from SF/Triggers are affected by this bug. Otherwise,
this patch does not affect stored procedure replication.
mysql-test/r/rpl_user_variables.result@stripped, 2007-01-23 16:45:37-05:00, cbell@mysql_cab_desk. +176 -0
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch adds the correct results for execution of the added test procedures to the
rpl_user_variables test.
mysql-test/t/rpl_user_variables.test@stripped, 2007-01-23 16:45:37-05:00, cbell@mysql_cab_desk. +300 -3
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch adds additional tests to the rpl_user_variables test that test many of the
different ways user-defined variables can be required to be replicated.
sql/item_func.cc@stripped, 2007-01-23 16:45:38-05:00, cbell@mysql_cab_desk. +8 -1
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
To properly log accesses to user-defined variables from stored functions/triggers,
the get_var_with_binlog() method needs to log references to such variables even from
non-table-updating statements within them.
sql/log.cc@stripped, 2007-01-23 16:45:38-05:00, cbell@mysql_cab_desk. +2 -2
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch modifies the start_union_events method to accept the query id from a parameter.
This allows callers to set the query_id to the id of the sub statement such as a trigger
or stored function. Which permits the code to identify when a user defined variable has
been used by the statement and this already present in THD::user_var_event.
Note:
The changes to sql_class.cc, sp_head.cc, and log.cc are designed to allow the proper
replication of access to user-defined variables under a special test case (the last case
shown in rpl_user_variables.test).
sql/sp_head.cc@stripped, 2007-01-23 16:45:39-05:00, cbell@mysql_cab_desk. +17 -1
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch modifies the code to allow for cases where events for function calls have
a separate union for each event and thus cannot use the query_id of the caller as the
start of the union. Thus, we use an artifically created query_id to set the start of
the events.
Note:
The changes to sql_class.cc, sp_head.cc, and log.cc are designed to allow the proper
replication of access to user-defined variables under a special test case (the last case
shown in rpl_user_variables.test).
sql/sql_class.cc@stripped, 2007-01-23 16:45:39-05:00, cbell@mysql_cab_desk. +7 -0
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch adds the query_id parameter to the calls to mysql_bin_log.start_union_events().
Note:
The changes to sql_class.cc, sp_head.cc, and log.cc are designed to allow the proper
replication of access to user-defined variables under a special test case (the last case
shown in rpl_user_variables.test).
sql/sql_class.h@stripped, 2007-01-23 16:45:40-05:00, cbell@mysql_cab_desk. +1 -1
BUG#20141 - User-defined variables are not replicated properly for SF/Triggers in SBR mode.
This patch adds the query_id parameter to the calls to mysql_bin_log.start_union_events().
# 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_desk.
# Root: C:/source/c++/mysql-5.0_BUG_20141
--- 1.322/sql/item_func.cc 2007-01-23 16:46:41 -05:00
+++ 1.323/sql/item_func.cc 2007-01-23 16:46:41 -05:00
@@ -4223,7 +4223,14 @@
user_var_entry *var_entry;
var_entry= get_variable(&thd->user_vars, name, 0);
- if (!(opt_bin_log && is_update_query(sql_command)))
+ /*
+ Any reference to user-defined variable which is done from stored
+ function or trigger affects their execution and the execution of the
+ calling statement. We must log all such variables even if they are
+ not involved in table-updating statements.
+ */
+ if (!(opt_bin_log &&
+ (is_update_query(sql_command) || thd->in_sub_stmt)))
{
*out_entry= var_entry;
return 0;
--- 1.200/sql/log.cc 2007-01-23 16:46:41 -05:00
+++ 1.201/sql/log.cc 2007-01-23 16:46:41 -05:00
@@ -1577,13 +1577,13 @@
return err;
}
-void MYSQL_LOG::start_union_events(THD *thd)
+void MYSQL_LOG::start_union_events(THD *thd, query_id_t query_id_param)
{
DBUG_ASSERT(!thd->binlog_evt_union.do_union);
thd->binlog_evt_union.do_union= TRUE;
thd->binlog_evt_union.unioned_events= FALSE;
thd->binlog_evt_union.unioned_events_trans= FALSE;
- thd->binlog_evt_union.first_query_id= thd->query_id;
+ thd->binlog_evt_union.first_query_id= query_id_param;
}
void MYSQL_LOG::stop_union_events(THD *thd)
--- 1.256/sql/sql_class.cc 2007-01-23 16:46:41 -05:00
+++ 1.257/sql/sql_class.cc 2007-01-23 16:46:41 -05:00
@@ -2051,6 +2051,10 @@
if (!lex->requires_prelocking() || is_update_query(lex->sql_command))
options&= ~OPTION_BIN_LOG;
+
+ if ((backup->options & OPTION_BIN_LOG) && is_update_query(lex->sql_command))
+ mysql_bin_log.start_union_events(this, this->query_id);
+
/* Disable result sets */
client_capabilities &= ~CLIENT_MULTI_RESULTS;
in_sub_stmt|= new_state;
@@ -2096,6 +2100,9 @@
limit_found_rows= backup->limit_found_rows;
sent_row_count= backup->sent_row_count;
client_capabilities= backup->client_capabilities;
+
+ if ((options & OPTION_BIN_LOG) && is_update_query(lex->sql_command))
+ mysql_bin_log.stop_union_events(this);
/*
The following is added to the old values as we are interested in the
--- 1.316/sql/sql_class.h 2007-01-23 16:46:41 -05:00
+++ 1.317/sql/sql_class.h 2007-01-23 16:46:41 -05:00
@@ -311,7 +311,7 @@
bool write(Log_event* event_info); // binary log write
bool write(THD *thd, IO_CACHE *cache, Log_event *commit_event);
- void start_union_events(THD *thd);
+ void start_union_events(THD *thd, query_id_t query_id_param);
void stop_union_events(THD *thd);
bool is_query_in_union(THD *thd, query_id_t query_id_param);
--- 1.17/mysql-test/r/rpl_user_variables.result 2007-01-23 16:46:41 -05:00
+++ 1.18/mysql-test/r/rpl_user_variables.result 2007-01-23 16:46:41 -05:00
@@ -108,4 +108,180 @@
slave-bin.000001 # Query 1 # use `test`; insert into t1 values (@a),(@a),(@a*5)
insert into t1 select * FROM (select @var1 union select @var2) AS t2;
drop table t1;
+End of 4.1 tests.
+DROP TABLE IF EXISTS t20;
+DROP TABLE IF EXISTS t21;
+DROP PROCEDURE IF EXISTS test.insert;
+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|
+SET @VAR=0;
+CALL test.insert();
+SET @VAR=1;
+CALL test.insert();
+On master: Check the tables for correct data
+SELECT * FROM t20;
+a
+SP_FALSE
+SP_TRUE
+SELECT * FROM t21;
+a
+TRIG_FALSE
+TRIG_TRUE
+On slave: Check the tables for correct data and it matches master
+SELECT * FROM t20;
+a
+SP_FALSE
+SP_TRUE
+SELECT * FROM t21;
+a
+TRIG_FALSE
+TRIG_TRUE
+DROP TABLE t20;
+DROP TABLE t21;
+DROP PROCEDURE test.insert;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS test.square;
+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());
+On master: Retrieve the values from the table
+SELECT * FROM t1;
+i
+1
+4
+9
+16
+25
+On slave: Retrieve the values from the table and verify they are the same as on master
+SELECT * FROM t1;
+i
+1
+4
+9
+16
+25
+DROP TABLE t1;
+DROP FUNCTION test.square;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+CREATE TABLE t1(a int);
+CREATE FUNCTION f1() returns int deterministic
+BEGIN
+return @a;
+END |
+CREATE FUNCTION f2() returns int deterministic
+BEGIN
+IF (@b > 0) then
+SET @c = (@a + @b);
+else
+SET @c = (@a - 1);
+END if;
+return @c;
+END |
+SET @a=500;
+INSERT INTO t1 values(f1());
+SET @b = 125;
+SET @c = 1;
+INSERT INTO t1 values(f2());
+On master: Retrieve the values from the table
+SELECT * from t1;
+a
+500
+625
+On slave: Check the tables for correct data and it matches master
+SELECT * from t1;
+a
+500
+625
+DROP TABLE t1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (i int);
+CREATE TABLE t2 (k int);
+CREATE trigger t1_bi before INSERT on t1 for each row
+BEGIN
+INSERT INTO t2 values (@a);
+SET @a:=42;
+INSERT INTO t2 values (@a);
+END |
+SET @a:=100;
+INSERT INTO t1 values (5);
+On master: Check to see that data was inserted correctly in both tables
+SELECT * from t1;
+i
+5
+SELECT * from t2;
+k
+100
+42
+On slave: Check the tables for correct data and it matches master
+SELECT * from t1;
+i
+5
+SELECT * from t2;
+k
+100
+42
+End of 5.0 tests.
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+CREATE TABLE t1 (i INT);
+CREATE FUNCTION f1() RETURNS INT RETURN @a;
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+INSERT INTO t1 VALUES (10 + @a);
+RETURN 0;
+END|
+SET @a:=123;
+SELECT f1(), f2();
+f1() f2()
+123 0
+On master: Check to see that data was inserted correctly
+INSERT INTO t1 VALUES(f1());
+SELECT * FROM t1;
+i
+133
+123
+On slave: Check the table for correct data and it matches master
+SELECT * FROM t1;
+i
+133
+123
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
stop slave;
--- 1.14/mysql-test/t/rpl_user_variables.test 2007-01-23 16:46:41 -05:00
+++ 1.15/mysql-test/t/rpl_user_variables.test 2007-01-23 16:46:41 -05:00
@@ -57,8 +57,305 @@
drop table t1;
save_master_pos;
-connection slave;
-sync_with_master;
+--echo End of 4.1 tests.
+
+# BUG#20141
+# The following tests ensure that if user-defined variables are used in SF/Triggers
+# that they are replicated correctly. These tests should be run in both SBR and RBR
+# modes.
+
+# This test uses a procedure that inserts data values based on the value of a
+# user-defined variable. It also has a trigger that inserts data based on the
+# same variable. Successful test runs show that the @var is replicated
+# properly and that the procedure and trigger insert the correct data on the
+# slave.
+#
+# The test of stored procedure was included for completeness. Replication of stored
+# procedures was not directly affected by BUG#20141.
+#
+# This test was constructed for BUG#20141
+
+--disable_warnings
+DROP TABLE IF EXISTS t20;
+DROP TABLE IF EXISTS t21;
+DROP PROCEDURE IF EXISTS test.insert;
+--enable_warnings
+
+CREATE TABLE t20 (a VARCHAR(20));
+CREATE TABLE t21 (a VARCHAR(20));
+DELIMITER |;
+
+# Create a procedure that uses the @var for flow control
+
+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 a trigger that uses the @var for flow control
+
+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;
+connection master;
+
+# Set @var and call the procedure, repeat with different values
+
+SET @VAR=0;
+CALL test.insert();
+SET @VAR=1;
+CALL test.insert();
+
+--echo On master: Check the tables for correct data
+
+SELECT * FROM t20;
+SELECT * FROM t21;
+
+sync_slave_with_master;
+
+--echo On slave: Check the tables for correct data and it matches master
+
+SELECT * FROM t20;
+SELECT * FROM t21;
+connection master;
+
+# Cleanup
+
+DROP TABLE t20;
+DROP TABLE t21;
+DROP PROCEDURE test.insert;
+
+# This test uses a stored function that uses user-defined variables to return data
+# This test was constructed for BUG#20141
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS test.square;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+# Create function that returns a value from @var. In this case, the square function
+
+CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var);
+
+sync_slave_with_master;
+connection master;
+
+# Set the @var to different values and insert them into a table
+
+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());
+
+--echo On master: Retrieve the values from the table
+
+SELECT * FROM t1;
+
+sync_slave_with_master;
+
+--echo On slave: Retrieve the values from the table and verify they are the same as on master
+
+SELECT * FROM t1;
+
+connection master;
+
+# Cleanup
+
+DROP TABLE t1;
+DROP FUNCTION test.square;
+
+# This test uses stored functions that uses user-defined variables to return data
+# based on the use of @vars inside a function body.
+# This test was constructed for BUG#14914
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+--enable_warnings
+
+CREATE TABLE t1(a int);
+DELIMITER |;
+
+# Create a function that simply returns the value of an @var.
+# Create a function that uses an @var for flow control, creates and uses another
+# @var and sets its value to a value based on another @var.
+
+CREATE FUNCTION f1() returns int deterministic
+BEGIN
+ return @a;
+END |
+
+CREATE FUNCTION f2() returns int deterministic
+BEGIN
+ IF (@b > 0) then
+ SET @c = (@a + @b);
+ else
+ SET @c = (@a - 1);
+ END if;
+ return @c;
+END |
+DELIMITER ;|
+
+sync_slave_with_master;
+connection master;
+
+# Set an @var to a value and insert data into a table using the first function.
+# Set two more @vars to some values and insert data into a table using the second function.
+
+SET @a=500;
+INSERT INTO t1 values(f1());
+SET @b = 125;
+SET @c = 1;
+INSERT INTO t1 values(f2());
+
+sync_slave_with_master;
+
+--echo On master: Retrieve the values from the table
+
+SELECT * from t1;
+
+--echo On slave: Check the tables for correct data and it matches master
+
+SELECT * from t1;
+
+connection master;
+
+# Cleanup
+
+DROP TABLE t1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+
+# This test uses a function that changes a user-defined variable in its body. This test
+# will ensure the @vars are replicated when needed and not interrupt the normal execution
+# of the function on the slave. This also applies to procedures and triggers.
+#
+# This test was constructed for BUG#25167
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+CREATE TABLE t1 (i int);
+CREATE TABLE t2 (k int);
+DELIMITER |;
+
+# Create a trigger that inserts data into another table, changes the @var then inserts
+# another row with the modified value.
+
+CREATE trigger t1_bi before INSERT on t1 for each row
+BEGIN
+ INSERT INTO t2 values (@a);
+ SET @a:=42;
+ INSERT INTO t2 values (@a);
+END |
+DELIMITER ;|
+
+sync_slave_with_master;
+connection master;
+
+# Set the @var to a value then insert data into first table.
+
+SET @a:=100;
+INSERT INTO t1 values (5);
+
+--echo On master: Check to see that data was inserted correctly in both tables
+
+SELECT * from t1;
+SELECT * from t2;
+
+sync_slave_with_master;
+
+--echo On slave: Check the tables for correct data and it matches master
+
+SELECT * from t1;
+SELECT * from t2;
+
+connection master;
+
+--echo End of 5.0 tests.
+
+# Cleanup
+
+DROP TABLE t1;
+DROP TABLE t2;
+
+# This test uses a stored function that uses user-defined variables to return data
+# The test ensures the value of the user-defined variable is replicated correctly
+# and in the correct order of assignment.
+# This test was constructed for BUG#20141
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+DROP FUNCTION IF EXISTS f2;
+--enable_warnings
+
+CREATE TABLE t1 (i INT);
+
+# Create two functions. One simply returns the user-defined variable. The other
+# returns a value based on the user-defined variable.
+
+CREATE FUNCTION f1() RETURNS INT RETURN @a;
+DELIMITER |;
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+ INSERT INTO t1 VALUES (10 + @a);
+ RETURN 0;
+END|
+DELIMITER ;|
+
+sync_slave_with_master;
+connection master;
+
+# Set the variable and execute the functions.
+
+SET @a:=123;
+SELECT f1(), f2();
+
+--echo On master: Check to see that data was inserted correctly
+
+INSERT INTO t1 VALUES(f1());
+SELECT * FROM t1;
+
+sync_slave_with_master;
+
+--echo On slave: Check the table for correct data and it matches master
+
+SELECT * FROM t1;
+
+connection master;
+
+# Cleanup
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP TABLE t1;
+
+sync_slave_with_master;
stop slave;
-# End of 4.1 tests
--- 1.230/sql/sp_head.cc 2007-01-23 16:46:41 -05:00
+++ 1.231/sql/sp_head.cc 2007-01-23 16:46:41 -05:00
@@ -1464,8 +1464,24 @@
binlog_save_options= thd->options;
if (need_binlog_call)
{
+ query_id_t q;
reset_dynamic(&thd->user_var_events);
- mysql_bin_log.start_union_events(thd);
+ /*
+ In case of artificially constructed events for function calls
+ we have separate union for each such event and hence can't use
+ query_id of real calling statement as the start of all these
+ unions (this will break logic of replication of user-defined
+ variables). So we use artifical value which is guaranteed to
+ be greater than all query_id's of all statements belonging
+ to previous events/unions.
+ Possible alternative to this is logging of all function invocations
+ as one select and not resetting THD::user_var_events before
+ each invocation.
+ */
+ VOID(pthread_mutex_lock(&LOCK_thread_count));
+ q= ::query_id;
+ VOID(pthread_mutex_unlock(&LOCK_thread_count));
+ mysql_bin_log.start_union_events(thd, q + 1);
}
/*
| Thread |
|---|
| • bk commit into 5.0 tree (cbell:1.2392) BUG#20141 | cbell | 23 Jan |
