MySQL Test Framework for Troubleshooting

用于故障排除的MySQL测试框架

展开查看详情

1.MySQL Test Framework for Troubleshooting February, 04, 2018 Sveta Smirnova

2.What my Family Thinks I Do 2

3.What my Boss Thinks I Do 3

4.What I Really Do 4

5.I Investigate • Why customer’s SQL works wrongly 5

6.I Investigate • Why customer’s SQL works wrongly • Is this true with all versions? 5

7.I Investigate • Why customer’s SQL works wrongly • Is this true with all versions? • With all branches? 5

8.I Often Need to Test • Customer’s server version 6

9.I Often Need to Test • Customer’s server version • Same major branch, but latest version 6

10.I Often Need to Test • Customer’s server version • Same major branch, but latest version • Latest version 6

11.I Often Need to Test • Customer’s server version • Same major branch, but latest version • Latest version • Upstream 6

12.I Often Need to Test • Customer’s server version • Same major branch, but latest version • Latest version • Upstream • Other forks (e.g. MariaDB) 6

13.I Often Need to Test • Customer’s server version • Same major branch, but latest version • Latest version • Upstream • Other forks (e.g. MariaDB) • Same scenario 6

14.Test Manually? Not an Option! 7

15.Solutions • MySQL Sandbox • Complicated installations with single command • Scripts to start/stop/restart mysqld • Does NOT enter code for you! 8

16.Solutions • MySQL Sandbox • Docker • Runs everything you wish with single command • Not much version variations pre-installed • You need to write Dockerfile if you want Custom setup Custom options Run SQL inside Docker • Does NOT check results! 8

17.Solutions • MySQL Sandbox • Docker • MySQL Test Framework 8

18.SQL you are Used to $cat mtr_test_1.test #--source include/have_innodb.inc source include/have_innodb.inc; CREATE TABLE ‘t‘ ( ‘id‘ int(11) NOT NULL, ‘f‘ varchar(100) DEFAULT NULL, PRIMARY KEY (‘id‘) ) ENGINE=InnoDB; insert into t values(12345, ’value1’), (54321, ’value2’); select * from t; show create table t; drop table if exists t; 9

19.Custom Options sveta@delly:~/build/ps-5.7/mysql-test$ cat suite/mtr_test/t/mtr_test_2-master.opt --transaction-isolation=read-committed 10

20.Concurrent Execution --source include/have_innodb.inc CREATE TABLE ‘t‘ (‘id‘ int(11) NOT NULL, ‘f‘ varchar(100) DEFAULT NULL, PRIMARY KEY (‘id‘)) ENGINE=InnoDB; insert into t values(12345, ’value1’), (54321, ’value2’); begin; select * from t; --connect(addconroot,localhost,root,,) --connection addconroot begin; update t set f=’foo’ where id=12345; commit; --connection default select * from t; 11 drop table if exists t;

21.Error Handling --source include/have_innodb.inc CREATE TABLE ‘t‘ (‘id‘ int(11) NOT NULL, ‘f‘ varchar(100) DEFAULT NULL, PRIMARY KEY (‘id‘)) ENGINE=InnoDB; insert into t values(12345, ’value1’), (54321, ’value2’); begin; select * from t where id=12345 lock in share mode; --connect(addconroot,localhost,root,,) --connection addconroot set innodb_lock_wait_timeout=3; begin; --error 1205 update t set f=’value3’ where id=12345; rollback; --connection default rollback; drop table if exists t; 12

22.Result Processing ... --connect(addconroot,localhost,root,,) --connection addconroot begin; select * from t where id=54321 for update; --connection default --send update t set f=’value3’ where id=54321 --connection addconroot update t set f=’value3’ where id=12345; --connection default --error 1213 --reap ... 13

23.External Commands ... --exec $MYSQL_DUMP test > $MYSQL_TEST_DIR/var/tmp/mtr_amer_5.sql --exec ls $MYSQL_TEST_DIR/var/tmp/ drop table t; show tables; --exec $MYSQL test < $MYSQL_TEST_DIR/var/tmp/mtr_amer_5.sql show tables; select * from t; ... 14

24.Flow Control --source include/have_innodb.inc CREATE TABLE ‘t‘ (‘id‘ int(11) NOT NULL, ‘f‘ varchar(100) DEFAULT NULL, PRIMARY KEY (‘id‘)) ENGINE=InnoDB; --disable_query_log --let $c=1 while ($c<100) { --eval insert into t values($c, md5($c)) --inc $c } --enable_query_log 15

25.Replication from the Box sveta@delly:~/build/ps-5.7/mysql-test$ cat suite/mtr_test/t/rpl_mtr_test_7.test --source include/master-slave.inc --source include/have_innodb.inc # We are on master CREATE TABLE ‘t‘ (‘id‘ int(11) NOT NULL, ‘f‘ varchar(100) DEFAULT NULL, PRIMARY KEY (‘id‘)) ENGINE=InnoDB; insert into t values(12345, ’value1’), (54321, ’value2’); select * from t; show create table t; --connection slave --vertical_results show slave status; --connection master --horizontal_results update t set f=’foo’; 16

26.Replication from the Box #We are on master --sync_slave_with_master #We are on slave select * from t; --connection master drop table if exists t; --sync_slave_with_master stop slave; 16

27.Replication Options • On master sveta@delly:~/build/ps-5.7/mysql-test$ cat suite/mtr_test/t/rpl_mtr_test_7-master.opt --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --binlog-format=row 17

28.Replication Options • On master sveta@delly:~/build/ps-5.7/mysql-test$ cat suite/mtr_test/t/rpl_mtr_test_7-master.opt --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --binlog-format=row • On slave sveta@delly:~/build/ps-5.7/mysql-test$ cat suite/mtr_test/t/rpl_mtr_test_7-slave.opt --gtid_mode=ON --log-slave-updates --enforce-gtid-consistency --binlog-format=row 17

29.Any Complicated Setup --let $rpl_topology= 1->2,2->3,3->1 --source include/rpl_init.inc 18