Hi Hema,
Approved, some suggestions below however:
1. Comments
# This test is created for WL#5059 <https://intranet.mysql.com/worklog/?tid=5059>
(Stress testing of mixed mode)
# The intention of this test case is to execute tests for mixed mode of
# replication by creating various cases which will lead to switch from statement
# to row based binary logging formats.
Maybe add a couple of comments here about what is being tested (e.g. UUID, USER,
CURRENT_USER, AUTO_INC update, FOUND_ROWS, ROW_COUNT, temporary table, view w/UUID()) and
maybe also
what will not be tested (or future enhancements to tests) (e.g. RAND(), SYS_DATE(),
CURRENT_TIME(),
NOW(), statements using system variables, LOAD_FILE(), INSERT DELAYED, UUID_SHORT(),
CONNECTION_ID())
).
2. view w/UUID:
--echo # Create view that uses UUID functions
CREATE VIEW db1.v1 AS SELECT UUID();
I suggest also using the view in an insert statement, e.g:
INSERT INTO db1.t3 SELECT FROM db1.v1;
3. triggers
Possibly add update/insert triggers with both BEFORE and AFTER trigger time - and
also delete triggers + delete/replace statements.
4. USER/CURRENT_USER
This is actually more a question than a suggestion:
Will having different user names on master and slave make a better test of the switch from
statement
to row based replication when the USER/CURRENT_USER functions are used? E.g. by renaming
the user on
the slave before using the USER/CURRENT_USER functions?
Regards,
Victor
> #At file:///export/home/tmp/Bug43549new/mysql-6.0-backup/ based on
> revid:hema@/stripped/
>
> 2872 Hema Sridharan 2009-09-30
> WL#5059 (Stress tests for mixed mode).
> The test is intended for stress tests of mixed mode where
> for some of the cases there is automatic switch from statement
> to row based format.
> added:
> mysql-test/suite/rpl/r/rpl_stress.result
> mysql-test/suite/rpl/t/rpl_stress.test
>
> per-file messages:
> mysql-test/suite/rpl/r/rpl_stress.result
> Result file for rpl_stress.test
> mysql-test/suite/rpl/t/rpl_stress.test
> New test added in repl suiterpl_stress.test
> === added file 'mysql-test/suite/rpl/r/rpl_stress.result'
> --- a/mysql-test/suite/rpl/r/rpl_stress.result 1970-01-01 00:00:00
> +0000
> +++ b/mysql-test/suite/rpl/r/rpl_stress.result 2009-09-29 23:18:36
> +0000
> @@ -0,0 +1,172 @@
> +stop slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +reset master;
> +reset slave;
> +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> +start slave;
> +# Connecting to master...
> +DROP DATABASE IF EXISTS db1;
> +CREATE DATABASE db1;
> +SET SESSION BINLOG_FORMAT=MIXED;
> +SET GLOBAL BINLOG_FORMAT=MIXED;
> +# Check the binlog formats
> +SHOW SESSION VARIABLES LIKE "binlog_format%";
> +Variable_name Value
> +binlog_format MIXED
> +SELECT @@GLOBAL.BINLOG_FORMAT, @@SESSION.BINLOG_FORMAT;
> +@@GLOBAL.BINLOG_FORMAT @@SESSION.BINLOG_FORMAT
> +MIXED MIXED
> +# Create tables and insert data
> +CREATE TABLE db1.t1(id INT, a CHAR(10));
> +CREATE TABLE db1.t2
> +(id INT NOT NULL AUTO_INCREMENT, a CHAR(100), PRIMARY
> KEY(id))ENGINE=InnoDB;
> +CREATE TABLE db1.t3(s VARCHAR(100));
> +CREATE TABLE db1.log (
> +i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
> +entry_dsc CHAR(100)
> +) ENGINE=MyISAM;
> +INSERT INTO db1.t1 VALUES(1, 'x'),(2, 'y'),(3,'w'),(4,'z');
> +INSERT INTO db1.t2 VALUES(1,UUID()),(2,'b'),(3,UUID());
> +INSERT INTO db1.t3 VALUES('p'),('q'),('r');
> +SELECT * FROM db1.t1;
> +id a
> +1 x
> +2 y
> +3 w
> +4 z
> +SELECT * FROM db1.t3;
> +s
> +p
> +q
> +r
> +# Create stored procedure with UUID functions
> +CREATE PROCEDURE db1.p1()
> +BEGIN
> +INSERT INTO db1.t1 VALUES(100, UUID());
> +END;||
> +CREATE FUNCTION db1.f1(a VARCHAR(100)) RETURNS BIGINT UNSIGNED
> +BEGIN +CALL db1.p1();
> +RETURN 100;
> +END;||
> +SELECT db1.f1(concat("foo6_2_",UUID()));||
> +db1.f1(concat("foo6_2_",UUID()))
> +100
> +Warnings:
> +Warning 1265 Data truncated for column 'a' at row 1
> +# Create triggers
> +CREATE TRIGGER db1.trg BEFORE INSERT ON db1.t3 FOR EACH ROW
> +BEGIN
> +SET NEW.s = concat(NEW.s,UUID());
> +END;||
> +INSERT INTO db1.t3 VALUES('TESTING');||
> +CREATE TRIGGER db1.trg2 AFTER UPDATE on db1.t2 FOR EACH ROW
> +BEGIN
> +INSERT INTO db1.log (entry_dsc) VALUES (concat('Update row ',
> old.id,' ',
> +old.a, '->', new.a));
> +END||
> +UPDATE db1.t2 SET a='try' WHERE id=1;
> +# Connecting to slave...
> +# Set the binlog format to row and create temporary table. The
> convert +# to mixed based format and see what happens.
> +SET SESSION BINLOG_FORMAT=ROW;
> +SET GLOBAL BINLOG_FORMAT=ROW;
> +CREATE TEMPORARY TABLE tmp(a CHAR(100));
> +INSERT INTO tmp VALUES("testing");
> +SET BINLOG_FORMAT=MIXED;
> +ERROR HY000: Cannot switch out of the row-based binary log format
> when the session has
> open temporary tables
> +DROP TABLE tmp;
> +SET SESSION BINLOG_FORMAT=MIXED;
> +SET GLOBAL BINLOG_FORMAT=MIXED;
> +# Connecting to master...
> +# Create view that uses UUID functions
> +CREATE VIEW db1.v1 AS SELECT UUID();
> +# Creating functions using USER() and CURRENT_USER()
> +CREATE FUNCTION m_user()
> +RETURNS VARCHAR(64)
> +BEGIN +DECLARE user VARCHAR(64);
> +SELECT USER() INTO user;
> +RETURN user;
> +END ;||
> +CREATE FUNCTION m_current_user()
> +RETURNS VARCHAR(64)
> +BEGIN
> +DECLARE user VARCHAR(64);
> +SELECT CURRENT_USER() INTO user;
> +RETURN user;
> +END ;||
> +CREATE TABLE db1.u (name VARCHAR(64));
> +INSERT INTO db1.u VALUES(USER());
> +INSERT INTO db1.u VALUES (m_user());
> +INSERT INTO db1.u VALUES (CURRENT_USER());
> +INSERT INTO db1.u VALUES (m_current_user());
> +SELECT * FROM db1.u;
> +name
> +root@localhost
> +root@localhost
> +root@localhost
> +root@localhost
> +CREATE TABLE db1.tt(id INT);
> +CREATE TABLE db1.ltb (sect INT, test INT, count INT);
> +INSERT INTO db1.tt VALUES(1),(2),(3);
> +INSERT INTO db1.tt VALUES(1),(0),(10),(12),(10),(2),(20),(21),
> (40),(30);
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id >5 LIMIT 1;
> +id
> +10
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO db1.ltb VALUES(1,1,@a);
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id < 5 ORDER BY id
> LIMIT 1;
> +id
> +0
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO db1.ltb VALUES(1,1,@a);
> +SELECT * FROM db1.ltb;
> +sect test count
> +1 1 7
> +1 1 6
> +CREATE PROCEDURE db1.pp(sect INT, test INT) BEGIN
> +INSERT INTO db1.ltb VALUES (sect,test,FOUND_ROWS());
> +END;||
> +CALL db1.pp(1,1);
> +SELECT * FROM db1.ltb WHERE sect = 1 ORDER BY sect,test;
> +sect test count
> +1 1 7
> +1 1 6
> +1 1 2
> +# Connecting to slave...
> +# Exercise other objects
> +SHOW DATABASES;
> +Database
> +information_schema
> +db1
> +mtr
> +mysql
> +test
> +SHOW TABLES FROM db1;
> +Tables_in_db1
> +log
> +ltb
> +t1
> +t2
> +t3
> +tt
> +u
> +v1
> +SELECT * FROM db1.ltb;
> +sect test count
> +1 1 7
> +1 1 6
> +1 1 2
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id > 5 ORDER BY id
> LIMIT 1;
> +id
> +10
> +# We are using non deterministic functions while tables, procedures and
> +# functions are created. Therefore, will compare the master dump and
> +# slave dump
> +# Diff the master dump and slave dump. If the diff files match, then
> the +# test will pass or else will fail but reject file will not be
> created.
> +# Test cleanup
> +DROP PROCEDURE db1.p1;
> +DROP PROCEDURE db1.pp;
> +DROP DATABASE db1;
>
> === added file 'mysql-test/suite/rpl/t/rpl_stress.test'
> --- a/mysql-test/suite/rpl/t/rpl_stress.test 1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_stress.test 2009-09-29 23:18:36 +0000
> @@ -0,0 +1,187 @@
> +# This test is created for WL#5059 (Stress testing of mixed mode)
> +# The intention of this test case is to execute tests for mixed mode of
> +# replication by creating various cases which will lead to switch
> from statement
> +# to row based binary logging formats.
> +
> +--source include/master-slave.inc
> +--source include/have_innodb.inc
> +--source include/have_binlog_format_mixed.inc
> +
> +--echo # Connecting to master...
> +connection master;
> +
> +--disable_warnings
> +DROP DATABASE IF EXISTS db1;
> +CREATE DATABASE db1;
> +--enable_warnings
> +
> +SET SESSION BINLOG_FORMAT=MIXED;
> +SET GLOBAL BINLOG_FORMAT=MIXED;
> +
> +--echo # Check the binlog formats
> +SHOW SESSION VARIABLES LIKE "binlog_format%";
> +SELECT @@GLOBAL.BINLOG_FORMAT, @@SESSION.BINLOG_FORMAT;
> +
> +--echo # Create tables and insert data
> +
> +CREATE TABLE db1.t1(id INT, a CHAR(10));
> +CREATE TABLE db1.t2
> +(id INT NOT NULL AUTO_INCREMENT, a CHAR(100), PRIMARY
> KEY(id))ENGINE=InnoDB;
> +CREATE TABLE db1.t3(s VARCHAR(100));
> +
> +CREATE TABLE db1.log (
> + i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
> + entry_dsc CHAR(100)
> +) ENGINE=MyISAM;
> +
> +INSERT INTO db1.t1 VALUES(1, 'x'),(2, 'y'),(3,'w'),(4,'z');
> +INSERT INTO db1.t2 VALUES(1,UUID()),(2,'b'),(3,UUID());
> +INSERT INTO db1.t3 VALUES('p'),('q'),('r');
> +
> +SELECT * FROM db1.t1;
> +SELECT * FROM db1.t3;
> +
> +--echo # Create stored procedure with UUID functions
> +
> +DELIMITER ||;
> +
> +CREATE PROCEDURE db1.p1()
> +BEGIN
> +INSERT INTO db1.t1 VALUES(100, UUID());
> +END;||
> +
> +CREATE FUNCTION db1.f1(a VARCHAR(100)) RETURNS BIGINT UNSIGNED
> +BEGIN +CALL db1.p1();
> +RETURN 100;
> +END;||
> +
> +SELECT db1.f1(concat("foo6_2_",UUID()));||
> +
> +--echo # Create triggers
> +
> +CREATE TRIGGER db1.trg BEFORE INSERT ON db1.t3 FOR EACH ROW
> +BEGIN
> +SET NEW.s = concat(NEW.s,UUID());
> +END;||
> +
> +INSERT INTO db1.t3 VALUES('TESTING');||
> +
> +CREATE TRIGGER db1.trg2 AFTER UPDATE on db1.t2 FOR EACH ROW
> +BEGIN
> + INSERT INTO db1.log (entry_dsc) VALUES (concat('Update row ',
> old.id,' ',
> +old.a, '->', new.a));
> +END||
> +DELIMITER ;||
> +
> +UPDATE db1.t2 SET a='try' WHERE id=1;
> +
> +--echo # Connecting to slave...
> +sync_slave_with_master;
> +connection slave;
> +
> +--echo # Set the binlog format to row and create temporary table. The
> convert +--echo # to mixed based format and see what happens.
> +
> +SET SESSION BINLOG_FORMAT=ROW;
> +SET GLOBAL BINLOG_FORMAT=ROW;
> +
> +CREATE TEMPORARY TABLE tmp(a CHAR(100));
> +INSERT INTO tmp VALUES("testing");
> +--error ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
> +SET BINLOG_FORMAT=MIXED;
> +
> +DROP TABLE tmp;
> +
> +SET SESSION BINLOG_FORMAT=MIXED;
> +SET GLOBAL BINLOG_FORMAT=MIXED;
> +
> +--echo # Connecting to master...
> +connection master;
> +
> +--echo # Create view that uses UUID functions
> +CREATE VIEW db1.v1 AS SELECT UUID();
> +
> +DELIMITER ||;
> +--echo # Creating functions using USER() and CURRENT_USER()
> +
> +CREATE FUNCTION m_user()
> + RETURNS VARCHAR(64)
> +BEGIN + DECLARE user VARCHAR(64);
> + SELECT USER() INTO user;
> + RETURN user;
> +END ;||
> +
> +CREATE FUNCTION m_current_user()
> + RETURNS VARCHAR(64)
> +BEGIN
> + DECLARE user VARCHAR(64);
> + SELECT CURRENT_USER() INTO user;
> + RETURN user;
> +END ;||
> +
> +DELIMITER ;||
> +
> +CREATE TABLE db1.u (name VARCHAR(64));
> +INSERT INTO db1.u VALUES(USER());
> +INSERT INTO db1.u VALUES (m_user());
> +INSERT INTO db1.u VALUES (CURRENT_USER());
> +INSERT INTO db1.u VALUES (m_current_user());
> +
> +SELECT * FROM db1.u;
> +
> +CREATE TABLE db1.tt(id INT);
> +CREATE TABLE db1.ltb (sect INT, test INT, count INT);
> +
> +INSERT INTO db1.tt VALUES(1),(2),(3);
> +INSERT INTO db1.tt VALUES(1),(0),(10),(12),(10),(2),(20),(21),
> (40),(30);
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id >5 LIMIT 1;
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO db1.ltb VALUES(1,1,@a);
> +
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id < 5 ORDER BY id
> LIMIT 1;
> +SELECT FOUND_ROWS() INTO @a;
> +INSERT INTO db1.ltb VALUES(1,1,@a);
> +
> +SELECT * FROM db1.ltb;
> +
> +DELIMITER ||;
> +CREATE PROCEDURE db1.pp(sect INT, test INT) BEGIN
> + INSERT INTO db1.ltb VALUES (sect,test,FOUND_ROWS());
> +END;||
> +DELIMITER ;||
> +
> +CALL db1.pp(1,1);
> +SELECT * FROM db1.ltb WHERE sect = 1 ORDER BY sect,test;
> +
> +--echo # Connecting to slave...
> +sync_slave_with_master;
> +connection slave;
> +
> +--echo # Exercise other objects
> +SHOW DATABASES;
> +SHOW TABLES FROM db1;
> +
> +SELECT * FROM db1.ltb;
> +SELECT SQL_CALC_FOUND_ROWS * FROM db1.tt WHERE id > 5 ORDER BY id
> LIMIT 1;
> +
> +--echo # We are using non deterministic functions while tables,
> procedures and
> +--echo # functions are created. Therefore, will compare the master
> dump and +--echo # slave dump
> +
> +--exec $MYSQL_DUMP --compact --order-by-primary
> --skip-extended-insert --no-create-info
> db1 > $MYSQLTEST_VARDIR/tmp/rpl_stress_master.sql
> +--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary
> --skip-extended-insert
> --no-create-info db1 > $MYSQLTEST_VARDIR/tmp/rpl_stress_slave.sql
> +
> +--echo # Diff the master dump and slave dump. If the diff files
> match, then the +--echo # test will pass or else will fail but reject
> file will not be created.
> +
> +diff_files $MYSQLTEST_VARDIR/tmp/rpl_stress_master.sql
> $MYSQLTEST_VARDIR/tmp/rpl_stress_slave.sql;
> +
> +connection master;
> +--echo # Test cleanup
> +
> +DROP PROCEDURE db1.p1;
> +DROP PROCEDURE db1.pp;
> +DROP DATABASE db1;
>
| Thread |
|---|
| • Re: bzr commit into mysql-6.0-backup branch (hema:2872) WL#5059 | Victor Kirkebo | 27 Oct |
