1######################################### 2# Purpose: testing the replication in mixed mode 3# Requirements: define binlog format for mysqld as in example below: 4# ./mysql-test-run.pl --mysqld=--binlog-format=mixed 5######################################### 6 7--source include/master-slave.inc 8 9# Check MIXED on both master and slave 10connection master; 11--source suite/rpl/include/rpl_mixed_show_binlog_format.inc 12connection slave; 13--source suite/rpl/include/rpl_mixed_show_binlog_format.inc 14connection master; 15 16 17CREATE DATABASE test_rpl; 18 19--echo 20--echo ******************** PREPARE TESTING ******************** 21USE test_rpl; 22eval CREATE TABLE t1 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type; 23eval CREATE TABLE t2 (a int auto_increment not null, b char(254), PRIMARY KEY(a)) ENGINE=$engine_type; 24 25# DELETE 26INSERT INTO t1 VALUES(1, 't1, text 1'); 27INSERT INTO t1 VALUES(2, 't1, text 2'); 28INSERT INTO t2 VALUES(1, 't2, text 1'); 29--echo 30--echo ******************** DELETE ******************** 31DELETE FROM t1 WHERE a = 1; 32DELETE FROM t2 WHERE b <> UUID(); 33--source suite/rpl/include/rpl_mixed_check_select.inc 34--source suite/rpl/include/rpl_mixed_clear_tables.inc 35 36# INSERT 37--echo 38--echo ******************** INSERT ******************** 39INSERT INTO t1 VALUES(1, 't1, text 1'); 40INSERT INTO t1 VALUES(2, UUID()); 41INSERT INTO t2 SELECT * FROM t1; 42INSERT INTO t2 VALUES (1, 't1, text 1') ON DUPLICATE KEY UPDATE b = 't2, text 1'; 43DELETE FROM t1 WHERE a = 2; 44DELETE FROM t2 WHERE a = 2; 45--source suite/rpl/include/rpl_mixed_check_select.inc 46--source suite/rpl/include/rpl_mixed_clear_tables.inc 47 48--echo 49--echo ******************** LOAD DATA INFILE ******************** 50--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR 51eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_mixed.dat' INTO TABLE t1 FIELDS TERMINATED BY '|' ; 52SELECT * FROM t1 ORDER BY a; 53--source suite/rpl/include/rpl_mixed_check_select.inc 54--source suite/rpl/include/rpl_mixed_clear_tables.inc 55 56# REPLACE 57--echo 58--echo ******************** REPLACE ******************** 59INSERT INTO t1 VALUES(1, 't1, text 1'); 60INSERT INTO t1 VALUES(2, 't1, text 2'); 61INSERT INTO t1 VALUES(3, 't1, text 3'); 62REPLACE INTO t1 VALUES(1, 't1, text 11'); 63REPLACE INTO t1 VALUES(2, UUID()); 64REPLACE INTO t1 SET a=3, b='t1, text 33'; 65DELETE FROM t1 WHERE a = 2; 66--source suite/rpl/include/rpl_mixed_check_select.inc 67--source suite/rpl/include/rpl_mixed_clear_tables.inc 68 69# SELECT 70--echo 71--echo ******************** SELECT ******************** 72INSERT INTO t1 VALUES(1, 't1, text 1'); 73SELECT * FROM t1 WHERE b <> UUID() ORDER BY a; 74--source suite/rpl/include/rpl_mixed_clear_tables.inc 75 76# JOIN 77--echo 78--echo ******************** JOIN ******************** 79INSERT INTO t1 VALUES(1, 'CCC'); 80INSERT INTO t1 VALUES(2, 'DDD'); 81INSERT INTO t2 VALUES(1, 'DDD'); 82INSERT INTO t2 VALUES(2, 'CCC'); 83SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a ORDER BY t1.a,t2.a; 84SELECT * FROM t1 INNER JOIN t2 ON t1.b = t2.b ORDER BY t1.a,t2.a; 85--source suite/rpl/include/rpl_mixed_clear_tables.inc 86 87# UNION 88--echo 89--echo ******************** UNION ******************** 90INSERT INTO t1 VALUES(1, 't1, text 1'); 91INSERT INTO t2 VALUES(1, 't2, text 1'); 92SELECT * FROM t1 UNION SELECT * FROM t2 WHERE t2.b <> UUID(); 93--source suite/rpl/include/rpl_mixed_clear_tables.inc 94 95# TRUNCATE 96--echo 97--echo ******************** TRUNCATE ******************** 98INSERT INTO t1 VALUES(1, 't1, text 1'); 99--source suite/rpl/include/rpl_mixed_check_select.inc 100TRUNCATE t1; 101--source suite/rpl/include/rpl_mixed_check_select.inc 102--source suite/rpl/include/rpl_mixed_clear_tables.inc 103 104# UPDATE 105--echo 106--echo ******************** UPDATE ******************** 107INSERT INTO t1 VALUES(1, 't1, text 1'); 108INSERT INTO t2 VALUES(1, 't2, text 1'); 109UPDATE t1 SET b = 't1, text 1 updated' WHERE a = 1; 110--source suite/rpl/include/rpl_mixed_check_select.inc 111UPDATE t1, t2 SET t1.b = 'test', t2.b = 'test'; 112--source suite/rpl/include/rpl_mixed_check_select.inc 113--source suite/rpl/include/rpl_mixed_clear_tables.inc 114 115# DESCRIBE 116--echo 117--echo ******************** DESCRIBE ******************** 118DESCRIBE t1; 119DESCRIBE t2 b; 120 121# USE 122--echo 123--echo ******************** USE ******************** 124USE test_rpl; 125 126# TRANSACTION 127--echo 128--echo ******************** TRANSACTION ******************** 129START TRANSACTION; 130INSERT INTO t1 VALUES (1, 'start'); 131COMMIT; 132--source suite/rpl/include/rpl_mixed_check_select.inc 133START TRANSACTION; 134INSERT INTO t1 VALUES (2, 'rollback'); 135ROLLBACK; 136--source suite/rpl/include/rpl_mixed_check_select.inc 137START TRANSACTION; 138INSERT INTO t1 VALUES (3, 'before savepoint s1'); 139SAVEPOINT s1; 140INSERT INTO t1 VALUES (4, 'after savepoint s1'); 141ROLLBACK TO SAVEPOINT s1; 142--source suite/rpl/include/rpl_mixed_check_select.inc 143START TRANSACTION; 144INSERT INTO t1 VALUES (5, 'before savepoint s2'); 145SAVEPOINT s2; 146INSERT INTO t1 VALUES (6, 'after savepoint s2'); 147INSERT INTO t1 VALUES (7, CONCAT('with UUID() ',UUID())); 148RELEASE SAVEPOINT s2; 149COMMIT; 150DELETE FROM t1 WHERE a = 7; 151--source suite/rpl/include/rpl_mixed_check_select.inc 152--source suite/rpl/include/rpl_mixed_clear_tables.inc 153 154# LOCK TABLES 155--echo 156--echo ******************** LOCK TABLES ******************** 157LOCK TABLES t1 READ , t2 READ; 158UNLOCK TABLES; 159 160# TRANSACTION ISOLATION LEVEL 161--echo 162--echo ******************** TRANSACTION ISOLATION LEVEL ******************** 163SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 164SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 165SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 166SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 167 168# XA 169# skipped 170 171# CREATE USER 172--echo 173--echo ******************** CREATE USER ******************** 174CREATE USER 'user_test_rpl'@'localhost' IDENTIFIED BY PASSWORD '*1111111111111111111111111111111111111111'; 175--source suite/rpl/include/rpl_mixed_check_user.inc 176 177# GRANT 178--echo 179--echo ******************** GRANT ******************** 180GRANT SELECT ON *.* TO 'user_test_rpl'@'localhost'; 181--source suite/rpl/include/rpl_mixed_check_user.inc 182 183# REVOKE 184--echo 185--echo ******************** REVOKE ******************** 186REVOKE SELECT ON *.* FROM 'user_test_rpl'@'localhost'; 187--source suite/rpl/include/rpl_mixed_check_user.inc 188 189# SET PASSWORD 190--echo 191--echo ******************** SET PASSWORD ******************** 192SET PASSWORD FOR 'user_test_rpl'@'localhost' = '*0000000000000000000000000000000000000000'; 193--source suite/rpl/include/rpl_mixed_check_user.inc 194 195# RENAME USER 196--echo 197--echo ******************** RENAME USER ******************** 198RENAME USER 'user_test_rpl'@'localhost' TO 'user_test_rpl_2'@'localhost'; 199--source suite/rpl/include/rpl_mixed_check_user.inc 200 201# DROP USER 202--echo 203--echo ******************** DROP USER ******************** 204DROP USER 'user_test_rpl_2'@'localhost'; 205--source suite/rpl/include/rpl_mixed_check_user.inc 206 207# Prepring for some following operations 208INSERT INTO t1 VALUES(100, 'test'); 209 210# ANALYZE 211--echo 212--echo ******************** ANALYZE ******************** 213ANALYZE TABLE t1; 214 215# BACKUP TABLE 216# skipped because deprecated 217 218# CHECK TABLE 219--echo 220--echo ******************** CHECK TABLE ******************** 221CHECK TABLE t1; 222 223# CHECKSUM TABLE 224--echo 225--echo ******************** CHECKSUM TABLE ******************** 226CHECKSUM TABLE t1; 227 228# OPTIMIZE TABLE 229--echo 230--echo ******************** OPTIMIZE TABLE ******************** 231OPTIMIZE TABLE t1; 232 233# REPAIR TABLE 234--echo 235--echo ******************** REPAIR TABLE ******************** 236REPAIR TABLE t1; 237 238# SET VARIABLE 239--echo 240--echo ******************** SET VARIABLE ******************** 241SET @test_rpl_var = 1; 242SHOW VARIABLES LIKE 'test_rpl_var'; 243 244# SHOW 245--echo 246--echo ******************** SHOW ******************** 247--source suite/rpl/include/rpl_mixed_check_db.inc 248 249 250# PROCEDURE 251--echo 252--echo ******************** PROCEDURE ******************** 253DELIMITER |; 254CREATE PROCEDURE p1 () 255BEGIN 256 UPDATE t1 SET b = 'test' WHERE a = 201; 257END| 258CREATE PROCEDURE p2 () 259BEGIN 260 UPDATE t1 SET b = UUID() WHERE a = 202; 261END| 262DELIMITER ;| 263INSERT INTO t1 VALUES(201, 'test 201'); 264CALL p1(); 265INSERT INTO t1 VALUES(202, 'test 202'); 266CALL p2(); 267DELETE FROM t1 WHERE a = 202; 268--source suite/rpl/include/rpl_mixed_check_select.inc 269ALTER PROCEDURE p1 COMMENT 'p1'; 270DROP PROCEDURE p1; 271DROP PROCEDURE p2; 272--source suite/rpl/include/rpl_mixed_clear_tables.inc 273 274# TRIGGER 275--echo 276--echo ******************** TRIGGER ******************** 277DELIMITER |; 278CREATE TRIGGER tr1 BEFORE INSERT ON t1 279FOR EACH ROW BEGIN 280 INSERT INTO t2 SET a = NEW.a, b = NEW.b; 281END| 282DELIMITER ;| 283INSERT INTO t1 VALUES (1, 'test'); 284--source suite/rpl/include/rpl_mixed_check_select.inc 285--source suite/rpl/include/rpl_mixed_clear_tables.inc 286DROP TRIGGER tr1; 287 288# EVENTS 289--echo 290--echo 291--echo ******************** EVENTS ******************** 292INSERT INTO t1 VALUES(1, 'test1'); 293CREATE EVENT e1 ON SCHEDULE EVERY '1' SECOND COMMENT 'e_second_comment' DO DELETE FROM t1; 294--source suite/rpl/include/rpl_mixed_check_event.inc 295--source suite/rpl/include/rpl_mixed_check_select.inc 296--sleep 2 297--source suite/rpl/include/rpl_mixed_check_select.inc 298ALTER EVENT e1 RENAME TO e2; 299--sleep 2 300--source suite/rpl/include/rpl_mixed_check_event.inc 301--source suite/rpl/include/rpl_mixed_check_select.inc 302DROP EVENT e2; 303--source suite/rpl/include/rpl_mixed_check_event.inc 304--source suite/rpl/include/rpl_mixed_clear_tables.inc 305 306# VIEWS 307--echo 308--echo ******************** VIEWS ******************** 309INSERT INTO t1 VALUES(1, 'test1'); 310INSERT INTO t1 VALUES(2, 'test2'); 311CREATE VIEW v1 AS SELECT * FROM t1 WHERE a = 1; 312CREATE VIEW v2 AS SELECT * FROM t1 WHERE b <> UUID(); 313--source suite/rpl/include/rpl_mixed_check_view.inc 314ALTER VIEW v1 AS SELECT * FROM t1 WHERE a = 2; 315--source suite/rpl/include/rpl_mixed_check_view.inc 316DROP VIEW v1; 317DROP VIEW v2; 318--source suite/rpl/include/rpl_mixed_clear_tables.inc 319 320# BINLOG EVENTS 321--echo 322--echo 323--echo ******************** SHOW BINLOG EVENTS ******************** 324--source include/show_binlog_events.inc 325sync_slave_with_master; 326# as we're using UUID we don't SELECT but use "diff" like in rpl_row_UUID 327--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test_rpl > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql 328--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test_rpl > $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql 329 330connection master; 331drop database test_rpl; 332sync_slave_with_master; 333 334# Let's compare. Note: If they match test will pass, if they do not match 335# the test will show that the diff statement failed and not reject file 336# will be created. You will need to go to the mysql-test dir and diff 337# the files your self to see what is not matching 338 339--diff_files $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_master.sql $MYSQLTEST_VARDIR/tmp/rpl_switch_stm_row_mixed_slave.sql 340--source include/rpl_end.inc 341