Debian Codegraph @

mysql-test/suite/rpl/r/rpl_mdev12179.result

raw
1 include/rpl_init.inc [topology=1->2]
2 connection server_2;
3 call mtr.add_suppression("The automatically created table.*name may not be entirely in lowercase");
4 SET GLOBAL gtid_pos_auto_engines="innodb";
5 ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first
6 include/stop_slave.inc
7 CHANGE MASTER TO master_use_gtid=slave_pos;
8 SELECT @@gtid_pos_auto_engines;
9 @@gtid_pos_auto_engines
10
11 SELECT @@SESSION.gtid_pos_auto_engines;
12 ERROR HY000: Variable 'gtid_pos_auto_engines' is a GLOBAL variable
13 SET GLOBAL gtid_pos_auto_engines= NULL;
14 ERROR 42000: Variable 'gtid_pos_auto_engines' can't be set to the value of 'NULL'
15 SET GLOBAL gtid_pos_auto_engines="innodb";
16 SELECT @@gtid_pos_auto_engines;
17 @@gtid_pos_auto_engines
18 InnoDB
19 SET GLOBAL gtid_pos_auto_engines="myisam,innodb";
20 SELECT @@gtid_pos_auto_engines;
21 @@gtid_pos_auto_engines
22 MyISAM,InnoDB
23 SET GLOBAL gtid_pos_auto_engines="innodb,myisam";
24 SELECT @@gtid_pos_auto_engines;
25 @@gtid_pos_auto_engines
26 InnoDB,MyISAM
27 SET GLOBAL gtid_pos_auto_engines="innodb,innodb,myisam,innodb,myisam,myisam,innodb";
28 SELECT @@gtid_pos_auto_engines;
29 @@gtid_pos_auto_engines
30 InnoDB,MyISAM
31 SET GLOBAL gtid_pos_auto_engines=DEFAULT;
32 SELECT @@gtid_pos_auto_engines;
33 @@gtid_pos_auto_engines
34
35 SET GLOBAL gtid_pos_auto_engines="";
36 SELECT @@gtid_pos_auto_engines;
37 @@gtid_pos_auto_engines
38
39 include/start_slave.inc
40 connection server_1;
41 CREATE TABLE t1 (a INT PRIMARY KEY);
42 INSERT INTO t1 VALUES (1);
43 SELECT * FROM t1 ORDER BY a;
44 a
45 1
46 connection server_2;
47 SELECT * FROM t1 ORDER BY a;
48 a
49 1
50 include/stop_slave.inc
51 SET sql_log_bin=0;
52 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
53 CREATE TABLE mysql.gtid_slave_pos_innodb LIKE mysql.gtid_slave_pos;
54 ALTER TABLE mysql.gtid_slave_pos_innodb ENGINE=InnoDB;
55 INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos;
56 TRUNCATE mysql.gtid_slave_pos;
57 SET sql_log_bin=1;
58 connection server_1;
59 INSERT INTO t1 VALUES (2);
60 INSERT INTO t1 VALUES (3);
61 SELECT * FROM t1 ORDER BY a;
62 a
63 1
64 2
65 3
66 include/save_master_gtid.inc
67 *** Restart server with --gtid-pos-auto-engines=innodb,myisam ***
68 connection server_2;
69 include/sync_with_master_gtid.inc
70 SELECT * FROM t1 ORDER BY a;
71 a
72 1
73 2
74 3
75 *** Verify no new gtid_slave_pos* tables are created ***
76 SELECT table_name, engine FROM information_schema.tables
77 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
78 ORDER BY table_name;
79 table_name engine
80 gtid_slave_pos Aria
81 gtid_slave_pos_innodb InnoDB
82 SELECT @@gtid_pos_auto_engines;
83 @@gtid_pos_auto_engines
84 InnoDB,MyISAM
85 include/stop_slave.inc
86 SET sql_log_bin=0;
87 INSERT INTO mysql.gtid_slave_pos_innodb SELECT * FROM mysql.gtid_slave_pos;
88 DROP TABLE mysql.gtid_slave_pos;
89 RENAME TABLE mysql.gtid_slave_pos_innodb TO mysql.gtid_slave_pos;
90 SET sql_log_bin=1;
91 connection server_1;
92 CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
93 INSERT INTO t1 VALUES (4);
94 INSERT INTO t2 VALUES (1);
95 SELECT * FROM t1 ORDER BY a;
96 a
97 1
98 2
99 3
100 4
101 SELECT * FROM t2 ORDER BY a;
102 a
103 1
104 include/save_master_gtid.inc
105 *** Restart server with --gtid-pos-auto-engines=myisam,innodb ***
106 connection server_2;
107 include/sync_with_master_gtid.inc
108 SELECT * FROM t1 ORDER BY a;
109 a
110 1
111 2
112 3
113 4
114 SELECT * FROM t2 ORDER BY a;
115 a
116 1
117 *** Verify that no new gtid_slave_pos* tables are auto-created ***
118 SELECT table_name, engine FROM information_schema.tables
119 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
120 ORDER BY table_name;
121 table_name engine
122 gtid_slave_pos InnoDB
123 include/stop_slave.inc
124 SET sql_log_bin=0;
125 ALTER TABLE mysql.gtid_slave_pos ENGINE=Aria;
126 SET sql_log_bin=1;
127 connection server_1;
128 INSERT INTO t1 VALUES (5);
129 INSERT INTO t2 VALUES (2);
130 SELECT * FROM t1 ORDER BY a;
131 a
132 1
133 2
134 3
135 4
136 5
137 SELECT * FROM t2 ORDER BY a;
138 a
139 1
140 2
141 include/save_master_gtid.inc
142 connection server_2;
143 include/sync_with_master_gtid.inc
144 SELECT * FROM t1 ORDER BY a;
145 a
146 1
147 2
148 3
149 4
150 5
151 SELECT * FROM t2 ORDER BY a;
152 a
153 1
154 2
155 *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created ***
156 SELECT lower(table_name), engine FROM information_schema.tables
157 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
158 ORDER BY table_name;
159 lower(table_name) engine
160 gtid_slave_pos Aria
161 gtid_slave_pos_innodb InnoDB
162 include/stop_slave.inc
163 SET sql_log_bin=0;
164 INSERT INTO mysql.gtid_slave_pos SELECT * FROM mysql.gtid_slave_pos_InnoDB;
165 DROP TABLE mysql.gtid_slave_pos_InnoDB;
166 SET sql_log_bin=1;
167 connection server_1;
168 INSERT INTO t1 VALUES (6);
169 INSERT INTO t2 VALUES (3);
170 SELECT * FROM t1 ORDER BY a;
171 a
172 1
173 2
174 3
175 4
176 5
177 6
178 SELECT * FROM t2 ORDER BY a;
179 a
180 1
181 2
182 3
183 include/save_master_gtid.inc
184 *** Restart server without --gtid-pos-auto-engines ***
185 connection server_2;
186 include/sync_with_master_gtid.inc
187 SELECT * FROM t1 ORDER BY a;
188 a
189 1
190 2
191 3
192 4
193 5
194 6
195 SELECT * FROM t2 ORDER BY a;
196 a
197 1
198 2
199 3
200 *** Verify that no mysql.gtid_slave_pos* table is auto-created ***
201 SELECT table_name, engine FROM information_schema.tables
202 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
203 ORDER BY table_name;
204 table_name engine
205 gtid_slave_pos Aria
206 SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id;
207 domain_id max(seq_no)
208 0 11
209 include/stop_slave.inc
210 SET GLOBAL gtid_pos_auto_engines="innodb";
211 include/start_slave.inc
212 connection server_1;
213 INSERT INTO t1 VALUES (7);
214 INSERT INTO t2 VALUES (4);
215 SELECT * FROM t1 ORDER BY a;
216 a
217 1
218 2
219 3
220 4
221 5
222 6
223 7
224 SELECT * FROM t2 ORDER BY a;
225 a
226 1
227 2
228 3
229 4
230 include/save_master_gtid.inc
231 connection server_2;
232 include/sync_with_master_gtid.inc
233 SELECT * FROM t1 ORDER BY a;
234 a
235 1
236 2
237 3
238 4
239 5
240 6
241 7
242 SELECT * FROM t2 ORDER BY a;
243 a
244 1
245 2
246 3
247 4
248 *** Verify that mysql.gtid_slave_pos_InnoDB is auto-created ***
249 SELECT lower(table_name), engine FROM information_schema.tables
250 WHERE table_schema='mysql' AND table_name LIKE 'gtid_slave_pos%'
251 ORDER BY table_name;
252 lower(table_name) engine
253 gtid_slave_pos Aria
254 gtid_slave_pos_innodb InnoDB
255 SELECT domain_id, max(seq_no) FROM mysql.gtid_slave_pos GROUP BY domain_id;
256 domain_id max(seq_no)
257 0 13
258 connection server_2;
259 *** Restart the slave server to prove 'gtid_slave_pos_innodb' autodiscovery ***
260 connection server_2;
261 SELECT max(seq_no) FROM mysql.gtid_slave_pos_InnoDB into @seq_no;
262 connection server_1;
263 INSERT INTO t2(a) SELECT 1+MAX(a) FROM t2;
264 include/save_master_gtid.inc
265 connection server_2;
266 include/sync_with_master_gtid.inc
267 include/stop_slave.inc
268 SET GLOBAL gtid_pos_auto_engines="";
269 SET sql_log_bin=0;
270 DROP TABLE mysql.gtid_slave_pos_InnoDB;
271 SET sql_log_bin=1;
272 include/start_slave.inc
273 connection server_1;
274 DROP TABLE t1, t2;
275 include/rpl_end.inc