Debian Codegraph @

mysql-test/suite/rpl/t/rpl_mdev12179.test

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