rpl_trigger.result 3.24 KB
Newer Older
1 2 3 4 5 6
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
jmiller@mysql.com's avatar
jmiller@mysql.com committed
7 8 9
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
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);
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);
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|
insert into t3 values(100,"log",0,0,0);
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
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);
select a,b, truncate(rand_value,4) from t1;
a	b	truncate(rand_value,4)
1	1	0.4320
2	2	0.3055
select * from t2;
a	b
1	2
3	0
4	0
5	0
500	0
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
a	name	old_a	old_b	truncate(rand_value,4)
100	log	0	0	0.0000
101	t1	1	1	0.3203
102	t1	0	2	0.5666
103	t2	1	2	0.9164
104	t2	3	0	0.8826
105	t2	4	0	0.6635
106	t2	5	0	0.6699
107	t2	500	0	0.3593

--- On slave --
select a,b, truncate(rand_value,4) from t1;
a	b	truncate(rand_value,4)
1	1	0.4320
2	2	0.3055
select * from t2;
a	b
1	2
3	0
4	0
5	0
500	0
select a,name, old_a, old_b, truncate(rand_value,4) from t3;
a	name	old_a	old_b	truncate(rand_value,4)
100	log	0	0	0.0000
101	t1	1	1	0.3203
102	t1	0	2	0.5666
103	t2	1	2	0.9164
104	t2	3	0	0.8826
105	t2	4	0	0.6635
106	t2	5	0	0.6699
107	t2	500	0	0.3593
drop table t1,t2,t3;
select get_lock("bug12480",2);
get_lock("bug12480",2)
1
create table t1 (a datetime,b  datetime, c datetime);
drop function if exists bug12480;
Warnings:
Note	1305	FUNCTION bug12480 does not exist
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
|
insert into t1 set a = now();
select a=b && a=c from t1;
a=b && a=c
1
95 96 97 98 99 100 101 102
SELECT routine_name, definer
FROM information_schema.routines;
routine_name	definer
bug12480	root@localhost
SELECT trigger_name, definer
FROM information_schema.triggers;
trigger_name	definer
t1_first	root@localhost
103 104

--- On slave --
105 106 107 108 109 110 111 112
SELECT routine_name, definer
FROM information_schema.routines;
routine_name	definer
bug12480	@
SELECT trigger_name, definer
FROM information_schema.triggers;
trigger_name	definer
t1_first	root@localhost
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127
select a=b && a=c from t1;
a=b && a=c
1
test
1
truncate table t1;
drop trigger t1_first;
insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());
select a=b && a=c from t1;
a=b && a=c
1
1
1
drop function bug12480;
drop table t1;
128 129 130 131 132 133 134 135 136 137 138 139
create table t1 (i int);
create table t2 (i int);
create trigger tr1 before insert on t1 for each row
begin
insert into t2 values (1);
end|
create database other;
use other;
insert into test.t1 values (1);
use test;
drop table t1,t2;
drop database other;