~drizzle-trunk/drizzle/development

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;

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

DROP TABLE IF EXISTS t3;

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;

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

SELECT routine_name, definer

FROM information_schema.routines

WHERE routine_name = 'bug12480';

routine_name	definer

bug12480	root@localhost

SELECT trigger_name, definer

FROM information_schema.triggers

WHERE trigger_name = 't1_first';

trigger_name	definer

t1_first	root@localhost

--- On slave --

SELECT routine_name, definer

FROM information_schema.routines

WHERE routine_name = 'bug12480';

routine_name	definer

bug12480	root@localhost

SELECT trigger_name, definer

FROM information_schema.triggers

WHERE trigger_name = 't1_first';

trigger_name	definer

t1_first	root@localhost

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;

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;

test case for BUG#13227

-------------------

10

-------------------

drop table if exists t110;

drop table if exists t210,t310;

create table t110 (f1 int)  /* 2 replicate */;

insert into t110 values (-5);

insert into t110 values (-4);

insert into t110 values (-3);

insert into t110 values (-2);

insert into t110 values (-1);

select * from t110;

f1

-5

-4

-3

-2

-1

create trigger trg110 before update on t110 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t210 where f1=NEW.f1;

INSERT INTO t310 values (r);

end|

create table t210 (f1 int, f2 int) /* slave local */;

create table t310 (f3 int) /* slave local */;

insert into t210 values (5, 5*100);

insert into t210 values (4, 4*100);

insert into t210 values (3, 3*100);

insert into t210 values (2, 2*100);

insert into t210 values (1, 1*100);

select * from t210;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t110 SET f1=5 where f1=-5;

SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t310 /* must be f3 5*100 */;

f3

500

UPDATE t110 SET f1=5 where f1=-5;

UPDATE t110 SET f1=4 where f1=-4;

UPDATE t110 SET f1=3 where f1=-3;

UPDATE t110 SET f1=2 where f1=-2;

UPDATE t110 SET f1=1 where f1=-1;

SELECT * from t110 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t310 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg110;

drop table t210,t310;

drop table t110;

-------------------

9

-------------------

drop table if exists t19;

drop table if exists t29,t39;

create table t19 (f1 int)  /* 2 replicate */;

insert into t19 values (-5);

insert into t19 values (-4);

insert into t19 values (-3);

insert into t19 values (-2);

insert into t19 values (-1);

select * from t19;

f1

-5

-4

-3

-2

-1

create trigger trg19 before update on t19 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t29 where f1=NEW.f1;

INSERT INTO t39 values (r);

end|

create table t29 (f1 int, f2 int) /* slave local */;

create table t39 (f3 int) /* slave local */;

insert into t29 values (5, 5*100);

insert into t29 values (4, 4*100);

insert into t29 values (3, 3*100);

insert into t29 values (2, 2*100);

insert into t29 values (1, 1*100);

select * from t29;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t19 SET f1=5 where f1=-5;

SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t39 /* must be f3 5*100 */;

f3

500

UPDATE t19 SET f1=5 where f1=-5;

UPDATE t19 SET f1=4 where f1=-4;

UPDATE t19 SET f1=3 where f1=-3;

UPDATE t19 SET f1=2 where f1=-2;

UPDATE t19 SET f1=1 where f1=-1;

SELECT * from t19 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t39 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg19;

drop table t29,t39;

drop table t19;

-------------------

8

-------------------

drop table if exists t18;

drop table if exists t28,t38;

create table t18 (f1 int)  /* 2 replicate */;

insert into t18 values (-5);

insert into t18 values (-4);

insert into t18 values (-3);

insert into t18 values (-2);

insert into t18 values (-1);

select * from t18;

f1

-5

-4

-3

-2

-1

create trigger trg18 before update on t18 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t28 where f1=NEW.f1;

INSERT INTO t38 values (r);

end|

create table t28 (f1 int, f2 int) /* slave local */;

create table t38 (f3 int) /* slave local */;

insert into t28 values (5, 5*100);

insert into t28 values (4, 4*100);

insert into t28 values (3, 3*100);

insert into t28 values (2, 2*100);

insert into t28 values (1, 1*100);

select * from t28;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t18 SET f1=5 where f1=-5;

SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t38 /* must be f3 5*100 */;

f3

500

UPDATE t18 SET f1=5 where f1=-5;

UPDATE t18 SET f1=4 where f1=-4;

UPDATE t18 SET f1=3 where f1=-3;

UPDATE t18 SET f1=2 where f1=-2;

UPDATE t18 SET f1=1 where f1=-1;

SELECT * from t18 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t38 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg18;

drop table t28,t38;

drop table t18;

-------------------

7

-------------------

drop table if exists t17;

drop table if exists t27,t37;

create table t17 (f1 int)  /* 2 replicate */;

insert into t17 values (-5);

insert into t17 values (-4);

insert into t17 values (-3);

insert into t17 values (-2);

insert into t17 values (-1);

select * from t17;

f1

-5

-4

-3

-2

-1

create trigger trg17 before update on t17 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t27 where f1=NEW.f1;

INSERT INTO t37 values (r);

end|

create table t27 (f1 int, f2 int) /* slave local */;

create table t37 (f3 int) /* slave local */;

insert into t27 values (5, 5*100);

insert into t27 values (4, 4*100);

insert into t27 values (3, 3*100);

insert into t27 values (2, 2*100);

insert into t27 values (1, 1*100);

select * from t27;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t17 SET f1=5 where f1=-5;

SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t37 /* must be f3 5*100 */;

f3

500

UPDATE t17 SET f1=5 where f1=-5;

UPDATE t17 SET f1=4 where f1=-4;

UPDATE t17 SET f1=3 where f1=-3;

UPDATE t17 SET f1=2 where f1=-2;

UPDATE t17 SET f1=1 where f1=-1;

SELECT * from t17 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t37 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg17;

drop table t27,t37;

drop table t17;

-------------------

6

-------------------

drop table if exists t16;

drop table if exists t26,t36;

create table t16 (f1 int)  /* 2 replicate */;

insert into t16 values (-5);

insert into t16 values (-4);

insert into t16 values (-3);

insert into t16 values (-2);

insert into t16 values (-1);

select * from t16;

f1

-5

-4

-3

-2

-1

create trigger trg16 before update on t16 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t26 where f1=NEW.f1;

INSERT INTO t36 values (r);

end|

create table t26 (f1 int, f2 int) /* slave local */;

create table t36 (f3 int) /* slave local */;

insert into t26 values (5, 5*100);

insert into t26 values (4, 4*100);

insert into t26 values (3, 3*100);

insert into t26 values (2, 2*100);

insert into t26 values (1, 1*100);

select * from t26;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t16 SET f1=5 where f1=-5;

SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t36 /* must be f3 5*100 */;

f3

500

UPDATE t16 SET f1=5 where f1=-5;

UPDATE t16 SET f1=4 where f1=-4;

UPDATE t16 SET f1=3 where f1=-3;

UPDATE t16 SET f1=2 where f1=-2;

UPDATE t16 SET f1=1 where f1=-1;

SELECT * from t16 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t36 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg16;

drop table t26,t36;

drop table t16;

-------------------

5

-------------------

drop table if exists t15;

drop table if exists t25,t35;

create table t15 (f1 int)  /* 2 replicate */;

insert into t15 values (-5);

insert into t15 values (-4);

insert into t15 values (-3);

insert into t15 values (-2);

insert into t15 values (-1);

select * from t15;

f1

-5

-4

-3

-2

-1

create trigger trg15 before update on t15 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t25 where f1=NEW.f1;

INSERT INTO t35 values (r);

end|

create table t25 (f1 int, f2 int) /* slave local */;

create table t35 (f3 int) /* slave local */;

insert into t25 values (5, 5*100);

insert into t25 values (4, 4*100);

insert into t25 values (3, 3*100);

insert into t25 values (2, 2*100);

insert into t25 values (1, 1*100);

select * from t25;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t15 SET f1=5 where f1=-5;

SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t35 /* must be f3 5*100 */;

f3

500

UPDATE t15 SET f1=5 where f1=-5;

UPDATE t15 SET f1=4 where f1=-4;

UPDATE t15 SET f1=3 where f1=-3;

UPDATE t15 SET f1=2 where f1=-2;

UPDATE t15 SET f1=1 where f1=-1;

SELECT * from t15 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t35 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg15;

drop table t25,t35;

drop table t15;

-------------------

4

-------------------

drop table if exists t14;

drop table if exists t24,t34;

create table t14 (f1 int)  /* 2 replicate */;

insert into t14 values (-5);

insert into t14 values (-4);

insert into t14 values (-3);

insert into t14 values (-2);

insert into t14 values (-1);

select * from t14;

f1

-5

-4

-3

-2

-1

create trigger trg14 before update on t14 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t24 where f1=NEW.f1;

INSERT INTO t34 values (r);

end|

create table t24 (f1 int, f2 int) /* slave local */;

create table t34 (f3 int) /* slave local */;

insert into t24 values (5, 5*100);

insert into t24 values (4, 4*100);

insert into t24 values (3, 3*100);

insert into t24 values (2, 2*100);

insert into t24 values (1, 1*100);

select * from t24;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t14 SET f1=5 where f1=-5;

SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

f1

5

-4

-3

-2

-1

SELECT * from t34 /* must be f3 5*100 */;

f3

500

UPDATE t14 SET f1=5 where f1=-5;

UPDATE t14 SET f1=4 where f1=-4;

UPDATE t14 SET f1=3 where f1=-3;

UPDATE t14 SET f1=2 where f1=-2;

UPDATE t14 SET f1=1 where f1=-1;

SELECT * from t14 /* must be f1 5 ... 1 */;

f1

5

4

3

2

1

SELECT * from t34 /* must be f3 5 * 100 ...  100 */;

f3

500

400

300

200

100

drop trigger trg14;

drop table t24,t34;

drop table t14;

-------------------

3

-------------------

drop table if exists t13;

drop table if exists t23,t33;

create table t13 (f1 int)  /* 2 replicate */;

insert into t13 values (-5);

insert into t13 values (-4);

insert into t13 values (-3);

insert into t13 values (-2);

insert into t13 values (-1);

select * from t13;

f1

-5

-4

-3

-2

-1

create trigger trg13 before update on t13 /* slave local */

for each row

begin

DECLARE r integer;

SELECT f2 INTO r FROM t23 where f1=NEW.f1;

INSERT INTO t33 values (r);

end|

create table t23 (f1 int, f2 int) /* slave local */;

create table t33 (f3 int) /* slave local */;

insert into t23 values (5, 5*100);

insert into t23 values (4, 4*100);

insert into t23 values (3, 3*100);

insert into t23 values (2, 2*100);

insert into t23 values (1, 1*100);

select * from t23;

f1	f2

5	500

4	400

3	300

2	200

1	100

UPDATE t13 SET f1=5 where f1=-5;