1# ==== Purpose ==== 2# 3# Test verifies that there is no deadlock or assertion in 4# slave_parallel_mode=optimistic configuration while applying admin command 5# like 'OPTIMIZE TABLE', 'REPAIR TABLE' and 'ANALYZE TABLE'. 6# 7# ==== Implementation ==== 8# 9# Steps: 10# 0 - Create a table, execute OPTIMIZE TABLE command on the table followed 11# by some DMLS. 12# 1 - No assert should happen on slave server. 13# 2 - Assert that 'OPTIMIZE TABLE', 'REPAIR TABLE' and 'ANALYZE TABLE' are 14# marked as 'DDL' in the binary log. 15# 16# ==== References ==== 17# 18# MDEV-17515: GTID Replication in optimistic mode deadlock 19# 20--source include/have_partition.inc 21--source include/have_innodb.inc 22--let $rpl_topology=1->2 23--source include/rpl_init.inc 24 25--connection server_1 26FLUSH TABLES; 27ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB; 28 29--connection server_2 30SET @save_slave_parallel_threads= @@GLOBAL.slave_parallel_threads; 31SET @save_slave_parallel_mode= @@GLOBAL.slave_parallel_mode; 32--source include/stop_slave.inc 33SET GLOBAL slave_parallel_threads=2; 34SET GLOBAL slave_parallel_mode=optimistic; 35--source include/start_slave.inc 36 37--connection server_1 38CREATE TABLE t1(a INT) ENGINE=INNODB; 39OPTIMIZE TABLE t1; 40INSERT INTO t1 VALUES(1); 41INSERT INTO t1 SELECT 1+a FROM t1; 42INSERT INTO t1 SELECT 2+a FROM t1; 43--save_master_pos 44 45--connection server_2 46--sync_with_master 47 48--echo # 49--echo # Verify that following admin commands are marked as ddl 50--echo # 'OPTIMIZE TABLE', 'REPAIR TABLE' and 'ANALYZE TABLE' 51--echo # 52--connection server_1 53 54OPTIMIZE TABLE t1; 55--let optimize_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 56 57REPAIR TABLE t1; 58--let repair_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 59 60ANALYZE TABLE t1; 61--let analyze_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 62 63let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); 64FLUSH LOGS; 65 66--let $MYSQLD_DATADIR= `select @@datadir` 67--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 68 69--let SEARCH_PATTERN= GTID $optimize_gtid ddl 70--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 71--source include/search_pattern_in_file.inc 72 73--let SEARCH_PATTERN= GTID $repair_gtid ddl 74--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 75--source include/search_pattern_in_file.inc 76 77--let SEARCH_PATTERN= GTID $analyze_gtid ddl 78--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 79--source include/search_pattern_in_file.inc 80 81--echo # 82--echo # Clean up 83--echo # 84DROP TABLE t1; 85--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 86--save_master_pos 87 88--connection server_2 89--sync_with_master 90FLUSH LOGS; 91 92--echo # 93--echo # Check that ALTER TABLE commands with ANALYZE, OPTIMIZE and REPAIR on 94--echo # partitions will be marked as DDL in binary log. 95--echo # 96--connection server_1 97CREATE TABLE t1(id INT) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (100), 98 PARTITION pmax VALUES LESS THAN (MAXVALUE)); 99INSERT INTO t1 VALUES (1), (10), (100), (1000); 100 101ALTER TABLE t1 ANALYZE PARTITION p0; 102--let analyze_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 103 104ALTER TABLE t1 OPTIMIZE PARTITION p0; 105--let optimize_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 106 107ALTER TABLE t1 REPAIR PARTITION p0; 108--let repair_gtid= `SELECT @@GLOBAL.gtid_binlog_pos` 109 110let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1); 111FLUSH LOGS; 112 113--exec $MYSQL_BINLOG $MYSQLD_DATADIR/$binlog_file > $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 114 115--let SEARCH_PATTERN= GTID $analyze_gtid ddl 116--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 117--source include/search_pattern_in_file.inc 118 119--let SEARCH_PATTERN= GTID $optimize_gtid ddl 120--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 121--source include/search_pattern_in_file.inc 122 123--let SEARCH_PATTERN= GTID $repair_gtid ddl 124--let SEARCH_FILE= $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 125--source include/search_pattern_in_file.inc 126 127--echo # 128--echo # Clean up 129--echo # 130DROP TABLE t1; 131--remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog.out 132--save_master_pos 133 134--connection server_2 135--sync_with_master 136 137--source include/stop_slave.inc 138SET GLOBAL slave_parallel_threads= @save_slave_parallel_threads; 139SET GLOBAL slave_parallel_mode= @save_slave_parallel_mode; 140--source include/start_slave.inc 141 142--source include/rpl_end.inc 143