1include/rpl_init.inc [topology=1->2] 2connection server_2; 3call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase"); 4SET GLOBAL gtid_pos_auto_engines="innodb"; 5ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first 6include/stop_slave.inc 7CHANGE MASTER TO master_use_gtid=slave_pos; 8SELECT @@gtid_pos_auto_engines; 9@@gtid_pos_auto_engines 10 11SELECT @@SESSION.gtid_pos_auto_engines; 12ERROR HY000: Variable 'gtid_pos_auto_engines' is a GLOBAL variable 13SET GLOBAL gtid_pos_auto_engines= NULL; 14ERROR 42000: Variable 'gtid_pos_auto_engines' can't be set to the value of 'NULL' 15SET GLOBAL gtid_pos_auto_engines="innodb"; 16SELECT @@gtid_pos_auto_engines; 17@@gtid_pos_auto_engines 18InnoDB 19SET GLOBAL gtid_pos_auto_engines="myisam,innodb"; 20SELECT @@gtid_pos_auto_engines; 21@@gtid_pos_auto_engines 22MyISAM,InnoDB 23SET GLOBAL gtid_pos_auto_engines="innodb,myisam"; 24SELECT @@gtid_pos_auto_engines; 25@@gtid_pos_auto_engines 26InnoDB,MyISAM 27SET GLOBAL gtid_pos_auto_engines="innodb,innodb,myisam,innodb,myisam,myisam,innodb"; 28SELECT @@gtid_pos_auto_engines; 29@@gtid_pos_auto_engines 30InnoDB,MyISAM 31SET GLOBAL gtid_pos_auto_engines=DEFAULT; 32SELECT @@gtid_pos_auto_engines; 33@@gtid_pos_auto_engines 34 35SET GLOBAL gtid_pos_auto_engines=""; 36SELECT @@gtid_pos_auto_engines; 37@@gtid_pos_auto_engines 38 39include/start_slave.inc 40connection server_1; 41CREATE TABLE t1 (a INT PRIMARY KEY); 42INSERT INTO t1 VALUES (1); 43SELECT * FROM t1 ORDER BY a; 44a 451 46connection server_2; 47SELECT * FROM t1 ORDER BY a; 48a 491 50include/stop_slave.inc 51SET sql_log_bin=0; 52ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria; 53CREATE TABLE mysql.gtid_slave_pos_innodb LIKE mysql.gtid_slave_pos; 54ALTER TABLE mysql.gtid_slave_pos_innodb ENGINE=InnoDB; 55INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos; 56TRUNCATE mysql.gtid_slave_pos; 57SET sql_log_bin=1; 58connection server_1; 59INSERT INTO t1 VALUES (2); 60INSERT INTO t1 VALUES (3); 61SELECT * FROM t1 ORDER BY a; 62a 631 642 653 66include/save_master_gtid.inc 67*** Restart server with --gtid-pos-auto-engines=innodb,myisam *** 68connection server_2; 69include/sync_with_master_gtid.inc 70SELECT * FROM t1 ORDER BY a; 71a 721 732 743 75*** Verify no new gtid_slave_pos* tables are created *** 76SELECT table_name, engine FROM information_schema.tables 77WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 78 ORDER BY table_name; 79table_name engine 80gtid_slave_pos Aria 81gtid_slave_pos_innodb InnoDB 82SELECT @@gtid_pos_auto_engines; 83@@gtid_pos_auto_engines 84InnoDB,MyISAM 85include/stop_slave.inc 86SET sql_log_bin=0; 87INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos; 88DROP TABLE mysql.gtid_slave_pos; 89RENAME TABLE mysql.gtid_slave_pos_innodb TO mysql.gtid_slave_pos; 90SET sql_log_bin=1; 91connection server_1; 92CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 93INSERT INTO t1 VALUES (4); 94INSERT INTO t2 VALUES (1); 95SELECT * FROM t1 ORDER BY a; 96a 971 982 993 1004 101SELECT * FROM t2 ORDER BY a; 102a 1031 104include/save_master_gtid.inc 105*** Restart server with --gtid-pos-auto-engines=myisam,innodb *** 106connection server_2; 107include/sync_with_master_gtid.inc 108SELECT * FROM t1 ORDER BY a; 109a 1101 1112 1123 1134 114SELECT * FROM t2 ORDER BY a; 115a 1161 117*** Verify that no new gtid_slave_pos* tables are auto-created *** 118SELECT table_name, engine FROM information_schema.tables 119WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 120 ORDER BY table_name; 121table_name engine 122gtid_slave_pos InnoDB 123include/stop_slave.inc 124SET sql_log_bin=0; 125ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria; 126SET sql_log_bin=1; 127connection server_1; 128INSERT INTO t1 VALUES (5); 129INSERT INTO t2 VALUES (2); 130SELECT * FROM t1 ORDER BY a; 131a 1321 1332 1343 1354 1365 137SELECT * FROM t2 ORDER BY a; 138a 1391 1402 141include/save_master_gtid.inc 142connection server_2; 143include/sync_with_master_gtid.inc 144SELECT * FROM t1 ORDER BY a; 145a 1461 1472 1483 1494 1505 151SELECT * FROM t2 ORDER BY a; 152a 1531 1542 155*** Verify that mysql.gtid_slave_pos_InnoDB is auto-created *** 156SELECT lower(table_name), engine FROM information_schema.tables 157WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 158 ORDER BY table_name; 159lower(table_name) engine 160gtid_slave_pos Aria 161gtid_slave_pos_innodb InnoDB 162include/stop_slave.inc 163SET sql_log_bin=0; 164INSERT INTO mysql.gtid_slave_pos SELECT * FROM mysql.gtid_slave_pos_InnoDB; 165DROP TABLE mysql.gtid_slave_pos_InnoDB; 166SET sql_log_bin=1; 167connection server_1; 168INSERT INTO t1 VALUES (6); 169INSERT INTO t2 VALUES (3); 170SELECT * FROM t1 ORDER BY a; 171a 1721 1732 1743 1754 1765 1776 178SELECT * FROM t2 ORDER BY a; 179a 1801 1812 1823 183include/save_master_gtid.inc 184*** Restart server without --gtid-pos-auto-engines *** 185connection server_2; 186include/sync_with_master_gtid.inc 187SELECT * FROM t1 ORDER BY a; 188a 1891 1902 1913 1924 1935 1946 195SELECT * FROM t2 ORDER BY a; 196a 1971 1982 1993 200*** Verify that no mysql.gtid_slave_pos* table is auto-created *** 201SELECT table_name, engine FROM information_schema.tables 202WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 203 ORDER BY table_name; 204table_name engine 205gtid_slave_pos Aria 206SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id; 207domain_id max(seq_no) 2080 11 209include/stop_slave.inc 210SET GLOBAL gtid_pos_auto_engines="innodb"; 211include/start_slave.inc 212connection server_1; 213INSERT INTO t1 VALUES (7); 214INSERT INTO t2 VALUES (4); 215SELECT * FROM t1 ORDER BY a; 216a 2171 2182 2193 2204 2215 2226 2237 224SELECT * FROM t2 ORDER BY a; 225a 2261 2272 2283 2294 230include/save_master_gtid.inc 231connection server_2; 232include/sync_with_master_gtid.inc 233SELECT * FROM t1 ORDER BY a; 234a 2351 2362 2373 2384 2395 2406 2417 242SELECT * FROM t2 ORDER BY a; 243a 2441 2452 2463 2474 248*** Verify that mysql.gtid_slave_pos_InnoDB is auto-created *** 249SELECT lower(table_name), engine FROM information_schema.tables 250WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%' 251 ORDER BY table_name; 252lower(table_name) engine 253gtid_slave_pos Aria 254gtid_slave_pos_innodb InnoDB 255SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id; 256domain_id max(seq_no) 2570 13 258connection server_2; 259*** Restart the slave server to prove 'gtid_slave_pos_innodb' autodiscovery *** 260connection server_2; 261SELECT max(seq_no) FROM mysql.gtid_slave_pos_InnoDB into @seq_no; 262Warnings: 263Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 264connection server_1; 265INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2; 266include/save_master_gtid.inc 267connection server_2; 268include/sync_with_master_gtid.inc 269include/stop_slave.inc 270SET GLOBAL gtid_pos_auto_engines=""; 271SET sql_log_bin=0; 272DROP TABLE mysql.gtid_slave_pos_InnoDB; 273SET sql_log_bin=1; 274include/start_slave.inc 275connection server_1; 276DROP TABLE t1, t2; 277include/rpl_end.inc 278