summaryrefslogtreecommitdiff
path: root/mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result')
-rw-r--r--mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result417
1 files changed, 417 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result b/mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result
new file mode 100644
index 00000000000..a4614b4b484
--- /dev/null
+++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_dd_advance.result
@@ -0,0 +1,417 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+DROP TABLE IF EXISTS t1, t2, t3;
+***** Test 1 RPL of CDD and Alter *****
+***** Test 1 setup *****
+CREATE LOGFILE GROUP lg1
+ADD UNDOFILE 'undofile.dat'
+INITIAL_SIZE 16M
+UNDO_BUFFER_SIZE = 1M
+ENGINE=NDB;
+ALTER LOGFILE GROUP lg1
+ADD UNDOFILE 'undofile02.dat'
+INITIAL_SIZE 4M
+ENGINE=NDB;
+CREATE TABLESPACE ts1
+ADD DATAFILE 'datafile.dat'
+USE LOGFILE GROUP lg1
+INITIAL_SIZE 12M
+ENGINE=NDB;
+ALTER TABLESPACE ts1
+ADD DATAFILE 'datafile02.dat'
+INITIAL_SIZE 4M
+ENGINE=NDB;
+CREATE TABLE t1
+(c1 INT NOT NULL PRIMARY KEY,
+c2 INT NOT NULL,
+c3 INT NOT NULL)
+TABLESPACE ts1 STORAGE DISK
+ENGINE=NDB;
+***** insert some data *****
+***** Select from Master *****
+SELECT * FROM t1 ORDER BY c1 LIMIT 5;
+c1 c2 c3
+1 2 4
+2 4 5
+3 6 6
+4 8 7
+5 10 8
+***** Select from Slave *****
+SELECT * FROM t1 ORDER BY c1 LIMIT 5;
+c1 c2 c3
+1 2 4
+2 4 5
+3 6 6
+4 8 7
+5 10 8
+FILE_NAME FILE_TYPE TABLESPACE_NAME LOGFILE_GROUP_NAME
+NULL UNDO LOG NULL lg1
+datafile.dat DATAFILE ts1 lg1
+datafile02.dat DATAFILE ts1 lg1
+undofile.dat UNDO LOG NULL lg1
+undofile02.dat UNDO LOG NULL lg1
+**** Do First Set of ALTERs in the master table ****
+CREATE INDEX t1_i ON t1(c2, c3);
+ALTER TABLE t1 ADD c4 TIMESTAMP;
+ALTER TABLE t1 ADD c5 DOUBLE;
+ALTER TABLE t1 ADD INDEX (c5);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` int(11) NOT NULL,
+ `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`,`c3`),
+ KEY `c5` (`c5`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+**** Show first set of ALTERs on SLAVE ****
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` int(11) NOT NULL,
+ `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`,`c3`),
+ KEY `c5` (`c5`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+**** Second set of alters test 1 ****
+ALTER TABLE t1 RENAME t2;
+ALTER TABLE t2 DROP INDEX c5;
+CREATE TABLE t1(c1 INT)ENGINE=NDB;
+INSERT INTO t1 VALUES(1);
+DROP TABLE t1;
+ALTER TABLE t2 RENAME t1;
+**** Show second set of ALTERs on MASTER ****
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` int(11) NOT NULL,
+ `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`,`c3`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+**** Show second set of ALTERs on SLAVE ****
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL,
+ `c2` int(11) NOT NULL,
+ `c3` int(11) NOT NULL,
+ `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`,`c3`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+**** Third and last set of alters for test1 ****
+ALTER TABLE t1 CHANGE c1 c1 DOUBLE;
+ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2);
+ALTER TABLE t1 DROP COLUMN c3;
+ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8;
+ALTER TABLE t1 CHANGE c4 c4 BLOB;
+ALTER TABLE t1 CHANGE c4 c3 BLOB;
+set @b1 = 'b1';
+set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
+UPDATE t1 SET c3=@b1 where c1 = 1;
+UPDATE t1 SET c3=@b1 where c1 = 2;
+**** Show last set of ALTERs on MASTER ****
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` double NOT NULL DEFAULT '0',
+ `c2` decimal(10,2) DEFAULT NULL,
+ `c3` blob,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1 LIMIT 5;
+c1 c2 c3 c5
+1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
+2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL
+3 6.00 0000-00-00 00:00:00 NULL
+4 8.00 0000-00-00 00:00:00 NULL
+5 10.00 0000-00-00 00:00:00 NULL
+**** Show last set of ALTERs on SLAVE ****
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` double NOT NULL DEFAULT '0',
+ `c2` decimal(10,2) DEFAULT NULL,
+ `c3` blob,
+ `c5` double DEFAULT NULL,
+ PRIMARY KEY (`c1`),
+ KEY `t1_i` (`c2`)
+) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 where c1 = 1;
+c1 c2 c3 c5
+1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
+DROP TABLE t1;
+STOP SLAVE;
+RESET SLAVE;
+RESET MASTER;
+START SLAVE;
+******** Create additional TABLESPACE test 2 **************
+CREATE TABLESPACE ts2
+ADD DATAFILE 'datafile03.dat'
+USE LOGFILE GROUP lg1
+INITIAL_SIZE 12M
+ENGINE=NDB;
+ALTER TABLESPACE ts2
+ADD DATAFILE 'datafile04.dat'
+INITIAL_SIZE 12M
+ENGINE=NDB;
+DROP DATABASE IF EXISTS tpcb;
+Warnings:
+Note 1008 Can't drop database 'tpcb'; database doesn't exist
+CREATE DATABASE tpcb;
+*********** Create TPCB Tables *****************
+CREATE TABLE tpcb.account
+(id INT, bid INT, balance DECIMAL(10,2),
+filler CHAR(255), PRIMARY KEY(id))
+TABLESPACE ts1 STORAGE DISK
+ENGINE=NDB;
+CREATE TABLE tpcb.branch
+(bid INT, balance DECIMAL(10,2), filler VARCHAR(255),
+PRIMARY KEY(bid))
+ENGINE=NDB;
+CREATE TABLE tpcb.teller
+(tid INT, balance DECIMAL(10,2), filler VARCHAR(255),
+PRIMARY KEY(tid))
+TABLESPACE ts2 STORAGE DISK
+ENGINE=NDB;
+CREATE TABLE tpcb.history
+(id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT,
+tid INT, bid INT, amount DECIMAL(10,2),
+tdate DATETIME, teller CHAR(20), uuidf LONGBLOB,
+filler CHAR(80),PRIMARY KEY (id))
+TABLESPACE ts2 STORAGE DISK
+ENGINE=NDB;
+********* Create Procedures and Functions ************
+CREATE PROCEDURE tpcb.load()
+BEGIN
+DECLARE acct INT DEFAULT 1000;
+DECLARE brch INT DEFAULT 100;
+DECLARE tell INT DEFAULT 1000;
+DECLARE tmp INT DEFAULT 100;
+WHILE brch > 0 DO
+SET tmp = 100;
+WHILE tmp > 0 DO
+INSERT INTO tpcb.account VALUES (acct, brch, 0.0, "FRESH ACCOUNT");
+SET acct = acct - 1;
+SET tmp = tmp -1;
+END WHILE;
+INSERT INTO tpcb.branch VALUES (brch, 0.0, "FRESH BRANCH");
+SET brch = brch - 1;
+END WHILE;
+WHILE tell > 0 DO
+INSERT INTO tpcb.teller VALUES (tell, 0.0, "FRESH TELLER");
+SET tell = tell - 1;
+END WHILE;
+END|
+CREATE FUNCTION tpcb.account_id () RETURNS INT
+BEGIN
+DECLARE num INT;
+DECLARE ran INT;
+SELECT RAND() * 10 INTO ran;
+IF (ran < 2)
+THEN
+SELECT RAND() * 10 INTO num;
+ELSEIF (ran < 4)
+THEN
+SELECT RAND() * 100 INTO num;
+ELSE
+SELECT RAND() * 1000 INTO num;
+END IF;
+IF (num < 1)
+THEN
+RETURN 1;
+END IF;
+RETURN num;
+END|
+CREATE FUNCTION tpcb.teller_id () RETURNS INT
+BEGIN
+DECLARE num INT;
+DECLARE ran INT;
+SELECT RAND() * 10 INTO ran;
+IF (ran < 2)
+THEN
+SELECT RAND() * 10 INTO num;
+ELSEIF (ran < 5)
+THEN
+SELECT RAND() * 100 INTO num;
+ELSE
+SELECT RAND() * 1000 INTO num;
+END IF;
+IF (num < 1)
+THEN
+RETURN 1;
+END IF;
+RETURN num;
+END|
+CREATE PROCEDURE tpcb.trans()
+BEGIN
+DECLARE acct INT DEFAULT 0;
+DECLARE brch INT DEFAULT 0;
+DECLARE tell INT DEFAULT 0;
+DECLARE bal DECIMAL(10,2) DEFAULT 0.0;
+DECLARE amount DECIMAL(10,2) DEFAULT 1.00;
+DECLARE test INT DEFAULT 0;
+DECLARE bbal DECIMAL(10,2) DEFAULT 0.0;
+DECLARE tbal DECIMAL(10,2) DEFAULT 0.0;
+DECLARE local_uuid VARCHAR(255);
+DECLARE local_user VARCHAR(255);
+DECLARE local_time TIMESTAMP;
+SELECT RAND() * 10 INTO test;
+SELECT tpcb.account_id() INTO acct;
+SELECT tpcb.teller_id() INTO tell;
+SELECT account.balance INTO bal FROM tpcb.account WHERE id = acct;
+SELECT account.bid INTO brch FROM tpcb.account WHERE id = acct;
+SELECT teller.balance INTO tbal FROM tpcb.teller WHERE tid = tell;
+SELECT branch.balance INTO bbal FROM tpcb.branch WHERE bid = brch;
+IF (test < 5)
+THEN
+SET bal = bal + amount;
+SET bbal = bbal + amount;
+SET tbal = tbal + amount;
+UPDATE tpcb.account SET balance = bal, filler = 'account updated'
+ WHERE id = acct;
+UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated'
+ WHERE bid = brch;
+UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated'
+ WHERE tid = tell;
+ELSE
+SET bal = bal - amount;
+SET bbal = bbal - amount;
+SET tbal = tbal - amount;
+UPDATE tpcb.account SET balance = bal, filler = 'account updated'
+ WHERE id = acct;
+UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated'
+ WHERE bid = brch;
+UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated'
+ WHERE tid = tell;
+END IF;
+SET local_uuid=UUID();
+SET local_user=USER();
+SET local_time= NOW();
+INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, local_time,local_user,
+local_uuid,'completed trans');
+END|
+****** TEST 2 test time *********************************
+USE tpcb;
+*********** Load up the database ******************
+CALL tpcb.load();
+********** Check load master and slave **************
+SELECT COUNT(*) FROM account;
+COUNT(*)
+10000
+USE tpcb;
+SELECT COUNT(*) FROM account;
+COUNT(*)
+10000
+******** Run in some transactions ***************
+***** Time to try slave sync ***********
+**** Must make sure slave is clean *****
+STOP SLAVE;
+RESET SLAVE;
+DROP PROCEDURE IF EXISTS tpcb.load;
+DROP PROCEDURE IF EXISTS tpcb.trans;
+DROP TABLE IF EXISTS tpcb.account;
+DROP TABLE IF EXISTS tpcb.teller;
+DROP TABLE IF EXISTS tpcb.branch;
+DROP TABLE IF EXISTS tpcb.history;
+DROP DATABASE tpcb;
+ALTER TABLESPACE ts1
+DROP DATAFILE 'datafile.dat'
+ENGINE=NDB;
+ALTER TABLESPACE ts1
+DROP DATAFILE 'datafile02.dat'
+ENGINE=NDB;
+DROP TABLESPACE ts1 ENGINE=NDB;
+ALTER TABLESPACE ts2
+DROP DATAFILE 'datafile03.dat'
+ENGINE=NDB;
+ALTER TABLESPACE ts2
+DROP DATAFILE 'datafile04.dat'
+ENGINE=NDB;
+DROP TABLESPACE ts2 ENGINE=NDB;
+DROP LOGFILE GROUP lg1 ENGINE=NDB;
+********** Take a backup of the Master *************
+SELECT COUNT(*) FROM history;
+COUNT(*)
+1000
+SELECT COUNT(*) FROM history;
+COUNT(*)
+2000
+CREATE TEMPORARY TABLE IF NOT EXISTS mysql.backup_info (id INT, backup_id INT) ENGINE = HEAP;
+DELETE FROM mysql.backup_info;
+LOAD DATA INFILE '../tmp.dat' INTO TABLE mysql.backup_info FIELDS TERMINATED BY ',';
+SELECT @the_backup_id:=backup_id FROM mysql.backup_info;
+@the_backup_id:=backup_id
+<the_backup_id>
+DROP TABLE IF EXISTS mysql.backup_info;
+************ Restore the slave ************************
+CREATE DATABASE tpcb;
+***** Check a few slave restore values ***************
+USE tpcb;
+SELECT COUNT(*) FROM account;
+COUNT(*)
+10000
+***** Add some more records to master *********
+***** Finsh the slave sync process *******
+@the_epoch:=MAX(epoch)
+<the_epoch>
+@the_pos:=Position @the_file:=SUBSTRING_INDEX(FILE, '/', -1)
+<the_pos> master-bin.000001
+* 4. *
+* 5. *
+START SLAVE;
+**** We should be ready to continue on *************
+****** Let's make sure we match *******
+***** MASTER *******
+USE tpcb;
+SELECT COUNT(*) FROM history;
+COUNT(*)
+4050
+****** SLAVE ********
+USE tpcb;
+SELECT COUNT(*) FROM history;
+COUNT(*)
+4050
+*** DUMP MASTER & SLAVE FOR COMPARE ********
+*************** TEST 2 CLEANUP SECTION ********************
+DROP PROCEDURE IF EXISTS tpcb.load;
+DROP PROCEDURE IF EXISTS tpcb.trans;
+DROP TABLE tpcb.account;
+DROP TABLE tpcb.teller;
+DROP TABLE tpcb.branch;
+DROP TABLE tpcb.history;
+DROP DATABASE tpcb;
+ALTER TABLESPACE ts1
+DROP DATAFILE 'datafile.dat'
+ENGINE=NDB;
+ALTER TABLESPACE ts1
+DROP DATAFILE 'datafile02.dat'
+ENGINE=NDB;
+DROP TABLESPACE ts1 ENGINE=NDB;
+ALTER TABLESPACE ts2
+DROP DATAFILE 'datafile03.dat'
+ENGINE=NDB;
+ALTER TABLESPACE ts2
+DROP DATAFILE 'datafile04.dat'
+ENGINE=NDB;
+DROP TABLESPACE ts2 ENGINE=NDB;
+DROP LOGFILE GROUP lg1 ENGINE=NDB;
+****** Do dumps compare ************
View Lorry Controller Status