2
# Test of triggers with replication
3
# Adding statement include due to Bug 12574
4
# TODO: Remove statement include once 12574 is patched
5
--source include/have_binlog_format_mixed_or_statement.inc
6
--source include/master-slave.inc
7
--source include/not_gtid_enabled.inc
10
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
12
call mtr.add_suppression("The master's UUID has changed, although this should not happen unless you have changed it manually.");
17
DROP TABLE IF EXISTS t1;
18
DROP TABLE IF EXISTS t2;
19
DROP TABLE IF EXISTS t3;
24
# #12482: Triggers has side effects with auto_increment values
27
create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
28
create table t2 (a int auto_increment, primary key (a), b int);
29
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);
32
create trigger t1 before insert on t1 for each row
34
insert into t3 values (NULL, "t1", new.a, new.b, rand());
37
create trigger t2 after insert on t2 for each row
39
insert into t3 values (NULL, "t2", new.a, new.b, rand());
43
insert into t3 values(100,"log",0,0,0);
45
# Ensure we always have same random numbers
46
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
48
# Emulate that we have rows 2-9 deleted on the slave
50
insert into t1 values(1,1,rand()),(NULL,2,rand());
51
insert into t2 (b) values(last_insert_id());
52
insert into t2 values(3,0),(NULL,0);
53
insert into t2 values(NULL,0),(500,0);
56
select a,b, truncate(rand_value,4) from t1;
58
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
59
sync_slave_with_master;
61
select "--- On slave --" as "";
63
select a,b, truncate(rand_value,4) from t1;
65
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
70
# #12480: NOW() is not constant in a trigger
71
# #12481: Using NOW() in a stored function breaks statement based replication
74
# Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep()
75
connect (con2,localhost,root,,);
77
select get_lock("bug12480",2);
80
create table t1 (a datetime,b datetime, c datetime);
82
drop function if exists bug12480;
87
create function bug12480() returns datetime
89
set @a=get_lock("bug12480",2);
93
create trigger t1_first before insert on t1
95
set @a=get_lock("bug12480",2);
97
set new.c= bug12480();
102
# The trigger causes a warning for unsafe statement when
103
# binlog_format=statement since it uses get_lock.
105
insert into t1 set a = now();
107
select a=b && a=c from t1;
108
let $time=`select a from t1`;
110
# Check that definer attribute is replicated properly:
111
# - dump definers on the master;
112
# - wait for the slave to synchronize with the master;
113
# - dump definers on the slave;
115
SELECT routine_name, definer
116
FROM information_schema.routines
117
WHERE routine_name = 'bug12480';
119
SELECT trigger_name, definer
120
FROM information_schema.triggers
121
WHERE trigger_name = 't1_first';
123
sync_slave_with_master;
125
select "--- On slave --" as "";
128
# XXX: Definers of stored procedures and functions are not replicated. WL#2897
129
# (Complete definer support in the stored routines) addresses this issue. So,
130
# the result file is expected to be changed after implementation of this WL
133
SELECT routine_name, definer
134
FROM information_schema.routines
135
WHERE routine_name = 'bug12480';
137
SELECT trigger_name, definer
138
FROM information_schema.triggers
139
WHERE trigger_name = 't1_first';
141
select a=b && a=c from t1;
143
eval select a='$time' as 'test' from t1;
150
drop trigger t1_first;
152
# The trigger causes a warning for unsafe statement when
153
# binlog_format=statement since it uses get_lock.
155
insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
157
select a=b && a=c from t1;
159
drop function bug12480;
163
# #14614: Replication of tables with trigger generates error message if databases is changed
164
# Note. The error message is emitted by _myfree() using fprintf() to the stderr
165
# and because of that does not fall into the .result file.
168
create table t1 (i int);
169
create table t2 (i int);
172
create trigger tr1 before insert on t1 for each row
174
insert into t2 values (1);
178
create database other;
180
insert into test.t1 values (1);
182
sync_slave_with_master;
191
# Test specific triggers including SELECT into var with replication
193
# slave performs an update to the replicatable table, t1,
194
# and modifies its local data, t3, by mean of its local trigger that uses
195
# another local table t2.
196
# Expected values are commented into queries.
198
# Body of the test executes in a loop since the problem occurred randomly.
204
--echo test case for BUG#13227
207
--echo -------------------
209
--echo -------------------
215
eval drop table if exists t1$rnd;
217
eval drop table if exists t2$rnd,t3$rnd;
221
eval create table t1$rnd (f1 int) /* 2 replicate */;
225
eval insert into t1$rnd values (-$i);
229
sync_slave_with_master;
231
eval select * from t1$rnd;
233
eval create trigger trg1$rnd before update on t1$rnd /* slave local */
237
SELECT f2 INTO r FROM t2$rnd where f1=NEW.f1;
238
INSERT INTO t3$rnd values (r);
241
eval create table t2$rnd (f1 int, f2 int) /* slave local */;
242
eval create table t3$rnd (f3 int) /* slave local */;
246
eval insert into t2$rnd values ($i, $i*100);
254
# trigger works as specified when updates from slave
255
eval select * from t2$rnd;
256
eval UPDATE t1$rnd SET f1=$max_rows where f1=-$max_rows;
257
eval SELECT * from t1$rnd /* must be f1 $max_rows, 1 - $max_rows 2 - $max_rows ... -1 */;
258
eval SELECT * from t3$rnd /* must be f3 $max_rows*100 */;
264
eval UPDATE t1$rnd SET f1=$i where f1=-$i;
268
sync_slave_with_master;
270
eval SELECT * from t1$rnd /* must be f1 $max_rows ... 1 */;
271
eval SELECT * from t3$rnd /* must be f3 $max_rows * 100 ... 100 */;
275
eval drop trigger trg1$rnd;
276
eval drop table t2$rnd,t3$rnd;
279
eval drop table t1$rnd;
280
sync_slave_with_master;
288
# BUG#16266: Definer is not fully qualified error during replication.
290
# The idea of this test is to emulate replication of a trigger from the old
291
# master (master w/o "DEFINER in triggers" support) to the new slave and check
293
# 1. the trigger on the slave will be replicated w/o errors;
294
# 2. the trigger on the slave will be non-SUID (will have no DEFINER);
295
# 3. the trigger can be activated later on the slave w/o errors.
297
# In order to emulate this kind of replication, we make the slave playing the binlog,
298
# recorded by 5.0.16 master. This binlog contains the following statements:
299
# CREATE TABLE t1(c INT);
300
# CREATE TABLE t2(s CHAR(200));
301
# CREATE TRIGGER trg1 AFTER INSERT ON t1
303
# INSERT INTO t2 VALUES(CURRENT_USER());
304
# INSERT INTO t1 VALUES(1);
307
# 1. Check that the trigger's replication is succeeded.
314
# Replace master's binlog.
317
let $MYSQLD_DATADIR= `select @@datadir`;
321
--let $rpl_server_number= 1
322
--source include/rpl_stop_server.inc
325
remove_file $MYSQLD_DATADIR/master-bin.000001;
326
copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLD_DATADIR/master-bin.000001;
328
--let $rpl_server_number= 1
329
--source include/rpl_start_server.inc
331
let $binlog_version= query_get_value(SHOW BINLOG EVENTS, Info, 1);
334
# Make the slave to replay the new binlog.
335
--echo --> Master binlog: $binlog_version
337
# Make the slave to replay the new binlog.
343
SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
345
# Check that the replication succeeded.
347
SHOW TABLES LIKE 't_';
352
# 2. Check that the trigger is non-SUID on the slave;
353
# 3. Check that the trigger can be activated on the slave.
355
# We disable warnings here since it affects the result file in
356
# different ways depending on the mode being used.
359
INSERT INTO t1 VALUES(2);
365
# That's all, cleanup.
374
# The master should be clean.
377
SHOW TABLES LIKE 't_';
389
# BUG#20438: CREATE statements for views, stored routines and triggers can be
393
--source include/rpl_reset.inc
395
--echo ---> Test for BUG#20438
397
# Prepare environment.
400
--echo ---> Preparing environment...
401
--echo ---> connection: master
405
DROP TABLE IF EXISTS t1;
406
DROP TABLE IF EXISTS t2;
410
--echo ---> Synchronizing slave with master...
412
--sync_slave_with_master
415
--echo ---> connection: master
421
--echo ---> Creating objects...
423
CREATE TABLE t1(c INT);
424
CREATE TABLE t2(c INT);
426
/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
428
INSERT INTO t2 VALUES(NEW.c * 10) */;
431
--echo ---> Inserting value...
433
INSERT INTO t1 VALUES(1);
436
--echo ---> Checking on master...
442
--echo ---> Synchronizing slave with master...
444
--sync_slave_with_master
446
--echo ---> connection: master
449
--echo ---> Checking on slave...
457
--echo ---> connection: master
461
--echo ---> Cleaning up...
466
--sync_slave_with_master
470
# BUG#23703: DROP TRIGGER needs an IF EXISTS
476
drop table if exists t1;
479
create table t1(a int, b varchar(50));
481
-- error ER_TRG_DOES_NOT_EXIST
482
drop trigger not_a_trigger;
484
drop trigger if exists not_a_trigger;
486
create trigger t1_bi before insert on t1
487
for each row set NEW.b := "In trigger t1_bi";
489
insert into t1 values (1, "a");
490
drop trigger if exists t1_bi;
491
insert into t1 values (2, "b");
492
drop trigger if exists t1_bi;
493
insert into t1 values (3, "c");
496
sync_slave_with_master;
502
sync_slave_with_master;
505
# Bug#40116: Uncommited changes are replicated and stay on slave after
510
--source include/rpl_reset.inc
512
source include/have_innodb.inc;
514
source include/have_innodb.inc;
517
create table t1 ( f int ) engine = innodb;
518
create table log ( r int ) engine = myisam;
521
for each row insert into log values ( new.f );
525
insert into t1 values ( 1 );
528
sync_slave_with_master;
530
let $diff_tables= master:t1, slave:t1;
531
--source include/diff_tables.inc
533
let $diff_tables= master:log, slave:log;
534
--source include/diff_tables.inc
538
sync_slave_with_master;
543
--source include/rpl_end.inc