path: root/mysql-test/suite/rpl/t/rpl_user_variables.test

###################################
#
# Test of replicating user variables
#
###################################

-- source include/master-slave.inc

disable_query_log;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
enable_query_log;

# Disable PS as the log positions differs
--disable_ps_protocol


# Clean up old slave's binlogs.
# The slave is started with --log-slave-updates
# and this test does SHOW BINLOG EVENTS on the slave's
# binlog. But previous tests can influence the current test's
# binlog (e.g. a temporary table in the previous test has not
# been explicitly deleted, or it has but the slave hasn't had
# enough time to catch it before STOP SLAVE, 
# and at the beginning of the current
# test the slave immediately writes DROP TEMPORARY TABLE this_old_table).
# We wait for the slave to have written all he wants to the binlog
# (otherwise RESET MASTER may come too early).
sync_slave_with_master;
reset master;
connection master;

create table t1(n char(30));
set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1;
set @s1:='This is a test', @r1:=12.5, @r2:=-12.5;
set @n1:=null;
set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def';
insert into t1 values (@i1), (@i2), (@i3), (@i4);
insert into t1 values (@r1), (@r2);
insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5);
insert into t1 values (@n1);
insert into t1 values (@n2); # not explicitly set before
insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1);
insert into t1 values (@a+(@b:=@a+1));
set @q:='abc';
insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2'));
set @a:=5;
insert into t1 values (@a),(@a);
# To flush the pending event, we add the following statement. RBR can
# concatenate the result of several statements, which SBR cannot.
select * from t1 where n = '<nonexistant>';
connection master1; # see if variable is reset in binlog when thread changes
insert into t1 values (@a),(@a),(@a*5);
SELECT * FROM t1 ORDER BY n;
sync_slave_with_master;
SELECT * FROM t1 ORDER BY n;
connection master;
insert into t1 select * FROM (select @var1 union  select @var2) AS t2;
drop table t1;
--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 Check the tables for correct data

SELECT * FROM t20;
SELECT * FROM t21;

sync_slave_with_master;

--echo 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);

# 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 Retrieve the values from the table

SELECT * FROM t1;

sync_slave_with_master;

--echo 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());

--echo Retrieve the values from the table

sync_slave_with_master;
connection master;

SELECT * from t1;

connection slave;

--echo 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 Check to see that data was inserted correctly in both tables

SELECT * from t1;
SELECT * from t2;

sync_slave_with_master;

--echo Check the tables for correct data and it matches master

SELECT * from t1;
SELECT * from t2;

connection master;
drop table t1, t2;

#
# Bug #12826: Possible to get inconsistent slave using SQL syntax Prepared Statements
#
connection master;
create table t1(a int, b int);
prepare s1 from 'insert into t1 values (@x:=@x+1, ?)';
set @x=1; execute s1 using @x;
select * from t1;
sync_slave_with_master;
connection slave;
select * from t1;
connection master;
drop table t1;

#
# Bug#33851: Passing UNSIGNED param to EXECUTE returns ERROR 1210
#

connection master;
create table t1(a int);
insert into t1 values (1),(2);
prepare s1 from 'insert into t1 select a from t1 limit ?';
set @x='1.1';
--disable_warnings
execute s1 using @x;
--enable_warnings
select * from t1;
sync_slave_with_master;
connection slave;
select * from t1;
connection master;
drop table t1;

--echo End of 5.0 tests.

# 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 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 Check to see that data was inserted correctly

INSERT INTO t1 VALUES(f1());
SELECT * FROM t1;

sync_slave_with_master;

--echo 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;

--source include/rpl_end.inc