MySQL Test Framework for Troubleshooting

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