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