1--source include/have_innodb.inc 2--let $rpl_topology=1->2 3--source include/rpl_init.inc 4 5--connection server_2 6call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase"); 7 8--error ER_SLAVE_MUST_STOP 9SET GLOBAL gtid_pos_auto_engines="innodb"; 10--source include/stop_slave.inc 11CHANGE MASTER TO master_use_gtid=slave_pos; 12 13# Test the @@gtid_pos_auto_engines sysvar. 14SELECT @@gtid_pos_auto_engines; 15--error ER_INCORRECT_GLOBAL_LOCAL_VAR 16SELECT @@SESSION.gtid_pos_auto_engines; 17--error ER_WRONG_VALUE_FOR_VAR 18SET GLOBAL gtid_pos_auto_engines= NULL; 19SET GLOBAL gtid_pos_auto_engines="innodb"; 20SELECT @@gtid_pos_auto_engines; 21SET GLOBAL gtid_pos_auto_engines="myisam,innodb"; 22SELECT @@gtid_pos_auto_engines; 23SET GLOBAL gtid_pos_auto_engines="innodb,myisam"; 24SELECT @@gtid_pos_auto_engines; 25SET GLOBAL gtid_pos_auto_engines="innodb,innodb,myisam,innodb,myisam,myisam,innodb"; 26SELECT @@gtid_pos_auto_engines; 27SET GLOBAL gtid_pos_auto_engines=DEFAULT; 28SELECT @@gtid_pos_auto_engines; 29SET GLOBAL gtid_pos_auto_engines=""; 30SELECT @@gtid_pos_auto_engines; 31 32--source include/start_slave.inc 33 34--connection server_1 35CREATE TABLE t1 (a INT PRIMARY KEY); 36INSERT INTO t1 VALUES (1); 37SELECT * FROM t1 ORDER BY a; 38--save_master_pos 39 40--connection server_2 41--sync_with_master 42SELECT * FROM t1 ORDER BY a; 43--source include/stop_slave.inc 44SET sql_log_bin=0; 45# Reset storage engine for mysql.gtid_slave_pos in case an earlier test 46# might have changed it to InnoDB. 47ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria; 48CREATE TABLE mysql.gtid_slave_pos_innodb LIKE mysql.gtid_slave_pos; 49ALTER TABLE mysql.gtid_slave_pos_innodb ENGINE=InnoDB; 50INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos; 51TRUNCATE mysql.gtid_slave_pos; 52SET sql_log_bin=1; 53 54# Restart the slave mysqld server, and verify that the GTID position is 55# read correctly from the new mysql.gtid_slave_pos_innodb table. 56 57--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 58wait 59EOF 60--shutdown_server 61--source include/wait_until_disconnected.inc 62 63--connection server_1 64INSERT INTO t1 VALUES (2); 65INSERT INTO t1 VALUES (3); 66SELECT * FROM t1 ORDER BY a; 67--source include/save_master_gtid.inc 68 69# Let the slave mysqld server start again. 70# As we are restarting, also take the opportunity to test --gtid-pos-auto-engines 71--echo *** Restart server with --gtid-pos-auto-engines=innodb,myisam *** 72--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 73restart: --skip-slave-start=0 --gtid-pos-auto-engines=innodb,myisam 74EOF 75 76--connection server_2 77--enable_reconnect 78--source include/wait_until_connected_again.inc 79 80--source include/sync_with_master_gtid.inc 81SELECT * FROM t1 ORDER BY a; 82 83--echo *** Verify no new gtid_slave_pos* tables are created *** 84SELECT table_name, engine FROM information_schema.tables 85 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 86 ORDER BY table_name; 87 88SELECT @@gtid_pos_auto_engines; 89--source include/stop_slave.inc 90SET sql_log_bin=0; 91INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos; 92DROP TABLE mysql.gtid_slave_pos; 93RENAME TABLE mysql.gtid_slave_pos_innodb TO mysql.gtid_slave_pos; 94SET sql_log_bin=1; 95 96--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 97wait 98EOF 99--shutdown_server 100--source include/wait_until_disconnected.inc 101 102--connection server_1 103CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 104INSERT INTO t1 VALUES (4); 105INSERT INTO t2 VALUES (1); 106SELECT * FROM t1 ORDER BY a; 107SELECT * FROM t2 ORDER BY a; 108--source include/save_master_gtid.inc 109 110--echo *** Restart server with --gtid-pos-auto-engines=myisam,innodb *** 111--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 112restart: --skip-slave-start=0 --gtid-pos-auto-engines=myisam,innodb 113EOF 114 115--connection server_2 116--enable_reconnect 117--source include/wait_until_connected_again.inc 118 119--source include/sync_with_master_gtid.inc 120SELECT * FROM t1 ORDER BY a; 121SELECT * FROM t2 ORDER BY a; 122 123--echo *** Verify that no new gtid_slave_pos* tables are auto-created *** 124SELECT table_name, engine FROM information_schema.tables 125 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 126 ORDER BY table_name; 127 128 129--source include/stop_slave.inc 130SET sql_log_bin=0; 131ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria; 132SET sql_log_bin=1; 133 134--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 135wait 136EOF 137--shutdown_server 138--source include/wait_until_disconnected.inc 139 140--connection server_1 141INSERT INTO t1 VALUES (5); 142INSERT INTO t2 VALUES (2); 143SELECT * FROM t1 ORDER BY a; 144SELECT * FROM t2 ORDER BY a; 145--source include/save_master_gtid.inc 146 147--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 148--echo *** Restart server with --gtid-pos-auto-engines=innodb *** 149restart: --skip-slave-start=0 --gtid-pos-auto-engines=innodb 150EOF 151 152--connection server_2 153--enable_reconnect 154--source include/wait_until_connected_again.inc 155 156--source include/sync_with_master_gtid.inc 157SELECT * FROM t1 ORDER BY a; 158SELECT * FROM t2 ORDER BY a; 159 160--echo *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created *** 161# Note, the create happens asynchronously, so wait for it. 162let $wait_condition= 163 SELECT EXISTS (SELECT * FROM information_schema.tables 164 WHERE table_schema='mysql' AND table_name='gtid_slave_pos_InnoDB'); 165--source include/wait_condition.inc 166# MDEV-15373 lowercases 'table_name' to satisfy --lower-case-table-names options 167SELECT lower(table_name), engine FROM information_schema.tables 168 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 169 ORDER BY table_name; 170 171 172--source include/stop_slave.inc 173SET sql_log_bin=0; 174INSERT INTO mysql.gtid_slave_pos SELECT * FROM mysql.gtid_slave_pos_InnoDB; 175DROP TABLE mysql.gtid_slave_pos_InnoDB; 176SET sql_log_bin=1; 177 178--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 179wait 180EOF 181--shutdown_server 182--source include/wait_until_disconnected.inc 183 184--connection server_1 185INSERT INTO t1 VALUES (6); 186INSERT INTO t2 VALUES (3); 187SELECT * FROM t1 ORDER BY a; 188SELECT * FROM t2 ORDER BY a; 189--source include/save_master_gtid.inc 190 191--echo *** Restart server without --gtid-pos-auto-engines *** 192--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 193restart: --skip-slave-start=0 194EOF 195 196--connection server_2 197--enable_reconnect 198--source include/wait_until_connected_again.inc 199 200--source include/sync_with_master_gtid.inc 201SELECT * FROM t1 ORDER BY a; 202SELECT * FROM t2 ORDER BY a; 203 204--echo *** Verify that no mysql.gtid_slave_pos* table is auto-created *** 205SELECT table_name, engine FROM information_schema.tables 206 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 207 ORDER BY table_name; 208SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id; 209 210--source include/stop_slave.inc 211SET GLOBAL gtid_pos_auto_engines="innodb"; 212--source include/start_slave.inc 213 214--connection server_1 215INSERT INTO t1 VALUES (7); 216INSERT INTO t2 VALUES (4); 217SELECT * FROM t1 ORDER BY a; 218SELECT * FROM t2 ORDER BY a; 219--source include/save_master_gtid.inc 220 221--connection server_2 222--source include/sync_with_master_gtid.inc 223SELECT * FROM t1 ORDER BY a; 224SELECT * FROM t2 ORDER BY a; 225 226--echo *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created *** 227let $wait_condition= 228 SELECT EXISTS (SELECT * FROM information_schema.tables 229 WHERE table_schema='mysql' AND table_name='gtid_slave_pos_InnoDB'); 230--source include/wait_condition.inc 231SELECT lower(table_name), engine FROM information_schema.tables 232 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 233 ORDER BY table_name; 234SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id; 235 236# Check that the auto-created InnoDB table starts being used without 237# needing slave restart. The auto-create happens asynchronously, so it 238# is non-deterministic when it will start being used. But we can wait 239# for it to happen. 240 241--let $count=300 242--let $done=0 243--let $old_silent= $keep_include_silent 244--let $keep_include_silent= 1 245--disable_query_log 246while (!$done) 247{ 248 --connection server_1 249 INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2; 250 --source include/save_master_gtid.inc 251 252 --connection server_2 253 --source include/sync_with_master_gtid.inc 254 --let $done=`SELECT COUNT(*) > 0 FROM mysql.gtid_slave_pos_InnoDB` 255 if (!$done) 256 { 257 dec $count; 258 if (!$count) 259 { 260 SELECT * FROM mysql.gtid_slave_pos_InnoDB; 261 --die Timeout waiting for mysql.gtid_slave_pos_InnoDB to be used 262 } 263 real_sleep 0.1; 264 } 265} 266--enable_query_log 267--let $keep_include_silent=$old_silent 268# Note that at this point, the contents of table t2, as well as the GTID 269# position, is non-deterministic. 270 271# MDEV-15373 engine gtid_slave_pos table name disobeys lower-case-table-names 272# This snippet verifies that engine gtid_slave_pos table is found, 273# its data are up-to-date. 274--write_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 275wait 276EOF 277--connection server_2 278--shutdown_server 279--source include/wait_until_disconnected.inc 280 281--echo *** Restart the slave server to prove 'gtid_slave_pos_innodb' autodiscovery *** 282--append_file $MYSQLTEST_VARDIR/tmp/mysqld.2.expect 283restart: --skip-slave-start=0 284EOF 285 286--connection server_2 287--enable_reconnect 288--source include/wait_until_connected_again.inc 289SELECT max(seq_no) FROM mysql.gtid_slave_pos_InnoDB into @seq_no; 290 291--connection server_1 292INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2; 293--source include/save_master_gtid.inc 294 295--connection server_2 296--source include/sync_with_master_gtid.inc 297if (`SELECT max(seq_no) <> @seq_no + 1 FROM mysql.gtid_slave_pos_InnoDB`) 298{ 299 SELECT * FROM mysql.gtid_slave_pos_InnoDB; 300 --die Inconsistent table 301} 302# 303# end of MDEV-15373 304 305#--connection server_2 306--source include/stop_slave.inc 307SET GLOBAL gtid_pos_auto_engines=""; 308SET sql_log_bin=0; 309DROP TABLE mysql.gtid_slave_pos_InnoDB; 310SET sql_log_bin=1; 311--source include/start_slave.inc 312 313--connection server_1 314DROP TABLE t1, t2; 315 316--source include/rpl_end.inc 317