| 1 |
############################################### |
| 2 |
# Purpose: To test advance DD and replication # |
| 3 |
############################################### |
| 4 |
|
| 5 |
#### Include Section #### |
| 6 |
--source include/have_ndb.inc |
| 7 |
--source include/have_binlog_format_mixed_or_row.inc |
| 8 |
--source suite/ndb_rpl/ndb_master-slave.inc |
| 9 |
--source suite/ndb/include/backup_restore_setup.inc |
| 10 |
|
| 11 |
###################################################### |
| 12 |
# Requirment: Cluster DD and replication must be able# |
| 13 |
# to handle ALTER tables and indexes and must rpl # |
| 14 |
# to the slave correctly # |
| 15 |
###################################################### |
| 16 |
|
| 17 |
## Test #1 replication of CDD and Alter Tables ##### |
| 18 |
--echo ***** Test 1 RPL of CDD and Alter ***** |
| 19 |
--echo ***** Test 1 setup ***** |
| 20 |
|
| 21 |
CREATE LOGFILE GROUP lg1 |
| 22 |
ADD UNDOFILE 'undofile.dat' |
| 23 |
INITIAL_SIZE 16M |
| 24 |
UNDO_BUFFER_SIZE = 1M |
| 25 |
ENGINE=NDB; |
| 26 |
|
| 27 |
ALTER LOGFILE GROUP lg1 |
| 28 |
ADD UNDOFILE 'undofile02.dat' |
| 29 |
INITIAL_SIZE 4M |
| 30 |
ENGINE=NDB; |
| 31 |
|
| 32 |
CREATE TABLESPACE ts1 |
| 33 |
ADD DATAFILE 'datafile.dat' |
| 34 |
USE LOGFILE GROUP lg1 |
| 35 |
INITIAL_SIZE 24M |
| 36 |
EXTENT_SIZE 256K |
| 37 |
ENGINE=NDB; |
| 38 |
|
| 39 |
ALTER TABLESPACE ts1 |
| 40 |
ADD DATAFILE 'datafile02.dat' |
| 41 |
INITIAL_SIZE 12M; |
| 42 |
|
| 43 |
CREATE TABLE t1 |
| 44 |
(c1 INT NOT NULL PRIMARY KEY, |
| 45 |
c2 INT NOT NULL, |
| 46 |
c3 INT NOT NULL) |
| 47 |
TABLESPACE ts1 STORAGE DISK |
| 48 |
ENGINE=NDB; |
| 49 |
|
| 50 |
--echo ***** insert some data ***** |
| 51 |
|
| 52 |
let $j= 900; |
| 53 |
--disable_query_log |
| 54 |
while ($j) |
| 55 |
{ |
| 56 |
eval INSERT INTO t1 VALUES($j,$j*2,$j+3); |
| 57 |
dec $j; |
| 58 |
} |
| 59 |
--enable_query_log |
| 60 |
|
| 61 |
--echo ***** Select from Master ***** |
| 62 |
|
| 63 |
SELECT * FROM t1 ORDER BY c1 LIMIT 5; |
| 64 |
|
| 65 |
--echo ***** Select from Slave ***** |
| 66 |
--sync_slave_with_master |
| 67 |
connection slave; |
| 68 |
SELECT * FROM t1 ORDER BY c1 LIMIT 5; |
| 69 |
|
| 70 |
################################### |
| 71 |
# Just to some File Schema check # |
| 72 |
################################### |
| 73 |
|
| 74 |
--disable_query_log |
| 75 |
SELECT DISTINCT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, LOGFILE_GROUP_NAME |
| 76 |
FROM INFORMATION_SCHEMA.FILES |
| 77 |
WHERE ENGINE="ndbcluster" ORDER BY FILE_NAME; |
| 78 |
--enable_query_log |
| 79 |
|
| 80 |
--echo **** Do First Set of ALTERs in the master table **** |
| 81 |
################################################### |
| 82 |
# On this first set of alters I expect: |
| 83 |
# 1. To be able to create and index on 2 columns |
| 84 |
# 2. To be able to create a unique index |
| 85 |
# 3. To be able to add two columns and have |
| 86 |
# it all replicated correctly to the slave cluster. |
| 87 |
################################################### |
| 88 |
connection master; |
| 89 |
CREATE INDEX t1_i ON t1(c2, c3); |
| 90 |
#Bug 18039 |
| 91 |
CREATE UNIQUE INDEX t1_i2 ON t1(c2); |
| 92 |
ALTER TABLE t1 ADD c4 TIMESTAMP; |
| 93 |
ALTER TABLE t1 ADD c5 DOUBLE; |
| 94 |
ALTER TABLE t1 ADD INDEX (c5); |
| 95 |
--disable_warnings |
| 96 |
SHOW CREATE TABLE t1; |
| 97 |
--enable_warnings |
| 98 |
|
| 99 |
--echo **** Show first set of ALTERs on SLAVE **** |
| 100 |
--sync_slave_with_master |
| 101 |
connection slave; |
| 102 |
--disable_warnings |
| 103 |
SHOW CREATE TABLE t1; |
| 104 |
--enable_warnings |
| 105 |
|
| 106 |
--echo **** Second set of alters test 1 **** |
| 107 |
############################################ |
| 108 |
# With this next set of alters we have had |
| 109 |
# Some issues with renames of tables. So this |
| 110 |
# test renames our main table, drop and index off |
| 111 |
# of it, creates another table with then name |
| 112 |
# of the orginal table, inserts a row, drops |
| 113 |
# the table and renames the orginal table back. |
| 114 |
# I want to make sure that 1) the cluster does |
| 115 |
# okay with this and 2) that it is replicated |
| 116 |
# correctly. |
| 117 |
############################################# |
| 118 |
connection master; |
| 119 |
ALTER TABLE t1 RENAME t2; |
| 120 |
ALTER TABLE t2 DROP INDEX c5; |
| 121 |
CREATE TABLE t1(c1 INT)ENGINE=NDB; |
| 122 |
INSERT INTO t1 VALUES(1); |
| 123 |
DROP TABLE t1; |
| 124 |
ALTER TABLE t2 RENAME t1; |
| 125 |
|
| 126 |
--echo **** Show second set of ALTERs on MASTER **** |
| 127 |
|
| 128 |
--disable_warnings |
| 129 |
SHOW CREATE TABLE t1; |
| 130 |
--enable_warnings |
| 131 |
|
| 132 |
--echo **** Show second set of ALTERs on SLAVE **** |
| 133 |
--sync_slave_with_master |
| 134 |
connection slave; |
| 135 |
--disable_warnings |
| 136 |
SHOW CREATE TABLE t1; |
| 137 |
--enable_warnings |
| 138 |
|
| 139 |
--echo **** Third and last set of alters for test1 **** |
| 140 |
######################################################### |
| 141 |
# In this last set of alters, we are messing with the |
| 142 |
# cluster ability to rebuild indexes, drop a column that make up |
| 143 |
# an index with another column and change types several times in |
| 144 |
# a row. I have choosen the BLOB as it seems to have had many |
| 145 |
# issues in this release. I want to make sure that the cluster |
| 146 |
# deals with these radical changes and that the replication to |
| 147 |
# the slave cluster is dones correctly. |
| 148 |
########################################################### |
| 149 |
connection master; |
| 150 |
ALTER TABLE t1 CHANGE c1 c1 DOUBLE; |
| 151 |
ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2); |
| 152 |
ALTER TABLE t1 DROP COLUMN c3; |
| 153 |
ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8mb4; |
| 154 |
ALTER TABLE t1 CHANGE c4 c4 BLOB; |
| 155 |
ALTER TABLE t1 CHANGE c4 c3 BLOB; |
| 156 |
set @b1 = 'b1'; |
| 157 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 158 |
UPDATE t1 SET c3=@b1 where c1 = 1; |
| 159 |
UPDATE t1 SET c3=@b1 where c1 = 2; |
| 160 |
|
| 161 |
--echo **** Show last set of ALTERs on MASTER **** |
| 162 |
|
| 163 |
--disable_warnings |
| 164 |
SHOW CREATE TABLE t1; |
| 165 |
--enable_warnings |
| 166 |
SELECT * FROM t1 ORDER BY c1 LIMIT 5; |
| 167 |
|
| 168 |
--echo **** Show last set of ALTERs on SLAVE **** |
| 169 |
--sync_slave_with_master |
| 170 |
connection slave; |
| 171 |
--disable_warnings |
| 172 |
SHOW CREATE TABLE t1; |
| 173 |
--enable_warnings |
| 174 |
# Bug 18094 |
| 175 |
SELECT * FROM t1 ORDER BY c1 LIMIT 5; |
| 176 |
SELECT * FROM t1 where c1 = 1; |
| 177 |
|
| 178 |
connection master; |
| 179 |
DROP TABLE t1; |
| 180 |
--sync_slave_with_master |
| 181 |
connection slave; |
| 182 |
STOP SLAVE; |
| 183 |
RESET SLAVE; |
| 184 |
connection master; |
| 185 |
RESET MASTER; |
| 186 |
connection slave; |
| 187 |
START SLAVE; |
| 188 |
|
| 189 |
################### TEST 2 TPCB for disk data ########################### |
| 190 |
# Requirement: To have Stored Procedures and Functions that are used to # |
| 191 |
# populate and post transactions to the data base using CDD that span # |
| 192 |
# 2 tables spaces and also use a memory only cluster tables. In addition# |
| 193 |
# The slave is to be stopped, cleaned and restored and synced with the # |
| 194 |
# Master cluster # |
| 195 |
######################################################################### |
| 196 |
|
| 197 |
--echo ******** Create additional TABLESPACE test 2 ************** |
| 198 |
|
| 199 |
connection master; |
| 200 |
CREATE TABLESPACE ts2 |
| 201 |
ADD DATAFILE 'datafile03.dat' |
| 202 |
USE LOGFILE GROUP lg1 |
| 203 |
INITIAL_SIZE 28M |
| 204 |
EXTENT_SIZE 256K |
| 205 |
ENGINE=NDB; |
| 206 |
|
| 207 |
ALTER TABLESPACE ts2 |
| 208 |
ADD DATAFILE 'datafile04.dat' |
| 209 |
INITIAL_SIZE 18M; |
| 210 |
|
| 211 |
let engine_type=NDBCLUSTER; |
| 212 |
let table_space=ts2; |
| 213 |
let format='RBR'; |
| 214 |
|
| 215 |
--source include/tpcb_disk_data.inc |
| 216 |
|
| 217 |
--echo ****** TEST 2 test time ********************************* |
| 218 |
USE tpcb; |
| 219 |
|
| 220 |
--echo *********** Load up the database ****************** |
| 221 |
CALL tpcb.load(); |
| 222 |
|
| 223 |
--echo ********** Check load master and slave ************** |
| 224 |
SELECT COUNT(*) FROM account; |
| 225 |
--sync_slave_with_master |
| 226 |
connection slave; |
| 227 |
USE tpcb; |
| 228 |
SELECT COUNT(*) FROM account; |
| 229 |
|
| 230 |
--echo ******** Run in some transactions *************** |
| 231 |
|
| 232 |
connection master; |
| 233 |
let $j= 100; |
| 234 |
--disable_query_log |
| 235 |
while ($j) |
| 236 |
{ |
| 237 |
eval CALL tpcb.trans($format); |
| 238 |
dec $j; |
| 239 |
} |
| 240 |
--enable_query_log |
| 241 |
|
| 242 |
|
| 243 |
--echo ***** Time to try slave sync *********** |
| 244 |
--sync_slave_with_master |
| 245 |
|
| 246 |
--echo **** Must make sure slave is clean ***** |
| 247 |
--connection slave |
| 248 |
USE tpcb; |
| 249 |
SELECT COUNT(*) FROM account; |
| 250 |
STOP SLAVE; |
| 251 |
RESET SLAVE; |
| 252 |
DROP PROCEDURE IF EXISTS tpcb.load; |
| 253 |
DROP PROCEDURE IF EXISTS tpcb.trans; |
| 254 |
DROP TABLE IF EXISTS tpcb.account; |
| 255 |
DROP TABLE IF EXISTS tpcb.teller; |
| 256 |
DROP TABLE IF EXISTS tpcb.branch; |
| 257 |
DROP TABLE IF EXISTS tpcb.history; |
| 258 |
DROP DATABASE tpcb; |
| 259 |
|
| 260 |
ALTER TABLESPACE ts1 |
| 261 |
DROP DATAFILE 'datafile.dat'; |
| 262 |
|
| 263 |
ALTER TABLESPACE ts1 |
| 264 |
DROP DATAFILE 'datafile02.dat'; |
| 265 |
|
| 266 |
DROP TABLESPACE ts1; |
| 267 |
|
| 268 |
ALTER TABLESPACE ts2 |
| 269 |
DROP DATAFILE 'datafile03.dat'; |
| 270 |
|
| 271 |
ALTER TABLESPACE ts2 |
| 272 |
DROP DATAFILE 'datafile04.dat'; |
| 273 |
|
| 274 |
DROP TABLESPACE ts2; |
| 275 |
|
| 276 |
DROP LOGFILE GROUP lg1 ENGINE=NDB; |
| 277 |
|
| 278 |
--echo ********** Take a backup of the Master ************* |
| 279 |
connection master; |
| 280 |
|
| 281 |
SELECT COUNT(*) FROM history; |
| 282 |
|
| 283 |
let $j= 100; |
| 284 |
--disable_query_log |
| 285 |
while ($j) |
| 286 |
{ |
| 287 |
eval CALL tpcb.trans($format); |
| 288 |
dec $j; |
| 289 |
} |
| 290 |
--enable_query_log |
| 291 |
|
| 292 |
SELECT COUNT(*) FROM history; |
| 293 |
|
| 294 |
--source include/ndb_backup.inc |
| 295 |
|
| 296 |
--echo ************ Restore the slave ************************ |
| 297 |
connection slave; |
| 298 |
CREATE DATABASE tpcb; |
| 299 |
|
| 300 |
--source include/ndb_restore_slave_eoption.inc |
| 301 |
|
| 302 |
# Wait until the following 7 object changes are synced: |
| 303 |
# Logfile group 'lg1' |
| 304 |
# Tablespace 'ts1' |
| 305 |
# Tablespace 'ts2' |
| 306 |
# Table 'tpcb.account' |
| 307 |
# Table 'tpcb.history' |
| 308 |
# Table 'tpcb.teller' |
| 309 |
# Table 'tpcb.branch' |
| 310 |
--let $max_wait = 60 |
| 311 |
--source wait_immediate_metadata_sync.inc |
| 312 |
|
| 313 |
--echo ***** Check a few slave restore values *************** |
| 314 |
connection slave; |
| 315 |
USE tpcb; |
| 316 |
SELECT COUNT(*) FROM account; |
| 317 |
|
| 318 |
--echo ***** Add some more records to master ********* |
| 319 |
connection master; |
| 320 |
let $j= 100; |
| 321 |
--disable_query_log |
| 322 |
while ($j) |
| 323 |
{ |
| 324 |
eval CALL tpcb.trans($format); |
| 325 |
dec $j; |
| 326 |
} |
| 327 |
--enable_query_log |
| 328 |
|
| 329 |
# |
| 330 |
# now setup replication to continue from last epoch |
| 331 |
# 1. get apply_status epoch from slave |
| 332 |
# 2. get corresponding _next_ binlog postition from master |
| 333 |
# 3. change master on slave |
| 334 |
# 4. add some transaction for slave to process |
| 335 |
# 5. start the replication |
| 336 |
|
| 337 |
--echo ***** Finsh the slave sync process ******* |
| 338 |
--disable_query_log |
| 339 |
# 1. 2. 3. |
| 340 |
--source suite/ndb_rpl/ndb_setup_slave.inc |
| 341 |
--enable_query_log |
| 342 |
|
| 343 |
# 4. |
| 344 |
--echo * 4. * |
| 345 |
connection master; |
| 346 |
let $j= 100; |
| 347 |
--disable_query_log |
| 348 |
while ($j) |
| 349 |
{ |
| 350 |
eval CALL tpcb.trans($format); |
| 351 |
dec $j; |
| 352 |
} |
| 353 |
--enable_query_log |
| 354 |
|
| 355 |
# 5. |
| 356 |
--echo * 5. * |
| 357 |
connection slave; |
| 358 |
START SLAVE; |
| 359 |
|
| 360 |
--echo **** We should be ready to continue on ************* |
| 361 |
|
| 362 |
connection master; |
| 363 |
--echo ****** Let's make sure we match ******* |
| 364 |
--echo ***** MASTER ******* |
| 365 |
USE tpcb; |
| 366 |
SELECT COUNT(*) FROM history; |
| 367 |
|
| 368 |
--echo ****** SLAVE ******** |
| 369 |
--sync_slave_with_master |
| 370 |
connection slave; |
| 371 |
USE tpcb; |
| 372 |
SELECT COUNT(*) FROM history; |
| 373 |
|
| 374 |
--echo *** DUMP MASTER & SLAVE FOR COMPARE ******** |
| 375 |
|
| 376 |
--exec $MYSQL_DUMP --no-tablespaces --compact --order-by-primary --skip-extended-insert tpcb account teller branch history > $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_M.sql |
| 377 |
|
| 378 |
--exec $MYSQL_DUMP_SLAVE --no-tablespaces --compact --order-by-primary --skip-extended-insert tpcb account teller branch history > $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_S.sql |
| 379 |
|
| 380 |
--echo *************** TEST 2 CLEANUP SECTION ******************** |
| 381 |
connection master; |
| 382 |
DROP PROCEDURE IF EXISTS tpcb.load; |
| 383 |
DROP PROCEDURE IF EXISTS tpcb.trans; |
| 384 |
DROP TABLE tpcb.account; |
| 385 |
DROP TABLE tpcb.teller; |
| 386 |
DROP TABLE tpcb.branch; |
| 387 |
DROP TABLE tpcb.history; |
| 388 |
DROP DATABASE tpcb; |
| 389 |
|
| 390 |
ALTER TABLESPACE ts1 |
| 391 |
DROP DATAFILE 'datafile.dat'; |
| 392 |
|
| 393 |
ALTER TABLESPACE ts1 |
| 394 |
DROP DATAFILE 'datafile02.dat'; |
| 395 |
|
| 396 |
DROP TABLESPACE ts1; |
| 397 |
|
| 398 |
ALTER TABLESPACE ts2 |
| 399 |
DROP DATAFILE 'datafile03.dat'; |
| 400 |
|
| 401 |
ALTER TABLESPACE ts2 |
| 402 |
DROP DATAFILE 'datafile04.dat'; |
| 403 |
|
| 404 |
DROP TABLESPACE ts2; |
| 405 |
|
| 406 |
DROP LOGFILE GROUP lg1 ENGINE=NDB; |
| 407 |
|
| 408 |
--sync_slave_with_master |
| 409 |
|
| 410 |
connection master; |
| 411 |
|
| 412 |
--echo ****** Do dumps compare ************ |
| 413 |
|
| 414 |
diff_files $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_M.sql $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_S.sql; |
| 415 |
|
| 416 |
## Note: Ths files should only get removed, if the above diff succeeds. |
| 417 |
|
| 418 |
--remove_file $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_M.sql |
| 419 |
--remove_file $MYSQLTEST_VARDIR/tmp/RPL_DD_ADV_S.sql |
| 420 |
|
| 421 |
# End 5.1 test case |
| 422 |
--source include/rpl_end.inc |
| 423 |
--source suite/ndb/include/backup_restore_cleanup.inc |
| 424 |
--remove_file $NDB_TOOLS_OUTPUT |