Debian Codegraph @

mysql-test/suite/ndb_ddl/metadata_immediate_sync.test

raw
1 --source setup.inc
2 --source suite/ndb/include/backup_restore_setup.inc
3
4 #
5 # Case 1: Basic schema detection + synchronization test using
6 # the ndb_metadata_sync option. This is a duplicate of
7 # case 1 in the metadata_sync test.
8 # - Cause mismatch between NDB Dictionary and DD
9 # - Check if these mismatches are detected properly by
10 # Ndb_metadata_change_monitor thread
11 # - Check if these mismatches detected are synchronized
12 #
13
14 # Create logfile group, tablespace, and tables in addition to
15 # those created in setup.inc
16 CREATE LOGFILE GROUP lg1
17 ADD UNDOFILE 'lg1_undofile.dat'
18 INITIAL_SIZE 1M
19 UNDO_BUFFER_SIZE = 1M
20 ENGINE NDB;
21
22 CREATE TABLESPACE ts1
23 ADD DATAFILE 'ts1_datafile.dat'
24 USE LOGFILE GROUP lg1
25 INITIAL_SIZE 2M
26 ENGINE NDB;
27
28 USE ndb_ddl_test;
29
30 CREATE TABLE t10 (
31 a INT PRIMARY KEY,
32 b VARCHAR(255)
33 ) ENGINE NDB
34 TABLESPACE ts1
35 STORAGE DISK;
36
37 INSERT INTO t10 VALUES(1, 'Beggars can\'t be choosers');
38
39 CREATE DATABASE ndb_ddl_test3;
40 USE ndb_ddl_test3;
41
42 CREATE TABLE t1 (
43 a INT,
44 b INT,
45 PRIMARY KEY(a,b)
46 ) ENGINE NDB;
47
48 CREATE TABLE t2(
49 a INT PRIMARY KEY,
50 b VARCHAR(255)
51 ) ENGINE NDB;
52
53 # backup
54 --disable_query_log
55 --source include/ndb_backup.inc
56 --enable_query_log
57
58 # Create a few more databases and tests
59 CREATE DATABASE ndb_ddl_test4;
60 USE ndb_ddl_test4;
61
62 CREATE TABLE t1 (
63 a INT,
64 b INT,
65 PRIMARY KEY(a,b)
66 ) ENGINE NDB;
67
68 CREATE TABLE t2(
69 a INT PRIMARY KEY,
70 b VARCHAR(255)
71 ) ENGINE NDB;
72
73 CREATE DATABASE ndb_ddl_test5;
74 USE ndb_ddl_test5;
75
76 CREATE TABLE t1 (
77 a INT,
78 b INT,
79 PRIMARY KEY(a,b)
80 ) ENGINE NDB;
81
82 USE ndb_ddl_test;
83
84 # Drop half the tables from ndb_ddl_test database
85 --let $counter = 6
86 while ($counter <= 10)
87 {
88 let $tx=t$counter;
89 --eval DROP TABLE $tx
90 --inc $counter
91 }
92
93 ALTER TABLESPACE ts1
94 DROP DATAFILE 'ts1_datafile.dat';
95 DROP TABLESPACE ts1;
96 DROP LOGFILE GROUP lg1
97 ENGINE NDB;
98
99 # Drop all tables in ndb_ddl_test3
100 USE ndb_ddl_test3;
101 DROP TABLE t1;
102 DROP TABLE t2;
103
104 # Restore into NDB, skip tables in ndb_ddl_test database that already exist
105 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --exclude-tables=ndb_ddl_test.t1,ndb_ddl_test.t2,ndb_ddl_test.t3,ndb_ddl_test.t4,ndb_ddl_test.t5 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
106 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r --exclude-tables=ndb_ddl_test.t1,ndb_ddl_test.t2,ndb_ddl_test.t3,ndb_ddl_test.t4,ndb_ddl_test.t5 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
107
108 # Drop tables from NDB only to cause mismatch
109 --exec $NDB_DROP_TABLE -d ndb_ddl_test4 t1 >> $NDB_TOOLS_OUTPUT
110 --exec $NDB_DROP_TABLE -d ndb_ddl_test5 t1 >> $NDB_TOOLS_OUTPUT
111
112 # Wait until the changes have been synced
113 --let $max_wait = 60
114 --source wait_immediate_metadata_sync.inc
115
116 # Check if the metadata is as expected after synchronization
117 SHOW DATABASES;
118 USE ndb_ddl_test;
119 SHOW TABLES;
120 USE ndb_ddl_test3;
121 SHOW TABLES;
122 USE ndb_ddl_test4;
123 SHOW TABLES;
124 USE ndb_ddl_test5;
125 SHOW TABLES;
126
127 # Run some DDL statements and queries on the tables synced
128 --sorted_result
129 SELECT * FROM ndb_ddl_test.t6;
130 --sorted_result
131 SELECT * FROM ndb_ddl_test.t8;
132 --sorted_result
133 SELECT * FROM ndb_ddl_test.t10;
134 ALTER TABLE ndb_ddl_test3.t1
135 ADD COLUMN c VARCHAR(255);
136 INSERT INTO ndb_ddl_test3.t1 VALUES(1, 1, 'Donkey\'s praise');
137 SELECT * FROM ndb_ddl_test3.t1;
138 --error ER_NO_SUCH_TABLE
139 SHOW CREATE TABLE ndb_ddl_test4.t1;
140 --error ER_NO_SUCH_TABLE
141 SHOW CREATE TABLE ndb_ddl_test5.t1;
142
143 # Check if tablespace and logfile group work fine after sync
144 CREATE TABLESPACE ts2
145 ADD DATAFILE 'ts2_datafile.dat'
146 USE LOGFILE GROUP lg1
147 INITIAL_SIZE 2M
148 ENGINE NDB;
149
150 ALTER TABLESPACE ts1
151 ADD DATAFILE 'ts1_datafile2.dat';
152 CREATE TABLE ndb_ddl_test4.t1 (
153 a INT PRIMARY KEY,
154 b INT
155 ) ENGINE NDB;
156 INSERT INTO ndb_ddl_test4.t1 VALUES(1,1);
157 SELECT * FROM ndb_ddl_test4.t1;
158
159 # Cleanup
160 DROP DATABASE ndb_ddl_test3;
161 DROP DATABASE ndb_ddl_test4;
162 DROP DATABASE ndb_ddl_test5;
163 --source cleanup.inc
164 ALTER TABLESPACE ts1
165 DROP DATAFILE 'ts1_datafile.dat';
166 ALTER TABLESPACE ts1
167 DROP DATAFILE 'ts1_datafile2.dat';
168 DROP TABLESPACE ts1;
169 ALTER TABLESPACE ts2
170 DROP DATAFILE 'ts2_datafile.dat';
171 DROP TABLESPACE ts2;
172 DROP LOGFILE GROUP lg1
173 ENGINE NDB;
174
175 #
176 # Case 2: Automatic synchronization of schemata.
177 # This is a duplicate of case 2 in the metadata_sync test
178 # - Cause mismatch in terms of schema between NDB Dictionary and DD
179 # - Check if the schema mismatch is detected
180 # - Check if the schema mismatch is synchronized
181 #
182
183 # db1 - Only NDB tables
184 CREATE DATABASE db1;
185 USE db1;
186 CREATE TABLE t1_ndb (
187 a INT PRIMARY KEY,
188 b INT
189 ) ENGINE NDB;
190 CREATE TABLE t2_ndb (
191 a INT PRIMARY KEY,
192 b VARCHAR(255)
193 ) ENGINE NDB;
194
195 # db2 - Only NDB tables
196 CREATE DATABASE db2;
197 USE db2;
198 CREATE TABLE t1_ndb (
199 a INT PRIMARY KEY,
200 b INT
201 ) ENGINE NDB;
202 CREATE TABLE t2_ndb (
203 a INT PRIMARY KEY,
204 b VARCHAR(255)
205 ) ENGINE NDB;
206
207 # db3 - Only InnoDB tables
208 CREATE DATABASE db3;
209 USE db3;
210 CREATE TABLE t1_innodb (
211 a INT PRIMARY KEY,
212 b INT
213 );
214 CREATE TABLE t2_innodb (
215 a INT PRIMARY KEY,
216 b VARCHAR(255)
217 );
218
219 # db4 - Both NDB and InnoDB tables
220 CREATE DATABASE db4;
221 USE db4;
222 CREATE TABLE t1_ndb (
223 a INT PRIMARY KEY,
224 b INT
225 ) ENGINE NDB;
226 CREATE TABLE t2_ndb (
227 a INT PRIMARY KEY,
228 b VARCHAR(255)
229 ) ENGINE NDB;
230 CREATE TABLE t1_innodb (
231 a INT PRIMARY KEY,
232 b INT
233 );
234 CREATE TABLE t2_innodb (
235 a INT PRIMARY KEY,
236 b VARCHAR(255)
237 );
238
239 # backup
240 --disable_query_log
241 --source include/ndb_backup.inc
242 --enable_query_log
243
244 # Cause mismatch by dropping db1 and db4 and restoring metadata
245 DROP DATABASE db1;
246 DROP DATABASE db4;
247
248 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m --exclude-databases=db2 $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
249
250 #
251 # At this point the state of the schemata is as follows:
252 # - db1: Used in NDB Dictionary, non-existent in DD
253 # - db2: Used in NDB Dictionary, exists in DD
254 # - db3: Not used in NDB Dictionary, exists in DD
255 # - db4: Used in NDB Dictionary, non-existent in DD
256 #
257 # The auto sync mechanism will create db1 and db4 in the DD.
258 # It will also synchronize all NDB tables contained in db1 and db4
259 #
260
261 # Wait until the changes have been synced
262 --let $max_wait = 60
263 --source wait_immediate_metadata_sync.inc
264
265 # Check if the objects have been synchronized as expected
266 SHOW DATABASES LIKE 'db%';
267 USE db1;
268 SHOW TABLES;
269 SHOW CREATE TABLE t1_ndb;
270 SHOW CREATE TABLE t2_ndb;
271 USE db2;
272 SHOW TABLES;
273 USE db3;
274 SHOW TABLES;
275 USE db4;
276 SHOW TABLES;
277 SHOW CREATE TABLE t1_ndb;
278 SHOW CREATE TABLE t2_ndb;
279
280 # Clean up
281 DROP DATABASE db1;
282 DROP DATABASE db2;
283 DROP DATABASE db3;
284 DROP DATABASE db4;
285
286 #
287 # Case 3: Automatic synchronization of tables restored
288 # with the --disable-indexes option.
289 # This is a variant of the metadata_sync_restore_disable_index test
290 # - Cause mismatch by restoring tables with --disable-indexes
291 # - Wait until the tables are excluded after 10 unsuccessful tries
292 # - Rebuild indexes and discover tables
293 # - Clear the excluded objects list
294 #
295
296 # Create tables with various indexes
297 USE test;
298 CREATE TABLE t1 (
299 a INT PRIMARY KEY,
300 b INT NOT NULL,
301 c INT NOT NULL,
302 UNIQUE ib(b),
303 INDEX ic(c)
304 ) ENGINE NDB;
305
306 INSERT INTO t1 VALUES (1,2,3),(2,3,5),(3,4,6),(4,5,8),(5,6,2),(6,7,2);
307
308 CREATE TABLE parent (
309 c1 INT PRIMARY KEY
310 ) ENGINE NDB;
311
312 INSERT INTO parent VALUES (1),(2);
313
314 CREATE TABLE child (
315 c1 INT PRIMARY KEY,
316 c2 INT,
317 INDEX xc2(c2),
318 FOREIGN KEY(c2)
319 REFERENCES parent(c1)
320 ON DELETE CASCADE
321 ) ENGINE NDB;
322
323 INSERT INTO child VALUES (1,1),(2,2);
324
325 --echo Backup
326 --source include/ndb_backup.inc
327
328 DROP TABLE t1,child,parent;
329
330 # Suppress expected errors
331 --disable_query_log
332 call mtr.add_suppression("Metadata check has failed");
333 call mtr.add_suppression("The NDB Dictionary table definition is not identical to the DD table definition");
334 call mtr.add_suppression("Diff in 'index_count'");
335 --enable_query_log
336
337 --echo Restore with --disable-indexes
338 --exec $NDB_RESTORE -b $the_backup_id -n 1 -m -r --disable-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
339 --exec $NDB_RESTORE -b $the_backup_id -n 2 -r $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
340
341 # Wait until the changes have been synced
342 --let $max_wait = 60
343 --source wait_immediate_metadata_sync.inc
344
345 --sorted_result
346 SELECT schema_name, name, type FROM performance_schema.ndb_sync_excluded_objects;
347
348 --echo Rebuild indexes in NDB
349 --exec $NDB_RESTORE -b $the_backup_id -n 1 --rebuild-indexes $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
350
351 --echo Retry sync now that the indexes have been created
352 --let $max_wait = 60
353 --source wait_immediate_metadata_sync.inc
354
355 # Check that the tables have been synced
356 SHOW TABLES;
357 --sorted_result
358 SELECT * FROM t1;
359 --sorted_result
360 SELECT * FROM parent;
361 --sorted_result
362 SELECT * FROM child;
363
364 # Verify that no objects are excluded
365 SELECT COUNT(*) FROM performance_schema.ndb_sync_excluded_objects;
366
367 # Clean-up
368 DROP TABLE t1,child,parent;
369
370 #
371 # Case 4: Mock tables created with foreign_key_checks set to 0.
372 # Mock tables are created when foreign_key_checks is set to 0
373 # and a child table has a foreign key referencing a parent table
374 # that "does not exist". These tables should not be automatically
375 # synced.
376 #
377
378 SET @old_foreign_key_checks = @@foreign_key_checks;
379 SET foreign_key_checks=0;
380
381 # Create child table referencing non-existent parent table
382 CREATE TABLE child (
383 c1 INT PRIMARY KEY,
384 c2 INT,
385 INDEX xc2(c2),
386 FOREIGN KEY(c2)
387 REFERENCES parent(c1)
388 ON DELETE CASCADE
389 ) ENGINE NDB;
390
391 INSERT INTO child VALUES (1,1),(2,2);
392
393 # Wait until all changes have been synced
394 --let $max_wait = 30
395 --source wait_immediate_metadata_sync.inc
396
397 # Sync of mock tables fails every time so we check if any such
398 # sync was attempted by looking at the ndb_sync_excluded_objects
399 # table. The table should contain zero rows which indicates that
400 # no mock tables were picked up for sync.
401 SELECT CASE COUNT(*) WHEN 0 THEN 'YES' ELSE 'NO' END AS mock_tables_sync_skipped
402 FROM performance_schema.ndb_sync_excluded_objects;
403
404 # Clean-up
405 DROP TABLE child;
406 SET foreign_key_checks = @old_foreign_key_checks;
407 --source suite/ndb/include/backup_restore_cleanup.inc
408 --remove_file $NDB_TOOLS_OUTPUT