~drizzle-trunk/drizzle/development

#

# Test of triggers with replication

# Adding statement include due to Bug 12574

# TODO: Remove statement include once 12574 is patched

--source include/have_binlog_format_mixed_or_statement.inc 

--source include/master-slave.inc

--disable_warnings

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

DROP TABLE IF EXISTS t3;

--enable_warnings

#

# #12482: Triggers has side effects with auto_increment values

#

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);

delimiter |;

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|

delimiter ;|

insert into t3 values(100,"log",0,0,0);

# Ensure we always have same random numbers

SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;

# Emulate that we have rows 2-9 deleted on the slave

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;

select * from t2;

select a,name, old_a, old_b, truncate(rand_value,4) from t3;

save_master_pos;

connection slave;

sync_with_master;

--disable_query_log

select "--- On slave --" as "";

--enable_query_log

select a,b, truncate(rand_value,4) from t1;

select * from t2;

select a,name, old_a, old_b, truncate(rand_value,4) from t3;

connection master;

drop table t1,t2,t3;

#

# #12480: NOW() is not constant in a trigger

# #12481: Using NOW() in a stored function breaks statement based replication

#

# Start by getting a lock on 'bug12480' to be able to use get_lock() as sleep()

connect (con2,localhost,root,,);

connection con2;

select get_lock("bug12480",2);

connection default;

create table t1 (a datetime,b  datetime, c datetime);

--disable_warnings

drop function if exists bug12480;

--enable_warnings

delimiter |;

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

|

delimiter ;|

insert into t1 set a = now();

select a=b && a=c from t1;

let $time=`select a from t1`;

# Check that definer attribute is replicated properly:

#   - dump definers on the master;

#   - wait for the slave to synchronize with the master;

#   - dump definers on the slave;

SELECT routine_name, definer

FROM information_schema.routines

WHERE routine_name = 'bug12480';

SELECT trigger_name, definer

FROM information_schema.triggers

WHERE trigger_name = 't1_first';

save_master_pos;

connection slave;

sync_with_master;

--disable_query_log

select "--- On slave --" as "";

--enable_query_log

# XXX: Definers of stored procedures and functions are not replicated. WL#2897

# (Complete definer support in the stored routines) addresses this issue. So,

# the result file is expected to be changed after implementation of this WL

# item.

SELECT routine_name, definer

FROM information_schema.routines

WHERE routine_name = 'bug12480';

SELECT trigger_name, definer

FROM information_schema.triggers

WHERE trigger_name = 't1_first';

select a=b && a=c from t1;

--disable_query_log

eval select a='$time' as 'test' from t1;

--enable_query_log

connection master;

disconnect con2;

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;

drop function bug12480;

drop table t1;

#

# #14614: Replication of tables with trigger generates error message if databases is changed

# Note. The error message is emitted by _myfree() using fprintf() to the stderr

# and because of that does not fall into the .result file.

#

create table t1 (i int);

create table t2 (i int);

delimiter |;

create trigger tr1 before insert on t1 for each row

begin

 insert into t2 values (1);

end|

delimiter ;|

create database other;

use other;

insert into test.t1 values (1);

save_master_pos;

connection slave;

sync_with_master;

connection master;

use test;

drop table t1,t2;

drop database other;

#

# Test specific triggers including SELECT into var with replication

# BUG#13227:

# slave performs an update to the replicatable table, t1, 

# and modifies its local data, t3, by mean of its local trigger that uses

# another local table t2.

# Expected values are commented into queries.

#

# Body of the test executes in a loop since the problem occurred randomly.

# 

let $max_rows=5;

let $rnd=10;

--echo test case for BUG#13227

while ($rnd)

{

  --echo -------------------

    echo $rnd;

  --echo -------------------

### SETUP

--disable_warnings

  connection master;

  eval drop table if exists t1$rnd;

  connection slave;

  eval drop table if exists t2$rnd,t3$rnd;

--enable_warnings

  connection master;

  eval create table t1$rnd (f1 int)  /* 2 replicate */;  

  let $i=$max_rows;

  while ($i)

  {

    eval insert into t1$rnd values (-$i);

    dec $i;

  }

  sync_slave_with_master;

#connection slave;

  eval select * from t1$rnd;

  delimiter |;

  eval create trigger trg1$rnd before update on t1$rnd /* slave local */

  for each row

  begin

    DECLARE r integer;

    SELECT f2 INTO r FROM t2$rnd where f1=NEW.f1;

    INSERT INTO t3$rnd values (r);

  end|

  delimiter ;|

  eval create table t2$rnd (f1 int, f2 int) /* slave local */;        

  eval create table t3$rnd (f3 int) /* slave local */;                

  let $i=$max_rows;

  while ($i) 

  {

    eval insert into t2$rnd values ($i, $i*100);

    dec $i;

  }

### Test

#connection slave;

# trigger works as specified when updates from slave

  eval select * from t2$rnd;

  eval UPDATE t1$rnd SET f1=$max_rows where f1=-$max_rows;

  eval SELECT * from t1$rnd /* must be f1 $max_rows, 1 - $max_rows 2 - $max_rows ... -1 */;

  eval SELECT * from t3$rnd /* must be f3 $max_rows*100 */;

  connection master;

  let $i=$max_rows;

  while ($i)

  {

    eval UPDATE t1$rnd SET f1=$i where f1=-$i;

    dec $i;

  }

  sync_slave_with_master;

#connection slave;

  eval SELECT * from t1$rnd /* must be f1 $max_rows ... 1 */;

  eval SELECT * from t3$rnd /* must be f3 $max_rows * 100 ...  100 */;

### CLEANUP

#connection slave;

  eval drop trigger trg1$rnd;

  eval drop table t2$rnd,t3$rnd;

  connection master;

  eval drop table t1$rnd;

  dec $rnd;

}

#

# BUG#16266: Definer is not fully qualified error during replication.

#

# The idea of this test is to emulate replication of a trigger from the old

# master (master w/o "DEFINER in triggers" support) to the new slave and check

# that:

#   1. the trigger on the slave will be replicated w/o errors;

#   2. the trigger on the slave will be non-SUID (will have no DEFINER);

#   3. the trigger can be activated later on the slave w/o errors.

#

# In order to emulate this kind of replication, we make the slave playing the binlog,

# recorded by 5.0.16 master. This binlog contains the following statements:

#   CREATE TABLE t1(c INT);

#   CREATE TABLE t2(s CHAR(200));

#   CREATE TRIGGER trg1 AFTER INSERT ON t1

#     FOR EACH ROW

#       INSERT INTO t2 VALUES(CURRENT_USER());

#   INSERT INTO t1 VALUES(1);

#

# 1. Check that the trigger's replication is succeeded.

# Stop the slave.

connection slave;

STOP SLAVE;

# Replace master's binlog.

connection master;

FLUSH LOGS;

exec cp $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLTEST_VARDIR/log/master-bin.000001;

# Make the slave to replay the new binlog.

connection slave;

RESET SLAVE;

START SLAVE;

SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;

# Check that the replication succeeded.

SHOW TABLES LIKE 't_';

SHOW TRIGGERS;

SELECT * FROM t1;

SELECT * FROM t2;

# 2. Check that the trigger is non-SUID on the slave;

# 3. Check that the trigger can be activated on the slave.

#

# We disable warnings here since it affects the result file in

# different ways depending on the mode being used.

disable_warnings;

INSERT INTO t1 VALUES(2);

enable_warnings;

SELECT * FROM t1;

SELECT * FROM t2;

# That's all, cleanup.

DROP TRIGGER trg1;

DROP TABLE t1;

DROP TABLE t2;

STOP SLAVE;

RESET SLAVE;

# The master should be clean.

connection master;

SHOW TABLES LIKE 't_';

SHOW TRIGGERS;

RESET MASTER;

# Restart slave.

connection slave;

START SLAVE;

#

# BUG#20438: CREATE statements for views, stored routines and triggers can be

# not replicable.

#

--echo

--echo ---> Test for BUG#20438

# Prepare environment.

--echo

--echo ---> Preparing environment...

--echo ---> connection: master

--connection master

--disable_warnings

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

--enable_warnings

--echo

--echo ---> Synchronizing slave with master...

--save_master_pos

--connection slave

--sync_with_master

--echo

--echo ---> connection: master

--connection master

# Test.

--echo

--echo ---> 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) */;

--echo

--echo ---> Inserting value...

INSERT INTO t1 VALUES(1);

--echo

--echo ---> Checking on master...

SELECT * FROM t1;

SELECT * FROM t2;

--echo

--echo ---> Synchronizing slave with master...

--save_master_pos

--connection slave

--sync_with_master

--echo ---> connection: master

--echo

--echo ---> Checking on slave...

SELECT * FROM t1;

SELECT * FROM t2;

# Cleanup.

--echo

--echo ---> connection: master

--connection master

--echo

--echo ---> Cleaning up...

DROP TABLE t1;

DROP TABLE t2;

--save_master_pos

--connection slave

--sync_with_master

--connection master

#

# BUG#23703: DROP TRIGGER needs an IF EXISTS

#

connection master;

--disable_warnings

drop table if exists t1;

--enable_warnings

create table t1(a int, b varchar(50));

-- error ER_TRG_DOES_NOT_EXIST

drop trigger not_a_trigger;

drop trigger if exists not_a_trigger;

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;

insert into t1 values (3, "c");

select * from t1;

save_master_pos;

connection slave;

sync_with_master;

select * from t1;

connection master;

drop table t1;

#

# End of tests

#

save_master_pos;

connection slave;

sync_with_master;