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

#
# Test of triggers with replication
# Adding statement include due to Bug 12574
# TODO: Remove statement include once 12574 is patched
--source include/have_binlog_format_mixed_or_statement.inc 
--source include/have_innodb.inc
--source include/master-slave.inc
connection master;

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_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;

--enable_warnings

#
# #12482: Triggers has side effects with auto_increment values
#

create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
create table t2 (a int auto_increment, primary key (a), b int) engine=innodb;
create table t3 (a int auto_increment, primary key (a), name varchar(64) not null, old_a int, old_b int, rand_value double not null);

delimiter |;
create trigger t1 before insert on t1 for each row
begin
 insert into t3 values (NULL, "t1", new.a, new.b, rand());
end|

create trigger t2 after insert on t2 for each row
begin
 insert into t3 values (NULL, "t2", new.a, new.b, rand());
end|
delimiter ;|

insert into t3 values(100,"log",0,0,0);

# Ensure we always have same random numbers
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;

# Emulate that we have rows 2-9 deleted on the slave
--disable_warnings
insert into t1 values(1,1,rand()),(NULL,2,rand());
insert into t2 (b) values(last_insert_id());
insert into t2 values(3,0),(NULL,0);
insert into t2 values(NULL,0),(500,0);
--enable_warnings

select a,b, truncate(rand_value,4) from t1;
select * from t2;
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
sync_slave_with_master;
select a,b, truncate(rand_value,4) from t1;
select * from t2;
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
connection master;
drop table t1,t2,t3;

#
# #12480: NOW() is not constant in a trigger
# #12481: Using NOW() in a stored function breaks statement based replication
#

# Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep()
connect (con2,localhost,root,,);
connection con2;
select get_lock("bug12480",2);
connection default;

create table t1 (a datetime,b  datetime, c datetime);
--disable_warnings
drop function if exists bug12480;
--enable_warnings

delimiter |;

create function bug12480() returns datetime
begin
  set @a=get_lock("bug12480",2);
  return now();
end|

create trigger t1_first before insert on t1
for each row begin
  set @a=get_lock("bug12480",2);
  set new.b= now();
  set new.c= bug12480();
end
|

delimiter ;|
# The trigger causes a warning for unsafe statement when
# binlog_format=statement since it uses get_lock.
--disable_warnings
insert into t1 set a = now();
--enable_warnings
select a=b && a=c from t1;
let $time=`select a from t1`;

# Check that definer attribute is replicated properly:
#   - dump definers on the master;
#   - wait for the slave to synchronize with the master;
#   - dump definers on the slave;

SELECT routine_name, definer
FROM information_schema.routines
WHERE routine_name = 'bug12480';

SELECT trigger_name, definer
FROM information_schema.triggers
WHERE trigger_name = 't1_first';

sync_slave_with_master;

# XXX: Definers of stored procedures and functions are not replicated. WL#2897
# (Complete definer support in the stored routines) addresses this issue. So,
# the result file is expected to be changed after implementation of this WL
# item.

SELECT routine_name, definer
FROM information_schema.routines
WHERE routine_name = 'bug12480';

SELECT trigger_name, definer
FROM information_schema.triggers
WHERE trigger_name = 't1_first';

select a=b && a=c from t1;
--disable_query_log
eval select a='$time' as 'test' from t1;
--enable_query_log

connection master;
disconnect con2;

truncate table t1;
drop trigger t1_first;

# The trigger causes a warning for unsafe statement when
# binlog_format=statement since it uses get_lock.
--disable_warnings
insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
--enable_warnings
select a=b && a=c from t1;

drop function bug12480;
drop table t1;

#
# #14614: Replication of tables with trigger generates error message if databases is changed
# Note. The error message is emitted by _myfree() using fprintf() to the stderr
# and because of that does not fall into the .result file.
#

create table t1 (i int);
create table t2 (i int);

delimiter |;
create trigger tr1 before insert on t1 for each row
begin
 insert into t2 values (1);
end|
delimiter ;|

create database other;
use other;
insert into test.t1 values (1);

sync_slave_with_master;

connection master;
use test;
drop table t1,t2;
drop database other;


#
# Test specific triggers including SELECT into var with replication
# BUG#13227:
# slave performs an update to the replicatable table, t1, 
# and modifies its local data, t3, by mean of its local trigger that uses
# another local table t2.
# Expected values are commented into queries.
#
# Body of the test executes in a loop since the problem occurred randomly.
# 

let $max_rows=5;
let $rnd=10;

--echo test case for BUG#13227
while ($rnd)
{
  --echo -------------------
    echo $rnd;
  --echo -------------------

### SETUP

--disable_warnings
  connection master;
  eval drop table if exists t1$rnd;
  connection slave;
  eval drop table if exists t2$rnd,t3$rnd;
--enable_warnings

  connection master;
  eval create table t1$rnd (f1 int)  /* 2 replicate */;  
  let $i=$max_rows;
  while ($i)
  {
    eval insert into t1$rnd values (-$i);
    dec $i;
  }

  sync_slave_with_master;
#connection slave;
  eval select * from t1$rnd;
  delimiter |;
  eval create trigger trg1$rnd before update on t1$rnd /* slave local */
  for each row
  begin
    DECLARE r integer;
    SELECT f2 INTO r FROM t2$rnd where f1=NEW.f1;
    INSERT INTO t3$rnd values (r);
  end|
  delimiter ;|
  eval create table t2$rnd (f1 int, f2 int) /* slave local */;        
  eval create table t3$rnd (f3 int) /* slave local */;                
  let $i=$max_rows;
  while ($i) 
  {
    eval insert into t2$rnd values ($i, $i*100);
    dec $i;
  }

### Test

#connection slave;

# trigger works as specified when updates from slave
  eval select * from t2$rnd;
  eval UPDATE t1$rnd SET f1=$max_rows where f1=-$max_rows;
  eval SELECT * from t1$rnd /* must be f1 $max_rows, 1 - $max_rows 2 - $max_rows ... -1 */;
  eval SELECT * from t3$rnd /* must be f3 $max_rows*100 */;

  connection master;
  let $i=$max_rows;
  while ($i)
  {
    eval UPDATE t1$rnd SET f1=$i where f1=-$i;
    dec $i;
  }
  
  sync_slave_with_master;
#connection slave;
  eval SELECT * from t1$rnd /* must be f1 $max_rows ... 1 */;
  eval SELECT * from t3$rnd /* must be f3 $max_rows * 100 ...  100 */;
  
### CLEANUP
#connection slave;
  eval drop trigger trg1$rnd;
  eval drop table t2$rnd,t3$rnd;
  
  connection master;
  eval drop table t1$rnd;
  sync_slave_with_master;
  connection master;
  
  dec $rnd;
}


#
# BUG#16266: Definer is not fully qualified error during replication.
#
# The idea of this test is to emulate replication of a trigger from the old
# master (master w/o "DEFINER in triggers" support) to the new slave and check
# that:
#   1. the trigger on the slave will be replicated w/o errors;
#   2. the trigger on the slave will be non-SUID (will have no DEFINER);
#   3. the trigger can be activated later on the slave w/o errors.
#
# In order to emulate this kind of replication, we make the slave playing the binlog,
# recorded by 5.0.16 master. This binlog contains the following statements:
#   CREATE TABLE t1(c INT);
#   CREATE TABLE t2(s CHAR(200));
#   CREATE TRIGGER trg1 AFTER INSERT ON t1
#     FOR EACH ROW
#       INSERT INTO t2 VALUES(CURRENT_USER());
#   INSERT INTO t1 VALUES(1);
#

# 1. Check that the trigger's replication is succeeded.

# Stop the slave.

connection slave;
STOP SLAVE;

# Replace master's binlog.

connection master;
let $MYSQLD_DATADIR= `select @@datadir`;
FLUSH LOGS;

# Stop master server
--let $rpl_server_number= 1
--source include/rpl_stop_server.inc

# Replace binlog
remove_file $MYSQLD_DATADIR/master-bin.000001;
copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLD_DATADIR/master-bin.000001;

--let $rpl_server_number= 1
--source include/rpl_start_server.inc

let $binlog_version= query_get_value(SHOW BINLOG EVENTS, Info, 1);


# Make the slave to replay the new binlog.
--echo --> Master binlog: $binlog_version

# Make the slave to replay the new binlog.

connection slave;
RESET SLAVE;
START SLAVE;

SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;

# Check that the replication succeeded.

SHOW TABLES LIKE 't_';
--replace_column 6 #
SHOW TRIGGERS;
SELECT * FROM t1;
SELECT * FROM t2;

# 2. Check that the trigger is non-SUID on the slave;
# 3. Check that the trigger can be activated on the slave.
#
# We disable warnings here since it affects the result file in
# different ways depending on the mode being used.

disable_warnings;
INSERT INTO t1 VALUES(2);
enable_warnings;

SELECT * FROM t1;
SELECT * FROM t2;

# That's all, cleanup.

DROP TRIGGER trg1;
DROP TABLE t1;
DROP TABLE t2;

STOP SLAVE;
RESET SLAVE;

# The master should be clean.

connection master;
SHOW TABLES LIKE 't_';
--replace_column 6 #
SHOW TRIGGERS;

RESET MASTER;

# Restart slave.

connection slave;
START SLAVE;


#
# BUG#20438: CREATE statements for views, stored routines and triggers can be
# not replicable.
#

--echo
--echo ---> Test for BUG#20438

# Prepare environment.

--echo
--echo ---> Preparing environment...
--connection master

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

--echo
--echo ---> Synchronizing slave with master...

--sync_slave_with_master

--echo
--connection master

# Test.

--echo
--echo ---> Creating objects...

CREATE TABLE t1(c INT);
CREATE TABLE t2(c INT);

/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
  FOR EACH ROW
    INSERT INTO t2 VALUES(NEW.c * 10) */;

--echo
--echo ---> Inserting value...

INSERT INTO t1 VALUES(1);

--echo
--echo ---> Checking on master...

SELECT * FROM t1;
SELECT * FROM t2;

--echo
--echo ---> Synchronizing slave with master...

--sync_slave_with_master

--echo
--echo ---> Checking on slave...

SELECT * FROM t1;
SELECT * FROM t2;

# Cleanup.

--echo
--connection master

--echo
--echo ---> Cleaning up...

DROP TABLE t1;
DROP TABLE t2;

--sync_slave_with_master
--connection master

#
# BUG#23703: DROP TRIGGER needs an IF EXISTS
#

connection master;

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1(a int, b varchar(50));

-- error ER_TRG_DOES_NOT_EXIST
drop trigger not_a_trigger;

drop trigger if exists not_a_trigger;

create trigger t1_bi before insert on t1
for each row set NEW.b := "In trigger t1_bi";

insert into t1 values (1, "a");
drop trigger if exists t1_bi;
insert into t1 values (2, "b");
drop trigger if exists t1_bi;
insert into t1 values (3, "c");

select * from t1;
sync_slave_with_master;
select * from t1;

connection master;

drop table t1;
sync_slave_with_master;

#
# Bug#40116: Uncommited changes are replicated and stay on slave after
# rollback on master
#

connection master;
--source include/rpl_reset.inc

connection slave;
connection master;

create table t1 ( f int ) engine = innodb;
create table log ( r int ) engine = myisam; 
create trigger tr
  after insert on t1
  for each row insert into log values ( new.f );

set autocommit = 0;
--disable_warnings
insert into t1 values ( 1 );
--enable_warnings
rollback;
sync_slave_with_master;

let $diff_tables= master:t1, slave:t1;
--source include/diff_tables.inc

let $diff_tables= master:log, slave:log;
--source include/diff_tables.inc

connection master;
drop table t1, log;
sync_slave_with_master;

#
# MDEV-6769 DROP TRIGGER IF NOT EXIST binlogged on master but not on slave
#
let $slave_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
connection master;
let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
drop trigger if exists notexistent;
source include/show_binlog_events.inc;
sync_slave_with_master;
let $binlog_start= $slave_pos;
source include/show_binlog_events.inc;
connection master;

#
# MDEV-6112 multiple triggers per table
#

create table t1 (a int primary key, b int);
create trigger tr3
  before insert on t1
  for each row set NEW.b := (NEW.b+1)*4;
create trigger tr1
  before insert on t1 
  for each row precedes tr3 set NEW.b := (NEW.b+1)*2;
create trigger tr2
  before insert on t1
  for each row follows tr1 set NEW.b := (NEW.b+1)*3;

--replace_column 6 #
show triggers;
insert into t1 values (1,1);
select * from t1;
sync_slave_with_master;
--replace_column 6 #
show triggers;
select * from t1;
connection master;
drop table t1;

#
# MDEV-10924 CREATE OR REPLACE TRIGGER which fails on PRECEDES/FOLLOWS drops
# the trigger, but isn't written to binlog; replication fails
#

CREATE TABLE t1 (i INT);
CREATE OR REPLACE TRIGGER tr BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
CREATE OR REPLACE TRIGGER tr BEFORE DELETE ON t1 FOR EACH ROW PRECEDES non_existing_trigger SET @a=2;
--replace_column 6 #
SHOW TRIGGERS;
--sync_slave_with_master
--replace_column 6 #
SHOW TRIGGERS;
connection master;
drop table t1;

#
# End of tests
#
--source include/rpl_end.inc