1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
|
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'lg1_undofile.dat'
INITIAL_SIZE 1M
UNDO_BUFFER_SIZE = 1M
ENGINE NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'ts1_datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 2M
ENGINE NDB;
USE ndb_ddl_test;
CREATE TABLE t10 (
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB
TABLESPACE ts1
STORAGE DISK;
INSERT INTO t10 VALUES(1, 'Beggars can\'t be choosers');
CREATE DATABASE ndb_ddl_test3;
USE ndb_ddl_test3;
CREATE TABLE t1 (
a INT,
b INT,
PRIMARY KEY(a,b)
) ENGINE NDB;
CREATE TABLE t2(
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB;
CREATE DATABASE ndb_ddl_test4;
USE ndb_ddl_test4;
CREATE TABLE t1 (
a INT,
b INT,
PRIMARY KEY(a,b)
) ENGINE NDB;
CREATE TABLE t2(
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB;
CREATE DATABASE ndb_ddl_test5;
USE ndb_ddl_test5;
CREATE TABLE t1 (
a INT,
b INT,
PRIMARY KEY(a,b)
) ENGINE NDB;
USE ndb_ddl_test;
DROP TABLE t6;
DROP TABLE t7;
DROP TABLE t8;
DROP TABLE t9;
DROP TABLE t10;
ALTER TABLESPACE ts1
DROP DATAFILE 'ts1_datafile.dat';
DROP TABLESPACE ts1;
DROP LOGFILE GROUP lg1
ENGINE NDB;
USE ndb_ddl_test3;
DROP TABLE t1;
DROP TABLE t2;
SET GLOBAL ndb_metadata_sync = true;
SHOW DATABASES;
Database
information_schema
mtr
mysql
ndb_ddl_test
ndb_ddl_test2
ndb_ddl_test3
ndb_ddl_test4
ndb_ddl_test5
ndbinfo
performance_schema
sys
test
USE ndb_ddl_test;
SHOW TABLES;
Tables_in_ndb_ddl_test
t1
t10
t2
t3
t4
t5
t6
t7
t8
t9
USE ndb_ddl_test3;
SHOW TABLES;
Tables_in_ndb_ddl_test3
t1
t2
USE ndb_ddl_test4;
SHOW TABLES;
Tables_in_ndb_ddl_test4
t2
USE ndb_ddl_test5;
SHOW TABLES;
Tables_in_ndb_ddl_test5
SELECT * FROM ndb_ddl_test.t6;
a
value1
value2
value3
value4
value5
SELECT * FROM ndb_ddl_test.t8;
a b c d e
1 1-t8 2017-10-20 09:13:01 NULL 1
2 2-t8 2017-10-20 09:13:02 NULL 2
3 3-t8 2017-10-20 09:13:03 NULL 3
4 4-t8 2017-10-20 09:13:04 NULL 4
5 5-t8 2017-10-20 09:13:05 NULL 5
SELECT * FROM ndb_ddl_test.t10;
a b
1 Beggars can't be choosers
ALTER TABLE ndb_ddl_test3.t1
ADD COLUMN c VARCHAR(255);
Warnings:
Warning 1478 Converted FIXED field 'c' to DYNAMIC to enable online ADD COLUMN
INSERT INTO ndb_ddl_test3.t1 VALUES(1, 1, 'Donkey\'s praise');
SELECT * FROM ndb_ddl_test3.t1;
a b c
1 1 Donkey's praise
SHOW CREATE TABLE ndb_ddl_test4.t1;
ERROR 42S02: Table 'ndb_ddl_test4.t1' doesn't exist
SHOW CREATE TABLE ndb_ddl_test5.t1;
ERROR 42S02: Table 'ndb_ddl_test5.t1' doesn't exist
CREATE TABLESPACE ts2
ADD DATAFILE 'ts2_datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 2M
ENGINE NDB;
ALTER TABLESPACE ts1
ADD DATAFILE 'ts1_datafile2.dat';
CREATE TABLE ndb_ddl_test4.t1 (
a INT PRIMARY KEY,
b INT
) ENGINE NDB;
INSERT INTO ndb_ddl_test4.t1 VALUES(1,1);
SELECT * FROM ndb_ddl_test4.t1;
a b
1 1
DROP DATABASE ndb_ddl_test3;
DROP DATABASE ndb_ddl_test4;
DROP DATABASE ndb_ddl_test5;
ALTER TABLESPACE ts1
DROP DATAFILE 'ts1_datafile.dat';
ALTER TABLESPACE ts1
DROP DATAFILE 'ts1_datafile2.dat';
DROP TABLESPACE ts1;
ALTER TABLESPACE ts2
DROP DATAFILE 'ts2_datafile.dat';
DROP TABLESPACE ts2;
DROP LOGFILE GROUP lg1
ENGINE NDB;
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1_ndb (
a INT PRIMARY KEY,
b INT
) ENGINE NDB;
CREATE TABLE t2_ndb (
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB;
CREATE DATABASE db2;
USE db2;
CREATE TABLE t1_ndb (
a INT PRIMARY KEY,
b INT
) ENGINE NDB;
CREATE TABLE t2_ndb (
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB;
CREATE DATABASE db3;
USE db3;
CREATE TABLE t1_innodb (
a INT PRIMARY KEY,
b INT
);
CREATE TABLE t2_innodb (
a INT PRIMARY KEY,
b VARCHAR(255)
);
CREATE DATABASE db4;
USE db4;
CREATE TABLE t1_ndb (
a INT PRIMARY KEY,
b INT
) ENGINE NDB;
CREATE TABLE t2_ndb (
a INT PRIMARY KEY,
b VARCHAR(255)
) ENGINE NDB;
CREATE TABLE t1_innodb (
a INT PRIMARY KEY,
b INT
);
CREATE TABLE t2_innodb (
a INT PRIMARY KEY,
b VARCHAR(255)
);
DROP DATABASE db1;
DROP DATABASE db4;
SET GLOBAL ndb_metadata_sync = true;
SHOW DATABASES LIKE 'db%';
Database (db%)
db1
db2
db3
db4
USE db1;
SHOW TABLES;
Tables_in_db1
t1_ndb
t2_ndb
SHOW CREATE TABLE t1_ndb;
Table Create Table
t1_ndb CREATE TABLE `t1_ndb` (
`a` int NOT NULL,
`b` int DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2_ndb;
Table Create Table
t2_ndb CREATE TABLE `t2_ndb` (
`a` int NOT NULL,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
USE db2;
SHOW TABLES;
Tables_in_db2
t1_ndb
t2_ndb
USE db3;
SHOW TABLES;
Tables_in_db3
t1_innodb
t2_innodb
USE db4;
SHOW TABLES;
Tables_in_db4
t1_ndb
t2_ndb
SHOW CREATE TABLE t1_ndb;
Table Create Table
t1_ndb CREATE TABLE `t1_ndb` (
`a` int NOT NULL,
`b` int DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t2_ndb;
Table Create Table
t2_ndb CREATE TABLE `t2_ndb` (
`a` int NOT NULL,
`b` varchar(255) DEFAULT NULL,
PRIMARY KEY (`a`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP DATABASE db1;
DROP DATABASE db2;
DROP DATABASE db3;
DROP DATABASE db4;
USE test;
CREATE TABLE t1 (
a INT PRIMARY KEY,
b INT NOT NULL,
c INT NOT NULL,
UNIQUE ib(b),
INDEX ic(c)
) ENGINE NDB;
INSERT INTO t1 VALUES (1,2,3),(2,3,5),(3,4,6),(4,5,8),(5,6,2),(6,7,2);
CREATE TABLE parent (
c1 INT PRIMARY KEY
) ENGINE NDB;
INSERT INTO parent VALUES (1),(2);
CREATE TABLE child (
c1 INT PRIMARY KEY,
c2 INT,
INDEX xc2(c2),
FOREIGN KEY(c2)
REFERENCES parent(c1)
ON DELETE CASCADE
) ENGINE NDB;
INSERT INTO child VALUES (1,1),(2,2);
Backup
DROP TABLE t1,child,parent;
Restore with --disable-indexes
SET GLOBAL ndb_metadata_sync = true;
SELECT schema_name, name, type FROM performance_schema.ndb_sync_excluded_objects;
schema_name name type
test child TABLE
test parent TABLE
test t1 TABLE
Rebuild indexes in NDB
Retry sync now that the indexes have been created
SET GLOBAL ndb_metadata_sync = true;
SHOW TABLES;
Tables_in_test
child
parent
t1
SELECT * FROM t1;
a b c
1 2 3
2 3 5
3 4 6
4 5 8
5 6 2
6 7 2
SELECT * FROM parent;
c1
1
2
SELECT * FROM child;
c1 c2
1 1
2 2
SELECT COUNT(*) FROM performance_schema.ndb_sync_excluded_objects;
COUNT(*)
0
DROP TABLE t1,child,parent;
SET @old_foreign_key_checks = @@foreign_key_checks;
SET foreign_key_checks=0;
CREATE TABLE child (
c1 INT PRIMARY KEY,
c2 INT,
INDEX xc2(c2),
FOREIGN KEY(c2)
REFERENCES parent(c1)
ON DELETE CASCADE
) ENGINE NDB;
INSERT INTO child VALUES (1,1),(2,2);
SET GLOBAL ndb_metadata_sync = true;
SELECT CASE COUNT(*) WHEN 0 THEN 'YES' ELSE 'NO' END AS mock_tables_sync_skipped
FROM performance_schema.ndb_sync_excluded_objects;
mock_tables_sync_skipped
YES
DROP TABLE child;
SET foreign_key_checks = @old_foreign_key_checks;
|
