1************************************************************
2* Creating multiple databases with identical tables
3* (have blobs and indexes to cover the hidden tables)
4************************************************************
5CREATE DATABASE db0;
6CREATE DATABASE db1;
7CREATE DATABASE db2;
8USE db0;
9CREATE TABLE t0 (
10id              INT             PRIMARY KEY,
11cint            INT,
12cvarchar        VARCHAR(5),
13cblob           BLOB(1000004),
14UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
15UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
16) ENGINE=NDB;
17USE db1;
18CREATE TABLE t0 (
19id              INT             PRIMARY KEY,
20cint            INT,
21cvarchar        VARCHAR(5),
22cblob           BLOB(1000004),
23UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
24UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
25) ENGINE=NDB;
26USE db2;
27CREATE TABLE t0 (
28id              INT             PRIMARY KEY,
29cint            INT,
30cvarchar        VARCHAR(5),
31cblob           BLOB(1000004),
32UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC),
33UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC)
34) ENGINE=NDB;
35************************************************************
36* Inserting data
37* (create disjunct sets of rows to merge without conflicts;
38*  at this time, ndb_restore does not offer any detection
39*  of data conflicts between databases in the backup or in
40*  memory; databases are restored in an unspecified order)
41* (make blob data long enough to be held in extra table)
42************************************************************
43USE db0;
44INSERT INTO t0 VALUES (0, 0, '00000', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
45INSERT INTO t0 VALUES (1, 1, '11111', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
46INSERT INTO t0 VALUES (2, 2, '22222', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
47USE db1;
48INSERT INTO t0 VALUES (3, 3, '33333', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
49INSERT INTO t0 VALUES (4, 4, '44444', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
50INSERT INTO t0 VALUES (5, 5, '55555', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
51USE db2;
52INSERT INTO t0 VALUES (6, 6, '66666', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
53INSERT INTO t0 VALUES (7, 7, '77777', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
54INSERT INTO t0 VALUES (8, 8, '88888', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');
55************************************************************
56* Creating in-memory copies of the NDB tables
57************************************************************
58CREATE TABLE db0.t0_data ENGINE=MYISAM AS SELECT * FROM db0.t0;
59CREATE TABLE db1.t0_data ENGINE=MYISAM AS SELECT * FROM db1.t0;
60CREATE TABLE db2.t0_data ENGINE=MYISAM AS SELECT * FROM db2.t0;
61************************************************************
62* Backing up databases
63************************************************************
64************************************************************
65* Restoring databases with no rewrite (sanity check)
66************************************************************
67CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
68CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
69CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
70DELETE FROM db0.t0;
71DELETE FROM db1.t0;
72DELETE FROM db2.t0;
73SELECT COUNT(*) FROM db0.t0;
74COUNT(*)
753
76SELECT COUNT(*) FROM db1.t0;
77COUNT(*)
783
79SELECT COUNT(*) FROM db2.t0;
80COUNT(*)
813
82SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
83COUNT(*)
843
85SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
86COUNT(*)
873
88SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
89COUNT(*)
903
91DROP TABLE db0.t0_temp;
92DROP TABLE db1.t0_temp;
93DROP TABLE db2.t0_temp;
94************************************************************
95* Negative testing: check wrong usage of command-line option
96* (expected exit code for usage errors: NDBT_WRONGARGS = 2)
97************************************************************
98************************************************************
99* Restoring databases with redundant/self-rewrite options
100************************************************************
101CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
102CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
103CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
104DELETE FROM db0.t0;
105DELETE FROM db1.t0;
106DELETE FROM db2.t0;
107SELECT COUNT(*) FROM db0.t0;
108COUNT(*)
1093
110SELECT COUNT(*) FROM db1.t0;
111COUNT(*)
1123
113SELECT COUNT(*) FROM db2.t0;
114COUNT(*)
1153
116SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
117COUNT(*)
1183
119SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
120COUNT(*)
1213
122SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
123COUNT(*)
1243
125DROP TABLE db0.t0_temp;
126DROP TABLE db1.t0_temp;
127DROP TABLE db2.t0_temp;
128************************************************************
129* Restoring databases with overriding rewrite options
130************************************************************
131CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data;
132CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data;
133CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data;
134DELETE FROM db0.t0;
135DELETE FROM db1.t0;
136DELETE FROM db2.t0;
137SELECT COUNT(*) FROM db0.t0;
138COUNT(*)
1393
140SELECT COUNT(*) FROM db1.t0;
141COUNT(*)
1423
143SELECT COUNT(*) FROM db2.t0;
144COUNT(*)
1453
146SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
147COUNT(*)
1483
149SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
150COUNT(*)
1513
152SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
153COUNT(*)
1543
155DROP TABLE db0.t0_temp;
156DROP TABLE db1.t0_temp;
157DROP TABLE db2.t0_temp;
158************************************************************
159* Restoring databases with a single rewrite
160************************************************************
161CREATE TABLE db0.t0_temp LIKE db0.t0_data;
162CREATE TABLE db1.t0_temp LIKE db1.t0_data;
163CREATE TABLE db2.t0_temp LIKE db2.t0_data;
164DELETE FROM db0.t0;
165DELETE FROM db1.t0;
166DELETE FROM db2.t0;
167SELECT COUNT(*) FROM db0.t0;
168COUNT(*)
1690
170SELECT COUNT(*) FROM db1.t0;
171COUNT(*)
1726
173SELECT COUNT(*) FROM db2.t0;
174COUNT(*)
1753
176INSERT db1.t0_temp SELECT * FROM db0.t0_data;
177INSERT db1.t0_temp SELECT * FROM db1.t0_data;
178INSERT db2.t0_temp SELECT * FROM db2.t0_data;
179SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
180COUNT(*)
1810
182SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
183COUNT(*)
1846
185SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
186COUNT(*)
1873
188DROP TABLE db0.t0_temp;
189DROP TABLE db1.t0_temp;
190DROP TABLE db2.t0_temp;
191************************************************************
192* Restoring databases with multiple rewrites
193************************************************************
194CREATE TABLE db0.t0_temp LIKE db0.t0_data;
195CREATE TABLE db1.t0_temp LIKE db1.t0_data;
196CREATE TABLE db2.t0_temp LIKE db2.t0_data;
197DELETE FROM db0.t0;
198DELETE FROM db1.t0;
199DELETE FROM db2.t0;
200SELECT COUNT(*) FROM db0.t0;
201COUNT(*)
2020
203SELECT COUNT(*) FROM db1.t0;
204COUNT(*)
2053
206SELECT COUNT(*) FROM db2.t0;
207COUNT(*)
2086
209INSERT db1.t0_temp SELECT * FROM db0.t0_data;
210INSERT db2.t0_temp SELECT * FROM db1.t0_data;
211INSERT db2.t0_temp SELECT * FROM db2.t0_data;
212SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
213COUNT(*)
2140
215SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
216COUNT(*)
2173
218SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
219COUNT(*)
2206
221DROP TABLE db0.t0_temp;
222DROP TABLE db1.t0_temp;
223DROP TABLE db2.t0_temp;
224************************************************************
225* Restoring databases with multiple rewrites into same target
226************************************************************
227CREATE TABLE db0.t0_temp LIKE db0.t0_data;
228CREATE TABLE db1.t0_temp LIKE db1.t0_data;
229CREATE TABLE db2.t0_temp LIKE db2.t0_data;
230DELETE FROM db0.t0;
231DELETE FROM db1.t0;
232DELETE FROM db2.t0;
233SELECT COUNT(*) FROM db0.t0;
234COUNT(*)
2350
236SELECT COUNT(*) FROM db1.t0;
237COUNT(*)
2380
239SELECT COUNT(*) FROM db2.t0;
240COUNT(*)
2419
242INSERT db2.t0_temp SELECT * FROM db0.t0_data;
243INSERT db2.t0_temp SELECT * FROM db1.t0_data;
244INSERT db2.t0_temp SELECT * FROM db2.t0_data;
245SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
246COUNT(*)
2470
248SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
249COUNT(*)
2500
251SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
252COUNT(*)
2539
254DROP TABLE db0.t0_temp;
255DROP TABLE db1.t0_temp;
256DROP TABLE db2.t0_temp;
257************************************************************
258* Restoring databases with swapping rewrites
259************************************************************
260CREATE TABLE db0.t0_temp LIKE db0.t0_data;
261CREATE TABLE db1.t0_temp LIKE db1.t0_data;
262CREATE TABLE db2.t0_temp LIKE db2.t0_data;
263DELETE FROM db0.t0;
264DELETE FROM db1.t0;
265DELETE FROM db2.t0;
266SELECT COUNT(*) FROM db0.t0;
267COUNT(*)
2683
269SELECT COUNT(*) FROM db1.t0;
270COUNT(*)
2713
272SELECT COUNT(*) FROM db2.t0;
273COUNT(*)
2743
275INSERT db1.t0_temp SELECT * FROM db0.t0_data;
276INSERT db0.t0_temp SELECT * FROM db1.t0_data;
277INSERT db2.t0_temp SELECT * FROM db2.t0_data;
278SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
279COUNT(*)
2803
281SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
282COUNT(*)
2833
284SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
285COUNT(*)
2863
287DROP TABLE db0.t0_temp;
288DROP TABLE db1.t0_temp;
289DROP TABLE db2.t0_temp;
290************************************************************
291* Restoring databases with permutating rewrites
292************************************************************
293CREATE TABLE db0.t0_temp LIKE db0.t0_data;
294CREATE TABLE db1.t0_temp LIKE db1.t0_data;
295CREATE TABLE db2.t0_temp LIKE db2.t0_data;
296DELETE FROM db0.t0;
297DELETE FROM db1.t0;
298DELETE FROM db2.t0;
299SELECT COUNT(*) FROM db0.t0;
300COUNT(*)
3013
302SELECT COUNT(*) FROM db1.t0;
303COUNT(*)
3043
305SELECT COUNT(*) FROM db2.t0;
306COUNT(*)
3073
308INSERT db1.t0_temp SELECT * FROM db0.t0_data;
309INSERT db2.t0_temp SELECT * FROM db1.t0_data;
310INSERT db0.t0_temp SELECT * FROM db2.t0_data;
311SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp;
312COUNT(*)
3133
314SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp;
315COUNT(*)
3163
317SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp;
318COUNT(*)
3193
320DROP TABLE db0.t0_temp;
321DROP TABLE db1.t0_temp;
322DROP TABLE db2.t0_temp;
323************************************************************
324* Deleting tables and databases
325************************************************************
326DROP DATABASE db0;
327DROP DATABASE db1;
328DROP DATABASE db2;
329