Edit File: rpl_trigger.result
include/master-slave.inc [connection master] 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 6 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 6 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 6 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 6 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; SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */; f1 5 -4 -3 -2 -1 SELECT * from t33 /* must be f3 5*100 */; f3 500 UPDATE t13 SET f1=5 where f1=-5; UPDATE t13 SET f1=4 where f1=-4; UPDATE t13 SET f1=3 where f1=-3; UPDATE t13 SET f1=2 where f1=-2; UPDATE t13 SET f1=1 where f1=-1; SELECT * from t13 /* must be f1 5 ... 1 */; f1 5 4 3 2 1 SELECT * from t33 /* must be f3 5 * 100 ... 100 */; f3 500 400 300 200 100 drop trigger trg13; drop table t23,t33; drop table t13; ------------------- 2 ------------------- drop table if exists t12; drop table if exists t22,t32; create table t12 (f1 int) /* 2 replicate */; insert into t12 values (-5); insert into t12 values (-4); insert into t12 values (-3); insert into t12 values (-2); insert into t12 values (-1); select * from t12; f1 -5 -4 -3 -2 -1 create trigger trg12 before update on t12 /* slave local */ for each row begin DECLARE r integer; SELECT f2 INTO r FROM t22 where f1=NEW.f1; INSERT INTO t32 values (r); end| create table t22 (f1 int, f2 int) /* slave local */; create table t32 (f3 int) /* slave local */; insert into t22 values (5, 5*100); insert into t22 values (4, 4*100); insert into t22 values (3, 3*100); insert into t22 values (2, 2*100); insert into t22 values (1, 1*100); select * from t22; f1 f2 5 500 4 400 3 300 2 200 1 100 UPDATE t12 SET f1=5 where f1=-5; SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */; f1 5 -4 -3 -2 -1 SELECT * from t32 /* must be f3 5*100 */; f3 500 UPDATE t12 SET f1=5 where f1=-5; UPDATE t12 SET f1=4 where f1=-4; UPDATE t12 SET f1=3 where f1=-3; UPDATE t12 SET f1=2 where f1=-2; UPDATE t12 SET f1=1 where f1=-1; SELECT * from t12 /* must be f1 5 ... 1 */; f1 5 4 3 2 1 SELECT * from t32 /* must be f3 5 * 100 ... 100 */; f3 500 400 300 200 100 drop trigger trg12; drop table t22,t32; drop table t12; ------------------- 1 ------------------- drop table if exists t11; drop table if exists t21,t31; create table t11 (f1 int) /* 2 replicate */; insert into t11 values (-5); insert into t11 values (-4); insert into t11 values (-3); insert into t11 values (-2); insert into t11 values (-1); select * from t11; f1 -5 -4 -3 -2 -1 create trigger trg11 before update on t11 /* slave local */ for each row begin DECLARE r integer; SELECT f2 INTO r FROM t21 where f1=NEW.f1; INSERT INTO t31 values (r); end| create table t21 (f1 int, f2 int) /* slave local */; create table t31 (f3 int) /* slave local */; insert into t21 values (5, 5*100); insert into t21 values (4, 4*100); insert into t21 values (3, 3*100); insert into t21 values (2, 2*100); insert into t21 values (1, 1*100); select * from t21; f1 f2 5 500 4 400 3 300 2 200 1 100 UPDATE t11 SET f1=5 where f1=-5; SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */; f1 5 -4 -3 -2 -1 SELECT * from t31 /* must be f3 5*100 */; f3 500 UPDATE t11 SET f1=5 where f1=-5; UPDATE t11 SET f1=4 where f1=-4; UPDATE t11 SET f1=3 where f1=-3; UPDATE t11 SET f1=2 where f1=-2; UPDATE t11 SET f1=1 where f1=-1; SELECT * from t11 /* must be f1 5 ... 1 */; f1 5 4 3 2 1 SELECT * from t31 /* must be f3 5 * 100 ... 100 */; f3 500 400 300 200 100 drop trigger trg11; drop table t21,t31; drop table t11; STOP SLAVE; FLUSH LOGS; include/rpl_stop_server.inc [server_number=1] include/rpl_start_server.inc [server_number=1] --> Master binlog: Server ver: 5.0.16-debug-log, Binlog ver: 4 RESET SLAVE; START SLAVE; SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0; MASTER_POS_WAIT('master-bin.000001', 513) >= 0 1 SHOW TABLES LIKE 't_'; Tables_in_test (t_) t1 t2 SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER NULL latin1 latin1_swedish_ci latin1_swedish_ci SELECT * FROM t1; c 1 SELECT * FROM t2; s @ INSERT INTO t1 VALUES(2); SELECT * FROM t1; c 1 2 SELECT * FROM t2; s @ root@localhost DROP TRIGGER trg1; DROP TABLE t1; DROP TABLE t2; STOP SLAVE; RESET SLAVE; SHOW TABLES LIKE 't_'; Tables_in_test (t_) SHOW TRIGGERS; Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation RESET MASTER; START SLAVE; ---> Test for BUG#20438 ---> Preparing environment... ---> connection: master DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; ---> Synchronizing slave with master... ---> connection: master ---> 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) */; ---> Inserting value... INSERT INTO t1 VALUES(1); ---> Checking on master... SELECT * FROM t1; c 1 SELECT * FROM t2; c 10 ---> Synchronizing slave with master... ---> connection: master ---> Checking on slave... SELECT * FROM t1; c 1 SELECT * FROM t2; c 10 ---> connection: master ---> Cleaning up... DROP TABLE t1; DROP TABLE t2; drop table if exists t1; create table t1(a int, b varchar(50)); drop trigger not_a_trigger; ERROR HY000: Trigger does not exist drop trigger if exists not_a_trigger; Warnings: Note 1360 Trigger does not exist 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; Warnings: Note 1360 Trigger does not exist insert into t1 values (3, "c"); select * from t1; a b 1 In trigger t1_bi 2 b 3 c select * from t1; a b 1 In trigger t1_bi 2 b 3 c drop table t1; include/rpl_reset.inc 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; insert into t1 values ( 1 ); rollback; Warnings: Warning 1196 Some non-transactional changed tables couldn't be rolled back include/diff_tables.inc [master:t1, slave:t1] include/diff_tables.inc [master:log, slave:log] drop table t1, log; include/rpl_end.inc
Back to File Manager