diff options
Diffstat (limited to 'mysql-test/suite/engines/funcs/t')
90 files changed, 229 insertions, 5342 deletions
diff --git a/mysql-test/suite/engines/funcs/t/rpl000010-slave.opt b/mysql-test/suite/engines/funcs/t/rpl000010-slave.opt deleted file mode 100644 index 0dbfb311e33..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000010-slave.opt +++ /dev/null @@ -1 +0,0 @@ ---disconnect-slave-event-count=2 diff --git a/mysql-test/suite/engines/funcs/t/rpl000010.test b/mysql-test/suite/engines/funcs/t/rpl000010.test deleted file mode 100644 index 261b9148774..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000010.test +++ /dev/null @@ -1,19 +0,0 @@ -# This tests the offset off by 22 mystery bug -# Must run slave with --disconnect-slave-event-count=1 --master-connect-retry=1 - -source include/master-slave.inc; - -create table t1 (n int not null auto_increment primary key); -insert into t1 values(NULL); -insert into t1 values(2); -save_master_pos; -connection slave; -sync_with_master; -select n from t1; -connection master; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl000011.test b/mysql-test/suite/engines/funcs/t/rpl000011.test deleted file mode 100644 index 32f6227f7c5..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000011.test +++ /dev/null @@ -1,17 +0,0 @@ -source include/master-slave.inc; - -create table t1 (n int); -insert into t1 values(1); -sync_slave_with_master; -stop slave; -start slave; -connection master; -insert into t1 values(2); -#let slave catch up -sync_slave_with_master; -select * from t1; -connection master; -drop table t1; -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl000013.test b/mysql-test/suite/engines/funcs/t/rpl000013.test deleted file mode 100644 index 2c727107563..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000013.test +++ /dev/null @@ -1,61 +0,0 @@ -# This test is to verify that DROP TEMPORARY TABLE -# is automatically binlogged and sent to slave -# when a temp table is dropped by disconnection -# of a master's conection. -# So it does not apply to row-based, where we neither need -# nor do this automatic binlogging. And if we run this test -# in row-based, it hangs waiting for an offset which is never -# reached (the "sync_with_master 1"), logically. - ---source include/have_binlog_format_mixed_or_statement.inc -source include/master-slave.inc; -save_master_pos; -connection slave; -sync_with_master; -connection master; - ---disable_query_log -CALL mtr.add_suppression(" Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - -create table t2(n int); -create temporary table t1 (n int); -insert into t1 values(1),(2),(3); ---disable_warnings -insert into t2 select * from t1; ---enable_warnings -connection master1; -create temporary table t1 (n int); -insert into t1 values (4),(5); ---disable_warnings -insert into t2 select * from t1 as t10; ---enable_warnings -save_master_pos; -disconnect master; -connection slave; -#add 1 to catch drop table -sync_with_master 1; -connection master1; -insert into t2 values(6); -save_master_pos; -disconnect master1; -connection slave; -# same trick to go one more event -sync_with_master 1; -select * from t2; -show status like 'Slave_open_temp_tables'; -# -# Clean up -# -connect (master2,localhost,root,,); -connection master2; - -# We will get a warning for t1 as this is a temporary table that doesn't -# exist in this connection. - -drop table if exists t1,t2; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl000017-slave.opt b/mysql-test/suite/engines/funcs/t/rpl000017-slave.opt deleted file mode 100644 index 58a964c90d0..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000017-slave.opt +++ /dev/null @@ -1 +0,0 @@ ---skip-slave-start diff --git a/mysql-test/suite/engines/funcs/t/rpl000017.test b/mysql-test/suite/engines/funcs/t/rpl000017.test deleted file mode 100644 index 2ba321cd8c3..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl000017.test +++ /dev/null @@ -1,22 +0,0 @@ -source include/master-slave.inc; -connection slave; -stop slave; -connection master; -grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; -grant replication slave on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; -connection slave; -start slave; -connection master; ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1(n int); -insert into t1 values(24); -sync_slave_with_master; -select * from t1; -connection master; -drop table t1; -delete from mysql.user where user="replicate"; -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_000010.test b/mysql-test/suite/engines/funcs/t/rpl_000010.test new file mode 100644 index 00000000000..155b54572e3 --- /dev/null +++ b/mysql-test/suite/engines/funcs/t/rpl_000010.test @@ -0,0 +1,2 @@ +--source suite/rpl/t/rpl_000010.test + diff --git a/mysql-test/suite/engines/funcs/t/rpl_000011.test b/mysql-test/suite/engines/funcs/t/rpl_000011.test new file mode 100644 index 00000000000..f7fc0f4a310 --- /dev/null +++ b/mysql-test/suite/engines/funcs/t/rpl_000011.test @@ -0,0 +1,18 @@ +--source include/master-slave.inc + +create table t1 (n int); +insert into t1 values(1); +--sync_slave_with_master +--source include/stop_slave.inc +--source include/start_slave.inc +connection master; +insert into t1 values(2); +#let slave catch up +--sync_slave_with_master +select * from t1; +connection master; +drop table t1; +--sync_slave_with_master + +# End of 4.1 tests +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_000013.test b/mysql-test/suite/engines/funcs/t/rpl_000013.test new file mode 100644 index 00000000000..c2f6c114ad6 --- /dev/null +++ b/mysql-test/suite/engines/funcs/t/rpl_000013.test @@ -0,0 +1,2 @@ +--source suite/rpl/t/rpl_000013.test + diff --git a/mysql-test/suite/engines/funcs/t/rpl_000015.test b/mysql-test/suite/engines/funcs/t/rpl_000015.test index 817ed6f407c..8d76fc674e5 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_000015.test +++ b/mysql-test/suite/engines/funcs/t/rpl_000015.test @@ -3,13 +3,13 @@ # Change Date: 2006-01-17 # Change: added order by in select ##################### -source include/master-slave.inc; +--source include/master-slave.inc reset master; show master status; save_master_pos; connection slave; -stop slave; +--source include/stop_slave.inc reset slave; --vertical_results --replace_result $MASTER_MYPORT MASTER_PORT @@ -29,7 +29,7 @@ eval change master to master_host='127.0.0.1',master_user='root', --replace_result $MASTER_MYPORT MASTER_PORT --replace_column 1 # 5 # 8 # 9 # 23 # 33 # show slave status; -start slave; +--source include/start_slave.inc sync_with_master; --vertical_results --replace_result $MASTER_MYPORT MASTER_PORT @@ -42,10 +42,11 @@ drop table if exists t1; --enable_warnings create table t1 (n int, PRIMARY KEY(n)); insert into t1 values (10),(45),(90); -sync_slave_with_master; +--sync_slave_with_master connection slave; SELECT * FROM t1 ORDER BY n; connection master; SELECT * FROM t1 ORDER BY n; drop table t1; -sync_slave_with_master; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_000017.test b/mysql-test/suite/engines/funcs/t/rpl_000017.test new file mode 100644 index 00000000000..f498ce10a58 --- /dev/null +++ b/mysql-test/suite/engines/funcs/t/rpl_000017.test @@ -0,0 +1,2 @@ +--source suite/rpl/t/rpl_000017.test + diff --git a/mysql-test/suite/engines/funcs/t/rpl_LD_INFILE.test b/mysql-test/suite/engines/funcs/t/rpl_LD_INFILE.test index ae647ed6648..e896d62a0ab 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_LD_INFILE.test +++ b/mysql-test/suite/engines/funcs/t/rpl_LD_INFILE.test @@ -1,38 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -############################################################################# -# TEST: To test the LOAD DATA INFILE in rbr # -############################################################################# - -# Includes --- source include/master-slave.inc - -# Begin clean up test section ---disable_warnings -connection master; -DROP TABLE IF EXISTS test.t1; ---enable_warnings - -# Section 1 test -CREATE TABLE test.t1 (a VARCHAR(255), PRIMARY KEY(a)); -LOAD DATA INFILE '../../std_data/words2.dat' INTO TABLE test.t1; -DELETE FROM test.t1 WHERE a = 'abashed'; -DELETE FROM test.t1; -LOAD DATA INFILE '../../std_data/words2.dat' INTO TABLE test.t1; - - -SELECT * FROM test.t1 ORDER BY a DESC; -save_master_pos; -sync_slave_with_master; -connection slave; -SELECT * FROM test.t1 ORDER BY a DESC; - -# Cleanup -#show binlog events; -connection master; -DROP TABLE test.t1; -sync_slave_with_master; - -# End of 5.0 test case +--source suite/rpl/t/rpl_LD_INFILE.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_REDIRECT.test b/mysql-test/suite/engines/funcs/t/rpl_REDIRECT.test deleted file mode 100644 index 078d1048794..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl_REDIRECT.test +++ /dev/null @@ -1,47 +0,0 @@ -# -# Test of automatic redirection of queries to master/slave. -# - -source include/master-slave.inc; -# We disable this for now as PS doesn't handle redirection ---disable_ps_protocol - -#first, make sure the slave has had enough time to register -save_master_pos; -connection slave; -sync_with_master; - -#discover slaves -connection master; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 16 # 23 # 33 # -SHOW SLAVE STATUS; ---replace_result $SLAVE_MYPORT SLAVE_PORT -SHOW SLAVE HOSTS; -rpl_probe; - -#turn on master/slave query direction auto-magic -enable_rpl_parse; -create table t1 ( n int); -insert into t1 values (1),(2),(3),(4); -disable_rpl_parse; -save_master_pos; -connection slave; -sync_with_master; -insert into t1 values(5); -connection master; -enable_rpl_parse; -# The first of the queries will be sent to the slave, the second to the master. -SELECT * FROM t1 ORDER BY n; -SELECT * FROM t1 ORDER BY n; -disable_rpl_parse; -SELECT * FROM t1 ORDER BY n; -connection slave; -SELECT * FROM t1 ORDER BY n; - -# Cleanup -connection master; -drop table t1; -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_alter.test b/mysql-test/suite/engines/funcs/t/rpl_alter.test index 576376a0264..12360f5a3e8 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_alter.test +++ b/mysql-test/suite/engines/funcs/t/rpl_alter.test @@ -1,24 +1,2 @@ -source include/master-slave.inc; ---disable_warnings -drop database if exists mysqltest; ---enable_warnings -create database mysqltest; +--source suite/rpl/t/rpl_alter.test -create table mysqltest.t1 ( n int); -alter table mysqltest.t1 add m int; -insert into mysqltest.t1 values (1,2); -create table mysqltest.t2 (n int); -insert into mysqltest.t2 values (45); -rename table mysqltest.t2 to mysqltest.t3, mysqltest.t1 to mysqltest.t2; -save_master_pos; -connection slave; -sync_with_master; -select * from mysqltest.t2; -select * from mysqltest.t3; -connection master; -drop database mysqltest; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_alter_db.test b/mysql-test/suite/engines/funcs/t/rpl_alter_db.test index 17ba06ce063..097eaf9071b 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_alter_db.test +++ b/mysql-test/suite/engines/funcs/t/rpl_alter_db.test @@ -1,12 +1,2 @@ -source include/master-slave.inc; -connection master; -use mysql; # to be different from initial `test' db of mysqltest client -alter database collate latin1_bin; -save_master_pos; +--source suite/rpl/t/rpl_alter_db.test -connection slave; -sync_with_master; - -# Restoring to the original state -connection master; -alter database collate latin1_swedish_ci; diff --git a/mysql-test/suite/engines/funcs/t/rpl_bit.test b/mysql-test/suite/engines/funcs/t/rpl_bit.test index 7f85313ae4c..822fae57f68 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_bit.test +++ b/mysql-test/suite/engines/funcs/t/rpl_bit.test @@ -1,93 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Sept/15/2005 # -############################################################################# -# Test: To test the replication of the bit field # -############################################################################# -# Change Author: JBM -# Change Date: 2006-01-16 -########## +--source suite/rpl/t/rpl_bit.test --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP TABLE IF EXISTS test.t1; ---enable_warnings -# End of cleanup - -# Begin test section 1 - -CREATE TABLE test.t1 ( - dummyKey INTEGER NOT NULL, - f01 TINYINT, - f10 TINYINT, - f12 TINYINT, - f15 TINYINT, - f16 TINYINT, - f7 TINYINT, - f9 TINYINT, - f29 TINYINT, - f0 TINYINT, - fA1 TINYINT, - C32 TINYINT, - A42 TINYINT, - CA3 TINYINT, - A044 TINYINT, - f001 TINYINT, - A3002 TINYINT, - fC003 TINYINT, - CA300 TINYINT, - A305 TINYINT, - CA321 TINYINT, - r001 TINYINT, - bit1 BIT(6), - bit2 BIT(6), - bit3 BIT(6), - State1 TINYINT, - State2 TINYINT, - State3 TINYINT, - State4 TINYINT, - SubState TINYINT, - gState TINYINT, - oSupp TINYINT, - tSupp TINYINT, - sSuppD TINYINT, - mSuppf TINYINT, - GSuppDf TINYINT, - VNotSupp TINYINT, - x034 TINYINT, -PRIMARY KEY USING HASH (dummyKey) ); - -LOCK TABLES test.t1 WRITE; -INSERT INTO test.t1 VALUES (6,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'111111',b'111110',b'110101',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (1,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'111111',b'000000',b'100100',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (2,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'000000',b'101010',b'010101',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (3,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'101010',b'111111',b'000000',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (4,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (5,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (7,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (8,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -UNLOCK TABLES; - - -SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034 FROM test.t1; -SELECT hex(bit1) FROM test.t1 ORDER BY bit1; -SELECT hex(bit2) from test.t1 ORDER BY bit2; -SELECT hex(bit3) from test.t1 ORDER BY bit3; -save_master_pos; - -connection slave; -sync_with_master; -SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034 FROM test.t1; -SELECT hex(bit1) FROM test.t1 ORDER BY bit1; -SELECT hex(bit2) from test.t1 ORDER BY bit2; -SELECT hex(bit3) from test.t1 ORDER BY bit3; - -connection master; -DROP TABLE IF EXISTS test.t1; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_bit_npk.test b/mysql-test/suite/engines/funcs/t/rpl_bit_npk.test index 12b587919f9..d2214ada074 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_bit_npk.test +++ b/mysql-test/suite/engines/funcs/t/rpl_bit_npk.test @@ -1,116 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Sept/15/2005 # -############################################################################# -# Test: To test the replication of the bit field # -############################################################################# +--source suite/rpl/t/rpl_bit_npk.test --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP TABLE IF EXISTS test.t1; ---enable_warnings -# End of cleanup - -# Begin test section 1 - -CREATE TABLE test.t1 ( - dummyKey INTEGER NOT NULL, - f01 TINYINT, - f10 TINYINT, - f12 TINYINT, - f15 TINYINT, - f16 TINYINT, - f7 TINYINT, - f9 TINYINT, - f29 TINYINT, - f0 TINYINT, - fA1 TINYINT, - C32 TINYINT, - A42 TINYINT, - CA3 TINYINT, - A044 TINYINT, - f001 TINYINT, - A3002 TINYINT, - fC003 TINYINT, - CA300 TINYINT, - A305 TINYINT, - CA321 TINYINT, - r001 TINYINT, - bit1 BIT(6), - bit2 BIT(6), - bit3 BIT(6), - State1 TINYINT, - State2 TINYINT, - State3 TINYINT, - State4 TINYINT, - SubState TINYINT, - gState TINYINT, - oSupp TINYINT, - tSupp TINYINT, - sSuppD TINYINT, - mSuppf TINYINT, - GSuppDf TINYINT, - VNotSupp TINYINT, - x034 TINYINT); - -LOCK TABLES test.t1 WRITE; -INSERT INTO test.t1 VALUES (6,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'111111',b'111110',b'110101',4,5,5,5,5,5,5,5,5,5,3,NULL,1); -INSERT INTO test.t1 VALUES (1,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'111111',b'000000',b'100100',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (2,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'000000',b'101010',b'010101',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (3,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'101010',b'111111',b'000000',4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (4,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,b'0',1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (5,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (7,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -INSERT INTO test.t1 VALUES (8,1,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,4,5,5,5,5,5,5,5,5,5,3,2,1); -UNLOCK TABLES; - -UPDATE test.t1 set x034 = 50 where bit3 = b'000000'; -UPDATE test.t1 set VNotSupp = 33 where bit1 = b'0'; -SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034 - FROM test.t1 - ORDER BY oSupp, sSuppD, GSuppDf, VNotSupp, x034; -SELECT hex(bit1) from test.t1 ORDER BY bit1; -SELECT hex(bit2) from test.t1 ORDER BY bit2; -SELECT hex(bit3) from test.t1 ORDER BY bit3; -save_master_pos; - -connection slave; -sync_with_master; -SELECT oSupp, sSuppD, GSuppDf, VNotSupp, x034 - FROM test.t1 - ORDER BY oSupp, sSuppD, GSuppDf, VNotSupp, x034; -SELECT hex(bit1) from test.t1 ORDER BY bit1; -SELECT hex(bit2) from test.t1 ORDER BY bit2; -SELECT hex(bit3) from test.t1 ORDER BY bit3; - -connection master; -CREATE TABLE test.t2 (a INT, b BIT(1)); -INSERT INTO test.t2 VALUES (1, b'0'); -INSERT INTO test.t2 VALUES (1, b'1'); -UPDATE test.t2 SET a = 2 WHERE b = b'1'; - -CREATE TABLE test.t3 (a INT, b INT); -INSERT INTO test.t3 VALUES (1, NULL); -INSERT INTO test.t3 VALUES (1, 0); -UPDATE test.t3 SET a = 2 WHERE b = 0; - -SELECT a, hex(b) FROM test.t2 ORDER BY a,b; -SELECT * FROM test.t3 ORDER BY a,b; -save_master_pos; - -connection slave; -sync_with_master; -SELECT a, hex(b) FROM test.t2 ORDER BY a,b; -SELECT * FROM test.t3 ORDER BY a,b; - -connection master; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_change_master.test b/mysql-test/suite/engines/funcs/t/rpl_change_master.test index c031464c95e..e7e70bd2ac6 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_change_master.test +++ b/mysql-test/suite/engines/funcs/t/rpl_change_master.test @@ -1,37 +1,2 @@ -# Verify that after CHANGE MASTER, replication (I/O thread and SQL -# thread) restart from where SQL thread left, not from where -# I/O thread left (some old bug fixed in 4.0.17) +--source suite/rpl/t/rpl_change_master.test -source include/master-slave.inc; - -connection master; -# Make SQL slave thread advance a bit -create table t1(n int); -sync_slave_with_master; -select * from t1; -# Now stop it and make I/O slave thread be ahead -stop slave sql_thread; -connection master; -insert into t1 values(1); -insert into t1 values(2); -save_master_pos; -connection slave; ---real_sleep 3 # wait for I/O thread to have read updates -stop slave; ---replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 7 # 8 # 9 # 23 # 33 # -show slave status; -change master to master_user='root'; ---replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 7 # 8 # 9 # 23 # 33 # -show slave status; -start slave; -sync_with_master; -select * from t1; -connection master; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_create_database.test b/mysql-test/suite/engines/funcs/t/rpl_create_database.test index 70cff8daca2..8690de2a604 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_create_database.test +++ b/mysql-test/suite/engines/funcs/t/rpl_create_database.test @@ -1,72 +1,2 @@ -# -# Tests for replication of statements that manipulate databases. -# -# For this test file, we have a number of databases. All databases -# with "greek" names will be replicated on the slave, while other names -# (e.g., american) will not be replicated. -# +--source suite/rpl/t/rpl_create_database.test -source include/master-slave.inc; - -# Bug#6391 (binlog-do-db rules ignored) -# In this case, 'mysqltest_bob' should not be replicated to the slave. ---disable_warnings -DROP DATABASE IF EXISTS mysqltest_prometheus; -DROP DATABASE IF EXISTS mysqltest_sisyfos; -DROP DATABASE IF EXISTS mysqltest_bob; -sync_slave_with_master; -# This database is not replicated -DROP DATABASE IF EXISTS mysqltest_bob; ---enable_warnings - -connection master; -CREATE DATABASE mysqltest_prometheus; -CREATE DATABASE mysqltest_sisyfos; -CREATE DATABASE mysqltest_bob; - -USE mysqltest_sisyfos; -# These should be replicated -CREATE TABLE t1 (b int); -INSERT INTO t1 VALUES(1); - -USE mysqltest_bob; -# These should *not* be replicated -CREATE TABLE t2 (b int); -INSERT INTO t2 VALUES(2); - -# Current database is now 'mysqltest_bob' -# The following should be replicated -ALTER DATABASE mysqltest_sisyfos CHARACTER SET latin1; - -USE mysqltest_sisyfos; -# The following should *not* be replicated -ALTER DATABASE mysqltest_bob CHARACTER SET latin1; - -SHOW DATABASES; -sync_slave_with_master; -SHOW DATABASES; - -connection master; -DROP DATABASE IF EXISTS mysqltest_sisyfos; -USE mysqltest_prometheus; -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1); -CREATE DATABASE mysqltest_sisyfos; -USE mysqltest_sisyfos; -CREATE TABLE t2 (a INT); -let $VERSION=`select version()`; -SHOW DATABASES; -sync_slave_with_master; -SHOW DATABASES; -USE mysqltest_prometheus; -SHOW TABLES; -USE mysqltest_sisyfos; -SHOW TABLES; - -connection master; -DROP DATABASE IF EXISTS mysqltest_prometheus; -DROP DATABASE IF EXISTS mysqltest_sisyfos; -DROP DATABASE IF EXISTS mysqltest_bob; -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_do_grant.test b/mysql-test/suite/engines/funcs/t/rpl_do_grant.test index 4e398114269..95e90ce1bc3 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_do_grant.test +++ b/mysql-test/suite/engines/funcs/t/rpl_do_grant.test @@ -1,98 +1,2 @@ -# Works in statement-based and row-based binlogging. -# Test that GRANT and other user management commands are replicated to the slave +--source suite/rpl/t/rpl_do_grant.test --- source include/master-slave.inc - -# do not be influenced by other tests. -connection master; -delete from mysql.user where user=_binary'rpl_do_grant'; -delete from mysql.db where user=_binary'rpl_do_grant'; -flush privileges; -save_master_pos; -connection slave; -sync_with_master; -# if these DELETE did nothing on the master, we need to do them manually on the -# slave. -delete from mysql.user where user=_binary'rpl_ignore_grant'; -delete from mysql.db where user=_binary'rpl_ignore_grant'; -flush privileges; - -# test replication of GRANT -connection master; -grant select on *.* to rpl_do_grant@localhost; -grant drop on test.* to rpl_do_grant@localhost; -save_master_pos; -connection slave; -sync_with_master; -show grants for rpl_do_grant@localhost; - -# test replication of SET PASSWORD -connection master; -set password for rpl_do_grant@localhost=password("does it work?"); -save_master_pos; -connection slave; -sync_with_master; -select password<>_binary'' from mysql.user where user=_binary'rpl_do_grant'; - -# clear what we have done, to not influence other tests. -connection master; -delete from mysql.user where user=_binary'rpl_do_grant'; -delete from mysql.db where user=_binary'rpl_do_grant'; -flush privileges; -sync_slave_with_master; -# The mysql database is not replicated, so we have to do the deletes -# manually on the slave as well. -delete from mysql.user where user=_binary'rpl_do_grant'; -delete from mysql.db where user=_binary'rpl_do_grant'; -flush privileges; - -# End of 4.1 tests - -connection master; ---error 1141 -show grants for rpl_do_grant@localhost; -connection slave; ---error 1141 -show grants for rpl_do_grant@localhost; - -connection master; -create user rpl_do_grant@localhost; -show grants for rpl_do_grant@localhost; ---error 1141 -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; -show grants for rpl_do_grant@localhost; ---error 1141 -show grants for rpl_do_grant2@localhost; - -connection master; -rename user rpl_do_grant@localhost to rpl_do_grant2@localhost; -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; -show grants for rpl_do_grant2@localhost; - -connection master; -grant DELETE,INSERT on mysqltest1.* to rpl_do_grant2@localhost; -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; -show grants for rpl_do_grant2@localhost; - -connection master; -revoke DELETE on mysqltest1.* from rpl_do_grant2@localhost; -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; -show grants for rpl_do_grant2@localhost; - -connection master; -revoke all privileges, grant option from rpl_do_grant2@localhost; -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; -show grants for rpl_do_grant2@localhost; - -connection master; -drop user rpl_do_grant2@localhost; ---error 1141 -show grants for rpl_do_grant2@localhost; -sync_slave_with_master; ---error 1141 -show grants for rpl_do_grant2@localhost; diff --git a/mysql-test/suite/engines/funcs/t/rpl_drop.test b/mysql-test/suite/engines/funcs/t/rpl_drop.test index b38007a755f..7dda6987502 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_drop.test +++ b/mysql-test/suite/engines/funcs/t/rpl_drop.test @@ -1,16 +1,2 @@ -# Testcase for BUG#4552 (DROP on two tables, one of which does not -# exist, must be binlogged with a non-zero error code) -source include/master-slave.inc; ---disable_warnings -drop table if exists t1, t2; ---enable_warnings -create table t1 (a int); ---error 1051 -drop table t1, t2; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests - +--source suite/rpl/t/rpl_drop.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_drop_db.test b/mysql-test/suite/engines/funcs/t/rpl_drop_db.test index fb26cc20f8a..54d5300be14 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_drop_db.test +++ b/mysql-test/suite/engines/funcs/t/rpl_drop_db.test @@ -1,61 +1,2 @@ -# test case for BUG#4680 -- if there are extra files in the db directory -# dropping the db on the master causes replication problems +--source suite/rpl/t/rpl_drop_db.test --- source include/master-slave.inc -connection master; - ---disable_warnings -drop database if exists mysqltest1; ---enable_warnings -create database mysqltest1; -create table mysqltest1.t1 (n int); -insert into mysqltest1.t1 values (1); -select * from mysqltest1.t1 into outfile 'mysqltest1/f1.txt'; -create table mysqltest1.t2 (n int); -create table mysqltest1.t3 (n int); ---replace_result \\ / ---error 1010 -drop database mysqltest1; -use mysqltest1; -show tables; - -# test the branch of the code that deals with the query buffer overflow - ---disable_query_log -let $1=50; -while ($1) -{ - eval create table mysqltest1.mysqltest_long_table_name$1 (n int); - dec $1; -} ---enable_query_log - ---replace_result \\ / ---error 1010 -drop database mysqltest1; -use mysqltest1; -show tables; -use test; -create table t1 (n int); -insert into t1 values (1234); -sync_slave_with_master; - -connection slave; -use mysqltest1; -show tables; -use test; -select * from t1; -drop table t1; - -#cleanup -connection slave; -stop slave; -drop database mysqltest1; - -connection master; -# Remove the "extra" file created above ---remove_file $MYSQLTEST_VARDIR/mysqld.1/data/mysqltest1/f1.txt -drop database mysqltest1; - -use test; -drop table t1; diff --git a/mysql-test/suite/engines/funcs/t/rpl_dual_pos_advance.test b/mysql-test/suite/engines/funcs/t/rpl_dual_pos_advance.test index 518fa9df885..bf5e84152bc 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_dual_pos_advance.test +++ b/mysql-test/suite/engines/funcs/t/rpl_dual_pos_advance.test @@ -6,7 +6,7 @@ # of their server id). # It also will test BUG#13861. -source include/master-slave.inc; +--source include/master-slave.inc # set up "dual head" @@ -18,7 +18,7 @@ connection master; --replace_result $SLAVE_MYPORT SLAVE_PORT eval change master to master_host="127.0.0.1",master_port=$SLAVE_MYPORT,master_user="root"; -start slave; +--source include/start_slave.inc # now we test it @@ -68,7 +68,7 @@ sync_with_master; # show tables; -# start slave; +# --source include/start_slave.inc # BUG#13023 is that Exec_master_log_pos may stay too low "forever": @@ -78,9 +78,7 @@ create table t4 (n int); # create 3 ignored events create table t5 (n int); create table t6 (n int); -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master connection slave; @@ -97,12 +95,11 @@ show tables; # cleanup -stop slave; +--source include/stop_slave.inc reset slave; drop table t1,t4,t5,t6; # add t2 and t3 later -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master # End of 4.1 tests +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_empty_master_crash.test b/mysql-test/suite/engines/funcs/t/rpl_empty_master_crash.test deleted file mode 100644 index 707d1eca8c2..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl_empty_master_crash.test +++ /dev/null @@ -1,15 +0,0 @@ -source include/master-slave.inc; - ---replace_column 1 # 8 # 9 # 16 # 23 # 33 # -show slave status; - -# -# Load table should not succeed on the master as this is not a slave -# ---error 1218 -load table t1 from master; -connection slave; ---error 1188 -load table t1 from master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_err_ignoredtable.test b/mysql-test/suite/engines/funcs/t/rpl_err_ignoredtable.test index adf1526a657..1459b24799e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_err_ignoredtable.test +++ b/mysql-test/suite/engines/funcs/t/rpl_err_ignoredtable.test @@ -1,68 +1,2 @@ -# Test for -# Bug #797: If a query is ignored on slave (replicate-ignore-table) the slave -# still checks that it has the same error as on the master. -########################################################################## +--source suite/rpl/t/rpl_err_ignoredtable.test --- source include/master-slave.inc - ---disable_query_log -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - -connection master; -create table t1 (a int primary key); -create table t4 (a int primary key); -# generate an error that goes to the binlog ---error 1022, 1062, 1582 -insert into t1 values (1),(1); -insert into t4 values (1),(2); -save_master_pos; -connection slave; -# as the t1 table is ignored on the slave, the slave should be able to sync -sync_with_master; -# check that the table has been ignored, because otherwise the test is nonsense -show tables like 't1'; -show tables like 't4'; -SELECT * FROM test.t4 ORDER BY a; -connection master; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - -# Now test that even critical errors (connection killed) -# are ignored if rules allow it. -# The "kill" idea was copied from rpl000001.test. - -connection master1; -select get_lock('crash_lock%20C', 10); - -connection master; -create table t2 (a int primary key); -insert into t2 values(1); -create table t3 (id int); -insert into t3 values(connection_id()); -send update t2 set a = a + 1 + get_lock('crash_lock%20C', 10); - -connection master1; -real_sleep 2; -select (@id := id) - id from t3; -kill @id; -drop table t2,t3; -insert into t4 values (3),(4); -connection master; ---error 0,1053,2013,1317 -reap; -connection master1; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t4 ORDER BY a; - -connection master1; -DROP TABLE test.t4; -save_master_pos; -connection slave; -sync_with_master; -# End of 4.1 tests -# Adding comment for force manual merge 5.0 -> wl1012. delete me if needed diff --git a/mysql-test/suite/engines/funcs/t/rpl_flushlog_loop.test b/mysql-test/suite/engines/funcs/t/rpl_flushlog_loop.test index 2e481f5e5e7..93207dfddf7 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_flushlog_loop.test +++ b/mysql-test/suite/engines/funcs/t/rpl_flushlog_loop.test @@ -6,31 +6,22 @@ # Start replication master -> slave # connection slave; ---disable_warnings -stop slave; ---enable_warnings + +--source include/stop_slave.inc + --replace_result $MASTER_MYPORT MASTER_PORT eval change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=$MASTER_MYPORT; -start slave; - +--source include/start_slave.inc # # Start replication slave -> master # connection master; ---disable_warnings -stop slave; ---enable_warnings + --replace_result $SLAVE_MYPORT SLAVE_PORT eval change master to master_host='127.0.0.1',master_user='root', master_password='',master_port=$SLAVE_MYPORT; -start slave; - -# -# Wait for start of slave IO and SQL threads -# -let $result_pattern= '%127.0.0.1%root%slave-bin.000001%slave-bin.000001%Yes%Yes%0%0%None%'; --- source $MYSQL_TEST_DIR/suite/engines/funcs/t/wait_slave_status.inc +--source include/start_slave.inc # # Flush logs of slave @@ -45,4 +36,5 @@ sleep 5; --replace_column 1 # 8 # 9 # 16 # 23 # 33 # --vertical_results SHOW SLAVE STATUS; -STOP SLAVE; +stop slave; +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_free_items.test b/mysql-test/suite/engines/funcs/t/rpl_free_items.test index 043e84160b8..31d65396d36 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_free_items.test +++ b/mysql-test/suite/engines/funcs/t/rpl_free_items.test @@ -1,22 +1,2 @@ -source include/master-slave.inc; -create table t1 (a int); -create table t2 (a int); -disable_query_log; -SET @query="INSERT INTO t2 SELECT * FROM t1 WHERE a REGEXP \"0\""; -let $1 = 2000; -while ($1) -{ - eval SET @query=concat(@query, " OR a REGEXP '$1'"); - dec $1; -} -let $1=`select @query`; -eval $1; -enable_query_log; -# I have seen the slave crash either now or at shutdown -sync_slave_with_master; -connection master; -drop table t1; -drop table t2; -sync_slave_with_master; +--source suite/rpl/t/rpl_free_items.test -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_get_lock.test b/mysql-test/suite/engines/funcs/t/rpl_get_lock.test index c57e9313899..a9265c7f864 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_get_lock.test +++ b/mysql-test/suite/engines/funcs/t/rpl_get_lock.test @@ -1,49 +1,2 @@ -source include/master-slave.inc; +--source suite/rpl/t/rpl_get_lock.test ---disable_query_log -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - -create table t1(n int); ---disable_warnings -insert into t1 values(get_lock("lock",2)); ---enable_warnings -dirty_close master; -connection master1; -select get_lock("lock",2); - -select release_lock("lock"); -#ignore -disable_query_log; -let $1=2000; -while ($1) -{ - do get_lock("lock",2); - do release_lock("lock"); - dec $1; -} -enable_query_log; -save_master_pos; -connection slave; -sync_with_master; -select get_lock("lock",3); -select * from t1; -# There is no point in testing REPLICATIION of the IS_*_LOCK -# functions; slave does not run with the same concurrency context as -# master (generally in slave we can't know that on master this lock -# was already held by another connection and so that the the -# get_lock() we're replicating timed out on master hence returned 0, -# or that the is_free_lock() we're playing returned 0 etc. -# But here all we do is test these functions outside of replication. -select is_free_lock("lock"), is_used_lock("lock") = connection_id(); -explain extended select is_free_lock("lock"), is_used_lock("lock"); -# Check lock functions -select is_free_lock("lock2"); -select is_free_lock(NULL); -connection master1; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_ignore_grant.test b/mysql-test/suite/engines/funcs/t/rpl_ignore_grant.test index 2e6e2ce9a31..234fec3361e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_ignore_grant.test +++ b/mysql-test/suite/engines/funcs/t/rpl_ignore_grant.test @@ -1,59 +1,2 @@ -# Test that GRANT is not replicated to the slave -# when --replicate-wild-ignore-table=mysql.% -# In BUG#980, this test would _randomly_ fail. +--source suite/rpl/t/rpl_ignore_grant.test -source include/master-slave.inc; - -# do not be influenced by other tests. -connection master; -delete from mysql.user where user=_binary'rpl_ignore_grant'; -delete from mysql.db where user=_binary'rpl_ignore_grant'; -flush privileges; -save_master_pos; -connection slave; -sync_with_master; -# as these DELETE were not replicated, we need to do them manually on the -# slave. -delete from mysql.user where user=_binary'rpl_ignore_grant'; -delete from mysql.db where user=_binary'rpl_ignore_grant'; -flush privileges; - -# test non-replication of GRANT -connection master; -grant select on *.* to rpl_ignore_grant@localhost; -grant drop on test.* to rpl_ignore_grant@localhost; -show grants for rpl_ignore_grant@localhost; -save_master_pos; -connection slave; -sync_with_master; ---error 1141 #("no such grant for user") -show grants for rpl_ignore_grant@localhost; -# check it another way -select count(*) from mysql.user where user=_binary'rpl_ignore_grant'; -select count(*) from mysql.db where user=_binary'rpl_ignore_grant'; - -# test non-replication of SET PASSWORD -# first force creation of the user on slave (because as the user does not exist -# on slave, the SET PASSWORD may be replicated but silently do nothing; this is -# not what we want; we want it to be not-replicated). -grant select on *.* to rpl_ignore_grant@localhost; -connection master; -set password for rpl_ignore_grant@localhost=password("does it work?"); -save_master_pos; -connection slave; -sync_with_master; -select password<>_binary'' from mysql.user where user=_binary'rpl_ignore_grant'; - -# clear what we have done, to not influence other tests. -connection master; -delete from mysql.user where user=_binary'rpl_ignore_grant'; -delete from mysql.db where user=_binary'rpl_ignore_grant'; -flush privileges; -save_master_pos; -connection slave; -sync_with_master; -delete from mysql.user where user=_binary'rpl_ignore_grant'; -delete from mysql.db where user=_binary'rpl_ignore_grant'; -flush privileges; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_ignore_revoke.test b/mysql-test/suite/engines/funcs/t/rpl_ignore_revoke.test index 00171605a92..1a981652645 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_ignore_revoke.test +++ b/mysql-test/suite/engines/funcs/t/rpl_ignore_revoke.test @@ -1,51 +1,2 @@ -# test verifies that REVOKE must not be replicated when -# slave server starts with --replicate-wild-ignore-table=mysql.% -# the option is set in rpl_ignore_revoke-slave.opt -# The first part of BUG#9483 for GRANT is checked by -# existed specific rpl_ignore_grant test case (BUG#980) +--source suite/rpl/t/rpl_ignore_revoke.test - -source include/master-slave.inc; - -### CLEAN-UP: create an account and manually duplicate it on the slave - -connection master; -grant select on *.* to 'user_foo'@'%' identified by 'user_foopass'; -revoke select on *.* from 'user_foo'@'%'; -select select_priv from mysql.user where user='user_foo' /* master:must be N */; - -sync_slave_with_master; -#connection slave; -grant select on *.* to 'user_foo'@'%' identified by 'user_foopass'; -revoke select on *.* from 'user_foo'@'%'; -select select_priv from mysql.user where user='user_foo' /* slave:must be N */; - - -### TEST - -#connection slave; -grant select on *.* to 'user_foo'@'%' identified by 'user_foopass'; -select select_priv from mysql.user where user='user_foo' /* slave:must be Y */; - -connection master; -revoke select on *.* from 'user_foo'; -select select_priv from mysql.user where user='user_foo' /* master:must be N */; - -sync_slave_with_master; -#connection slave; -select select_priv from mysql.user where user='user_foo' /* slave:must get Y */; - -### CLEAN-UP - -connection slave; ---disable_abort_on_error -revoke select on *.* FROM 'user_foo'; ---enable_abort_on_error - -connection master; -delete from mysql.user where user="user_foo"; -sync_slave_with_master; - -# Since changes to mysql.* are ignored, the revoke need to -# be done on slave as well -delete from mysql.user where user="user_foo"; diff --git a/mysql-test/suite/engines/funcs/t/rpl_ignore_table_update.test b/mysql-test/suite/engines/funcs/t/rpl_ignore_table_update.test index fe030f90411..9ace16dd247 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_ignore_table_update.test +++ b/mysql-test/suite/engines/funcs/t/rpl_ignore_table_update.test @@ -1,38 +1,2 @@ -# This one assumes we are ignoring updates on table mysqltest_foo, but doing -# the ones on all other tables +--source suite/rpl/t/rpl_ignore_table_update.test -source include/master-slave.inc; -connection slave; - -# -# For this test we must be in the test database -# -use test; - ---disable_warnings -drop table if exists mysqltest_foo; -drop table if exists mysqltest_bar; ---enable_warnings - -create table mysqltest_foo (n int); -insert into mysqltest_foo values(4); -connection master; -use test; -create table mysqltest_foo (n int); -insert into mysqltest_foo values(5); -create table mysqltest_bar (m int); -insert into mysqltest_bar values(15); -create table t1 (k int); -insert into t1 values(55); -save_master_pos; -connection slave; -sync_with_master; -select mysqltest_foo.n,mysqltest_bar.m,t1.k from mysqltest_foo,mysqltest_bar,t1; -connection master; -drop table mysqltest_foo,mysqltest_bar,t1; -save_master_pos; -connection slave; -sync_with_master; -drop table mysqltest_foo,mysqltest_bar,t1; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_init_slave.test b/mysql-test/suite/engines/funcs/t/rpl_init_slave.test index 139b4902e12..85869ffbb99 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_init_slave.test +++ b/mysql-test/suite/engines/funcs/t/rpl_init_slave.test @@ -1,34 +1,2 @@ -source include/master-slave.inc; +--source suite/rpl/t/rpl_init_slave.test -# -# Test of init_slave variable -# - -save_master_pos; -connection slave; -sleep 1; -show variables like 'init_slave'; -show variables like 'max_connections'; -sync_with_master; -reset master; -connection master; -show variables like 'init_slave'; -show variables like 'max_connections'; -save_master_pos; -connection slave; -sync_with_master; -# Save variable value -set @my_global_init_connect= @@global.init_connect; -set global init_connect="set @c=1"; -show variables like 'init_connect'; -connection master; -save_master_pos; -connection slave; -sync_with_master; -stop slave; - -# Restore changed global variable -set global init_connect= @my_global_init_connect; -set global max_connections= default; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_insert.test b/mysql-test/suite/engines/funcs/t/rpl_insert.test index f57a6e226d1..1a5d5ecba54 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_insert.test +++ b/mysql-test/suite/engines/funcs/t/rpl_insert.test @@ -1,44 +1,2 @@ ---echo # ---echo # Bug#20821: INSERT DELAYED fails to write some rows to binlog ---echo # +--source suite/rpl/t/rpl_insert.test ---source include/not_embedded.inc ---source include/not_windows.inc ---source include/master-slave.inc - ---disable_warnings -CREATE SCHEMA IF NOT EXISTS mysqlslap; -USE mysqlslap; ---enable_warnings - -CREATE TABLE t1 (id INT, name VARCHAR(64)); - -let $query = "INSERT INTO t1 VALUES (1, 'Dr. No'), (2, 'From Russia With Love'), (3, 'Goldfinger'), (4, 'Thunderball'), (5, 'You Only Live Twice')"; ---exec $MYSQL_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";" - -# Wait until all the 5000 inserts has been inserted into the table ---disable_query_log -let $counter= 300; # Max 30 seconds wait -while (`select count(*)!=5000 from mysqlslap.t1`) -{ - sleep 0.1; - dec $counter; - if (!$counter) - { - Number of records in t1 didnt reach 5000; - } -} ---enable_query_log - -SELECT COUNT(*) FROM mysqlslap.t1; -sync_slave_with_master; -SELECT COUNT(*) FROM mysqlslap.t1; - ---echo # ---echo # Cleanup ---echo # - -connection master; -USE test; -DROP SCHEMA mysqlslap; -sync_slave_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_insert_select.test b/mysql-test/suite/engines/funcs/t/rpl_insert_select.test index 677be526982..bc9ec9da636 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_insert_select.test +++ b/mysql-test/suite/engines/funcs/t/rpl_insert_select.test @@ -1,6 +1,6 @@ # Testcase for BUG#10456 - INSERT INTO ... SELECT violating a primary key # breaks replication - +-- source include/have_binlog_format_mixed_or_row.inc -- source include/master-slave.inc connection master; @@ -10,10 +10,11 @@ create table t2 (n int); insert into t2 values (1); insert ignore into t1 select * from t2; insert into t1 values (2); -sync_slave_with_master; +--sync_slave_with_master connection slave; select * from t1; connection master; drop table t1,t2; -sync_slave_with_master; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_loaddata2.test b/mysql-test/suite/engines/funcs/t/rpl_loaddata2.test index 439c2b48ca5..b357054b4db 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_loaddata2.test +++ b/mysql-test/suite/engines/funcs/t/rpl_loaddata2.test @@ -3,7 +3,7 @@ CREATE TABLE t1 (word CHAR(20) NOT NULL); LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; SELECT * FROM t1 ORDER BY word; -sync_slave_with_master; +--sync_slave_with_master # Check SELECT * FROM t1 ORDER BY word; @@ -11,4 +11,5 @@ SELECT * FROM t1 ORDER BY word; # Cleanup connection master; drop table t1; -sync_slave_with_master; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_loaddata_m.test b/mysql-test/suite/engines/funcs/t/rpl_loaddata_m.test index 42c3ad99f33..901c40b5079 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_loaddata_m.test +++ b/mysql-test/suite/engines/funcs/t/rpl_loaddata_m.test @@ -1,52 +1,2 @@ -# See if the master logs LOAD DATA INFILE correctly when binlog_*_db rules -# exist. -# This is for BUG#1100 (LOAD DATA INFILE was half-logged). -###################################################### -# Change Author: JBM -# Change Date: 2005-12-22 -# Change: Test rewritten to remove show binlog events -# and to test the option better + Cleanup -###################################################### --- source include/master-slave.inc +--source suite/rpl/t/rpl_loaddata_m.test ---disable_warnings -drop database if exists mysqltest; ---enable_warnings - -connection master; -# 'test' database should be ignored by the slave -USE test; -CREATE TABLE t1(a INT, b INT, UNIQUE(b)); -LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE test.t1; -SELECT COUNT(*) FROM test.t1; - -# 'mysqltest' database should NOT be ignored by the slave -CREATE DATABASE mysqltest; -USE mysqltest; -CREATE TABLE t1(a INT, b INT, UNIQUE(b)); -LOAD DATA INFILE '../../std_data/rpl_loaddata.dat' INTO TABLE mysqltest.t1; -SELECT COUNT(*) FROM mysqltest.t1; - -# Now lets check the slave to see what we have :-) -save_master_pos; -connection slave; -sync_with_master; - -SHOW DATABASES; - -USE test; -SHOW TABLES; - -USE mysqltest; -SHOW TABLES; -SELECT COUNT(*) FROM mysqltest.t1; - -#show binlog events; - -# Cleanup -connection master; -DROP DATABASE mysqltest; -DROP TABLE test.t1; -sync_slave_with_master; - -# End of test diff --git a/mysql-test/suite/engines/funcs/t/rpl_loaddata_s.test b/mysql-test/suite/engines/funcs/t/rpl_loaddata_s.test index a06df3bbfc9..7521f62403e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_loaddata_s.test +++ b/mysql-test/suite/engines/funcs/t/rpl_loaddata_s.test @@ -1,30 +1,2 @@ -# See if the slave logs (in its own binlog, with --log-slave-updates) a -# replicated LOAD DATA INFILE correctly when it has binlog_*_db rules. -# This is for BUG#1100 (LOAD DATA INFILE was half-logged). +--source suite/rpl/t/rpl_loaddata_s.test --- source include/have_binlog_format_mixed_or_statement.inc --- source include/master-slave.inc - -connection slave; -# Not sure why we connect to slave and then try to reset master, but I will leave it [JBM] -reset master; - -connection master; -# 'test' is the current database -create table test.t1(a int, b int, unique(b)); -load data infile '../../std_data/rpl_loaddata.dat' into table test.t1; - -# Test logging on slave; - -save_master_pos; -connection slave; -sync_with_master; -select count(*) from test.t1; # check that LOAD was replicated ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -show binlog events from 107; # should be nothing - -# Cleanup -connection master; -drop table test.t1; -sync_slave_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_loaddatalocal.test b/mysql-test/suite/engines/funcs/t/rpl_loaddatalocal.test index e272be3c13b..36e863c4573 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_loaddatalocal.test +++ b/mysql-test/suite/engines/funcs/t/rpl_loaddatalocal.test @@ -6,7 +6,7 @@ # only the first 4KB, 8KB or 16KB usually. # - the loaded file's first line was not written entirely to the # master's binlog (1st char was absent) -source include/master-slave.inc; +--source include/master-slave.inc create table t1(a int); let $1=10000; @@ -26,15 +26,11 @@ truncate table t1; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR eval load data local infile '$MYSQLTEST_VARDIR/tmp/rpl_loaddatalocal.select_outfile' into table t1; remove_file $MYSQLTEST_VARDIR/tmp/rpl_loaddatalocal.select_outfile; -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master select a,count(*) from t1 group by a; connection master; drop table t1; -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master # End of 4.1 tests @@ -54,12 +50,9 @@ create table t1(a int primary key); eval load data local infile '$MYSQLTEST_VARDIR/tmp/rpl_loaddatalocal.select_outfile' into table t1; remove_file $MYSQLTEST_VARDIR/tmp/rpl_loaddatalocal.select_outfile; SELECT * FROM t1 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master SELECT * FROM t1 ORDER BY a; connection master; drop table t1; -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_loadfile.test b/mysql-test/suite/engines/funcs/t/rpl_loadfile.test index 26235d89016..26829b265f1 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_loadfile.test +++ b/mysql-test/suite/engines/funcs/t/rpl_loadfile.test @@ -41,7 +41,7 @@ CALL test.p1(); --enable_warnings SELECT * FROM test.t1 ORDER BY blob_column; save_master_pos; -sync_slave_with_master; +--sync_slave_with_master connection slave; SELECT * FROM test.t1 ORDER BY blob_column; @@ -49,6 +49,6 @@ SELECT * FROM test.t1 ORDER BY blob_column; connection master; DROP PROCEDURE IF EXISTS test.p1; DROP TABLE test.t1; -sync_slave_with_master; - +--sync_slave_with_master +--source include/rpl_end.inc # End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_log_pos.test b/mysql-test/suite/engines/funcs/t/rpl_log_pos.test index 22deee6b5f3..53b38d9a2de 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_log_pos.test +++ b/mysql-test/suite/engines/funcs/t/rpl_log_pos.test @@ -1,58 +1,2 @@ -########## -# Change Author: JBM -# Change Date: 2006-01-16 -########## +--source suite/rpl/t/rpl_log_pos.test -# -# Testing of setting slave to wrong log position with master_log_pos -# - -# Passes with rbr no problem, removed statement include [jbm] - -source include/master-slave.inc; ---replace_column 3 <Binlog_Ignore_DB> -show master status; -sync_slave_with_master; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; -stop slave; -change master to master_log_pos=107; -start slave; -sleep 5; -stop slave; -change master to master_log_pos=107; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; -start slave; -sleep 5; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; -stop slave; -change master to master_log_pos=178; -start slave; -sleep 2; ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 8 # 9 # 23 # 33 # -show slave status; -connection master; ---replace_column 3 <Binlog_Ignore_DB> -show master status; -create table if not exists t1 (n int); -drop table if exists t1; -create table t1 (n int); -insert into t1 values (1),(2),(3); -save_master_pos; -connection slave; -stop slave; -change master to master_log_pos=207; -start slave; -sync_with_master; -select * from t1 ORDER BY n; -connection master; -drop table t1; -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_many_optimize.test b/mysql-test/suite/engines/funcs/t/rpl_many_optimize.test index 91fab0b27a8..337cc1b36be 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_many_optimize.test +++ b/mysql-test/suite/engines/funcs/t/rpl_many_optimize.test @@ -1,22 +1,2 @@ -# Test for BUG#7658 "optimize crashes slave thread (1 in 1000)]" +--source suite/rpl/t/rpl_many_optimize.test -source include/master-slave.inc; - -create table t1 (a int not null auto_increment primary key, b int, key(b)); -INSERT INTO t1 (a) VALUES (1),(2); -# Now many OPTIMIZE to test if we crash (BUG#7658) -let $1=300; -disable_query_log; -disable_result_log; -while ($1) -{ - eval OPTIMIZE TABLE t1; - dec $1; -} -enable_result_log; -enable_query_log; -drop table t1; -# Bug was that slave segfaulted after ~ a hundred of OPTIMIZE (or ANALYZE) -sync_slave_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_master_pos_wait.test b/mysql-test/suite/engines/funcs/t/rpl_master_pos_wait.test deleted file mode 100644 index 893c8746efc..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl_master_pos_wait.test +++ /dev/null @@ -1,18 +0,0 @@ -# See if master_pos_wait(,,timeout) -# Terminates with "timeout expired" (-1) -source include/master-slave.inc; -save_master_pos; -connection slave; -sync_with_master; -# Ask for a master log that has certainly not been reached yet -# timeout= 2 seconds -select master_pos_wait('master-bin.999999',0,2); -explain extended select master_pos_wait('master-bin.999999',0,2); -# Testcase for bug 651 (master_pos_wait() hangs if slave idle and STOP SLAVE). -send select master_pos_wait('master-bin.999999',0); -connection slave1; -stop slave sql_thread; -connection slave; -reap; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_misc_functions.test b/mysql-test/suite/engines/funcs/t/rpl_misc_functions.test index 6e4bedf7371..2f0786a9271 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_misc_functions.test +++ b/mysql-test/suite/engines/funcs/t/rpl_misc_functions.test @@ -1,46 +1,2 @@ -# -# Test of replicating some difficult functions -# -source include/master-slave.inc; +--source suite/rpl/t/rpl_misc_functions.test ---disable_query_log -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - -create table t1(id int, i int, r1 int, r2 int, p varchar(100)); -insert into t1 values(1, connection_id(), 0, 0, ""); -# don't put rand and password in the same query, to see if they replicate -# independently -# Pure rand test ---disable_warnings -insert into t1 values(2, 0, rand()*1000, rand()*1000, ""); ---enable_warnings -# change the rand suite on the master (we do this because otherwise password() -# benefits from the fact that the above rand() is well replicated : -# it picks the same sequence element, which hides a possible bug in password() replication. -set sql_log_bin=0; -insert into t1 values(6, 0, rand(), rand(), ""); -delete from t1 where id=6; -set sql_log_bin=1; -# Pure password test -insert into t1 values(3, 0, 0, 0, password('does_this_work?')); -# "altogether now" ---disable_warnings -insert into t1 values(4, connection_id(), rand()*1000, rand()*1000, password('does_this_still_work?')); ---enable_warnings -select * into outfile '../../tmp/rpl_misc_functions.outfile' from t1; -sync_slave_with_master; -create table t2 like t1; -# read the values from the master table ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -eval load data local infile '$MYSQLTEST_VARDIR/tmp/rpl_misc_functions.outfile' into table t2; -# compare them with the replica; the SELECT below should return no row -select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p); -stop slave; -drop table t1; -drop table t2; - -connection master; -drop table t1; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_multi_delete.test b/mysql-test/suite/engines/funcs/t/rpl_multi_delete.test index a251cbf8833..53347fbf127 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_multi_delete.test +++ b/mysql-test/suite/engines/funcs/t/rpl_multi_delete.test @@ -1,26 +1,2 @@ -source include/master-slave.inc; -create table t1 (a int primary key); -create table t2 (a int); +--source suite/rpl/t/rpl_multi_delete.test -insert into t1 values (1); -insert into t2 values (1); - - -delete t1.* from t1, t2 where t1.a = t2.a; - -save_master_pos; -select * from t1; -select * from t2; - -connection slave; -sync_with_master; -select * from t1; -select * from t2; - -connection master; -drop table t1,t2; -save_master_pos; -connection slave; -sync_with_master; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_multi_delete2.test b/mysql-test/suite/engines/funcs/t/rpl_multi_delete2.test index e91fad1872a..17df048f930 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_multi_delete2.test +++ b/mysql-test/suite/engines/funcs/t/rpl_multi_delete2.test @@ -1,68 +1,2 @@ -#multi delete replication bugs - - -source include/master-slave.inc; - -#BUG#11139 - improper wild-table and table rules -#checking for multi deletes with an alias - -connection master; -set sql_log_bin=0; -create database mysqltest_from; -set sql_log_bin=1; - -connection slave; -create database mysqltest_to; - - -connection master; -use mysqltest_from; ---disable_warnings -drop table if exists a; ---enable_warnings -CREATE TABLE a (i INT); -INSERT INTO a VALUES(1); -DELETE alias FROM a alias WHERE alias.i=1; -SELECT * FROM a; -insert into a values(2),(3); -delete alias FROM a alias where alias.i=2; -select * from a; -save_master_pos; -connection slave; - -use mysqltest_to; -sync_with_master; -select * from a; - -# BUG#3461 -connection master; -create table t1 (a int primary key); -create table t2 (a int); - -insert into t1 values (1); -insert into t2 values (1); - -delete t1.* from t1, t2 where t1.a = t2.a; - -save_master_pos; -select * from t1; -select * from t2; - -connection slave; -# BUG#3461 would cause sync to fail -sync_with_master; -error 1146; -select * from t1; -error 1146; -select * from t2; - -# cleanup -connection master; -set sql_log_bin=0; -drop database mysqltest_from; -set sql_log_bin=1; -connection slave; -drop database mysqltest_to; - -# End of 4.1 tests +--source suite/rpl/t/rpl_multi_delete2.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_multi_update4.test b/mysql-test/suite/engines/funcs/t/rpl_multi_update4.test index 4991a385f6f..5538e6cba77 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_multi_update4.test +++ b/mysql-test/suite/engines/funcs/t/rpl_multi_update4.test @@ -1,45 +1,2 @@ -# Let's verify that multi-update is not always skipped by slave if -# some replicate-* rules exist. -# (BUG#15699) +--source suite/rpl/t/rpl_multi_update4.test -source include/master-slave.inc; - -### Clean-up - -connection master; ---disable_warnings -drop database if exists d1; -drop database if exists d2; - -connection slave; -drop database if exists d2; ---enable_warnings - -### Do on master - -connection master; -create database d1; # accepted by slave -create table d1.t0 (id int); -create database d2; # ignored by slave -use d2; -create table t1 (id int); -create table t2 (id int); -insert into t1 values (1), (2), (3), (4), (5); -insert into t2 select id + 3 from t1; -# a problematic query which must be filter out by slave -update t1 join t2 using (id) set t1.id = 0; -insert into d1.t0 values (0); # replication works - -### Check on slave - -sync_slave_with_master; -use d1; -select * from t0 where id=0; # must find - -### Clean-up -connection master; -drop database d1; -drop database d2; -sync_slave_with_master; - -# End of test diff --git a/mysql-test/suite/engines/funcs/t/rpl_ps.test b/mysql-test/suite/engines/funcs/t/rpl_ps.test index 09c7b779f65..d40c5737912 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_ps.test +++ b/mysql-test/suite/engines/funcs/t/rpl_ps.test @@ -1,49 +1,2 @@ -# -# Test of replicating user variables -# -########################################################### -source include/master-slave.inc; +--source suite/rpl/t/rpl_ps.test -#save_master_pos; -#connection slave; -#sync_with_master; -#reset master; -#connection master; - ---disable_warnings -drop table if exists t1; ---enable_warnings - -create table t1(n char(30)); - -prepare stmt1 from 'insert into t1 values (?)'; -set @var1= "from-master-1"; -execute stmt1 using @var1; -set @var1= "from-master-2-'',"; -execute stmt1 using @var1; -SELECT * FROM t1 ORDER BY n; - -set @var2= 'insert into t1 values (concat("from-var-", ?))'; -prepare stmt2 from @var2; -set @var1='from-master-3'; -execute stmt2 using @var1; - -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM t1 ORDER BY n; - -connection master; - -drop table t1; - -save_master_pos; -connection slave; -sync_with_master; -stop slave; - -# End of 4.1 tests - -reset master; -reset slave; -disconnect master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_rbr_to_sbr.test b/mysql-test/suite/engines/funcs/t/rpl_rbr_to_sbr.test deleted file mode 100644 index f4e6239c679..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl_rbr_to_sbr.test +++ /dev/null @@ -1,47 +0,0 @@ --- source include/have_binlog_format_mixed.inc --- source include/master-slave.inc - -# Test that the slave temporarily switches to ROW when seeing row -# events when it is in MIXED mode - ---echo **** On Master **** -CREATE TABLE t1 (a INT, b LONG); -INSERT INTO t1 VALUES (1,1), (2,2); -INSERT INTO t1 VALUES (3,UUID()), (4,UUID()); -let $VERSION=`select version()`; ---replace_result $VERSION VERSION ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -# Different number of binlog events are generated by different engines ---disable_result_log -SHOW BINLOG EVENTS; ---enable_result_log -sync_slave_with_master; ---echo **** On Slave **** ---replace_result $MASTER_MYPORT MASTER_PORT ---replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # ---query_vertical SHOW SLAVE STATUS ---replace_result $VERSION VERSION ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -# Different number of binlog events are generated by different engines ---disable_result_log -SHOW BINLOG EVENTS; ---enable_result_log ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_slave.sql - -connection master; -DROP TABLE IF EXISTS t1; - -# Let's compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching - -diff_files $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_master.sql $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_slave.sql; - -# If all is good, we can remove the files - -remove_file $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_master.sql; -remove_file $MYSQLTEST_VARDIR/tmp/rpl_rbr_to_sbr_slave.sql; diff --git a/mysql-test/suite/engines/funcs/t/rpl_relayspace.test b/mysql-test/suite/engines/funcs/t/rpl_relayspace.test index 70315c14f34..5c60f57b496 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_relayspace.test +++ b/mysql-test/suite/engines/funcs/t/rpl_relayspace.test @@ -1,34 +1,2 @@ -# The slave is started with relay_log_space_limit=10 bytes, -# to force the deadlock after one event. +--source suite/rpl/t/rpl_relayspace.test -source include/master-slave.inc; -connection slave; -stop slave; -connection master; -# This will generate a master's binlog > 10 bytes -create table t1 (a int); -drop table t1; -create table t1 (a int); -drop table t1; -connection slave; -reset slave; -start slave io_thread; -# Give the I/O thread time to block. -sleep 2; -# A bug caused the I/O thread to refuse stopping. -stop slave io_thread; -reset slave; -start slave; -# The I/O thread stops filling the relay log when -# it's >10b. And the SQL thread cannot purge this relay log -# as purge is done only when the SQL thread switches to another -# relay log, which does not exist here. -# So we should have a deadlock. -# if it is not resolved automatically we'll detect -# it with master_pos_wait that waits for farther than 1Ob; -# it will timeout after 10 seconds; -# also the slave will probably not cooperate to shutdown -# (as 2 threads are locked) -select master_pos_wait('master-bin.001',200,6)=-1; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_replicate_ignore_db.test b/mysql-test/suite/engines/funcs/t/rpl_replicate_ignore_db.test index bcfef919fad..edadb14a3ee 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_replicate_ignore_db.test +++ b/mysql-test/suite/engines/funcs/t/rpl_replicate_ignore_db.test @@ -1,30 +1,2 @@ -# see if --replicate-ignore-db works +--source suite/rpl/t/rpl_replicate_ignore_db.test ---source include/master-slave.inc - ---disable_warnings -drop database if exists mysqltest1; -drop database if exists mysqltest2; ---enable_warnings -create database mysqltest1; -create database mysqltest2; - -use mysqltest1; -create table t1 (a int); -insert into t1 values(1); -sync_slave_with_master; ---error 1146 -select * from mysqltest1.t1; - -connection master; -use mysqltest2; -create table t1 (a int); -insert into t1 values(1); -sync_slave_with_master; -select * from mysqltest2.t1; - -# cleanup -connection master; -drop database mysqltest1; -drop database mysqltest2; -sync_slave_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_NOW.test b/mysql-test/suite/engines/funcs/t/rpl_row_NOW.test index bf9576ae11b..b909062b8cc 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_NOW.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_NOW.test @@ -1,74 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -# Updated 08/30/2005 Added dumps and diff # -############################################################################# -#TEST: Taken and modfied from http://bugs.mysql.com/bug.php?id=12480 # -############################################################################# +--source suite/rpl/t/rpl_row_NOW.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -create database if not exists mysqltest1; -DROP TABLE IF EXISTS mysqltest1.t1; ---enable_warnings - - -# Begin test section 1 -CREATE TABLE mysqltest1.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT, - a TIMESTAMP DEFAULT '2005-05-05 01:01:01', - b TIMESTAMP DEFAULT '2005-05-05 01:01:01', - PRIMARY KEY(n)); -delimiter |; -CREATE FUNCTION mysqltest1.f1() RETURNS TIMESTAMP -BEGIN - DECLARE v1 INT DEFAULT 300; - WHILE v1 > 0 DO - SET v1 = v1 - 1; - END WHILE; - RETURN NOW(); -END| -delimiter ;| - -INSERT INTO mysqltest1.t1 VALUES(NULL,NOW(),mysqltest1.f1()); - -delimiter |; -CREATE TRIGGER mysqltest1.trig1 BEFORE INSERT ON mysqltest1.t1 -FOR EACH ROW BEGIN - SET new.b = mysqltest1.f1(); -END| -delimiter ;| - -INSERT INTO mysqltest1.t1 SET n = NULL, a = now(); - -sync_slave_with_master; - -connection master; - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/NOW_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/NOW_slave.sql - -# lets cleanup -DROP TABLE IF EXISTS mysqltest1.t1; -DROP FUNCTION mysqltest1.f1; - -# Lets compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching :-) The failed dump -# files will be located in $MYSQLTEST_VARDIR/tmp - -diff_files $MYSQLTEST_VARDIR/tmp/NOW_master.sql $MYSQLTEST_VARDIR/tmp/NOW_slave.sql; - -# If all is good, when can cleanup our dump files. -remove_file $MYSQLTEST_VARDIR/tmp/NOW_master.sql; -remove_file $MYSQLTEST_VARDIR/tmp/NOW_slave.sql; - -DROP DATABASE mysqltest1; -sync_slave_with_master; -# End of 5.1 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_USER.test b/mysql-test/suite/engines/funcs/t/rpl_row_USER.test index c3639d05c48..e48b25293fa 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_USER.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_USER.test @@ -1,57 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -############################################################################# -# TEST: To test the UUID() in rbr # -############################################################################# -# Change Author: JBM -# Change Date: 2006-01-16 -########## +--source suite/rpl/t/rpl_row_USER.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - -# Begin clean up test section -connection master; ---disable_warnings -DROP DATABASE IF EXISTS mysqltest1; -CREATE DATABASE mysqltest1; ---enable_warnings - -# Section 1 test -CREATE USER tester IDENTIFIED BY 'test'; -GRANT ALL ON mysqltest1.* TO 'tester'@'%' IDENTIFIED BY 'test'; -GRANT ALL ON mysqltest1.* TO ''@'localhost%'; -FLUSH PRIVILEGES; -connect (m_1,localhost,tester,,mysqltest1); - -connection m_1; -CREATE TABLE mysqltest1.t1 (a INT, users VARCHAR(255), PRIMARY KEY(a)); -INSERT INTO mysqltest1.t1 VALUES(1,USER()); -INSERT INTO mysqltest1.t1 VALUES(2,CURRENT_USER()); -delimiter |; -create procedure mysqltest1.p1() -begin - INSERT INTO mysqltest1.t1 VALUES(3,USER()); - INSERT INTO mysqltest1.t1 VALUES(4,CURRENT_USER()); -end| -delimiter ;| - -CALL mysqltest1.p1(); -connection master; -SELECT * FROM mysqltest1.t1 ORDER BY a; -sync_slave_with_master; -SELECT * FROM mysqltest1.t1 ORDER BY a; - -connection master; -# Lets cleanup - -REVOKE ALL ON mysqltest1.* FROM 'tester'@'%'; -REVOKE ALL ON mysqltest1.* FROM ''@'localhost%'; -DROP DATABASE mysqltest1; -DROP USER 'tester'; -DROP USER ''@'localhost%'; -FLUSH PRIVILEGES; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_drop.test b/mysql-test/suite/engines/funcs/t/rpl_row_drop.test index 20c217a7c3a..35090d7f32f 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_drop.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_drop.test @@ -1,48 +1,2 @@ --- source include/have_binlog_format_row.inc --- source include/master-slave.inc +--source suite/rpl/t/rpl_row_drop.test -# Bug#12415: DROP of temporary table on master stops slave -connection master; ---echo **** On Master **** -CREATE TABLE t1 (a int); -CREATE TABLE t2 (a int); -CREATE TEMPORARY TABLE t2 (a int, b int); -SHOW TABLES; -sync_slave_with_master; ---echo **** On Slave **** -SHOW TABLES; -connection master; ---echo **** On Master **** -DROP TABLE t2; # Dropping the temporary table -SHOW TABLES; -sync_slave_with_master; ---echo **** On Slave **** -SHOW TABLES; # There should be two tables on the slave - -connection master; ---echo **** On Master **** -CREATE TEMPORARY TABLE t2 (a int, b int); -SHOW TABLES; -sync_slave_with_master; ---echo **** On Slave **** -SHOW TABLES; -connection master; ---echo **** On Master **** -# Should drop the non-temporary table t1 and the temporary table t2 -DROP TABLE t1,t2; -let $VERSION=`select version()`; ---replace_result $VERSION VERSION ---replace_regex /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS; -SHOW TABLES; -sync_slave_with_master; ---echo **** On Slave **** -SHOW TABLES; - ---disable_query_log ---disable_warnings -connection master; -DROP TABLE IF EXISTS t2; -sync_slave_with_master; ---enable_warnings ---enable_query_log diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_func001.test b/mysql-test/suite/engines/funcs/t/rpl_row_func001.test index 53fb55118e6..7671d9947c1 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_func001.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_func001.test @@ -1,57 +1,2 @@ -############################################################################# -# This test is being created to test out the non deterministic items with # -# row based replication. # -# Original Author: JBM # -# Original Date: Aug/10/2005 # -# Update: 08/29/2005 change name to initails # -############################################################################# +--source suite/rpl/t/rpl_row_func001.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings ---error 0,1305 -DROP FUNCTION test.f1; -DROP TABLE IF EXISTS test.t1; - - ---enable_warnings - -# Section 1 test from bug #12487 Uses stored function to insert rows to see what is replicated. - -create table test.t1 (a int, PRIMARY KEY(a)); - -delimiter //; -create function test.f1(i int) returns int -begin -insert into test.t1 values(i); -return 0; -end// -delimiter ;// - -select test.f1(1); -select test.f1(2); -select * from test.t1; - -save_master_pos; -sync_slave_with_master; -connection slave; -#show create table test.t1; -select * from test.t1; - -connection master; - -#Used for debugging -#show binlog events; - -# Cleanup - -DROP FUNCTION test.f1; -DROP TABLE test.t1; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_inexist_tbl.test b/mysql-test/suite/engines/funcs/t/rpl_row_inexist_tbl.test index 736071a8ece..793b94e63a6 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_inexist_tbl.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_inexist_tbl.test @@ -2,7 +2,7 @@ # not have --source include/have_binlog_format_row.inc -source include/master-slave.inc; +--source include/master-slave.inc connection master; create table t1 (a int not null primary key); @@ -11,7 +11,7 @@ create table t2 (a int); insert into t2 values (1); update t1, t2 set t1.a = 0 where t1.a = t2.a; -sync_slave_with_master; +--sync_slave_with_master # t2 should not have been replicated # t1 should have been properly updated show tables; @@ -23,13 +23,16 @@ insert into t1 values (1); connection slave; # slave should have stopped because can't find table t1 -wait_for_slave_to_stop; -# see if we have a good error message: ---replace_result $MASTER_MYPORT MASTER_MYPORT ---replace_column 1 # 7 # 8 # 9 # 22 # 23 # 33 # ---vertical_results -show slave status; +# 1146 = ER_NO_SUCH_TABLE +call mtr.add_suppression("Slave SQL.*Error executing row event: .Table .test.t1. doesn.t exist., error.* 1146"); +--let $slave_sql_errno= 1146 +--source include/wait_for_slave_sql_error.inc + +--echo ==== Clean up ==== +--source include/stop_slave_io.inc +RESET SLAVE; -# cleanup connection master; drop table t1, t2; +--let $rpl_only_running_threads= 1 +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_max_relay_size.test b/mysql-test/suite/engines/funcs/t/rpl_row_max_relay_size.test index 6df58122051..f7098d0d7bf 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_max_relay_size.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_max_relay_size.test @@ -4,6 +4,6 @@ # Test of manual relay log rotation with FLUSH LOGS. # Requires statement logging -source include/have_binlog_format_row.inc; +--source include/have_binlog_format_row.inc -source suite/rpl/include/rpl_max_relay_size.test; +--source suite/rpl/include/rpl_max_relay_size.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp001.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp001.test index ae6116bea4e..0610ef0274a 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp001.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp001.test @@ -1,146 +1,2 @@ -############################################################################# -# This test is being created to test out the non deterministic items with # -# row based replication. # -# Original Author: JBM # -# Original Date: Aug/09/2005 # -# Updated: Aug/29/2005 -############################################################################# -# Test: Includes two stored procedure tests. First test uses SP to insert # -# values from RAND() and NOW() into a table. # -# The second test uses SP with CASE structure to decide what to text # -# to update a given table with. # -############################################################################ +--source suite/rpl/t/rpl_row_sp001.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - --- disable_query_log --- disable_result_log - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; - --- enable_query_log --- enable_result_log - -# Begin test section 1 for non deterministic SP -let $message=<Begin test section 1 (non deterministic SP)>; ---source include/show_msg.inc - -create table test.t1 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); - -delimiter //; -create procedure test.p1() -begin - INSERT INTO test.t1 (f,d) VALUES (RAND(),NOW()); -end// -delimiter ;// - -# show binlog events; - --- disable_query_log --- disable_result_log -let $1=10; -while ($1) -{ - call test.p1(); - sleep 1; - dec $1; -} --- enable_result_log --- enable_query_log - -## Used for debugging -#show binlog events; -#select * from test.t1; -#sync_slave_with_master; -#select * from test.t1; -#connection master; - -let $message=<End test section 1 (non deterministic SP)>; ---source include/show_msg.inc - - -CREATE TABLE test.t2 (a INT NOT NULL AUTO_INCREMENT, t CHAR(4), PRIMARY KEY(a)); - -delimiter //; -CREATE PROCEDURE test.p2(n int) -begin -CASE n -WHEN 1 THEN - UPDATE test.t2 set t ='Tex'; -WHEN 2 THEN - UPDATE test.t2 set t ='SQL'; -ELSE - UPDATE test.t2 set t ='NONE'; -END CASE; -end// -delimiter ;// - -INSERT INTO test.t2 VALUES(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW'),(NULL,'NEW'); - -SELECT * FROM t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM t2 ORDER BY a; - -connection master; -call test.p2(1); -SELECT * FROM t2 ORDER BY a; -sync_slave_with_master; -SELECT * FROM t2 ORDER BY a; - - -connection master; -call test.p2(2); -SELECT * FROM t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM t2 ORDER BY a; - -connection master; -call test.p2(3); -SELECT * FROM t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM t2 ORDER BY a; - -##Used for debugging -#show binlog events; - -# time to dump the databases and so we can see if they match - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp001_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp001_slave.sql - -# First lets cleanup - -connection master; -DROP PROCEDURE test.p1; -DROP PROCEDURE test.p2; -DROP TABLE test.t1; -DROP TABLE test.t2; -sync_slave_with_master; - -# Lets compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching :-) Failed dump files -# will be located in $MYSQLTEST_VARDIR/tmp - -diff_files $MYSQLTEST_VARDIR/tmp/sp001_master.sql $MYSQLTEST_VARDIR/tmp/sp001_slave.sql; - -# If all is good, when can cleanup our dump files. -remove_file $MYSQLTEST_VARDIR/tmp/sp001_master.sql; -remove_file $MYSQLTEST_VARDIR/tmp/sp001_slave.sql; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp005.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp005.test index d8a5aacc5e6..a304dbe009b 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp005.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp005.test @@ -1,108 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/15/2005 # -# Updated: Aug/29/2005: Removed sleeps # -############################################################################# -# Test: Tests SPs with cursors, flow logic, and alter sp. In addition the # -# tests SPs with insert and update operations. # -############################################################################# +--source suite/rpl/t/rpl_row_sp005.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t3; ---enable_warnings -# End of cleanup - -# Begin test section 1 -CREATE TABLE IF NOT EXISTS test.t1(id INT, data CHAR(16),PRIMARY KEY(id)); -CREATE TABLE IF NOT EXISTS test.t2(id2 INT,PRIMARY KEY(id2)); -CREATE TABLE IF NOT EXISTS test.t3(id3 INT,PRIMARY KEY(id3), c CHAR(16)); - -delimiter |; -CREATE PROCEDURE test.p1() -BEGIN -DECLARE done INT DEFAULT 0; - DECLARE spa CHAR(16); - DECLARE spb,spc INT; - DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1 ORDER BY id; - DECLARE cur2 CURSOR FOR SELECT id2 FROM test.t2 ORDER BY id2; - DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; - - OPEN cur1; - OPEN cur2; - - REPEAT - FETCH cur1 INTO spb, spa; - FETCH cur2 INTO spc; - IF NOT done THEN - IF spb < spc THEN - INSERT INTO test.t3 VALUES (spb,spa); - ELSE - INSERT INTO test.t3 VALUES (spc,spa); - END IF; - END IF; - UNTIL done END REPEAT; - - CLOSE cur1; - CLOSE cur2; -END| -CREATE PROCEDURE test.p2() -BEGIN - INSERT INTO test.t1 VALUES (4,'MySQL'),(20,'ROCKS'),(11,'Texas'),(10,'kyle'); - INSERT INTO test.t2 VALUES (4),(2),(1),(3); - UPDATE test.t1 SET id=id+4 WHERE id=4; -END| -delimiter ;| - -let $message=< ---- Master selects-- >; ---source include/show_msg.inc -CALL test.p2(); -SELECT * FROM test.t1 ORDER BY id; -SELECT * FROM test.t2 ORDER BY id2; - -let $message=< ---- Slave selects-- >; ---source include/show_msg.inc -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t1 ORDER BY id; -SELECT * FROM test.t2 ORDER BY id2; - -let $message=< ---- Master selects-- >; ---source include/show_msg.inc -connection master; -CALL test.p1(); -sleep 6; -SELECT * FROM test.t3 ORDER BY id3; - -let $message=< ---- Slave selects-- >; ---source include/show_msg.inc -connection slave; -SELECT * FROM test.t3 ORDER BY id3; - -connection master; - -ALTER PROCEDURE test.p1 MODIFIES SQL DATA; -#show binlog events; - -# Cleanup - -connection master; -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp008.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp008.test index b1295820c99..3cc0ed26ea2 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp008.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp008.test @@ -1,57 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/15/2005 # -# Update: 08/29/2005 Remove sleep # -############################################################################# -# TEST: Use SQL_CALC_FOUND_ROWS and insert results into a table inside a sp # -############################################################################# +--source suite/rpl/t/rpl_row_sp008.test - -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP TABLE IF EXISTS test.t2; ---enable_warnings -# End of cleanup - - - -# Begin test section 1 -CREATE TABLE test.t1 (a INT,PRIMARY KEY(a)); -CREATE TABLE test.t2 (a INT,PRIMARY KEY(a)); -INSERT INTO test.t1 VALUES(1),(2); - -delimiter |; -CREATE PROCEDURE test.p1() -BEGIN - SELECT SQL_CALC_FOUND_ROWS * FROM test.t1 LIMIT 1; - INSERT INTO test.t2 VALUES(FOUND_ROWS()); -END| -delimiter ;| - -let $message=< ---- Master selects-- >; ---source include/show_msg.inc -CALL test.p1(); -SELECT * FROM test.t2; - -let $message=< ---- Slave selects-- >; ---source include/show_msg.inc -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t2; - -# Cleanup - -connection master; -DROP PROCEDURE IF EXISTS test.p1; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp009.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp009.test index 505ed582ba9..56b42a0953d 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp009.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp009.test @@ -1,102 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -# Updated: 08/29/2005 removed sleeps and added master pos save and snyc # -############################################################################# -#TEST: Taken and modfied from http://bugs.mysql.com/bug.php?id=12168 # -############################################################################# +--source suite/rpl/t/rpl_row_sp009.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; - - -# Begin test section 1 -CREATE TABLE test.t1 (a INT, PRIMARY KEY(a)); -INSERT INTO test.t1 VALUES (1),(2),(3),(4); -CREATE TABLE test.t2 (a INT, PRIMARY KEY(a)); - -delimiter |; -CREATE PROCEDURE test.p1 (arg1 CHAR(1)) -BEGIN - DECLARE b, c INT; - IF arg1 = 'a' THEN - BEGIN - DECLARE cur1 CURSOR FOR SELECT A FROM test.t1 WHERE a % 2; - DECLARE continue handler for not found set b = 1; - SET b = 0; - OPEN cur1; - c1_repeat: REPEAT - FETCH cur1 INTO c; - IF (b = 1) THEN - LEAVE c1_repeat; - END IF; - - INSERT INTO test.t2 VALUES (c); - UNTIL b = 1 - END REPEAT; - CLOSE cur1; - END; - END IF; - IF arg1 = 'b' THEN - BEGIN - DECLARE cur2 CURSOR FOR SELECT a FROM test.t1 WHERE NOT a % 2; - DECLARE continue handler for not found set b = 1; - SET b = 0; - OPEN cur2; - c2_repeat: REPEAT - FETCH cur2 INTO c; - IF (b = 1) THEN - LEAVE c2_repeat; - END IF; - - INSERT INTO test.t2 VALUES (c); - UNTIL b = 1 - END REPEAT; - CLOSE cur2; - END; - END IF; -END| -delimiter ;| - -CALL test.p1('a'); -SELECT * FROM test.t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t2 ORDER BY a; -connection master; -truncate test.t2; - -# this next call fails, but should not -call test.p1('b'); -select * from test.t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t2 ORDER BY a; - -connection master; -truncate test.t2; -SELECT * FROM test.t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t2 ORDER BY a; - -# Cleanup -connection master; -DROP PROCEDURE test.p1; -DROP TABLE test.t1; -DROP TABLE test.t2; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp010.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp010.test index 28b82217517..a52fc311315 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp010.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp010.test @@ -1,80 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -# Update: 08/29/2005 remove sleep added master pos save and sync # -############################################################################# -#TEST: Taken and modfied from http://bugs.mysql.com/bug.php?id=11126 # -############################################################################# +--source suite/rpl/t/rpl_row_sp010.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; -DROP PROCEDURE IF EXISTS test.p4; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; - - -# Begin test section 1 -delimiter |; -CREATE PROCEDURE test.p1() -BEGIN - INSERT INTO test.t1 VALUES(2); -END| -CREATE PROCEDURE test.p2() -BEGIN - DROP TEMPORARY TABLE IF EXISTS test.t1; - CREATE TEMPORARY TABLE test.t1 (a int, PRIMARY KEY(a)); - INSERT INTO test.t1 VALUES(1); - CALL test.p1(); -END| -delimiter ;| -CALL test.p2(); -SELECT * FROM test.t1 ORDER BY a; - -save_master_pos; -connection slave; -sync_with_master; -show tables; - -connection master; -delimiter |; -CREATE PROCEDURE test.p3() -BEGIN - INSERT INTO test.t2 VALUES(7); -END| -CREATE PROCEDURE test.p4() -BEGIN - DROP TABLE IF EXISTS test.t2; - CREATE TABLE test.t2 (a int, PRIMARY KEY(a)); - INSERT INTO test.t2 VALUES(6); - CALL test.p3(); -END| -delimiter ;| -CALL test.p4(); -SELECT * FROM test.t2 ORDER BY a; - -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t2 ORDER BY a; - -# Cleanup -connection master; -#show binlog events; -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; -DROP PROCEDURE IF EXISTS test.p4; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp011.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp011.test index 1c24904dd5a..c24282c8f16 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp011.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp011.test @@ -1,111 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/18/2005 # -# Updated: 08/29/2005 turned on diff and commented out debug SQL statements# -############################################################################# -#TEST: SP to test alter table and nested SP calls # -############################################################################# +--source suite/rpl/t/rpl_row_sp011.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; -DROP PROCEDURE IF EXISTS test.p4; -DROP PROCEDURE IF EXISTS test.p5; -DROP PROCEDURE IF EXISTS test.p6; -DROP PROCEDURE IF EXISTS test.p7; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; - - -# Begin test section 1 -CREATE TABLE test.t1 (a int, PRIMARY KEY(a)); -INSERT INTO test.t1 VALUES (1); - -delimiter |; -CREATE PROCEDURE test.p1() -BEGIN - ALTER TABLE test.t1 ADD COLUMN b CHAR(4) AFTER a; - UPDATE test.t1 SET b = 'rbr' WHERE a = 1; - CALL test.p2(); -END| -CREATE PROCEDURE test.p2() -BEGIN - ALTER TABLE test.t1 ADD COLUMN f FLOAT AFTER b; - UPDATE test.t1 SET f = RAND() WHERE a = 1; - CALL test.p3(); -END| -CREATE PROCEDURE test.p3() -BEGIN - ALTER TABLE test.t1 RENAME test.t2; - CALL test.p4(); -END| -CREATE PROCEDURE test.p4() -BEGIN - ALTER TABLE test.t2 ADD INDEX (f); - ALTER TABLE test.t2 CHANGE a a INT UNSIGNED NOT NULL AUTO_INCREMENT; - INSERT INTO test.t2 VALUES (NULL,'TEST',RAND()); - CALL test.p5(); -END| -CREATE PROCEDURE test.p5() -BEGIN - ALTER TABLE test.t2 ORDER BY f; - INSERT INTO test.t2 VALUES (NULL,'STM',RAND()); - CALL test.p6(); -END| -CREATE PROCEDURE test.p6() -BEGIN - ALTER TABLE test.t2 ADD COLUMN b2 CHAR(4) FIRST; - ALTER TABLE test.t2 ADD COLUMN to_drop BIT(8) AFTER b2; - INSERT INTO test.t2 VALUES ('new',1,NULL,'STM',RAND()); - CALL test.p7(); -END| -CREATE PROCEDURE test.p7() -BEGIN - ALTER TABLE test.t2 DROP COLUMN to_drop; - INSERT INTO test.t2 VALUES ('gone',NULL,'STM',RAND()); -END| -delimiter ;| -CALL test.p1(); - -#SELECT * FROM test.t2; -sync_slave_with_master; -#SELECT * FROM test.t2; - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp011_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp011_slave.sql - -# Cleanup -connection master; -#show binlog events; -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; -DROP PROCEDURE IF EXISTS test.p4; -DROP PROCEDURE IF EXISTS test.p5; -DROP PROCEDURE IF EXISTS test.p6; -DROP PROCEDURE IF EXISTS test.p7; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -sync_slave_with_master; - -# Lets compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching :-) Failed test -# Will leave dump files in $MYSQLTEST_VARDIR/tmp - -diff_files $MYSQLTEST_VARDIR/tmp/sp011_master.sql $MYSQLTEST_VARDIR/tmp/sp011_slave.sql; - -# If all is good, when can cleanup our dump files. -remove_file $MYSQLTEST_VARDIR/tmp/sp011_master.sql; -remove_file $MYSQLTEST_VARDIR/tmp/sp011_slave.sql; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_sp012.test b/mysql-test/suite/engines/funcs/t/rpl_row_sp012.test index f3963c7537a..f23e5c905a6 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_sp012.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_sp012.test @@ -1,75 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/22/2005 # -# Update: 08/29/2005 Added save pos and sync # -############################################################################# -#TEST: SP to test security and current_user and user # -############################################################################# - - -# Includes --- source include/have_binlog_format_row.inc --- source include/not_embedded.inc --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; - - -# Begin test section 1 -# Create user user1 with no particular access rights -grant usage on *.* to user1@localhost; -flush privileges; - -SELECT CURRENT_USER(); -SELECT USER(); -CREATE PROCEDURE test.p1 () SQL SECURITY INVOKER SELECT CURRENT_USER(), USER(); -CREATE PROCEDURE test.p2 () SQL SECURITY DEFINER CALL test.p1(); -CREATE PROCEDURE test.p3 () SQL SECURITY INVOKER CALL test.p1(); -GRANT EXECUTE ON PROCEDURE p1 TO user1@localhost; -GRANT EXECUTE ON PROCEDURE p2 TO user1@localhost; -GRANT EXECUTE ON PROCEDURE p3 TO user1@localhost; - -# Need to wait for the rights to be applied at the slave -sync_slave_with_master; - -let $message=<******** Master user1 p3 & p2 calls *******>; ---source include/show_msg.inc -connect (muser1,localhost,user1,,); -connection muser1; -SELECT CURRENT_USER(); -SELECT USER(); -CALL test.p3(); -CALL test.p2(); - -let $message=<******** Slave user1 p3 & p2 calls *******>; ---source include/show_msg.inc -connect (suser1,127.0.0.1,user1,,test,$SLAVE_MYPORT,); - -connection master; -save_master_pos; -connection suser1; -sync_with_master; - -SELECT CURRENT_USER(); -SELECT USER(); -CALL test.p3(); -CALL test.p2(); - -# Cleanup -connection master; -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p3; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP USER 'user1'@'localhost'; -sync_slave_with_master; - -# End of 5.0 test case +--source suite/rpl/t/rpl_row_sp012.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_stop_middle.test b/mysql-test/suite/engines/funcs/t/rpl_row_stop_middle.test index da363736100..397bc9a1d61 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_stop_middle.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_stop_middle.test @@ -9,8 +9,8 @@ connection master; create table t1 (a int not null auto_increment primary key, b int, key(b)); -sync_slave_with_master; -stop slave; +--sync_slave_with_master +--source include/stop_slave.inc connection master; INSERT INTO t1 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); INSERT INTO t1 (a) SELECT null FROM t1; @@ -28,13 +28,13 @@ INSERT INTO t1 (a) SELECT null FROM t1; INSERT INTO t1 (a) SELECT null FROM t1; connection slave; -start slave; +--source include/start_slave.inc # hope one second is not enough for slave to reach the last # Rows_log_event, so that test actually tests something. real_sleep 1; -stop slave; +--source include/stop_slave.inc # see if slave hangs on DROP TABLE diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_trig001.test b/mysql-test/suite/engines/funcs/t/rpl_row_trig001.test index 7b1fca2d6a1..77fd9fbf90e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_trig001.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_trig001.test @@ -1,100 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/09/2005 # -############################################################################# -# TEST: Use after insert and before inset triggers and stored procdures to # -# Update and insert data # -############################################################################# +--source suite/rpl/t/rpl_row_trig001.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - --- disable_query_log --- disable_result_log - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p2; -DROP PROCEDURE IF EXISTS test.p3; ---error 0,1360 -DROP TRIGGER test.t2_ai; ---error 0,1360 -DROP TRIGGER test.t3_bi_t2; ---error 0,1360 -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; - - -# test section 1, lets add a trigger to the mix. Taken from bug #12280 -let $message=<Begin test section 1 (Tiggers & SP)>; ---source include/show_msg.inc - -CREATE TABLE test.t1 (n MEDIUMINT NOT NULL, d DATETIME, PRIMARY KEY(n)); -CREATE TABLE test.t2 (n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); -CREATE TABLE test.t3 (n MEDIUMINT NOT NULL AUTO_INCREMENT, d DATETIME, PRIMARY KEY(n)); - -INSERT INTO test.t1 VALUES (1,NOW()); - -delimiter //; -CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t1 SET d=NOW() where n = 1;// -CREATE PROCEDURE test.p3() -BEGIN - INSERT INTO test.t3 (d) VALUES (NOW()); -END// -CREATE TRIGGER test.t3_bi_t2 BEFORE INSERT ON test.t2 FOR EACH ROW CALL test.p3()// -CREATE PROCEDURE test.p2() -BEGIN - INSERT INTO test.t2 (f,d) VALUES (RAND(),NOW()); -END// -delimiter ;// - --- disable_query_log --- disable_result_log -let $1=10; -while ($1) -{ - CALL test.p2(); - sleep 1; - dec $1; -} --- enable_result_log --- enable_query_log - -#show binlog events; -#select * from test.t2; -#select * from test.t3; -#connection slave; -#select * from test.t2; -#select * from test.t3; - -let $message=<End test section 2 (Tiggers & SP)>; ---source include/show_msg.inc - -# time to dump the databases and so we can see if they match - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trig001_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trig001_slave.sql - -# Cleanup -connection master; -DROP PROCEDURE test.p2; -DROP PROCEDURE test.p3; -DROP TRIGGER test.t2_ai; -DROP TRIGGER test.t3_bi_t2; -DROP TABLE test.t1; -DROP TABLE test.t2; -DROP TABLE test.t3; -sync_slave_with_master; - -# Lets compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching :-) Failed tests -# will leave dump files in $MYSQLTEST_VARDIR/tmp - -diff_files $MYSQLTEST_VARDIR/tmp/trig001_master.sql $MYSQLTEST_VARDIR/tmp/trig001_slave.sql; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_trig002.test b/mysql-test/suite/engines/funcs/t/rpl_row_trig002.test index 44b7d8b1dc2..8b7342c32a1 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_trig002.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_trig002.test @@ -1,82 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/14/2005 # -# Updated: 08/29/2005 added save master pos and sync with master # -############################################################################# -# TEST: Taken and modified from BUG#12048 After Insert updates replication # -############################################################################# +--source suite/rpl/t/rpl_row_trig002.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - -#-- disable_query_log -#-- disable_result_log - -# Begin clean up test section -connection master; ---disable_warnings ---error 0,1360 -DROP TRIGGER test.t2_ai; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; ---enable_warnings - -# test section 1, Taken from bug #12408 - -CREATE TABLE test.t2 (value CHAR(30),domain_id INT, mailaccount_id INT, program CHAR(30),keey CHAR(30),PRIMARY KEY(domain_id)); - -CREATE TABLE test.t3 (value CHAR(30),domain_id INT, mailaccount_id INT, program CHAR(30),keey CHAR(30),PRIMARY KEY(domain_id)); - -CREATE TABLE test.t1 (id INT,domain CHAR(30),PRIMARY KEY(id)); - -delimiter |; -CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW UPDATE test.t3 ms, test.t1 d SET ms.value='No' WHERE ms.domain_id = (SELECT max(id) FROM test.t1 WHERE domain='example.com') AND ms.mailaccount_id IS NULL AND ms.program='spamfilter' AND ms.keey='scan_incoming'| -delimiter ;| - -INSERT INTO test.t1 VALUES (1, 'example.com'),(2, 'mysql.com'),(3, 'earthmotherwear.com'), (4, 'yahoo.com'),(5, 'example.com'); - -SELECT * FROM test.t1 ORDER BY id; -#show binlog events; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t1 ORDER BY id; -connection master; - -INSERT INTO test.t3 VALUES ('Yes', 5, NULL, 'spamfilter','scan_incoming'); -INSERT INTO test.t3 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming'); -INSERT INTO test.t2 VALUES ('Yes', 1, NULL, 'spamfilter','scan_incoming'); - -select * from test.t2; ---sorted_result -select * from test.t3; -save_master_pos; -connection slave; -sync_with_master; -select * from test.t2; ---sorted_result -select * from test.t3; -connection master; - -DELETE FROM test.t1 WHERE id = 1; - -SELECT * FROM test.t1 ORDER BY id; -connection master; -SELECT * FROM test.t1 ORDER BY id; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t1 ORDER BY id; - -# Cleanup -connection master; -#show binlog events; -DROP TRIGGER test.t2_ai; -DROP TABLE test.t1; -DROP TABLE test.t2; -DROP TABLE test.t3; -sync_slave_with_master; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_trig003.test b/mysql-test/suite/engines/funcs/t/rpl_row_trig003.test index 4a1bbc5ca89..2bb581995d0 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_trig003.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_trig003.test @@ -1,152 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/16/2005 # -# Updated: 8/29/2005 Remove sleep calls add dump and diff # -############################################################################# -# TEST: This test includes all trigger types. BEFORE/AFTER INSERT, UPDATE & # -# DELETE. In addition, includes cursor, bit, varchar, flow control, # -# looping, ROUND(), NOW(), YEAR(), TIMESTAMP # -############################################################################# +--source suite/rpl/t/rpl_row_trig003.test -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - -#-- disable_query_log -#-- disable_result_log - -# Begin clean up test section -connection master; ---disable_warnings ---error 0,1360 -DROP TRIGGER test.t1_bi; ---error 0,1360 -DROP TRIGGER test.t2_ai; ---error 0,1360 -DROP TRIGGER test.t1_bu; ---error 0,1360 -DROP TRIGGER test.t2_au; ---error 0,1360 -DROP TRIGGER test.t1_bd; ---error 0,1360 -DROP TRIGGER test.t2_ad; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; ---enable_warnings - -# test section 1 - -CREATE TABLE test.t1 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id)); -CREATE TABLE test.t2 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id)); -CREATE TABLE test.t3 (id MEDIUMINT NOT NULL AUTO_INCREMENT, b1 BIT(8), vc VARCHAR(255), bc CHAR(255), d DECIMAL(10,4) DEFAULT 0, f FLOAT DEFAULT 0, total BIGINT UNSIGNED, y YEAR, t TIMESTAMP,PRIMARY KEY(id)); - -# Note Most of these cause the slave to core or do not produce desired results. Currently commenting out the ones not working until they are fixed. - -delimiter |; -CREATE TRIGGER test.t1_bi BEFORE INSERT ON test.t1 FOR EACH ROW UPDATE test.t3 SET b1=1 and y=YEAR(NOW())| -CREATE TRIGGER test.t2_ai AFTER INSERT ON test.t2 FOR EACH ROW BEGIN - INSERT INTO test.t3 VALUES(NULL,0,'MySQL Replication team rocks!', 'Dark beer in prague is #1',12345.34,12.51,0,1965,NOW()); - UPDATE test.t3 SET f = ROUND(f); -END| -CREATE TRIGGER test.t1_bu BEFORE UPDATE on test.t1 FOR EACH ROW BEGIN - UPDATE test.t3 SET y = '2000'; - INSERT INTO test.t3 VALUES(NULL,1,'Testing MySQL databases before update ', 'Insert should work',621.43, 0105.21,0,1974,NOW()); -END| -CREATE TRIGGER test.t2_au AFTER UPDATE on test.t2 FOR EACH ROW BEGIN - DECLARE done INT DEFAULT 0; - DECLARE a DECIMAL(10,4); - DECLARE b FLOAT; - DECLARE num MEDIUMINT; - DECLARE cur1 CURSOR FOR SELECT t2.id, t2.d, t2.f FROM test.t2; - DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; - - OPEN cur1; - - REPEAT - FETCH cur1 INTO num, a, b; - IF NOT done THEN - UPDATE test.t3 SET total =(a*b) WHERE ID = num; - END IF; - UNTIL done END REPEAT; - CLOSE cur1; -END| -CREATE TRIGGER test.t1_bd BEFORE DELETE on test.t1 FOR EACH ROW BEGIN - DECLARE done INT DEFAULT 0; - DECLARE a BIT(8); - DECLARE b VARCHAR(255); - DECLARE c CHAR(255); - DECLARE d DECIMAL(10,4); - DECLARE e FLOAT; - DECLARE f BIGINT UNSIGNED; - DECLARE g YEAR; - DECLARE h TIMESTAMP; - DECLARE cur1 CURSOR FOR SELECT t1.b1, t1.vc, t1.bc, t1.d, t1.f, t1.total, t1.y, t1.t FROM test.t1; - DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; - - OPEN cur1; - - REPEAT - FETCH cur1 INTO a, b, c, d, e, f, g, h; - IF NOT done THEN - INSERT INTO test.t3 VALUES(NULL, a, b, c, d, e, f, g, h); - END IF; - UNTIL done END REPEAT; - CLOSE cur1; -END| -CREATE TRIGGER test.t2_ad AFTER DELETE ON test.t2 FOR EACH ROW - DELETE FROM test.t1| -delimiter ;| - -INSERT INTO test.t1 VALUES(NULL,1,'Testing MySQL databases is a cool ', 'Must make it bug free for the customer',654321.4321,15.21,0,1965,NOW()); -INSERT INTO test.t2 VALUES(NULL,0,'Testing MySQL databases is a cool ', 'MySQL Customers ROCK!',654321.4321,1.24521,0,YEAR(NOW()),NOW()); - -UPDATE test.t1 SET b1 = 0 WHERE b1 = 1; - -INSERT INTO test.t2 VALUES(NULL,1,'This is an after update test.', 'If this works, total will not be zero on the master or slave',1.4321,5.221,0,YEAR(NOW()),NOW()); -UPDATE test.t2 SET b1 = 0 WHERE b1 = 1; - -INSERT INTO test.t1 VALUES(NULL,1,'add some more test data test.', 'and hope for the best', 3.321,5.221,0,YEAR(NOW()),NOW()); - -# To make sure BUG#14698 is gone, we sleep 2 seconds before calling trigger -# (with the bug in, that caused differences in TIMESTAMP columns). -# We just need to let the machine's clock advance, it's not -# to do synchronization, so real_sleep is good. -real_sleep 2; - -DELETE FROM test.t1 WHERE id = 1; - -DELETE FROM test.t2 WHERE id = 1; - -save_master_pos; -connection slave; -sync_with_master; -connection master; - -# time to dump the databases and so we can see if they match - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trg003_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/trg003_slave.sql - -# cleanup ---disable_warnings ---error 0,1360 -DROP TRIGGER test.t1_bi; ---error 0,1360 -DROP TRIGGER test.t2_ai; ---error 0,1360 -DROP TRIGGER test.t1_bu; ---error 0,1360 -DROP TRIGGER test.t2_au; ---error 0,1360 -DROP TRIGGER test.t1_bd; ---error 0,1360 -DROP TRIGGER test.t2_ad; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; ---enable_warnings - -diff_files $MYSQLTEST_VARDIR/tmp/trg003_master.sql $MYSQLTEST_VARDIR/tmp/trg003_slave.sql; - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_until.test b/mysql-test/suite/engines/funcs/t/rpl_row_until.test index 7f8b0000042..d919bf7773b 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_until.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_until.test @@ -1,126 +1,2 @@ --- source include/have_binlog_format_row.inc --- source include/master-slave.inc +--source suite/rpl/t/rpl_row_until.test -# Note: The test is dependent on binlog positions - -# Create some events on master -connection master; -CREATE TABLE t1(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY); -INSERT INTO t1 VALUES (1),(2),(3),(4); -DROP TABLE t1; -# Save master log position for query DROP TABLE t1 -save_master_pos; -let $master_pos_drop_t1= query_get_value(SHOW BINLOG EVENTS, Pos, 11); -let $master_log_file= query_get_value(SHOW BINLOG EVENTS, Log_name, 11); - -CREATE TABLE t2(n INT NOT NULL AUTO_INCREMENT PRIMARY KEY); -# Save master log position for query CREATE TABLE t2 -save_master_pos; -let $master_pos_create_t2= query_get_value(SHOW BINLOG EVENTS, Pos, 12); - -INSERT INTO t2 VALUES (1),(2); -save_master_pos; -# Save master log position for query INSERT INTO t2 VALUES (1),(2); -let $master_pos_insert1_t2= query_get_value(SHOW BINLOG EVENTS, End_log_pos, 19); -sync_slave_with_master; - -# Save relay log position for query INSERT INTO t2 VALUES (1),(2); -let $relay_pos_insert1_t2= query_get_value(show slave status, Relay_Log_Pos, 1); - -connection master; -INSERT INTO t2 VALUES (3),(4); -DROP TABLE t2; -# Save master log position for query DROP TABLE t2; -let $master_pos_drop_t2= query_get_value(SHOW BINLOG EVENTS, End_log_pos, 26); -sync_slave_with_master; - ---source include/stop_slave.inc -# Reset slave. -RESET SLAVE; ---disable_query_log -eval CHANGE MASTER TO MASTER_USER='root', MASTER_CONNECT_RETRY=1, MASTER_HOST='127.0.0.1', MASTER_PORT=$MASTER_MYPORT; ---enable_query_log - -# Try to replicate all queries until drop of t1 -connection slave; -echo START SLAVE UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=master_pos_drop_t1; ---disable_query_log -eval START SLAVE UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=$master_pos_drop_t1; ---enable_query_log ---source include/wait_for_slave_sql_to_stop.inc - -# Here table should be still not deleted -SELECT * FROM t1; ---let $slave_param= Exec_Master_Log_Pos ---let $slave_param_value= $master_pos_drop_t1 ---source include/check_slave_param.inc - -# This should fail right after start ---replace_result 291 MASTER_LOG_POS -START SLAVE UNTIL MASTER_LOG_FILE='master-no-such-bin.000001', MASTER_LOG_POS=291; ---source include/wait_for_slave_sql_to_stop.inc -# again this table should be still not deleted -SELECT * FROM t1; - ---let $slave_param= Exec_Master_Log_Pos ---let $slave_param_value= $master_pos_drop_t1 ---source include/check_slave_param.inc - -# Try replicate all up to and not including the second insert to t2; -echo START SLAVE UNTIL RELAY_LOG_FILE='slave-relay-bin.000002', RELAY_LOG_POS=relay_pos_insert1_t2; ---disable_query_log -eval START SLAVE UNTIL RELAY_LOG_FILE='slave-relay-bin.000002', RELAY_LOG_POS=$relay_pos_insert1_t2; ---enable_query_log ---source include/wait_for_slave_sql_to_stop.inc -SELECT * FROM t2; - ---let $slave_param= Exec_Master_Log_Pos ---let $slave_param_value= $master_pos_insert1_t2 ---source include/check_slave_param.inc - -# clean up -START SLAVE; ---source include/wait_for_slave_to_start.inc -connection master; -sync_slave_with_master; ---source include/stop_slave.inc - -# This should stop immediately as we are already there -echo START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=master_pos_create_t2; ---disable_query_log -eval START SLAVE SQL_THREAD UNTIL MASTER_LOG_FILE='$master_log_file', MASTER_LOG_POS=$master_pos_create_t2; ---enable_query_log -let $slave_param= Until_Log_Pos; -let $slave_param_value= $master_pos_create_t2; ---source include/wait_for_slave_param.inc ---source include/wait_for_slave_sql_to_stop.inc -# here the sql slave thread should be stopped ---let $slave_param= Exec_Master_Log_Pos ---let $slave_param_value= $master_pos_drop_t2 ---source include/check_slave_param.inc - -#testing various error conditions ---replace_result 561 MASTER_LOG_POS ---error 1277 -START SLAVE UNTIL MASTER_LOG_FILE='master-bin', MASTER_LOG_POS=561; ---replace_result 561 MASTER_LOG_POS 12 RELAY_LOG_POS ---error 1277 -START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=561, RELAY_LOG_POS=12; ---error 1277 -START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001'; ---error 1277 -START SLAVE UNTIL RELAY_LOG_FILE='slave-relay-bin.000009'; ---replace_result 561 MASTER_LOG_POS ---error 1277 -START SLAVE UNTIL RELAY_LOG_FILE='slave-relay-bin.000002', MASTER_LOG_POS=561; -# Warning should be given for second command -START SLAVE; ---replace_result 740 MASTER_LOG_POS -START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=740; - ---source include/stop_slave.inc -# Clear slave IO error. -RESET SLAVE; - ---let $rpl_only_running_threads= 1 ---source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_row_view01.test b/mysql-test/suite/engines/funcs/t/rpl_row_view01.test index 1ccfcb4eb27..6d58666f883 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_row_view01.test +++ b/mysql-test/suite/engines/funcs/t/rpl_row_view01.test @@ -1,82 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/19/2005 # -# Updated: 08/29/2005 Remove sleeps # -############################################################################# -#TEST: row based replication of views # -############################################################################# -# Includes --- source include/have_binlog_format_row.inc --- source include/master-slave.inc - -# Begin clean up test section -connection master; ---disable_warnings -create database if not exists mysqltest1; -DROP VIEW IF EXISTS mysqltest1.v1; -DROP VIEW IF EXISTS mysqltest1.v2; -DROP VIEW IF EXISTS mysqltest1.v3; -DROP VIEW IF EXISTS mysqltest1.v4; -DROP TABLE IF EXISTS mysqltest1.t3; -DROP TABLE IF EXISTS mysqltest1.t1; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP TABLE IF EXISTS mysqltest1.t4; - -# Begin test section 1 -CREATE TABLE mysqltest1.t1 (a INT, c CHAR(6),PRIMARY KEY(a)); -CREATE TABLE mysqltest1.t2 (a INT, c CHAR(6),PRIMARY KEY(a)); -CREATE TABLE mysqltest1.t3 (a INT, c CHAR(6), c2 CHAR(6), PRIMARY KEY(a)); -CREATE TABLE mysqltest1.t4 (a INT, qty INT, price INT,PRIMARY KEY(a)); -CREATE TABLE mysqltest1.t5 (qty INT, price INT, total INT, PRIMARY KEY(qty)); -INSERT INTO mysqltest1.t1 VALUES (1,'Thank'),(2,'it'),(3,'Friday'); -INSERT INTO mysqltest1.t2 VALUES (1,'GOD'),(2,'is'),(3,'TGIF'); -INSERT INTO mysqltest1.t4 VALUES(1, 3, 50),(2, 18, 3),(4, 4, 4); - - -CREATE VIEW mysqltest1.v2 AS SELECT qty, price, qty*price AS value FROM mysqltest1.t4 ORDER BY qty; -CREATE VIEW mysqltest1.v1 AS SELECT t1.a, t1.c, t2.c as c2 FROM mysqltest1.t1 as t1, mysqltest1.t2 AS t2 WHERE mysqltest1.t1.a = mysqltest1.t2.a ORDER BY a; -CREATE VIEW mysqltest1.v3 AS SELECT * FROM mysqltest1.t1; -CREATE VIEW mysqltest1.v4 AS SELECT * FROM mysqltest1.v3 WHERE a > 1 WITH LOCAL CHECK OPTION; - - -SELECT * FROM mysqltest1.v2; -SELECT * FROM mysqltest1.v1; -sync_slave_with_master; -SELECT * FROM mysqltest1.v2; -SELECT * FROM mysqltest1.v1; -connection master; - -INSERT INTO mysqltest1.t5 SELECT * FROM mysqltest1.v2; -INSERT INTO mysqltest1.t3 SELECT * FROM mysqltest1.v1; - -SELECT * FROM mysqltest1.t5 ORDER BY qty; -SELECT * FROM mysqltest1.t3 ORDER BY a; -sync_slave_with_master; -SELECT * FROM mysqltest1.t5 ORDER BY qty; -SELECT * FROM mysqltest1.t3 ORDER BY a; -connection master; - -INSERT INTO mysqltest1.v4 VALUES (4,'TEST'); - -SELECT * FROM mysqltest1.t1 ORDER BY a; -SELECT * FROM mysqltest1.v4 ORDER BY a; -sync_slave_with_master; -SELECT * FROM mysqltest1.t1 ORDER BY a; -SELECT * FROM mysqltest1.v4 ORDER BY a; - -connection master; - -# lets cleanup -DROP VIEW IF EXISTS mysqltest1.v1; -DROP VIEW IF EXISTS mysqltest1.v2; -DROP VIEW IF EXISTS mysqltest1.v3; -DROP VIEW IF EXISTS mysqltest1.v4; -DROP TABLE IF EXISTS mysqltest1.t3; -DROP TABLE IF EXISTS mysqltest1.t1; -DROP TABLE IF EXISTS mysqltest1.t2; -DROP TABLE IF EXISTS mysqltest1.t4; -DROP DATABASE mysqltest1; -sync_slave_with_master; - -# End of 5.1 test case +--source suite/rpl/t/rpl_row_view01.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_server_id1.test b/mysql-test/suite/engines/funcs/t/rpl_server_id1.test index 71310750b60..a1425531b2c 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_server_id1.test +++ b/mysql-test/suite/engines/funcs/t/rpl_server_id1.test @@ -4,12 +4,12 @@ # ignored, which has caught our customers), unless # --replicate-same-server-id. -source include/master-slave.inc; +--source include/master-slave.inc connection slave; create table t1 (n int); reset master; # replicate ourselves -stop slave; +--source include/stop_slave.inc --replace_result $SLAVE_MYPORT SLAVE_PORT eval change master to master_port=$SLAVE_MYPORT; --replace_result $SLAVE_MYPORT SLAVE_PORT @@ -17,10 +17,16 @@ eval change master to master_port=$SLAVE_MYPORT; show slave status; start slave; insert into t1 values (1); -# can't MASTER_POS_WAIT(), it does not work in this weird setup -# (when slave is its own master without --replicate-same-server-id) -sleep 2; # enough time for the event to be replicated (it should not) -show status like "slave_running"; -drop table t1; -# End of 4.1 tests +--let $slave_param=Last_IO_Errno +--let $slave_param_value=1593 +--source include/wait_for_slave_param.inc + +--let $slave_field_result_replace= / at [0-9]*/ at XXX/ +--let $status_items= Last_IO_Errno, Last_IO_Error +--source include/show_slave_status.inc + +--source include/stop_slave.inc +reset slave; +reset master; +drop table t1; diff --git a/mysql-test/suite/engines/funcs/t/rpl_server_id2.test b/mysql-test/suite/engines/funcs/t/rpl_server_id2.test index 0f2eb560d18..8d48746ba5e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_server_id2.test +++ b/mysql-test/suite/engines/funcs/t/rpl_server_id2.test @@ -1,26 +1,2 @@ -# This test checks that a slave DOES execute queries originating -# from itself, if running with --replicate-same-server-id. +--source suite/rpl/t/rpl_server_id2.test -source include/master-slave.inc; -connection slave; -create table t1 (n int); -reset master; -# replicate ourselves -stop slave; ---replace_result $SLAVE_MYPORT SLAVE_PORT -eval change master to master_port=$SLAVE_MYPORT; ---replace_result $SLAVE_MYPORT SLAVE_PORT ---replace_column 18 # -show slave status; -start slave; -insert into t1 values (1); -save_master_pos; -sync_with_master; -select * from t1; # check that indeed 2 were inserted -# We stop the slave before cleaning up otherwise we'll get -# 'drop table t1' executed twice, so an error in the slave.err -# (not critical). -stop slave; -drop table t1; - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_session_var.test b/mysql-test/suite/engines/funcs/t/rpl_session_var.test index a6f4b496a23..f1686e107b6 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_session_var.test +++ b/mysql-test/suite/engines/funcs/t/rpl_session_var.test @@ -1,42 +1,2 @@ -# Replication of session variables. -# FOREIGN_KEY_CHECKS is tested in rpl_insert_id.test +--source suite/rpl/t/rpl_session_var.test -source include/master-slave.inc; -drop table if exists t1; -create table t1(a varchar(100),b int); -set @@session.sql_mode=pipes_as_concat; -insert into t1 values('My'||'SQL', 1); -set @@session.sql_mode=default; -insert into t1 values('1'||'2', 2); -select * from t1 where b<3 order by a; -save_master_pos; -connection slave; -sync_with_master; -select * from t1 where b<3 order by a; -connection master; -# if the slave does the next sync_with_master fine, then it means it accepts the -# two lines of ANSI syntax below, which is what we want to check. -set @@session.sql_mode=ignore_space; -insert into t1 values(password ('MySQL'), 3); -set @@session.sql_mode=ansi_quotes; -create table "t2" ("a" int); -drop table t1, t2; -set @@session.sql_mode=default; -create table t1(a int auto_increment primary key); -create table t2(b int, a int); -set @@session.sql_auto_is_null=1; -insert into t1 values(null); -insert into t2 select 1,a from t1 where a is null; -set @@session.sql_auto_is_null=0; -insert into t1 values(null); -insert into t2 select 2,a from t1 where a is null; -select * from t2 order by b; -save_master_pos; -connection slave; -sync_with_master; -select * from t2 order by b; -connection master; -drop table t1,t2; -save_master_pos; -connection slave; -sync_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_sf.test b/mysql-test/suite/engines/funcs/t/rpl_sf.test index 6a741d80172..7ce6dc002ae 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_sf.test +++ b/mysql-test/suite/engines/funcs/t/rpl_sf.test @@ -1,5 +1,5 @@ # Bug#16456 RBR: rpl_sp.test expects query to fail, but passes in RBR -source include/master-slave.inc; +--source include/master-slave.inc # save status let $oblf=`select @@SESSION.BINLOG_FORMAT`; @@ -66,3 +66,4 @@ drop function fn16456; eval set binlog_format=$oblf; eval set global log_bin_trust_function_creators=$otfc; --enable_query_log +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_skip_error.test b/mysql-test/suite/engines/funcs/t/rpl_skip_error.test index ff81e2f010e..083dfaa2075 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_skip_error.test +++ b/mysql-test/suite/engines/funcs/t/rpl_skip_error.test @@ -2,12 +2,10 @@ # 2006-02-07 By JBM: Added order by ######################################### # Note that errors are ignored by opt file. -source include/master-slave.inc; +--source include/master-slave.inc create table t1 (n int not null primary key); -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master insert into t1 values (1); connection master; # Here we expect (ignored) error, since 1 is already in slave table @@ -16,14 +14,13 @@ insert into t1 values (1); # These should work fine insert into t1 values (2),(3); -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master select * from t1 ORDER BY n; # Cleanup connection master; drop table t1; -sync_slave_with_master; +--sync_slave_with_master # End of 4.1 tests +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_slave_status.test b/mysql-test/suite/engines/funcs/t/rpl_slave_status.test index b3d6e49e215..677d95117fc 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_slave_status.test +++ b/mysql-test/suite/engines/funcs/t/rpl_slave_status.test @@ -1,58 +1,2 @@ ---source include/master-slave.inc +--source suite/rpl/t/rpl_slave_status.test -############################################################################ -# Test case for BUG#10780 -# -# REQUIREMENT -# A slave without replication privileges should have Slave_IO_Running = No - -# 1. Create new replication user -connection master; -grant replication slave on *.* to rpl@127.0.0.1 identified by 'rpl'; - -connection slave; -stop slave; -change master to master_user='rpl',master_password='rpl'; -start slave; - -# 2. Do replication as new user -connection master; ---disable_warnings -drop table if exists t1; ---enable_warnings -create table t1 (n int); -insert into t1 values (1); -save_master_pos; -connection slave; -sync_with_master; -select * from t1; - -# 3. Delete new replication user -connection master; -drop user rpl@127.0.0.1; -sleep 1; -flush privileges; -connection slave; - -# 4. Restart slave without privileges -# (slave.err will contain access denied error for this START SLAVE command) -stop slave; -start slave; - -# 5. Make sure Slave_IO_Running = No ---replace_result $MASTER_MYPORT MASTER_MYPORT -# Column 1 is replaced, since the output can be either -# "Connecting to master" or "Waiting for master update" ---replace_column 1 # 7 # 8 # 9 # 22 # 23 # 35 # 36 # ---vertical_results -show slave status; - -# Cleanup (Note that slave IO thread is not running) -connection slave; -drop table t1; -connection master; -drop table t1; - -# end of test case for BUG#10780 - -# end of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_sp.test b/mysql-test/suite/engines/funcs/t/rpl_sp.test index ad2cc29375b..773062275c9 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_sp.test +++ b/mysql-test/suite/engines/funcs/t/rpl_sp.test @@ -1,496 +1,2 @@ -# row-based and statement have expected binlog difference in result files - -# Test of replication of stored procedures (WL#2146 for MySQL 5.0) -# Modified by WL#2971. - -source include/have_binlog_format_mixed.inc; -source include/master-slave.inc; - -# we need a db != test, where we don't have automatic grants ---disable_warnings -drop database if exists mysqltest1; ---enable_warnings -create database mysqltest1; -use mysqltest1; -create table t1 (a varchar(100)); -sync_slave_with_master; -use mysqltest1; - -# ********************** PART 1 : STORED PROCEDURES *************** - -# Does the same proc as on master get inserted into mysql.proc ? -# (same definer, same properties...) - -connection master; - -delimiter |; - -# Stored procedures don't have the limitations that functions have -# regarding binlogging: it's ok to create a procedure as not -# deterministic and updating data, while it's not ok to create such a -# function. We test this. - -create procedure foo() -begin - declare b int; - set b = 8; - insert into t1 values (b); - insert into t1 values (unix_timestamp()); -end| -delimiter ;| - -# we replace columns having times -# (even with fixed timestamp displayed time may changed based on TZ) ---replace_result localhost.localdomain localhost 127.0.0.1 localhost ---replace_column 13 # 14 # -select * from mysql.proc where name='foo' and db='mysqltest1'; -sync_slave_with_master; -# You will notice in the result that the definer does not match what -# it is on master, it is a known bug on which Alik is working ---replace_result localhost.localdomain localhost 127.0.0.1 localhost ---replace_column 13 # 14 # -select * from mysql.proc where name='foo' and db='mysqltest1'; - -connection master; -# see if timestamp used in SP on slave is same as on master -set timestamp=1000000000; -call foo(); -select * from t1; -sync_slave_with_master; -select * from t1; - -# Now a SP which is not updating tables - -connection master; -delete from t1; -create procedure foo2() - select * from mysqltest1.t1; -call foo2(); - -# check that this is allowed (it's not for functions): -alter procedure foo2 contains sql; - -# SP with definer's right - -drop table t1; -create table t1 (a int); -create table t2 like t1; - -create procedure foo3() - deterministic - insert into t1 values (15); - -# let's create a non-privileged user -grant CREATE ROUTINE, EXECUTE on mysqltest1.* to "zedjzlcsjhd"@127.0.0.1; -grant SELECT on mysqltest1.t1 to "zedjzlcsjhd"@127.0.0.1; -grant SELECT, INSERT on mysqltest1.t2 to "zedjzlcsjhd"@127.0.0.1; - -# ToDo: BUG#14931: There is a race between the last grant binlogging, and -# the binlogging in the new connection made below, causing sporadic test -# failures due to switched statement order in binlog. To fix this we do -# SELECT 1 in the first connection before starting the second, ensuring -# that binlogging is done in the expected order. -# Please remove this SELECT 1 when BUG#14931 is fixed. -SELECT 1; - -connect (con1,127.0.0.1,zedjzlcsjhd,,mysqltest1,$MASTER_MYPORT,); -connection con1; - -# this routine will fail in the second INSERT because of privileges -delimiter |; -create procedure foo4() - deterministic - begin - insert into t2 values(3); - insert into t1 values (5); - end| - -delimiter ;| - -# I add ,0 so that it does not print the error in the test output, -# because this error is hostname-dependent ---error 1142,0 -call foo4(); # invoker has no INSERT grant on table t1 => failure - -connection master; -call foo3(); # success (definer == root) -show warnings; - ---error 1142,0 -call foo4(); # definer's rights => failure - -# we test replication of ALTER PROCEDURE -alter procedure foo4 sql security invoker; -call foo4(); # invoker's rights => success -show warnings; - -# Note that half-failed procedure calls are ok with binlogging; -# if we compare t2 on master and slave we see they are identical: - -select * from t1; -select * from t2; -sync_slave_with_master; -select * from t1; -select * from t2; - -# Test of DROP PROCEDURE - ---replace_result localhost.localdomain localhost 127.0.0.1 localhost ---replace_column 13 # 14 # -select * from mysql.proc where name="foo4" and db='mysqltest1'; -connection master; -drop procedure foo4; -select * from mysql.proc where name="foo4" and db='mysqltest1'; -sync_slave_with_master; -select * from mysql.proc where name="foo4" and db='mysqltest1'; - -# ********************** PART 2 : FUNCTIONS *************** - -connection master; -drop procedure foo; -drop procedure foo2; -drop procedure foo3; - -delimiter |; -# check that needs "deterministic" ---error 1418 -create function fn1(x int) - returns int -begin - insert into t1 values (x); - return x+2; -end| -create function fn1(x int) - returns int - deterministic -begin - insert into t1 values (x); - return x+2; -end| - -delimiter ;| -delete t1,t2 from t1,t2; -select fn1(20); -insert into t2 values(fn1(21)); ---sorted_result -select * from t1; -select * from t2; -sync_slave_with_master; ---sorted_result -select * from t1; -select * from t2; - -connection master; -delimiter |; - -drop function fn1; - -create function fn1() - returns int - no sql -begin - return unix_timestamp(); -end| - -delimiter ;| -# check that needs "deterministic" ---error 1418 -alter function fn1 contains sql; - -delete from t1; -set timestamp=1000000000; -insert into t1 values(fn1()); - -connection con1; - -delimiter |; ---error 1419 # only full-global-privs user can create a function -create function fn2() - returns int - no sql -begin - return unix_timestamp(); -end| -delimiter ;| -connection master; -set global log_bin_trust_function_creators=0; -set global log_bin_trust_function_creators=1; -# slave needs it too otherwise will not execute what master allowed: -connection slave; -set global log_bin_trust_function_creators=1; - -connection con1; - -delimiter |; -create function fn2() - returns int - no sql -begin - return unix_timestamp(); -end| -delimiter ;| - -connection master; - -# Now a function which is supposed to not update tables -# as it's "reads sql data", so should not give error even if -# non-deterministic. - -delimiter |; -create function fn3() - returns int - not deterministic - reads sql data -begin - return 0; -end| -delimiter ;| - -select fn3(); ---replace_result localhost.localdomain localhost 127.0.0.1 localhost ---replace_column 13 # 14 # -select * from mysql.proc where db='mysqltest1'; -select * from t1; - -sync_slave_with_master; -use mysqltest1; -select * from t1; ---replace_result localhost.localdomain localhost 127.0.0.1 localhost ---replace_column 13 # 14 # -select * from mysql.proc where db='mysqltest1'; - -# ********************** PART 3 : TRIGGERS *************** - -connection con1; -# now fails due to missing trigger grant (err 1142 i/o 1227) due to new -# check in sql_trigger.cc (v1.44) by anozdrin on 2006/02/01 --azundris ---error ER_TABLEACCESS_DENIED_ERROR -create trigger trg before insert on t1 for each row set new.a= 10; - -connection master; -delete from t1; -# TODO: when triggers can contain an update, test that this update -# does not go into binlog. -# I'm not setting user vars in the trigger, because replication of user vars -# would take care of propagating the user var's value to slave, so even if -# the trigger was not executed on slave it would not be discovered. -create trigger trg before insert on t1 for each row set new.a= 10; -insert into t1 values (1); -select * from t1; -sync_slave_with_master; -select * from t1; - -connection master; -delete from t1; -drop trigger trg; -insert into t1 values (1); -select * from t1; ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -#show binlog events in 'master-bin.000001' from 106; -sync_slave_with_master; -select * from t1; - - -# -# Test for bug #13969 "Routines which are replicated from master can't be -# executed on slave". -# -connection master; -create procedure foo() - not deterministic - reads sql data - select * from t1; -sync_slave_with_master; -# This should not fail -call foo(); -connection master; -drop procedure foo; -sync_slave_with_master; - - -# Clean up -connection master; -drop function fn1; -drop database mysqltest1; -drop user "zedjzlcsjhd"@127.0.0.1; -use test; -sync_slave_with_master; -use test; - -# -# Bug#14077 "Failure to replicate a stored function with a cursor": -# verify that stored routines with cursors work on slave. -# -connection master; ---disable_warnings -drop function if exists f1; ---enable_warnings -delimiter |; -create function f1() returns int reads sql data -begin - declare var integer; - declare c cursor for select a from v1; - open c; - fetch c into var; - close c; - return var; -end| -delimiter ;| -create view v1 as select 1 as a; -create table t1 (a int); -insert into t1 (a) values (f1()); -select * from t1; -drop view v1; -drop function f1; -sync_slave_with_master; -connection slave; -select * from t1; - -# -# Bug#16621 "INSERTs in Stored Procedures causes data corruption in the Binary -# Log for 5.0.18" -# - -# Prepare environment. - -connection master; - ---disable_warnings -DROP PROCEDURE IF EXISTS p1; -DROP TABLE IF EXISTS t1; ---enable_warnings - -# Test case. - -CREATE TABLE t1(col VARCHAR(10)); - -CREATE PROCEDURE p1(arg VARCHAR(10)) - INSERT INTO t1 VALUES(arg); - -CALL p1('test'); - -SELECT * FROM t1; - -sync_slave_with_master; -SELECT * FROM t1; - -# Cleanup -connection master; -DROP PROCEDURE p1; - - -# -# 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 PROCEDURE IF EXISTS p1; -DROP FUNCTION IF EXISTS f1; ---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 procedure... - -/*!50003 CREATE PROCEDURE p1() SET @a = 1 */; - -/*!50003 CREATE FUNCTION f1() RETURNS INT RETURN 0 */; - ---echo ---echo ---> Checking on master... - -SHOW CREATE PROCEDURE p1; -SHOW CREATE FUNCTION f1; - ---echo ---echo ---> Synchronizing slave with master... - ---save_master_pos ---connection slave ---sync_with_master - ---echo ---> connection: master - ---echo ---echo ---> Checking on slave... - -SHOW CREATE PROCEDURE p1; -SHOW CREATE FUNCTION f1; - -# Cleanup. - ---echo ---echo ---> connection: master ---connection master - ---echo ---echo ---> Cleaning up... - -DROP PROCEDURE p1; -DROP FUNCTION f1; - ---save_master_pos ---connection slave ---sync_with_master ---connection master - - -# cleanup -connection master; -drop table t1; -sync_slave_with_master; - -# Restore log_bin_trust_function_creators to original value -set global log_bin_trust_function_creators=0; -connection master; -set global log_bin_trust_function_creators=0; ---echo End of 5.0 tests - -# -# Bug22043: MySQL don't add "USE <DATABASE>" before "DROP PROCEDURE IF EXISTS" -# -connection master; -reset master; ---disable_warnings -drop database if exists mysqltest; -drop database if exists mysqltest2; ---enable_warnings -create database mysqltest; -create database mysqltest2; -use mysqltest2; -create table t ( t integer ); -create procedure mysqltest.test() begin end; -insert into t values ( 1 ); -#show binlog events in 'master-bin.000001' from 106; ---error ER_BAD_DB_ERROR -create procedure `\\`.test() begin end; -# Clean up -drop database mysqltest; -drop database mysqltest2; - ---echo End of 5.1 tests +--source suite/rpl/t/rpl_sp.test diff --git a/mysql-test/suite/engines/funcs/t/rpl_sp004.test b/mysql-test/suite/engines/funcs/t/rpl_sp004.test index 967e7007c15..ca5c802b279 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_sp004.test +++ b/mysql-test/suite/engines/funcs/t/rpl_sp004.test @@ -1,97 +1,2 @@ -############################################################################# -# Original Author: JBM # -# Original Date: Aug/14/2005 # -############################################################################# -# Test: This test contains two sp that create and drop tables, insert and # -# updated data and uses the NOW() function. # -############################################################################# +--source suite/rpl/t/rpl_sp004.test - -# Includes --- source include/master-slave.inc - - -# Begin clean up test section -connection master; ---disable_warnings -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t3; ---enable_warnings -# End of cleanup - -# Begin test section 1 - -delimiter |; -CREATE PROCEDURE test.p1() -BEGIN - CREATE TABLE IF NOT EXISTS test.t1(a INT,PRIMARY KEY(a)); - CREATE TABLE IF NOT EXISTS test.t2(a INT,PRIMARY KEY(a)); - INSERT INTO test.t1 VALUES (4),(2),(1),(3); - UPDATE test.t1 SET a=a+4 WHERE a=4; - INSERT INTO test.t2 (a) SELECT t1.a FROM test.t1; - UPDATE test.t1 SET a=a+4 WHERE a=8; - CREATE TABLE IF NOT EXISTS test.t3(n MEDIUMINT NOT NULL AUTO_INCREMENT, f FLOAT, d DATETIME, PRIMARY KEY(n)); -END| -CREATE PROCEDURE test.p2() -BEGIN - DROP TABLE IF EXISTS test.t1; - DROP TABLE IF EXISTS test.t2; - INSERT INTO test.t3 VALUES(NULL,11111111.233333,NOW()); -END| -delimiter ;| - -CALL test.p1(); -SELECT * FROM test.t1 ORDER BY a; -SELECT * FROM test.t2 ORDER BY a; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t1 ORDER BY a; -SELECT * FROM test.t2 ORDER BY a; - -connection master; -CALL test.p2(); -USE test; -SHOW TABLES; -#SELECT * FROM test.t3; -save_master_pos; -connection slave; -sync_with_master; -USE test; -SHOW TABLES; -#SELECT * FROM test.t3; - -connection master; -CALL test.p1(); -SELECT * FROM test.t1 ORDER BY a; -SELECT * FROM test.t2 ORDER BY a; -#SELECT * FROM test.t3; -save_master_pos; -connection slave; -sync_with_master; -SELECT * FROM test.t1 ORDER BY a; -SELECT * FROM test.t2 ORDER BY a; -#SELECT * FROM test.t3; - ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp004_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/sp004_slave.sql - -# Cleanup -connection master; -#show binlog events; -DROP PROCEDURE IF EXISTS test.p1; -DROP PROCEDURE IF EXISTS test.p2; -DROP TABLE IF EXISTS test.t1; -DROP TABLE IF EXISTS test.t2; -DROP TABLE IF EXISTS test.t3; -sync_slave_with_master; - -# If the test fails, you will need to diff the dumps to see why. - -diff_files $MYSQLTEST_VARDIR/tmp/sp004_master.sql $MYSQLTEST_VARDIR/tmp/sp004_slave.sql; - - -# End of 5.0 test case diff --git a/mysql-test/suite/engines/funcs/t/rpl_sp_effects.test b/mysql-test/suite/engines/funcs/t/rpl_sp_effects.test index 94ce539291d..29b27c728e4 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_sp_effects.test +++ b/mysql-test/suite/engines/funcs/t/rpl_sp_effects.test @@ -1,208 +1,2 @@ -########################################## -# Change Author: JBM -# Change Date: 2006-05-02 -########################################## +--source suite/rpl/t/rpl_sp_effects.test -# Test of replication of stored procedures (WL#2146 for MySQL 5.0) --- source include/master-slave.inc - -# **************************************************************** -connection master; - -# cleanup ---disable_warnings -drop procedure if exists p1; -drop procedure if exists p2; -drop function if exists f1; -drop table if exists t1,t2; -drop view if exists v1; ---enable_warnings -create table t1 (a int); - -SET GLOBAL log_bin_trust_function_creators = 1; - -# 1. Test simple variables use. -delimiter //; -create procedure p1() -begin - declare spv int default 0; - while spv < 5 do - insert into t1 values(spv+1); - set spv=spv+1; - end while; -end// -delimiter ;// - -call p1(); - -sync_slave_with_master; -connection slave; -SELECT * FROM t1 ORDER BY a; -connection master; -SELECT * FROM t1 ORDER BY a; - -# 2. Test SP variable name -delimiter //; -create procedure p2() -begin - declare a int default 4; - create table t2 as select a; -end// -delimiter ;// - -call p2(); -SELECT * FROM t2 ORDER BY a; -sync_slave_with_master; -connection slave; -SELECT * FROM t2 ORDER BY a; - -connection master; -drop procedure p1; -drop procedure p2; -drop table t2; - -# 3. Test FUNCTIONs in various places - -delimiter //; -create function f1(x int) returns int -begin - insert into t1 values(x); - return x+1; -end// - -create procedure p1(a int, b int) -begin - declare v int default f1(5); - if (f1(6)) then - select 'yes'; - end if; - set v = f1(7); - while f1(8) < 1 do - select 'this cant be'; - end while; - -end// -delimiter ;// - -call p1(f1(1), f1(2)); -SELECT * FROM t1 ORDER BY a; - -create table t2(a int); -insert into t2 values (10),(11); -SELECT a,f1(a) FROM t2 ORDER BY a; - -# This shouldn't put separate 'call f1(3)' into binlog: -insert into t2 select f1(3); -SELECT 'master:',a FROM t1 ORDER BY a; - -sync_slave_with_master; -connection slave; -SELECT 'slave:',a FROM t1 ORDER BY a; - -connection master; -drop procedure p1; -delete from t1; -delete from t2; - -# 4. VIEWs -delete from t1; -insert into t2 values(1),(2); -create view v1 as select f1(a) as f from t2; -select * from v1 order by f; -SELECT 'master:',a FROM t1 ORDER BY a; - -sync_slave_with_master; -connection slave; -SELECT 'slave:',a FROM t1 ORDER BY a; - -connection master; -drop view v1; -delete from t1; - -# 5. Prepared statements. -prepare s1 from 'select f1(?)'; -set @xx=123; -execute s1 using @xx; -SELECT 'master:',a FROM t1 ORDER BY a; - -sync_slave_with_master; -connection slave; -SELECT 'slave:',a FROM t1 ORDER BY a; - -connection master; -delete from t1; - -# 5. Cursors. -# t2 has (1),(2); -delimiter //; -create procedure p1(spv int) -begin - declare c cursor for select f1(spv) from t2; - while (spv > 2) do - open c; - fetch c into spv; - close c; - set spv= spv - 10; - end while; -end// -delimiter ;// -call p1(15); -SELECT 'master:',a FROM t1 ORDER BY a; -sync_slave_with_master; -connection slave; -SELECT 'slave:',a FROM t1 ORDER BY a; - -connection master; -drop procedure p1; -drop function f1; -drop table t1,t2; - -# BUG#12637: User variables + SPs replication -create table t1 (a int); -delimiter //; -create procedure p1() -begin - insert into t1 values(@x); - set @x=@x+1; - insert into t1 values(@x); - if (f2()) then - insert into t1 values(1243); - end if; -end// - -create function f2() returns int -begin - insert into t1 values(@z); - set @z=@z+1; - insert into t1 values(@z); - return 0; -end// - -create function f1() returns int -begin - insert into t1 values(@y); - call p1(); - return 0; -end// - -delimiter ;// - -set @x=10; -set @y=20; -set @z=100; -select f1(); - -set @x=30; -call p1(); - -SELECT 'master', a FROM t1 ORDER BY a; -sync_slave_with_master; -connection slave; -SELECT 'slave', a FROM t1 ORDER BY a; - -connection master; -drop table t1; -drop function f1; -drop function f2; -drop procedure p1; -sync_slave_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_start_stop_slave.test b/mysql-test/suite/engines/funcs/t/rpl_start_stop_slave.test index 19988cf902a..632a1a0232c 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_start_stop_slave.test +++ b/mysql-test/suite/engines/funcs/t/rpl_start_stop_slave.test @@ -1,10 +1,10 @@ -source include/master-slave.inc; +--source include/master-slave.inc # # Bug#6148 () # connection slave; -stop slave; +--source include/stop_slave.inc # Let the master do lots of insertions connection master; @@ -17,20 +17,19 @@ while ($1) dec $1; } enable_query_log; -save_master_pos; connection slave; -start slave; -sleep 1; -stop slave io_thread; +--source include/start_slave.inc +--source include/stop_slave_io.inc start slave io_thread; -sync_with_master; +--source include/wait_for_slave_io_to_start.inc connection master; -drop table t1; -save_master_pos; +--sync_slave_with_master -connection slave; -sync_with_master; +connection master; +drop table t1; +--sync_slave_with_master -# End of 4.1 tests +# End of test +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_stm_mystery22.test b/mysql-test/suite/engines/funcs/t/rpl_stm_mystery22.test index 017593fdfba..ca1b14201d3 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_stm_mystery22.test +++ b/mysql-test/suite/engines/funcs/t/rpl_stm_mystery22.test @@ -20,7 +20,7 @@ # first, cause a duplicate key problem on the slave create table t1(n int auto_increment primary key, s char(10)); -sync_slave_with_master; +--sync_slave_with_master insert into t1 values (2,'old'); connection master; insert into t1 values(NULL,'new'); @@ -31,13 +31,13 @@ connection slave; wait_for_slave_to_stop; select * from t1 order by n; delete from t1 where n = 2; ---disable_warnings -start slave; ---enable_warnings + +--source include/start_slave.inc + sync_with_master; #now the buggy slave would be confused on the offset but it can replicate #in order to make it break, we need to stop/start the slave one more time -stop slave; +--source include/stop_slave.inc connection master; # to be able to really confuse the slave, we need some non-auto-increment # events in the log @@ -51,9 +51,9 @@ set sql_log_bin=1; delete from t1 where n=4; save_master_pos; connection slave; ---disable_warnings -start slave; ---enable_warnings + +--source include/start_slave.inc + #now the truth comes out - if the slave is buggy, it will never sync because #the slave thread is not able to read events sync_with_master; @@ -61,6 +61,6 @@ select * from t1 order by n; #clean up connection master; drop table t1; -sync_slave_with_master; - +--sync_slave_with_master +--source include/rpl_end.inc # End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_stm_no_op.test b/mysql-test/suite/engines/funcs/t/rpl_stm_no_op.test index 66dc89bd712..e1f920b0928 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_stm_no_op.test +++ b/mysql-test/suite/engines/funcs/t/rpl_stm_no_op.test @@ -1,93 +1,2 @@ -# It's true only in statement-based replication that a statement which -# updates no rows (UPDATE/DELETE) is binlogged; in row-based -# replication, as we log modified rows, nothing is binlogged in this -# case. So this test is meaningul only in statement-based (and if it was -# enabled in row-based, it would fail as expected). +--source suite/rpl/t/rpl_stm_no_op.test --- source include/have_binlog_format_mixed_or_statement.inc - -source include/master-slave.inc; - -# see if DROP DATABASE is binlogged even if no effect -connection slave; -create database mysqltest; -connection master; -drop database if exists mysqltest; -sync_slave_with_master; -# can't read dir ---replace_result "Errcode: 1" "Errcode: X" "Errcode: 2" "Errcode: X" \\ / ---error 1049 -show tables from mysqltest; - -# see if DROP TABLE is binlogged even if no effect -connection slave; -create table t1 (a int); -connection master; -drop table if exists t1; -sync_slave_with_master; -# table does not exist ---error 1146 -select * from t1; - -# see if single-table DELETE is binlogged even if no effect -connection master; -create table t1 (a int, b int); -sync_slave_with_master; -insert into t1 values(1,1); -connection master; -delete from t1; -sync_slave_with_master; -select * from t1; - -# see if single-table UPDATE is binlogged even if no effect -insert into t1 values(1,1); -connection master; -insert into t1 values(2,1); -update t1 set a=2; -sync_slave_with_master; -select * from t1; - -# End of 4.1 tests - -# see if multi-table UPDATE is binlogged even if no effect (BUG#13348) - -connection master; -create table t2 (a int, b int); -delete from t1; -insert into t1 values(1,1); -insert into t2 values(1,1); - -sync_slave_with_master; -# force a difference to see if master's multi-UPDATE will correct it -update t1 set a=2; - -connection master; -UPDATE t1, t2 SET t1.a = t2.a; - -sync_slave_with_master; -select * from t1; -select * from t2; - -# See if multi-table DELETE is binlogged even if no effect - -connection master; -delete from t1; -delete from t2; - -sync_slave_with_master; -# force a difference to see if master's multi-DELETE will correct it -insert into t1 values(1,1); -insert into t2 values(1,1); - -connection master; -DELETE t1.*, t2.* from t1, t2; - -sync_slave_with_master; -select * from t1; -select * from t2; - - -# cleanup -connection master; -drop table t1, t2; -sync_slave_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_switch_stm_row_mixed.test b/mysql-test/suite/engines/funcs/t/rpl_switch_stm_row_mixed.test index 396ba4073e4..cd826c6be1e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_switch_stm_row_mixed.test +++ b/mysql-test/suite/engines/funcs/t/rpl_switch_stm_row_mixed.test @@ -1,564 +1 @@ --- source include/master-slave.inc - -connection default; -set @saved_binlog_format = @@global.binlog_format; - -# Since this test generates row-based events in the binary log, the -# slave SQL thread cannot be in STATEMENT mode to execute this test, -# so we only execute it for MIXED and ROW as default value of -# BINLOG_FORMAT. - -connection slave; --- source include/have_binlog_format_mixed_or_row.inc - -connection master; ---disable_warnings -drop database if exists mysqltest1; -create database mysqltest1; ---enable_warnings -use mysqltest1; -set session binlog_format=row; -set global binlog_format=row; - -show global variables like "binlog_format%"; -show session variables like "binlog_format%"; -select @@global.binlog_format, @@session.binlog_format; - -CREATE TABLE t1 (a varchar(100)); - -prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency_1_"; -insert into t1 values("work_2_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work_3_")); -execute stmt1 using @string; -deallocate prepare stmt1; - -insert into t1 values(concat("for_4_",UUID())); -insert into t1 select "yesterday_5_"; - -# verify that temp tables prevent a switch to SBR -create temporary table tmp(a char(100)); -insert into tmp values("see_6_"); ---error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR -set binlog_format=statement; -insert into t1 select * from tmp; -drop temporary table tmp; - -# Now we go to SBR -set binlog_format=statement; -show global variables like "binlog_format%"; -show session variables like "binlog_format%"; -select @@global.binlog_format, @@session.binlog_format; -set global binlog_format=statement; -show global variables like "binlog_format%"; -show session variables like "binlog_format%"; -select @@global.binlog_format, @@session.binlog_format; - -prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency_7_"; -insert into t1 values("work_8_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work_9_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -insert into t1 values("for_10_"); -insert into t1 select "yesterday_11_"; - -# test SET DEFAULT (=statement at this point of test) -set binlog_format=default; -select @@global.binlog_format, @@session.binlog_format; -# due to cluster it's hard to set back to default -#--error ER_NO_DEFAULT -set global binlog_format=default; -select @@global.binlog_format, @@session.binlog_format; - -prepare stmt1 from 'insert into t1 select ?'; -set @string="emergency_12_"; -insert into t1 values("work_13_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values("work_14_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -insert into t1 values("for_15_"); -insert into t1 select "yesterday_16_"; - -# and now the mixed mode - -set binlog_format=mixed; -select @@global.binlog_format, @@session.binlog_format; -set global binlog_format=mixed; -select @@global.binlog_format, @@session.binlog_format; - -prepare stmt1 from 'insert into t1 select concat(UUID(),?)'; -set @string="emergency_17_"; -insert into t1 values("work_18_"); -execute stmt1 using @string; -deallocate prepare stmt1; - -prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work_19_")); -execute stmt1 using @string; -deallocate prepare stmt1; - -insert into t1 values(concat("for_20_",UUID())); -insert into t1 select "yesterday_21_"; - -prepare stmt1 from 'insert into t1 select ?'; -insert into t1 values(concat(UUID(),"work_22_")); -execute stmt1 using @string; -deallocate prepare stmt1; - -insert into t1 values(concat("for_23_",UUID())); -insert into t1 select "yesterday_24_"; - -# Test of CREATE TABLE SELECT - -create table t2 select rpad(UUID(),100,' '); -create table t3 select 1 union select UUID(); -create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3); -create table t5 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); -# what if UUID() is first: ---disable_warnings -insert into t5 select UUID() from t1 where 3 in (select 1 union select 2 union select 3 union select * from t4); ---enable_warnings - -# inside a stored procedure - -delimiter |; -create procedure foo() -begin -insert into t1 values("work_25_"); -insert into t1 values(concat("for_26_",UUID())); -insert into t1 select "yesterday_27_"; -end| -create procedure foo2() -begin -insert into t1 values(concat("emergency_28_",UUID())); -insert into t1 values("work_29_"); -insert into t1 values(concat("for_30_",UUID())); -set session binlog_format=row; # accepted for stored procs -insert into t1 values("more work_31_"); -set session binlog_format=mixed; -end| -create function foo3() returns bigint unsigned -begin - set session binlog_format=row; # rejected for stored funcs - insert into t1 values("alarm"); - return 100; -end| -create procedure foo4(x varchar(100)) -begin -insert into t1 values(concat("work_250_",x)); -insert into t1 select "yesterday_270_"; -end| -delimiter ;| -call foo(); -call foo2(); -call foo4("hello"); -call foo4(UUID()); -call foo4("world"); - -# test that can't SET in a stored function ---error ER_STORED_FUNCTION_PREVENTS_SWITCH_BINLOG_FORMAT -select foo3(); -select * from t1 where a="alarm"; - -# Tests of stored functions/triggers/views for BUG#20930 "Mixed -# binlogging mode does not work with stored functions, triggers, -# views" - -# Function which calls procedure -drop function foo3; -delimiter |; -create function foo3() returns bigint unsigned -begin - insert into t1 values("foo3_32_"); - call foo(); - return 100; -end| -delimiter ;| -insert into t2 select foo3(); - -prepare stmt1 from 'insert into t2 select foo3()'; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; - -# Test if stored function calls stored function which calls procedure -# which requires row-based. - -delimiter |; -create function foo4() returns bigint unsigned -begin - insert into t2 select foo3(); - return 100; -end| -delimiter ;| -select foo4(); - -prepare stmt1 from 'select foo4()'; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; - -# A simple stored function -delimiter |; -create function foo5() returns bigint unsigned -begin - insert into t2 select UUID(); - return 100; -end| -delimiter ;| -select foo5(); - -prepare stmt1 from 'select foo5()'; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; - -# A simple stored function where UUID() is in the argument -delimiter |; -create function foo6(x varchar(100)) returns bigint unsigned -begin - insert into t2 select x; - return 100; -end| -delimiter ;| -select foo6("foo6_1_"); -select foo6(concat("foo6_2_",UUID())); - -prepare stmt1 from 'select foo6(concat("foo6_3_",UUID()))'; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; - - -# Test of views using UUID() - -create view v1 as select uuid(); -create table t11 (data varchar(255)); -insert into t11 select * from v1; -# Test of querying INFORMATION_SCHEMA which parses the view's body, -# to verify that it binlogs statement-based (is not polluted by -# the parsing of the view's body). -insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11'); -prepare stmt1 from "insert into t11 select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysqltest1' and TABLE_NAME IN ('v1','t11')"; -execute stmt1; -execute stmt1; -deallocate prepare stmt1; - -# Test of triggers with UUID() -delimiter |; -create trigger t11_bi before insert on t11 for each row -begin - set NEW.data = concat(NEW.data,UUID()); -end| -delimiter ;| -insert into t11 values("try_560_"); - -# DELAYED option not supported by table created -# using innodb. -# Test that INSERT DELAYED works in mixed mode (BUG#20649) -#insert delayed into t2 values("delay_1_"); -#insert delayed into t2 values(concat("delay_2_",UUID())); -#insert delayed into t2 values("delay_6_"); - -# Test for BUG#20633 (INSERT DELAYED RAND()/user_variable does not -# replicate fine in statement-based ; we test that in mixed mode it -# works). -#insert delayed into t2 values(rand()); -#set @a=2.345; -#insert delayed into t2 values(@a); - -sleep 4; # time for the delayed inserts to reach disk - -# If you want to do manual testing of the mixed mode regarding UDFs (not -# testable automatically as quite platform- and compiler-dependent), -# you just need to set the variable below to 1, and to -# "make udf_example.so" in sql/, and to copy sql/udf_example.so to -# MYSQL_TEST_DIR/lib/mysql. -let $you_want_to_test_UDF=0; -if ($you_want_to_test_UDF) -{ - CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so'; - prepare stmt1 from 'insert into t1 select metaphon(?)'; - set @string="emergency_133_"; - insert into t1 values("work_134_"); - execute stmt1 using @string; - deallocate prepare stmt1; - prepare stmt1 from 'insert into t1 select ?'; - insert into t1 values(metaphon("work_135_")); - execute stmt1 using @string; - deallocate prepare stmt1; - insert into t1 values(metaphon("for_136_")); - insert into t1 select "yesterday_137_"; - create table t6 select metaphon("for_138_"); - create table t7 select 1 union select metaphon("for_139_"); - create table t8 select * from t1 where 3 in (select 1 union select 2 union select metaphon("for_140_") union select 3); - create table t9 select * from t1 where 3 in (select 1 union select 2 union select curdate() union select 3); -} - -create table t20 select * from t1; # save for comparing later -create table t21 select * from t2; -create table t22 select * from t3; -drop table t1,t2,t3; - -# This tests the fix to -# BUG#19630 stored function inserting into two auto_increment breaks statement-based binlog -# We verify that under the mixed binlog mode, a stored function -# modifying at least two tables having an auto_increment column, -# is binlogged row-based. Indeed in statement-based binlogging, -# only the auto_increment value generated for the first table -# is recorded in the binlog, the value generated for the 2nd table -# lacking. - -create table t1 (a int primary key auto_increment, b varchar(100)); -create table t2 (a int primary key auto_increment, b varchar(100)); -create table t3 (b varchar(100)); -delimiter |; -create function f (x varchar(100)) returns int deterministic -begin - insert into t1 values(null,x); - insert into t2 values(null,x); - return 1; -end| -delimiter ;| -select f("try_41_"); -# Two operations which compensate each other except that their net -# effect is that they advance the auto_increment counter of t2 on slave: -sync_slave_with_master; -use mysqltest1; -insert into t2 values(2,null),(3,null),(4,null); -delete from t2 where a>=2; - -connection master; -# this is the call which didn't replicate well -select f("try_42_"); -sync_slave_with_master; - -# now use prepared statement and test again, just to see that the RBB -# mode isn't set at PREPARE but at EXECUTE. - -insert into t2 values(3,null),(4,null); -delete from t2 where a>=3; - -connection master; -prepare stmt1 from 'select f(?)'; -set @string="try_43_"; -insert into t1 values(null,"try_44_"); # should be SBB -execute stmt1 using @string; # should be RBB -deallocate prepare stmt1; -sync_slave_with_master; - -# verify that if only one table has auto_inc, it does not trigger RBB -# (we'll check in binlog further below) - -connection master; -create table t12 select * from t1; # save for comparing later -drop table t1; -create table t1 (a int, b varchar(100), key(a)); -select f("try_45_"); - -# restore table's key -create table t13 select * from t1; -drop table t1; -create table t1 (a int primary key auto_increment, b varchar(100)); - -# now test if it's two functions, each of them inserts in one table - -drop function f; -# we need a unique key to have sorting of rows by mysqldump -create table t14 (unique (a)) select * from t2; -truncate table t2; -delimiter |; -create function f1 (x varchar(100)) returns int deterministic -begin - insert into t1 values(null,x); - return 1; -end| -create function f2 (x varchar(100)) returns int deterministic -begin - insert into t2 values(null,x); - return 1; -end| -delimiter ;| -select f1("try_46_"),f2("try_47_"); - -sync_slave_with_master; -insert into t2 values(2,null),(3,null),(4,null); -delete from t2 where a>=2; - -connection master; -# Test with SELECT and INSERT -select f1("try_48_"),f2("try_49_"); -insert into t3 values(concat("try_50_",f1("try_51_"),f2("try_52_"))); -sync_slave_with_master; - -# verify that if f2 does only read on an auto_inc table, this does not -# switch to RBB -connection master; -drop function f2; -delimiter |; -create function f2 (x varchar(100)) returns int deterministic -begin - declare y int; - insert into t1 values(null,x); - set y = (select count(*) from t2); - return y; -end| -delimiter ;| -select f1("try_53_"),f2("try_54_"); -sync_slave_with_master; - -# And now, a normal statement with a trigger (no stored functions) - -connection master; -drop function f2; -delimiter |; -create trigger t1_bi before insert on t1 for each row -begin - insert into t2 values(null,"try_55_"); -end| -delimiter ;| -insert into t1 values(null,"try_56_"); -# and now remove one auto_increment and verify SBB -alter table t1 modify a int, drop primary key; -insert into t1 values(null,"try_57_"); -sync_slave_with_master; - -# Test for BUG#20499 "mixed mode with temporary table breaks binlog" -# Slave used to have only 2 rows instead of 3. -connection master; -CREATE TEMPORARY TABLE t15 SELECT UUID(); -create table t16 like t15; -INSERT INTO t16 SELECT * FROM t15; -# we'll verify that this one is done RBB -insert into t16 values("try_65_"); -drop table t15; -# we'll verify that this one is done SBB -insert into t16 values("try_66_"); -sync_slave_with_master; - -# and now compare: - -connection master; - -# first check that data on master is sensible -select count(*) from t1; -select count(*) from t2; -select count(*) from t3; -select count(*) from t4; -select count(*) from t5; -select count(*) from t11; -select count(*) from t20; -select count(*) from t21; -select count(*) from t22; -select count(*) from t12; -select count(*) from t13; -select count(*) from t14; -select count(*) from t16; -if ($you_want_to_test_UDF) -{ - select count(*) from t6; - select count(*) from t7; - select count(*) from t8; - select count(*) from t9; -} - -sync_slave_with_master; - -# -# Bug#20863 If binlog format is changed between update and unlock of -# tables, wrong binlog -# - -connection master; -DROP TABLE IF EXISTS t11; -SET SESSION BINLOG_FORMAT=STATEMENT; -CREATE TABLE t11 (song VARCHAR(255)); -LOCK TABLES t11 WRITE; -SET SESSION BINLOG_FORMAT=ROW; -INSERT INTO t11 VALUES('Several Species of Small Furry Animals Gathered Together in a Cave and Grooving With a Pict'); -SET SESSION BINLOG_FORMAT=STATEMENT; -INSERT INTO t11 VALUES('Careful With That Axe, Eugene'); -UNLOCK TABLES; - ---query_vertical SELECT * FROM t11 -sync_slave_with_master; -USE mysqltest1; ---query_vertical SELECT * FROM t11 - -connection master; -DROP TABLE IF EXISTS t12; -SET SESSION BINLOG_FORMAT=MIXED; -CREATE TABLE t12 (data LONG); -LOCK TABLES t12 WRITE; -INSERT INTO t12 VALUES(UUID()); -UNLOCK TABLES; - ---disable_result_log ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -show binlog events; ---enable_result_log -sync_slave_with_master; - -# as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql ---exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql - -# Let's compare. Note: If they match test will pass, if they do not match -# the test will show that the diff statement failed and not reject file -# will be created. You will need to go to the mysql-test dir and diff -# the files your self to see what is not matching - -diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql; - -connection master; ---disable_result_log ---replace_column 2 # 5 # ---replace_regex /table_id: [0-9]+/table_id: #/ -show binlog events; ---enable_result_log - -# Now test that mysqlbinlog works fine on a binlog generated by the -# mixed mode - -# BUG#11312 "DELIMITER is not written to the binary log that causes -# syntax error" makes that mysqlbinlog will fail if we pass it the -# text of queries; this forces us to use --base64-output here. - -# BUG#20929 "BINLOG command causes invalid free plus assertion -# failure" makes mysqld segfault when receiving --base64-output - -# So I can't enable this piece of test -# SIGH - -if ($enable_when_11312_or_20929_fixed) -{ ---exec $MYSQL_BINLOG --base64-output $MYSQLTEST_VARDIR/log/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql -drop database mysqltest1; ---exec $MYSQL < $MYSQLTEST_VARDIR/tmp/mysqlbinlog_mixed.sql ---exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info mysqltest1 > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql -# the old mysqldump output on slave is the same as what it was on -# master before restoring on master. -diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql; -} - -drop database mysqltest1; -sync_slave_with_master; - -connection default; -set global binlog_format= @saved_binlog_format; +--source extra/rpl_tests/rpl_switch_stm_row_mixed.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_temp_table.test b/mysql-test/suite/engines/funcs/t/rpl_temp_table.test index c13470f20b6..43b5ce49129 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_temp_table.test +++ b/mysql-test/suite/engines/funcs/t/rpl_temp_table.test @@ -1,69 +1,2 @@ -# drop table t1 t2 t3 are included int master-slave.inc -# meaningful only in statement-based: +--source suite/rpl/t/rpl_temp_table.test --- source include/have_binlog_format_mixed_or_statement.inc --- source include/master-slave.inc - ---disable_query_log -CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - - -create table t2 (n int, PRIMARY KEY(n)); -create temporary table t1 (n int); -create temporary table t3 (n int not null); - -insert into t1 values(1),(2),(3),(100),(25),(26),(200),(300); ---disable_warnings -insert into t2 select * from t1; ---enable_warnings -alter table t3 add primary key(n); - -flush logs; -insert into t3 values (1010); ---disable_warnings -insert into t2 select * from t3; ---enable_warnings - -drop table if exists t3; -insert into t2 values (1012); - -connection master1; -create temporary table t1 (n int); -insert into t1 values (4),(5); ---disable_warnings -insert into t2 select * from t1; ---enable_warnings - -save_master_pos; -disconnect master; - -connection slave; -#add 1 to the saved position, so we will catch drop table on disconnect -#for sure -sync_with_master 1; - -connection master1; -insert into t2 values(61); - -save_master_pos; -disconnect master1; - -connection slave; -#same trick - make sure we catch drop of temporary table on disconnect -sync_with_master 1; - -select * from t2; -select count(*) from t2; -select sum(n) from t2; -show status like 'Slave_open_temp_tables'; - -# -# Clean up -# -connect (master2,localhost,root,,); -connection master2; -drop table if exists t1,t2; -save_master_pos; -connection slave; -sync_with_master; diff --git a/mysql-test/suite/engines/funcs/t/rpl_temporary.test b/mysql-test/suite/engines/funcs/t/rpl_temporary.test index aa90a5153bf..460ea55a675 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_temporary.test +++ b/mysql-test/suite/engines/funcs/t/rpl_temporary.test @@ -1,235 +1,2 @@ +--source suite/rpl/t/rpl_temporary.test --- source include/master-slave.inc - -# Clean up old slave's binlogs. -# The slave is started with --log-slave-updates -# and this test does SHOW BINLOG EVENTS on the slave's -# binlog. But previous tests can influence the current test's -# binlog (e.g. a temporary table in the previous test has not -# been explicitly deleted, or it has but the slave hasn't had -# enough time to catch it before STOP SLAVE, -# and at the beginning of the current -# test the slave immediately writes DROP TEMPORARY TABLE this_old_table). -# We wait for the slave to have written all he wants to the binlog -# (otherwise RESET MASTER may come too early). -save_master_pos; -connection slave; -sync_with_master; -reset master; -connection master; - ---disable_query_log -CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); ---enable_query_log - -connect (con1,localhost,root,,); - -#added on 2007/5/18 -connection con1; -CREATE USER ''@localhost; - -connect (con2,localhost,root,,); -# We want to connect as an unprivileged user. But if we use user="" then this -# will pick the Unix login, which will cause problems if you're running the test -# as root. -connect (con3,localhost,zedjzlcsjhd,,); - -# We are going to use SET PSEUDO_THREAD_ID in this test; -# check that it requires the SUPER privilege. - -connection con3; -SET @save_select_limit=@@session.sql_select_limit; ---error 1227 -SET @@session.sql_select_limit=10, @@session.pseudo_thread_id=100; -SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed -# While we are here we also test that SQL_LOG_BIN can't be set ---error 1227 -SET @@session.sql_select_limit=10, @@session.sql_log_bin=0; -SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed -# Now as root, to be sure it works -connection con2; -SET @@session.pseudo_thread_id=100; -SET @@session.pseudo_thread_id=connection_id(); -SET @@session.sql_log_bin=0; -SET @@session.sql_log_bin=1; - -connection con3; -let $VERSION=`select version()`; - ---disable_warnings -drop table if exists t1,t2; ---enable_warnings - -create table t1(f int); -create table t2(f int); -insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); - -connection con1; -create temporary table t3(f int); ---disable_warnings -insert into t3 select * from t1 where f<6; ---enable_warnings -sleep 1; - -connection con2; -create temporary table t3(f int); -sleep 1; - -connection con1; ---disable_warnings -insert into t2 select count(*) from t3; ---enable_warnings -sleep 1; - -connection con2; ---disable_warnings -insert into t3 select * from t1 where f>=4; ---enable_warnings -sleep 1; - -connection con1; -drop temporary table t3; -sleep 1; - -connection con2; ---disable_warnings -insert into t2 select count(*) from t3; ---enable_warnings -drop temporary table t3; - -select * from t2 ORDER BY f; - -# Commented out 8/30/2005 to make compatable with both sbr and rbr -#--replace_result $VERSION VERSION -#--replace_column 2 # 5 # -#show binlog events; - -drop table t1, t2; - -use test; -SET TIMESTAMP=1040323920; -create table t1(f int); -SET TIMESTAMP=1040323931; -create table t2(f int); -SET TIMESTAMP=1040323938; -insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); - -SET TIMESTAMP=1040323945; -SET @@session.pseudo_thread_id=1; -create temporary table t3(f int); -SET TIMESTAMP=1040323952; -SET @@session.pseudo_thread_id=1; ---disable_warnings -insert into t3 select * from t1 where f<6; ---enable_warnings -SET TIMESTAMP=1040324145; -SET @@session.pseudo_thread_id=2; -create temporary table t3(f int); -SET TIMESTAMP=1040324186; -SET @@session.pseudo_thread_id=1; ---disable_warnings -insert into t2 select count(*) from t3; ---enable_warnings -SET TIMESTAMP=1040324200; -SET @@session.pseudo_thread_id=2; ---disable_warnings -insert into t3 select * from t1 where f>=4; ---enable_warnings -SET TIMESTAMP=1040324211; -SET @@session.pseudo_thread_id=1; -drop temporary table t3; -SET TIMESTAMP=1040324219; -SET @@session.pseudo_thread_id=2; ---disable_warnings -insert into t2 select count(*) from t3; ---enable_warnings -SET TIMESTAMP=1040324224; -SET @@session.pseudo_thread_id=2; -drop temporary table t3; - -select * from t2 ORDER BY f; -drop table t1,t2; - -# Create last a temporary table that is not dropped at end to ensure that we -# don't get any memory leaks for this - -create temporary table t3 (f int); -#sync_with_master; - -# The server will now close done - -# -# Bug#17284 erroneous temp table cleanup on slave -# - -connection master; -create temporary table t4 (f int); -create table t5 (f int); -#sync_with_master; -# find dumper's $id -select id from information_schema.processlist where command='Binlog Dump' into @id; -kill @id; # to stimulate reconnection by slave w/o timeout ---disable_warnings -insert into t5 select * from t4; ---enable_warnings -save_master_pos; - -connection slave; -sync_with_master; -select * from t5 /* must be 1 after reconnection */; - -connection master; -drop temporary table t4; -drop table t5; - -# -# BUG#17263 incorrect generation DROP temp tables -# Temporary tables of connection are dropped in batches -# where a batch correspond to pseudo_thread_id -# value was set up at the moment of temp table creation -# -connection con1; -set @@session.pseudo_thread_id=100; -create temporary table t101 (id int); -create temporary table t102 (id int); -set @@session.pseudo_thread_id=200; -create temporary table t201 (id int); -create temporary table `t``201` (id int); -# emulate internal temp table not to come to binlog -create temporary table `#sql_not_user_table202` (id int); -set @@session.pseudo_thread_id=300; -create temporary table t301 (id int); -create temporary table t302 (id int); -create temporary table `#sql_not_user_table303` (id int); - - -#Added on 2007/5/18 -DROP USER ''@localhost; - -disconnect con1; - -#now do something to show that slave is ok after DROP temp tables -connection master; -create table t1(f int); -insert into t1 values (1); - -sync_slave_with_master; -#connection slave; -select * from t1 /* must be 1 */; - -connection master; -drop table t1; - -# -#14157: utf8 encoding in binlog without set character_set_client -# -exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=latin1 test -e "create table t1 (a int); set names latin1; create temporary table äöüÄÖÜ (a int); insert into äöüÄÖÜ values (1); insert into t1 select * from äöüÄÖÜ"; - -sync_slave_with_master; -#connection slave; -select * from t1; - -connection master; -drop table t1; - -# End of 5.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_trigger.test b/mysql-test/suite/engines/funcs/t/rpl_trigger.test index 77cc7a3b2c0..4f61e738200 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_trigger.test +++ b/mysql-test/suite/engines/funcs/t/rpl_trigger.test @@ -5,16 +5,7 @@ --source include/have_binlog_format_mixed_or_statement.inc --source include/master-slave.inc -disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); -enable_query_log; - ---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 @@ -45,19 +36,16 @@ SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186; --disable_warnings 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); +insert into t2 values(3,0); +insert into t2 values(NULL,0); +insert into t2 values(NULL,0); +insert into t2 values(500,0); --enable_warnings 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 +--sync_slave_with_master 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; @@ -109,17 +97,14 @@ let $time=`select a from t1`; # - dump definers on the slave; SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; SELECT trigger_name, definer -FROM information_schema.triggers; +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 +--sync_slave_with_master # XXX: Definers of stored procedures and functions are not replicated. WL#2897 # (Complete definer support in the stored routines) addresses this issue. So, @@ -127,10 +112,12 @@ select "--- On slave --" as ""; # item. SELECT routine_name, definer -FROM information_schema.routines; +FROM information_schema.routines +WHERE routine_name = 'bug12480'; SELECT trigger_name, definer -FROM information_schema.triggers; +FROM information_schema.triggers +WHERE trigger_name = 't1_first'; select a=b && a=c from t1; --disable_query_log @@ -170,9 +157,7 @@ create database other; use other; insert into test.t1 values (1); -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master connection master; use test; @@ -219,7 +204,7 @@ while ($rnd) dec $i; } - sync_slave_with_master; + --sync_slave_with_master #connection slave; eval select * from t1$rnd; delimiter |; @@ -258,7 +243,7 @@ while ($rnd) dec $i; } - sync_slave_with_master; + --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 */; @@ -270,6 +255,8 @@ while ($rnd) connection master; eval drop table t1$rnd; + --sync_slave_with_master + connection master; dec $rnd; } @@ -300,26 +287,42 @@ while ($rnd) # Stop the slave. connection slave; -STOP SLAVE; +--source include/stop_slave.inc # Replace master's binlog. connection master; +let $MYSQLD_DATADIR= `select @@datadir`; FLUSH LOGS; -let $DATADIR = `select @@datadir`; -remove_file $DATADIR/master-bin.000001; -copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $DATADIR/master-bin.000001; + +# Stop master server +--let $rpl_server_number= 1 +--source include/rpl_stop_server.inc + +# Replace binlog +remove_file $MYSQLD_DATADIR/master-bin.000001; +copy_file $MYSQL_TEST_DIR/std_data/bug16266.000001 $MYSQLD_DATADIR/master-bin.000001; + +--let $rpl_server_number= 1 +--source include/rpl_start_server.inc + +let $binlog_version= query_get_value(SHOW BINLOG EVENTS, Info, 1); + + +# Make the slave to replay the new binlog. +--echo --> Master binlog: $binlog_version # Make the slave to replay the new binlog. connection slave; RESET SLAVE; -START SLAVE; +--source include/start_slave.inc SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0; # Check that the replication succeeded. SHOW TABLES LIKE 't_'; +--replace_column 6 # SHOW TRIGGERS; SELECT * FROM t1; SELECT * FROM t2; @@ -338,7 +341,7 @@ DROP TRIGGER trg1; DROP TABLE t1; DROP TABLE t2; -STOP SLAVE; +--source include/stop_slave.inc RESET SLAVE; # The master should be clean. @@ -352,7 +355,7 @@ RESET MASTER; # Restart slave. connection slave; -START SLAVE; +--source include/start_slave.inc # @@ -367,7 +370,6 @@ START SLAVE; --echo --echo ---> Preparing environment... ---echo ---> connection: master --connection master --disable_warnings @@ -378,12 +380,9 @@ DROP TABLE IF EXISTS t2; --echo --echo ---> Synchronizing slave with master... ---save_master_pos ---connection slave ---sync_with_master +--sync_slave_with_master --echo ---echo ---> connection: master --connection master # Test. @@ -412,11 +411,7 @@ SELECT * FROM t2; --echo --echo ---> Synchronizing slave with master... ---save_master_pos ---connection slave ---sync_with_master - ---echo ---> connection: master +--sync_slave_with_master --echo --echo ---> Checking on slave... @@ -427,7 +422,6 @@ SELECT * FROM t2; # Cleanup. --echo ---echo ---> connection: master --connection master --echo @@ -436,9 +430,7 @@ SELECT * FROM t2; DROP TABLE t1; DROP TABLE t2; ---save_master_pos ---connection slave ---sync_with_master +--sync_slave_with_master --connection master # @@ -469,9 +461,7 @@ insert into t1 values (3, "c"); select * from t1; -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master select * from t1; @@ -481,6 +471,5 @@ drop table if exists t1,t11; # # End of tests # -save_master_pos; -connection slave; -sync_with_master; +--sync_slave_with_master +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_trunc_temp.test b/mysql-test/suite/engines/funcs/t/rpl_trunc_temp.test index 28bcb0c06c3..fa072022824 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_trunc_temp.test +++ b/mysql-test/suite/engines/funcs/t/rpl_trunc_temp.test @@ -1,35 +1,2 @@ -# Requires statement logging --- source include/have_binlog_format_mixed_or_statement.inc +--source suite/rpl/t/rpl_trunc_temp.test -source include/master-slave.inc; - -# -# Bug#17137 Running "truncate table" on temporary table -# leaves the table open on a slave -# - -create temporary table t1 (n int); -insert into t1 values(1); -sync_slave_with_master; -show status like 'Slave_open_temp_tables'; - -# Perform a delete from temp table -connection master; -delete from t1; -sync_slave_with_master; -show status like 'Slave_open_temp_tables'; - -# Perform truncate on temp table -connection master; -truncate t1; -sync_slave_with_master; -show status like 'Slave_open_temp_tables'; - -# Disconnect the master, temp table on slave should dissapear -disconnect master; ---real_sleep 3 # time for DROP to be read by slave -connection slave; -show status like 'Slave_open_temp_tables'; - - -# End of 4.1 tests diff --git a/mysql-test/suite/engines/funcs/t/rpl_user_variables.test b/mysql-test/suite/engines/funcs/t/rpl_user_variables.test index 530cda3d87a..1b78f2b5fb5 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_user_variables.test +++ b/mysql-test/suite/engines/funcs/t/rpl_user_variables.test @@ -1,57 +1,2 @@ -################################### -# -# Test of replicating user variables -# -################################### +--source suite/rpl/t/rpl_user_variables.test --- source include/master-slave.inc -# Disable PS as the log positions differs ---disable_ps_protocol - - -# Clean up old slave's binlogs. -# The slave is started with --log-slave-updates -# and this test does SHOW BINLOG EVENTS on the slave's -# binlog. But previous tests can influence the current test's -# binlog (e.g. a temporary table in the previous test has not -# been explicitly deleted, or it has but the slave hasn't had -# enough time to catch it before STOP SLAVE, -# and at the beginning of the current -# test the slave immediately writes DROP TEMPORARY TABLE this_old_table). -# We wait for the slave to have written all he wants to the binlog -# (otherwise RESET MASTER may come too early). -save_master_pos; -connection slave; -sync_with_master; -reset master; -connection master; - -create table t1(n char(30)); -set @i1:=12345678901234, @i2:=-12345678901234, @i3:=0, @i4:=-1; -set @s1:='This is a test', @r1:=12.5, @r2:=-12.5; -set @n1:=null; -set @s2:='', @s3:='abc\'def', @s4:= 'abc\\def', @s5:= 'abc''def'; -insert into t1 values (@i1), (@i2), (@i3), (@i4); -insert into t1 values (@r1), (@r2); -insert into t1 values (@s1), (@s2), (@s3), (@s4), (@s5); -insert into t1 values (@n1); -insert into t1 values (@n2); # not explicitly set before -insert into t1 values (@a:=0), (@a:=@a+1), (@a:=@a+1); -insert into t1 values (@a+(@b:=@a+1)); -set @q:='abc'; -insert t1 values (@q), (@q:=concat(@q, 'n1')), (@q:=concat(@q, 'n2')); -set @a:=5; -insert into t1 values (@a),(@a); -# To flush the pending event, we add the following statement. RBR can -# concatenate the result of several statements, which SBR cannot. -select * from t1 where n = '<nonexistant>'; -connection master1; # see if variable is reset in binlog when thread changes -insert into t1 values (@a),(@a),(@a*5); -SELECT * FROM t1 ORDER BY n; -sync_slave_with_master; -SELECT * FROM t1 ORDER BY n; -connection master; -insert into t1 select * FROM (select @var1 union select @var2) AS t2; -drop table t1; -sync_slave_with_master; -stop slave; diff --git a/mysql-test/suite/engines/funcs/t/rpl_variables.test b/mysql-test/suite/engines/funcs/t/rpl_variables.test index 031131a3f2b..ca612a5593b 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_variables.test +++ b/mysql-test/suite/engines/funcs/t/rpl_variables.test @@ -1,4 +1,4 @@ -source include/master-slave.inc; +--source include/master-slave.inc # Init for restoration of variable values set @my_slave_net_timeout =@@global.slave_net_timeout; @@ -19,3 +19,4 @@ show variables like 'slave_skip_errors'; # Cleanup set global slave_net_timeout=@my_slave_net_timeout; set global sql_slave_skip_counter=@my_sql_slave_skip_counter; +--source include/rpl_end.inc diff --git a/mysql-test/suite/engines/funcs/t/rpl_view-slave.opt b/mysql-test/suite/engines/funcs/t/rpl_view-slave.opt deleted file mode 100644 index 79b3bf6174b..00000000000 --- a/mysql-test/suite/engines/funcs/t/rpl_view-slave.opt +++ /dev/null @@ -1 +0,0 @@ ---replicate-ignore-table=test.foo diff --git a/mysql-test/suite/engines/funcs/t/rpl_view.test b/mysql-test/suite/engines/funcs/t/rpl_view.test index 3eff8f7550a..b81b22ece4e 100644 --- a/mysql-test/suite/engines/funcs/t/rpl_view.test +++ b/mysql-test/suite/engines/funcs/t/rpl_view.test @@ -1,155 +1,2 @@ -# NYI - row-based cannot use CREATE ... SELECT +--source suite/rpl/t/rpl_view.test -source include/master-slave.inc; ---disable_warnings -drop table if exists t1,v1; -drop view if exists t1,v1; -sync_slave_with_master; -reset master; ---enable_warnings - -# -# Check that creation drop of view is replicated, also check replication of -# updating of view -# -connection master; -create table t1 (a int); -insert into t1 values (1); -create view v1 as select a from t1; -insert into v1 values (2); -select * from v1 order by a; -sync_slave_with_master; -# view already have to be on slave -select * from v1 order by a; -connection master; -update v1 set a=3 where a=1; -select * from v1 order by a; -sync_slave_with_master; -select * from v1 order by a; -connection master; -delete from v1 where a=2; -select * from v1 order by a; -sync_slave_with_master; -select * from v1 order by a; -connection master; -# 'alter view' internally maped to creation, but still check that it works -alter view v1 as select a as b from t1; -sync_slave_with_master; -select * from v1 order by 1; -connection master; -drop view v1; -sync_slave_with_master; -#error, because view have to be removed from slave --- error 1146 -select * from v1 order by a; -connection master; -drop table t1; -sync_slave_with_master; -# Change Author: JBM -# Change Date: 2005-12-22 -# Change: Commented out binlog events to work with SBR and RBR -#--replace_column 2 # 5 # -# show binlog events limit 1,100; - -# -# 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 VIEW IF EXISTS v1; ---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); - -/*!50003 CREATE VIEW v1 AS SELECT * FROM t1 */; - ---echo ---echo ---> Inserting value... - -INSERT INTO t1 VALUES(1); - ---echo ---echo ---> Checking on master... - -SELECT * FROM t1; - ---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; - -# Cleanup. - ---echo ---echo ---> connection: master ---connection master - ---echo ---echo ---> Cleaning up... - -DROP VIEW v1; -DROP TABLE t1; - ---save_master_pos ---connection slave ---sync_with_master ---connection master - -# -# BUG#19419: "VIEW: View that the column name is different -# by master and slave is made". -# -connection master; -create table t1(a int, b int); -insert into t1 values (1, 1), (1, 2), (1, 3); -create view v1(a, b) as select a, sum(b) from t1 group by a; - -sync_slave_with_master; -explain v1; -show create table v1; -select * from v1; - -connection master; -drop table t1; -drop view v1; - -sync_slave_with_master; - ---echo End of 5.0 tests |