~drizzle-trunk/drizzle/development

source include/master-slave.inc;

source include/have_innodb.inc;

--echo **** On Slave ****

connection slave;

source include/have_innodb.inc;

STOP SLAVE;

--echo **** On Master ****

connection master;

SET SESSION BINLOG_FORMAT=ROW;

CREATE TABLE t1 (a INT, b INT);

CREATE TABLE t2 (c INT, d INT);

INSERT INTO t1 VALUES (1,1),(2,4),(3,9);

INSERT INTO t2 VALUES (1,1),(2,8),(3,27);

UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;

source include/show_binlog_events.inc;

# These tables should be changed

SELECT * FROM t1;

SELECT * FROM t2;

save_master_pos;

--echo **** On Slave ****

connection slave;

# Stop when reaching the the first table map event.

START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=762;

wait_for_slave_to_stop;

--replace_result $MASTER_MYPORT MASTER_PORT

--replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 #

query_vertical SHOW SLAVE STATUS;

# Now we skip *one* table map event. If the execution starts right

# after that table map event, *one* of the involved tables will be

# changed.

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

sync_with_master;

# These values should be what was inserted, not what was

# updated. Since we are skipping the first table map of the group

# representing the UPDATE statement above, we should skip the entire

# group and not start executing at the first table map.

SELECT * FROM t1;

SELECT * FROM t2;

STOP SLAVE;

RESET SLAVE;

connection master;

RESET MASTER;

SET SESSION BINLOG_FORMAT=STATEMENT;

SET @foo = 12;

INSERT INTO t1 VALUES(@foo, 2*@foo);

save_master_pos;

source include/show_binlog_events.inc;

connection slave;

START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=106;

wait_for_slave_to_stop;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

sync_with_master;

--replace_result $MASTER_MYPORT MASTER_PORT

--replace_column 1 # 8 # 9 # 23 # 33 # 35 # 36 #

query_vertical SHOW SLAVE STATUS;

--echo **** On Master ****

connection master;

DROP TABLE t1, t2;

sync_slave_with_master;

#

# More tests for BUG#28618

#

# Case 1.

# ROW binlog format and non-transactional tables.

# Create the group of events via triggers and try to skip 

# some items of that group.

#  

connection master;

SET SESSION BINLOG_FORMAT=ROW;

SET AUTOCOMMIT=0;

CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;

CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;

CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;

INSERT INTO t1 VALUES (1,'master/slave');

INSERT INTO t2 VALUES (1,'master/slave');

INSERT INTO t3 VALUES (1,'master/slave');

DELIMITER |;

CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW

BEGIN

  INSERT INTO t2 VALUES (NEW.a,NEW.b);

  DELETE FROM t2 WHERE a < NEW.a;

END|

CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW

BEGIN

  UPDATE t3 SET a =2, b = 'master only';

END|

DELIMITER ;|

--echo **** On Slave ****

sync_slave_with_master;

STOP SLAVE;

source include/wait_for_slave_to_stop.inc;

--echo **** On Master ****

connection master;

UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;

DROP TRIGGER tr1;

DROP TRIGGER tr2;

INSERT INTO t1 VALUES (3,'master/slave');

INSERT INTO t2 VALUES (3,'master/slave');

INSERT INTO t3 VALUES (3,'master/slave');

SELECT * FROM t1 ORDER BY a;

SELECT * FROM t2 ORDER BY a;

SELECT * FROM t3 ORDER BY a;

save_master_pos;

--echo *** On Slave ***

connection slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

source include/wait_for_slave_to_start.inc;

sync_with_master;

SELECT * FROM t1 ORDER BY a;

SELECT * FROM t2 ORDER BY a;

SELECT * FROM t3 ORDER BY a;

connection master;

DROP TABLE t1, t2, t3;

sync_slave_with_master;

--echo **** Case 2: Row binlog format and transactional tables ****

# Create the transaction and try to skip some

# queries from one.

--echo *** On Master ***

connection master;

CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb;

CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb;

CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb;

--echo **** On Slave ****

sync_slave_with_master;

STOP SLAVE;

source include/wait_for_slave_to_stop.inc;

--echo *** On Master ***

connection master;

BEGIN;

INSERT INTO t4 VALUES (2, 'master only');

INSERT INTO t5 VALUES (2, 'master only');

INSERT INTO t6 VALUES (2, 'master only');

COMMIT;

BEGIN;

INSERT INTO t4 VALUES (3, 'master/slave');

INSERT INTO t5 VALUES (3, 'master/slave');

INSERT INTO t6 VALUES (3, 'master/slave');

COMMIT;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

save_master_pos;

--echo *** On Slave ***

connection slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

source include/wait_for_slave_to_start.inc;

sync_with_master;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

# Test skipping two groups

--echo **** On Slave ****

connection slave;

STOP SLAVE;

source include/wait_for_slave_to_stop.inc;

--echo *** On Master ***

connection master;

BEGIN;

INSERT INTO t4 VALUES (6, 'master only');

INSERT INTO t5 VALUES (6, 'master only');

INSERT INTO t6 VALUES (6, 'master only');

COMMIT;

BEGIN;

INSERT INTO t4 VALUES (7, 'master only');

INSERT INTO t5 VALUES (7, 'master only');

INSERT INTO t6 VALUES (7, 'master only');

COMMIT;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

save_master_pos;

--echo *** On Slave ***

connection slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=10;

START SLAVE;

source include/wait_for_slave_to_start.inc;

sync_with_master;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

#

# And the same, but with autocommit = 0

#

connection slave;

STOP SLAVE;

source include/wait_for_slave_to_stop.inc;

connection master;

SET AUTOCOMMIT=0;

INSERT INTO t4 VALUES (4, 'master only');

INSERT INTO t5 VALUES (4, 'master only');

INSERT INTO t6 VALUES (4, 'master only');

COMMIT;

INSERT INTO t4 VALUES (5, 'master/slave');

INSERT INTO t5 VALUES (5, 'master/slave');

INSERT INTO t6 VALUES (5, 'master/slave');

COMMIT;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

save_master_pos;

--echo *** On Slave ***

connection slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

source include/wait_for_slave_to_start.inc;

sync_with_master;

SELECT * FROM t4 ORDER BY a;

SELECT * FROM t5 ORDER BY a;

SELECT * FROM t6 ORDER BY a;

connection master;

DROP TABLE t4, t5, t6;

sync_slave_with_master;

--echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table ****

# LOAD DATA creates two events in binary log for statement binlog format.

# Try to skip the first.

--echo *** On Master ***

connection master;

CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;

--echo *** On Slave ***

sync_slave_with_master;

STOP SLAVE;

source include/wait_for_slave_to_stop.inc;

--echo *** On Master ***

connection master;

SET SESSION BINLOG_FORMAT=STATEMENT;

exec cp ./suite/rpl/data/rpl_bug28618.dat $MYSQLTEST_VARDIR/tmp/;

--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR

eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';

remove_file $MYSQLTEST_VARDIR/tmp/rpl_bug28618.dat;

SELECT * FROM t10 ORDER BY a;

save_master_pos;

--echo *** On Slave ***

connection slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

source include/wait_for_slave_to_start.inc;

sync_with_master;

SELECT * FROM t10 ORDER BY a;

connection master;

DROP TABLE t10;

sync_slave_with_master;