1# The include statement below is a temp one for tests that are yet to 2#be ported to run with InnoDB, 3#but needs to be kept for tests that would need MyISAM in future. 4--source include/force_myisam_default.inc 5 6###################################################################### 7# Test restoring backups into renamed databases 8###################################################################### 9 10-- source include/have_ndb.inc 11 12# mysqld's configuration is not relevant to this test 13-- source include/not_embedded.inc 14 15--echo ************************************************************ 16--echo * Creating multiple databases with identical tables 17--echo * (have blobs and indexes to cover the hidden tables) 18--echo ************************************************************ 19 20CREATE DATABASE db0; 21CREATE DATABASE db1; 22CREATE DATABASE db2; 23 24USE db0; 25CREATE TABLE t0 ( 26 id INT PRIMARY KEY, 27 cint INT, 28 cvarchar VARCHAR(5), 29 cblob BLOB(1000004), 30 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC), 31 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC) 32) ENGINE=NDB; 33 34USE db1; 35CREATE TABLE t0 ( 36 id INT PRIMARY KEY, 37 cint INT, 38 cvarchar VARCHAR(5), 39 cblob BLOB(1000004), 40 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC), 41 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC) 42) ENGINE=NDB; 43 44USE db2; 45CREATE TABLE t0 ( 46 id INT PRIMARY KEY, 47 cint INT, 48 cvarchar VARCHAR(5), 49 cblob BLOB(1000004), 50 UNIQUE INDEX UNIQUE_t0_0 USING BTREE (cint ASC), 51 UNIQUE INDEX UNIQUE_t0_2 USING BTREE (cvarchar ASC) 52) ENGINE=NDB; 53 54 55--echo ************************************************************ 56--echo * Inserting data 57--echo * (create disjunct sets of rows to merge without conflicts; 58--echo * at this time, ndb_restore does not offer any detection 59--echo * of data conflicts between databases in the backup or in 60--echo * memory; databases are restored in an unspecified order) 61--echo * (make blob data long enough to be held in extra table) 62--echo ************************************************************ 63 64USE db0; 65INSERT INTO t0 VALUES (0, 0, '00000', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 66INSERT INTO t0 VALUES (1, 1, '11111', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 67INSERT INTO t0 VALUES (2, 2, '22222', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 68 69USE db1; 70INSERT INTO t0 VALUES (3, 3, '33333', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 71INSERT INTO t0 VALUES (4, 4, '44444', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 72INSERT INTO t0 VALUES (5, 5, '55555', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 73 74USE db2; 75INSERT INTO t0 VALUES (6, 6, '66666', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 76INSERT INTO t0 VALUES (7, 7, '77777', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 77INSERT INTO t0 VALUES (8, 8, '88888', '123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'); 78 79--echo ************************************************************ 80--echo * Creating in-memory copies of the NDB tables 81--echo ************************************************************ 82 83CREATE TABLE db0.t0_data ENGINE=MYISAM AS SELECT * FROM db0.t0; 84CREATE TABLE db1.t0_data ENGINE=MYISAM AS SELECT * FROM db1.t0; 85CREATE TABLE db2.t0_data ENGINE=MYISAM AS SELECT * FROM db2.t0; 86 87--echo ************************************************************ 88--echo * Backing up databases 89--echo ************************************************************ 90 91--source include/ndb_backup.inc 92 93# command shortcuts, cover rebuilding of indexes 94--let $restore_cmd=$NDB_RESTORE --no-defaults 95--let $restore_cmd=$restore_cmd --disable-indexes --rebuild-indexes 96--let $restore_cmd=$restore_cmd -b $the_backup_id -r 97--let $restore_cmd=$restore_cmd --backup_path=$NDB_BACKUPS-$the_backup_id 98 99--echo ************************************************************ 100--echo * Restoring databases with no rewrite (sanity check) 101--echo ************************************************************ 102 103# create temporary tables against which to compare data 104CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data; 105CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data; 106CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data; 107 108# restore NDB tables 109DELETE FROM db0.t0; 110DELETE FROM db1.t0; 111DELETE FROM db2.t0; 112--let $restore_opt= 113--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 114--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 115 116# summary-check ndb tables 117SELECT COUNT(*) FROM db0.t0; 118SELECT COUNT(*) FROM db1.t0; 119SELECT COUNT(*) FROM db2.t0; 120 121# verify ndb tables 122SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 123SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 124SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 125 126# delete temporary tables 127DROP TABLE db0.t0_temp; 128DROP TABLE db1.t0_temp; 129DROP TABLE db2.t0_temp; 130 131--echo ************************************************************ 132--echo * Negative testing: check wrong usage of command-line option 133--echo * (expected exit code for usage errors: NDBT_WRONGARGS = 2) 134--echo ************************************************************ 135 136# empty argument 137--let $restore_opt=--rewrite-database= 138--error 2 139--exec $restore_cmd -n 1 $restore_opt > /dev/null 140 141# missing separator 142--let $restore_opt=--rewrite-database=aaaa 143--error 2 144--exec $restore_cmd -n 1 $restore_opt > /dev/null 145 146# missing source and target 147--let $restore_opt=--rewrite-database=, 148--error 2 149--exec $restore_cmd -n 1 $restore_opt > /dev/null 150 151# missing source 152--let $restore_opt=--rewrite-database=,a 153--error 2 154--exec $restore_cmd -n 1 $restore_opt > /dev/null 155 156# missing target 157--let $restore_opt=--rewrite-database=a, 158--error 2 159--exec $restore_cmd -n 1 $restore_opt > /dev/null 160 161--echo ************************************************************ 162--echo * Restoring databases with redundant/self-rewrite options 163--echo ************************************************************ 164 165# create temporary tables against which to compare data 166CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data; 167CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data; 168CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data; 169 170# restore NDB tables 171DELETE FROM db0.t0; 172DELETE FROM db1.t0; 173DELETE FROM db2.t0; 174--let $restore_opt=--rewrite-database=db0,db0 --rewrite-database=db1,db1 175--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 176--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 177 178# summary-check ndb tables 179SELECT COUNT(*) FROM db0.t0; 180SELECT COUNT(*) FROM db1.t0; 181SELECT COUNT(*) FROM db2.t0; 182 183# verify ndb tables 184SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 185SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 186SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 187 188# delete temporary tables 189DROP TABLE db0.t0_temp; 190DROP TABLE db1.t0_temp; 191DROP TABLE db2.t0_temp; 192 193--echo ************************************************************ 194--echo * Restoring databases with overriding rewrite options 195--echo ************************************************************ 196 197# create temporary tables against which to compare data 198CREATE TABLE db0.t0_temp ENGINE=MYISAM AS SELECT * FROM db0.t0_data; 199CREATE TABLE db1.t0_temp ENGINE=MYISAM AS SELECT * FROM db1.t0_data; 200CREATE TABLE db2.t0_temp ENGINE=MYISAM AS SELECT * FROM db2.t0_data; 201 202# restore NDB tables 203DELETE FROM db0.t0; 204DELETE FROM db1.t0; 205DELETE FROM db2.t0; 206# no rewrite, since the later option overrides the former 207--let $restore_opt=--rewrite-database=db0,db1 --rewrite-database=db0,db0 208--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 209--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 210 211# summary-check ndb tables 212SELECT COUNT(*) FROM db0.t0; 213SELECT COUNT(*) FROM db1.t0; 214SELECT COUNT(*) FROM db2.t0; 215 216# verify ndb tables 217SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 218SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 219SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 220 221# delete temporary tables 222DROP TABLE db0.t0_temp; 223DROP TABLE db1.t0_temp; 224DROP TABLE db2.t0_temp; 225 226--echo ************************************************************ 227--echo * Restoring databases with a single rewrite 228--echo ************************************************************ 229 230# create temporary tables against which to compare data 231CREATE TABLE db0.t0_temp LIKE db0.t0_data; 232CREATE TABLE db1.t0_temp LIKE db1.t0_data; 233CREATE TABLE db2.t0_temp LIKE db2.t0_data; 234 235# restore NDB tables 236DELETE FROM db0.t0; 237DELETE FROM db1.t0; 238DELETE FROM db2.t0; 239--let $restore_opt=--rewrite-database=db0,db1 240--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 241--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 242 243# summary-check ndb tables 244SELECT COUNT(*) FROM db0.t0; 245SELECT COUNT(*) FROM db1.t0; 246SELECT COUNT(*) FROM db2.t0; 247 248# fill temporary tables 249INSERT db1.t0_temp SELECT * FROM db0.t0_data; 250INSERT db1.t0_temp SELECT * FROM db1.t0_data; 251INSERT db2.t0_temp SELECT * FROM db2.t0_data; 252 253# verify ndb tables 254SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 255SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 256SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 257 258# delete temporary tables 259DROP TABLE db0.t0_temp; 260DROP TABLE db1.t0_temp; 261DROP TABLE db2.t0_temp; 262 263--echo ************************************************************ 264--echo * Restoring databases with multiple rewrites 265--echo ************************************************************ 266 267# create temporary tables against which to compare data 268CREATE TABLE db0.t0_temp LIKE db0.t0_data; 269CREATE TABLE db1.t0_temp LIKE db1.t0_data; 270CREATE TABLE db2.t0_temp LIKE db2.t0_data; 271 272# restore NDB tables 273DELETE FROM db0.t0; 274DELETE FROM db1.t0; 275DELETE FROM db2.t0; 276--let $restore_opt=--rewrite-database=db0,db1 --rewrite-database=db1,db2 277--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 278--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 279 280# summary-check ndb tables 281SELECT COUNT(*) FROM db0.t0; 282SELECT COUNT(*) FROM db1.t0; 283SELECT COUNT(*) FROM db2.t0; 284 285# fill temporary tables 286INSERT db1.t0_temp SELECT * FROM db0.t0_data; 287INSERT db2.t0_temp SELECT * FROM db1.t0_data; 288INSERT db2.t0_temp SELECT * FROM db2.t0_data; 289 290# verify ndb tables 291SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 292SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 293SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 294 295# delete temporary tables 296DROP TABLE db0.t0_temp; 297DROP TABLE db1.t0_temp; 298DROP TABLE db2.t0_temp; 299 300--echo ************************************************************ 301--echo * Restoring databases with multiple rewrites into same target 302--echo ************************************************************ 303 304# create temporary tables against which to compare data 305CREATE TABLE db0.t0_temp LIKE db0.t0_data; 306CREATE TABLE db1.t0_temp LIKE db1.t0_data; 307CREATE TABLE db2.t0_temp LIKE db2.t0_data; 308 309# restore NDB tables 310DELETE FROM db0.t0; 311DELETE FROM db1.t0; 312DELETE FROM db2.t0; 313--let $restore_opt=--rewrite-database=db0,db2 --rewrite-database=db1,db2 314--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 315--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 316 317# summary-check ndb tables 318SELECT COUNT(*) FROM db0.t0; 319SELECT COUNT(*) FROM db1.t0; 320SELECT COUNT(*) FROM db2.t0; 321 322# fill temporary tables 323INSERT db2.t0_temp SELECT * FROM db0.t0_data; 324INSERT db2.t0_temp SELECT * FROM db1.t0_data; 325INSERT db2.t0_temp SELECT * FROM db2.t0_data; 326 327# verify ndb tables 328SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 329SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 330SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 331 332# delete temporary tables 333DROP TABLE db0.t0_temp; 334DROP TABLE db1.t0_temp; 335DROP TABLE db2.t0_temp; 336 337--echo ************************************************************ 338--echo * Restoring databases with swapping rewrites 339--echo ************************************************************ 340 341# create temporary tables against which to compare data 342CREATE TABLE db0.t0_temp LIKE db0.t0_data; 343CREATE TABLE db1.t0_temp LIKE db1.t0_data; 344CREATE TABLE db2.t0_temp LIKE db2.t0_data; 345 346# restore NDB tables 347DELETE FROM db0.t0; 348DELETE FROM db1.t0; 349DELETE FROM db2.t0; 350--let $restore_opt=--rewrite-database=db0,db1 --rewrite-database=db1,db0 351--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 352--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 353 354# summary-check ndb tables 355SELECT COUNT(*) FROM db0.t0; 356SELECT COUNT(*) FROM db1.t0; 357SELECT COUNT(*) FROM db2.t0; 358 359# fill temporary tables 360INSERT db1.t0_temp SELECT * FROM db0.t0_data; 361INSERT db0.t0_temp SELECT * FROM db1.t0_data; 362INSERT db2.t0_temp SELECT * FROM db2.t0_data; 363 364# verify ndb tables 365SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 366SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 367SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 368 369# delete temporary tables 370DROP TABLE db0.t0_temp; 371DROP TABLE db1.t0_temp; 372DROP TABLE db2.t0_temp; 373 374--echo ************************************************************ 375--echo * Restoring databases with permutating rewrites 376--echo ************************************************************ 377 378# create temporary tables against which to compare data 379CREATE TABLE db0.t0_temp LIKE db0.t0_data; 380CREATE TABLE db1.t0_temp LIKE db1.t0_data; 381CREATE TABLE db2.t0_temp LIKE db2.t0_data; 382 383# restore NDB tables 384DELETE FROM db0.t0; 385DELETE FROM db1.t0; 386DELETE FROM db2.t0; 387--let $restore_opt=--rewrite-database=db0,db1 --rewrite-database=db1,db2 388--let $restore_opt=$restore_opt --rewrite-database=db2,db0 389--exec $restore_cmd -n 1 $restore_opt --print > /dev/null 390--exec $restore_cmd -n 2 $restore_opt --print > /dev/null 391 392# summary-check ndb tables 393SELECT COUNT(*) FROM db0.t0; 394SELECT COUNT(*) FROM db1.t0; 395SELECT COUNT(*) FROM db2.t0; 396 397# fill temporary tables 398INSERT db1.t0_temp SELECT * FROM db0.t0_data; 399INSERT db2.t0_temp SELECT * FROM db1.t0_data; 400INSERT db0.t0_temp SELECT * FROM db2.t0_data; 401 402# verify ndb tables 403SELECT COUNT(*) FROM db0.t0 NATURAL JOIN db0.t0_temp; 404SELECT COUNT(*) FROM db1.t0 NATURAL JOIN db1.t0_temp; 405SELECT COUNT(*) FROM db2.t0 NATURAL JOIN db2.t0_temp; 406 407# delete temporary tables 408DROP TABLE db0.t0_temp; 409DROP TABLE db1.t0_temp; 410DROP TABLE db2.t0_temp; 411 412--echo ************************************************************ 413--echo * Deleting tables and databases 414--echo ************************************************************ 415 416DROP DATABASE db0; 417DROP DATABASE db1; 418DROP DATABASE db2; 419