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
8
CALL mtr.add_suppression("Statement may not be safe to log in statement format.");
11
DROP TABLE IF EXISTS t1;
12
DROP TABLE IF EXISTS t2;
13
DROP TABLE IF EXISTS t3;
18
# #12482: Triggers has side effects with auto_increment values
21
create table t1 (a int auto_increment, primary key (a), b int, rand_value double not null);
22
create table t2 (a int auto_increment, primary key (a), b int);
23
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);
26
create trigger t1 before insert on t1 for each row
28
insert into t3 values (NULL, "t1", new.a, new.b, rand());
31
create trigger t2 after insert on t2 for each row
33
insert into t3 values (NULL, "t2", new.a, new.b, rand());
37
insert into t3 values(100,"log",0,0,0);
39
# Ensure we always have same random numbers
40
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
42
# Emulate that we have rows 2-9 deleted on the slave
44
insert into t1 values(1,1,rand()),(NULL,2,rand());
45
insert into t2 (b) values(last_insert_id());
46
insert into t2 values(3,0),(NULL,0);
47
insert into t2 values(NULL,0),(500,0);
50
select a,b, truncate(rand_value,4) from t1;
52
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
53
sync_slave_with_master;
55
select "--- On slave --" as "";
57
select a,b, truncate(rand_value,4) from t1;
59
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
64
# #12480: NOW() is not constant in a trigger
65
# #12481: Using NOW() in a stored function breaks statement based replication
68
# Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep()
69
connect (con2,localhost,root,,);
71
select get_lock("bug12480",2);
74
create table t1 (a datetime,b datetime, c datetime);
76
drop function if exists bug12480;
81
create function bug12480() returns datetime
83
set @a=get_lock("bug12480",2);
87
create trigger t1_first before insert on t1
89
set @a=get_lock("bug12480",2);
91
set new.c= bug12480();
96
# The trigger causes a warning for unsafe statement when
97
# binlog_format=statement since it uses get_lock.
99
insert into t1 set a = now();
101
select a=b && a=c from t1;
102
let $time=`select a from t1`;
104
# Check that definer attribute is replicated properly:
105
# - dump definers on the master;
106
# - wait for the slave to synchronize with the master;
107
# - dump definers on the slave;
109
SELECT routine_name, definer
110
FROM information_schema.routines
111
WHERE routine_name = 'bug12480';
113
SELECT trigger_name, definer
114
FROM information_schema.triggers
115
WHERE trigger_name = 't1_first';
117
sync_slave_with_master;
119
select "--- On slave --" as "";
122
# XXX: Definers of stored procedures and functions are not replicated. WL#2897
123
# (Complete definer support in the stored routines) addresses this issue. So,
124
# the result file is expected to be changed after implementation of this WL
127
SELECT routine_name, definer
128
FROM information_schema.routines
129
WHERE routine_name = 'bug12480';
131
SELECT trigger_name, definer
132
FROM information_schema.triggers
133
WHERE trigger_name = 't1_first';
135
select a=b && a=c from t1;
137
eval select a='$time' as 'test' from t1;
144
drop trigger t1_first;
146
# The trigger causes a warning for unsafe statement when
147
# binlog_format=statement since it uses get_lock.
149
insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
151
select a=b && a=c from t1;
153
drop function bug12480;
157
# #14614: Replication of tables with trigger generates error message if databases is changed
158
# Note. The error message is emitted by _myfree() using fprintf() to the stderr
159
# and because of that does not fall into the .result file.
162
create table t1 (i int);
163
create table t2 (i int);
166
create trigger tr1 before insert on t1 for each row
168
insert into t2 values (1);
172
create database other;
174
insert into test.t1 values (1);
176
sync_slave_with_master;
185
# Test specific triggers including SELECT into var with replication
187
# slave performs an update to the replicatable table, t1,
188
# and modifies its local data, t3, by mean of its local trigger that uses
189
# another local table t2.
190
# Expected values are commented into queries.
192
# Body of the test executes in a loop since the problem occurred randomly.
198
--echo test case for BUG#13227
201
--echo -------------------
203
--echo -------------------
209
eval drop table if exists t1$rnd;
211
eval drop table if exists t2$rnd,t3$rnd;
215
eval create table t1$rnd (f1 int) /* 2 replicate */;
219
eval insert into t1$rnd values (-$i);
223
sync_slave_with_master;
225
eval select * from t1$rnd;
227
eval create trigger trg1$rnd before update on t1$rnd /* slave local */
231
SELECT f2 INTO r FROM t2$rnd where f1=NEW.f1;
232
INSERT INTO t3$rnd values (r);
235
eval create table t2$rnd (f1 int, f2 int) /* slave local */;
236
eval create table t3$rnd (f3 int) /* slave local */;
240
eval insert into t2$rnd values ($i, $i*100);
248
# trigger works as specified when updates from slave
249
eval select * from t2$rnd;
250
eval UPDATE t1$rnd SET f1=$max_rows where f1=-$max_rows;
251
eval SELECT * from t1$rnd /* must be f1 $max_rows, 1 - $max_rows 2 - $max_rows ... -1 */;
252
eval SELECT * from t3$rnd /* must be f3 $max_rows*100 */;
258
eval UPDATE t1$rnd SET f1=$i where f1=-$i;
262
sync_slave_with_master;
264
eval SELECT * from t1$rnd /* must be f1 $max_rows ... 1 */;
265
eval SELECT * from t3$rnd /* must be f3 $max_rows * 100 ... 100 */;
269
eval drop trigger trg1$rnd;
270
eval drop table t2$rnd,t3$rnd;
273
eval drop table t1$rnd;
274
sync_slave_with_master;
282
# BUG#16266: Definer is not fully qualified error during replication.
284
# The idea of this test is to emulate replication of a trigger from the old
285
# master (master w/o "DEFINER in triggers" support) to the new slave and check
287
# 1. the trigger on the slave will be replicated w/o errors;
288
# 2. the trigger on the slave will be non-SUID (will have no DEFINER);
289
# 3. the trigger can be activated later on the slave w/o errors.
291
# In order to emulate this kind of replication, we make the slave playing the binlog,
292
# recorded by 5.0.16 master. This binlog contains the following statements:
293
# CREATE TABLE t1(c INT);
294
# CREATE TABLE t2(s CHAR(200));
295
# CREATE TRIGGER trg1 AFTER INSERT ON t1
297
# INSERT INTO t2 VALUES(CURRENT_USER());
298
# INSERT INTO t1 VALUES(1);
301
# 1. Check that the trigger's replication is succeeded.
308
# Replace master's binlog.
311
let $MYSQLD_DATADIR= `select @@datadir`;
315
--echo --> Stop master server
316
--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
320
--source include/wait_until_disconnected.inc
322
remove_file $MYSQLD_DATADIR/master-bin.000001;
323
copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLD_DATADIR/master-bin.000001;
325
--echo --> Start master server
326
--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
330
--source include/wait_until_connected_again.inc
331
let $binlog_version= query_get_value(SHOW BINLOG EVENTS, Info, 1);
333
# Make the slave to replay the new binlog.
334
--echo --> Master binlog: $binlog_version
336
# Make the slave to replay the new binlog.
342
SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;
344
# Check that the replication succeeded.
346
SHOW TABLES LIKE 't_';
351
# 2. Check that the trigger is non-SUID on the slave;
352
# 3. Check that the trigger can be activated on the slave.
354
# We disable warnings here since it affects the result file in
355
# different ways depending on the mode being used.
358
INSERT INTO t1 VALUES(2);
364
# That's all, cleanup.
373
# The master should be clean.
376
SHOW TABLES LIKE 't_';
388
# BUG#20438: CREATE statements for views, stored routines and triggers can be
393
--echo ---> Test for BUG#20438
395
# Prepare environment.
398
--echo ---> Preparing environment...
399
--echo ---> connection: master
403
DROP TABLE IF EXISTS t1;
404
DROP TABLE IF EXISTS t2;
408
--echo ---> Synchronizing slave with master...
410
--sync_slave_with_master
413
--echo ---> connection: master
419
--echo ---> Creating objects...
421
CREATE TABLE t1(c INT);
422
CREATE TABLE t2(c INT);
424
/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1
426
INSERT INTO t2 VALUES(NEW.c * 10) */;
429
--echo ---> Inserting value...
431
INSERT INTO t1 VALUES(1);
434
--echo ---> Checking on master...
440
--echo ---> Synchronizing slave with master...
442
--sync_slave_with_master
444
--echo ---> connection: master
447
--echo ---> Checking on slave...
455
--echo ---> connection: master
459
--echo ---> Cleaning up...
464
--sync_slave_with_master
468
# BUG#23703: DROP TRIGGER needs an IF EXISTS
474
drop table if exists t1;
477
create table t1(a int, b varchar(50));
479
-- error ER_TRG_DOES_NOT_EXIST
480
drop trigger not_a_trigger;
482
drop trigger if exists not_a_trigger;
484
create trigger t1_bi before insert on t1
485
for each row set NEW.b := "In trigger t1_bi";
487
insert into t1 values (1, "a");
488
drop trigger if exists t1_bi;
489
insert into t1 values (2, "b");
490
drop trigger if exists t1_bi;
491
insert into t1 values (3, "c");
494
sync_slave_with_master;
500
sync_slave_with_master;
503
# Bug#40116: Uncommited changes are replicated and stay on slave after
508
source include/master-slave-reset.inc;
509
source include/have_innodb.inc;
511
source include/have_innodb.inc;
514
create table t1 ( f int ) engine = innodb;
515
create table log ( r int ) engine = myisam;
518
for each row insert into log values ( new.f );
521
insert into t1 values ( 1 );
523
sync_slave_with_master;
525
let $diff_table_1=master:test.t1;
526
let $diff_table_2=slave:test.t1;
527
--source include/diff_tables.inc
529
let $diff_table_1=master:test.log;
530
let $diff_table_2=slave:test.log;
531
--source include/diff_tables.inc
535
sync_slave_with_master;