diff options
| -rw-r--r-- | mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test | 290 | ||||
| -rw-r--r-- | mysql-test/include/show_binlog_using_logname.inc | 13 | ||||
| -rw-r--r-- | mysql-test/include/tpcb.inc | 155 | ||||
| -rw-r--r-- | mysql-test/include/tpcb_disk_data.inc | 166 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ndb_dd_advance.result | 178 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ndb_mix_innodb.result | 129 | ||||
| -rw-r--r-- | mysql-test/r/rpl_ndb_stm_innodb.result | 104 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_dd_advance.test | 191 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_mix_innodb-master.opt | 1 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_mix_innodb.test | 36 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_stm_innodb-master.opt | 2 | ||||
| -rw-r--r-- | mysql-test/t/rpl_ndb_stm_innodb.test | 93 |
12 files changed, 986 insertions, 372 deletions
diff --git a/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test new file mode 100644 index 00000000000..926c4106d6d --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_ndb_apply_status.test @@ -0,0 +1,290 @@ +############################################# +#Authors: TU and Jeb +#Date: 2007/04 +#Purpose: Generic replication to cluster +# and ensuring that the ndb_apply_status +# table is updated. +############################################# +# Notes: +# include/select_ndb_apply_status.inc +# Selects out the log name, start & end pos +# from the ndb_apply_status table +# +# include/show_binlog_using_logname.inc +# To select out 1 row from offset 1 +# from the start position in the binlog whose +# name is = log_name +# +# include/tpcb.inc +# Creates DATABASE tpcb, the tables and +# stored procedures for loading the DB +# and for running transactions against DB. +############################################## + + +--echo +--echo *** Test 1 *** +--echo + +connection master; +create table t1 (a int key, b int) engine innodb; +create table t2 (a int key, b int) engine innodb; + +--echo + +--sync_slave_with_master +alter table t1 engine ndb; +alter table t2 engine ndb; + +--echo + +# check binlog position without begin +connection master; +insert into t1 values (1,2); + +--echo + +--sync_slave_with_master +--source include/select_ndb_apply_status.inc + +--echo + +connection master; +# here is actually a bug, since there is no begin statement, the +# query is autocommitted, and end_pos shows end of the insert and not +# end of the commit +--replace_result $start_pos <start_pos> +--replace_column 5 # +--eval show binlog events from $start_pos limit 1 +--echo +--replace_result $start_pos <start_pos> $end_pos <end_pos> +--replace_column 2 # +--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ +--eval show binlog events from $start_pos limit 1,1 + +--echo + +# check binlog position with begin +begin; +insert into t1 values (2,3); +insert into t2 values (3,4); +commit; + +--echo + +--sync_slave_with_master +--source include/select_ndb_apply_status.inc + +connection master; +--replace_result $start_pos <start_pos> +--replace_column 5 # +--eval show binlog events from $start_pos limit 1 +--echo +--replace_result $start_pos <start_pos> +--replace_column 2 # 4 # 5 # +--eval show binlog events from $start_pos limit 1,2 +--echo +--replace_result $start_pos <start_pos> $end_pos <end_pos> +--replace_column 2 # +--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ +--eval show binlog events from $start_pos limit 3,1 + +--echo + +connection master; +DROP TABLE test.t1, test.t2; +--sync_slave_with_master +SHOW TABLES; + +# Run in some transactions using stored procedures +# and ensure that the ndb_apply_status table is +# updated to show the transactions + + +--echo +--echo *** Test 2 *** +--echo + +# Create database/tables and stored procdures +connection master; +--source include/tpcb.inc + +# Switch tables on slave to use NDB +--sync_slave_with_master +USE tpcb; +ALTER TABLE account ENGINE NDB; +ALTER TABLE branch ENGINE NDB; +ALTER TABLE teller ENGINE NDB; +ALTER TABLE history ENGINE NDB; + +--echo + +# Load DB tpcb and run some transactions +connection master; +--disable_query_log +CALL tpcb.load(); +SET AUTOCOMMIT=0; +let $run= 5; +while ($run) +{ + START TRANSACTION; + --eval CALL tpcb.trans($rpl_format); + eval SET @my_errno= $mysql_errno; + let $run_good= `SELECT @my_errno = 0`; + let $run_bad= `SELECT @my_errno <> 0`; + if ($run_good) + { + COMMIT; + } + if ($run_bad) + { + ROLLBACK; + } + dec $run; +} + +SET AUTOCOMMIT=1; +--enable_query_log + +--sync_slave_with_master +--source include/select_ndb_apply_status.inc + +--echo + +connection master; +--source include/show_binlog_using_logname.inc + +# Flush the logs on the master moving all +# Transaction to a new binlog and ensure +# that the ndb_apply_status table is updated +# to show the use of the new binlog. + +--echo +--echo ** Test 3 ** +--echo + +# Flush logs on master which should force it +# to switch to binlog #2 + +FLUSH LOGS; + +# Run in some transaction to increase end pos in +# binlog + +--disable_query_log +SET AUTOCOMMIT=0; +let $run= 5; +while ($run) +{ + START TRANSACTION; + --eval CALL tpcb.trans($rpl_format); + eval SET @my_errno= $mysql_errno; + let $run_good= `SELECT @my_errno = 0`; + let $run_bad= `SELECT @my_errno <> 0`; + if ($run_good) + { + COMMIT; + } + if ($run_bad) + { + ROLLBACK; + } + dec $run; +} +SET AUTOCOMMIT=1; +--enable_query_log + +--echo + +--sync_slave_with_master +--source include/select_ndb_apply_status.inc + +--echo + +connection master; +--source include/show_binlog_using_logname.inc + +# Now we reset both the master and the slave +# Run some more transaction and ensure +# that the ndb_apply_status is updated +# correctly + +--echo +--echo ** Test 4 ** +--echo + +# Reset both slave and master +# This should reset binlog to #1 +--source include/master-slave-reset.inc + +--echo + +# Run in some transactions and check +connection master; +--disable_query_log +SET AUTOCOMMIT=0; +let $run= 5; +while ($run) +{ + START TRANSACTION; + --eval CALL tpcb.trans($rpl_format); + eval SET @my_errno= $mysql_errno; + let $run_good= `SELECT @my_errno = 0`; + let $run_bad= `SELECT @my_errno <> 0`; + if ($run_good) + { + COMMIT; + } + if ($run_bad) + { + ROLLBACK; + } + dec $run; +} +SET AUTOCOMMIT=1; +--enable_query_log + +--sync_slave_with_master +--source include/select_ndb_apply_status.inc + +--echo + +connection master; +--source include/show_binlog_using_logname.inc + +# Since we are doing replication, it is a good +# idea to check to make sure all data was +# Replicated correctly + +--echo +--echo *** DUMP MASTER & SLAVE FOR COMPARE ******** + +--exec $MYSQL_DUMP -n -t --compact --order-by-primary --skip-extended-insert tpcb account teller branch history > $MYSQLTEST_VARDIR/tmp/master_apply_status.sql + +--exec $MYSQL_DUMP_SLAVE -n -t --compact --order-by-primary --skip-extended-insert tpcb account teller branch history > $MYSQLTEST_VARDIR/tmp/slave_apply_status.sql + +connection master; +DROP DATABASE tpcb; + +--sync_slave_with_master + +####### Commenting out until decision on Bug#27960 ########### + +#--source include/select_ndb_apply_status.inc + +#connection master; +#--eval SHOW BINLOG EVENTS in '$log_name' from $start_pos +#--source include/show_binlog_using_logname.inc + +--echo ****** Do dumps compare ************ + + +diff_files $MYSQLTEST_VARDIR/tmp/master_apply_status.sql $MYSQLTEST_VARDIR/tmp/slave_apply_status.sql; + +## Note: Ths files should only get removed, if the above diff succeeds. + +--exec rm $MYSQLTEST_VARDIR/tmp/master_apply_status.sql +--exec rm $MYSQLTEST_VARDIR/tmp/slave_apply_status.sql + + +# End of 5.1 Test diff --git a/mysql-test/include/show_binlog_using_logname.inc b/mysql-test/include/show_binlog_using_logname.inc new file mode 100644 index 00000000000..d78c28e5916 --- /dev/null +++ b/mysql-test/include/show_binlog_using_logname.inc @@ -0,0 +1,13 @@ +######################################################## +# Author: Jeb +# Date: 2007/04 +# Purpose: To select out 1 row from offset 1 +# from the start position in the binlog whose +# name is = log_name +######################################################## + +--replace_result $start_pos <start_pos> $end_pos <end_pos> +--replace_column 2 # +--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ +--eval show binlog events in '$log_name' from $start_pos limit $off_set,1 + diff --git a/mysql-test/include/tpcb.inc b/mysql-test/include/tpcb.inc new file mode 100644 index 00000000000..84a5c98f9c2 --- /dev/null +++ b/mysql-test/include/tpcb.inc @@ -0,0 +1,155 @@ +################################################## +# Author: Jeb +# Date: 2007/04 +# Purpose: To create a tpcb database, tables and +# stored procedures to load the database +# and run transactions against the DB +################################################## +--disable_warnings +DROP DATABASE IF EXISTS tpcb; +--enable_warnings +CREATE DATABASE tpcb; + +--echo +CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), + filler CHAR(255), PRIMARY KEY(id)); +--echo +CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), + PRIMARY KEY(bid)); +--echo +CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), + PRIMARY KEY(tid)); +--echo +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)); + +--echo +--echo --- Create stored procedures & functions --- +--echo + +--disable_query_log +delimiter |; +CREATE PROCEDURE tpcb.load() +BEGIN + DECLARE acct INT DEFAULT 100; + DECLARE brch INT DEFAULT 10; + DECLARE tell INT DEFAULT 100; + DECLARE tmp INT DEFAULT 10; + 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 < 5) + THEN + SELECT RAND() * 10 INTO num; + ELSE + SELECT RAND() * 100 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 < 5) + THEN + SELECT RAND() * 10 INTO num; + ELSE + SELECT RAND() * 100 INTO num; + END IF; + IF (num < 1) + THEN + RETURN 1; + END IF; + RETURN num; +END| + +CREATE PROCEDURE tpcb.trans(in format varchar(3)) +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; + + IF (format = 'SBR') + THEN + 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'); + ELSE + INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(), + UUID(),'completed trans'); + END IF; +END| +delimiter ;| +--enable_query_log +--echo +--echo *** Stored Procedures Created *** +--echo + diff --git a/mysql-test/include/tpcb_disk_data.inc b/mysql-test/include/tpcb_disk_data.inc new file mode 100644 index 00000000000..dbdf3766bbc --- /dev/null +++ b/mysql-test/include/tpcb_disk_data.inc @@ -0,0 +1,166 @@ +################################################## +# Author: Jeb +# Date: 2007/05 +# Purpose: To create a tpcb database using Disk Data, +# tables and stored procedures to load the database +# and run transactions against the DB +################################################## +--disable_warnings +DROP DATABASE IF EXISTS tpcb; +--enable_warnings +CREATE DATABASE tpcb; + +--echo +eval CREATE TABLE tpcb.account + (id INT, bid INT, balance DECIMAL(10,2), + filler CHAR(255), PRIMARY KEY(id)) + TABLESPACE $table_space STORAGE DISK + ENGINE=$engine_type; +--echo +eval CREATE TABLE tpcb.branch + (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), + PRIMARY KEY(bid))TABLESPACE $table_space STORAGE DISK + ENGINE=$engine_type; +--echo +eval CREATE TABLE tpcb.teller + (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), + PRIMARY KEY(tid)) TABLESPACE $table_space STORAGE DISK + ENGINE=$engine_type; + +--echo +eval 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 $table_space STORAGE DISK + ENGINE=$engine_type; + +--echo +--echo --- Create stored procedures & functions --- +--echo + +--disable_query_log +delimiter |; +CREATE PROCEDURE tpcb.load() +BEGIN + DECLARE acct INT DEFAULT 100; + DECLARE brch INT DEFAULT 10; + DECLARE tell INT DEFAULT 100; + DECLARE tmp INT DEFAULT 10; + 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 < 5) + THEN + SELECT RAND() * 10 INTO num; + ELSE + SELECT RAND() * 100 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 < 5) + THEN + SELECT RAND() * 10 INTO num; + ELSE + SELECT RAND() * 100 INTO num; + END IF; + IF (num < 1) + THEN + RETURN 1; + END IF; + RETURN num; +END| + +CREATE PROCEDURE tpcb.trans(in format varchar(3)) +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; + + IF (format = 'SBR') + THEN + 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'); + ELSE + INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(), + UUID(),'completed trans'); + END IF; +END| +delimiter ;| +--enable_query_log +--echo +--echo *** Stored Procedures Created *** +--echo + diff --git a/mysql-test/r/rpl_ndb_dd_advance.result b/mysql-test/r/rpl_ndb_dd_advance.result index a4614b4b484..7f26313894c 100644 --- a/mysql-test/r/rpl_ndb_dd_advance.result +++ b/mysql-test/r/rpl_ndb_dd_advance.result @@ -56,6 +56,7 @@ 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); +CREATE UNIQUE INDEX t1_i2 ON t1(c2); ALTER TABLE t1 ADD c4 TIMESTAMP; ALTER TABLE t1 ADD c5 DOUBLE; ALTER TABLE t1 ADD INDEX (c5); @@ -68,6 +69,7 @@ t1 CREATE TABLE `t1` ( `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 @@ -81,6 +83,7 @@ t1 CREATE TABLE `t1` ( `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 @@ -101,6 +104,7 @@ t1 CREATE TABLE `t1` ( `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Show second set of ALTERs on SLAVE **** @@ -113,6 +117,7 @@ t1 CREATE TABLE `t1` ( `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Third and last set of alters for test1 **** @@ -135,6 +140,7 @@ t1 CREATE TABLE `t1` ( `c3` blob, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c1 LIMIT 5; @@ -153,8 +159,16 @@ t1 CREATE TABLE `t1` ( `c3` blob, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), + UNIQUE KEY `t1_i2` (`c2`), 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 SELECT * FROM t1 where c1 = 1; c1 c2 c3 c5 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL @@ -167,148 +181,44 @@ START SLAVE; CREATE TABLESPACE ts2 ADD DATAFILE 'datafile03.dat' USE LOGFILE GROUP lg1 -INITIAL_SIZE 12M +INITIAL_SIZE 10M ENGINE=NDB; ALTER TABLESPACE ts2 ADD DATAFILE 'datafile04.dat' -INITIAL_SIZE 12M +INITIAL_SIZE 5M 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 + +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 +TABLESPACE ts2 STORAGE DISK +ENGINE=NDBCLUSTER; + +CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), -PRIMARY KEY(bid)) -ENGINE=NDB; -CREATE TABLE tpcb.teller +PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK +ENGINE=NDBCLUSTER; + +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 +PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK +ENGINE=NDBCLUSTER; + +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| +ENGINE=NDBCLUSTER; + +--- Create stored procedures & functions --- + + +*** Stored Procedures Created *** + ****** TEST 2 test time ********************************* USE tpcb; *********** Load up the database ****************** @@ -316,11 +226,11 @@ CALL tpcb.load(); ********** Check load master and slave ************** SELECT COUNT(*) FROM account; COUNT(*) -10000 +1000 USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) -10000 +1000 ******** Run in some transactions *************** ***** Time to try slave sync *********** **** Must make sure slave is clean ***** @@ -351,10 +261,10 @@ DROP LOGFILE GROUP lg1 ENGINE=NDB; ********** Take a backup of the Master ************* SELECT COUNT(*) FROM history; COUNT(*) -1000 +100 SELECT COUNT(*) FROM history; COUNT(*) -2000 +200 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 ','; @@ -368,7 +278,7 @@ CREATE DATABASE tpcb; USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) -10000 +1000 ***** Add some more records to master ********* ***** Finsh the slave sync process ******* @the_epoch:=MAX(epoch) @@ -384,12 +294,12 @@ START SLAVE; USE tpcb; SELECT COUNT(*) FROM history; COUNT(*) -4050 +400 ****** SLAVE ******** USE tpcb; SELECT COUNT(*) FROM history; COUNT(*) -4050 +400 *** DUMP MASTER & SLAVE FOR COMPARE ******** *************** TEST 2 CLEANUP SECTION ******************** DROP PROCEDURE IF EXISTS tpcb.load; diff --git a/mysql-test/r/rpl_ndb_mix_innodb.result b/mysql-test/r/rpl_ndb_mix_innodb.result new file mode 100644 index 00000000000..625d06de44b --- /dev/null +++ b/mysql-test/r/rpl_ndb_mix_innodb.result @@ -0,0 +1,129 @@ +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; + +*** Test 1 *** + +create table t1 (a int key, b int) engine innodb; +create table t2 (a int key, b int) engine innodb; + +alter table t1 engine ndb; +alter table t2 engine ndb; + +insert into t1 values (1,2); + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events from <start_pos> limit 1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 <start_pos> Query 1 # use `test`; insert into t1 values (1,2) + +show binlog events from <start_pos> limit 1,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 445 COMMIT /* XID */ + +begin; +insert into t1 values (2,3); +insert into t2 values (3,4); +commit; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> +show binlog events from <start_pos> limit 1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 <start_pos> Query 1 # use `test`; BEGIN + +show binlog events from <start_pos> limit 1,2; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Query # # use `test`; insert into t1 values (2,3) +master-bin.000001 # Query # # use `test`; insert into t2 values (3,4) + +show binlog events from <start_pos> limit 3,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +DROP TABLE test.t1, test.t2; +SHOW TABLES; +Tables_in_test + +*** Test 2 *** + +DROP DATABASE IF EXISTS tpcb; +CREATE DATABASE tpcb; + +CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), +filler CHAR(255), PRIMARY KEY(id)); + +CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), +PRIMARY KEY(bid)); + +CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), +PRIMARY KEY(tid)); + +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)); + +--- Create stored procedures & functions --- + + +*** Stored Procedures Created *** + +USE tpcb; +ALTER TABLE account ENGINE NDB; +ALTER TABLE branch ENGINE NDB; +ALTER TABLE teller ENGINE NDB; +ALTER TABLE history ENGINE NDB; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000001' from <start_pos> limit 9,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +** Test 3 ** + +FLUSH LOGS; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000002' from <start_pos> limit 9,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Xid 1 <end_pos> COMMIT /* XID */ + +** Test 4 ** + +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; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000001' from <start_pos> limit 9,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +*** DUMP MASTER & SLAVE FOR COMPARE ******** +DROP DATABASE tpcb; +****** Do dumps compare ************ diff --git a/mysql-test/r/rpl_ndb_stm_innodb.result b/mysql-test/r/rpl_ndb_stm_innodb.result index 9ed54a11c1c..426a09f945c 100644 --- a/mysql-test/r/rpl_ndb_stm_innodb.result +++ b/mysql-test/r/rpl_ndb_stm_innodb.result @@ -4,34 +4,126 @@ reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; + +*** Test 1 *** + create table t1 (a int key, b int) engine innodb; create table t2 (a int key, b int) engine innodb; + alter table t1 engine ndb; alter table t2 engine ndb; + insert into t1 values (1,2); -select @start_pos:=start_pos, @end_pos:=end_pos from mysql.ndb_apply_status; -@start_pos:=start_pos @end_pos:=end_pos -<start_pos> <end_pos> + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + show binlog events from <start_pos> limit 1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 <start_pos> Query 1 # use `test`; insert into t1 values (1,2) + show binlog events from <start_pos> limit 1,1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Xid 1 445 COMMIT /* XID */ + begin; insert into t1 values (2,3); insert into t2 values (3,4); commit; -select @start_pos:=start_pos, @end_pos:=end_pos from mysql.ndb_apply_status; -@start_pos:=start_pos @end_pos:=end_pos -<start_pos> <end_pos> + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> show binlog events from <start_pos> limit 1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 <start_pos> Query 1 # use `test`; BEGIN + show binlog events from <start_pos> limit 1,2; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; insert into t1 values (2,3) master-bin.000001 # Query # # use `test`; insert into t2 values (3,4) + show binlog events from <start_pos> limit 3,1; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +DROP TABLE test.t1, test.t2; +SHOW TABLES; +Tables_in_test + +*** Test 2 *** + +DROP DATABASE IF EXISTS tpcb; +CREATE DATABASE tpcb; + +CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), +filler CHAR(255), PRIMARY KEY(id)); + +CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), +PRIMARY KEY(bid)); + +CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), +PRIMARY KEY(tid)); + +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)); + +--- Create stored procedures & functions --- + + +*** Stored Procedures Created *** + +USE tpcb; +ALTER TABLE account ENGINE NDB; +ALTER TABLE branch ENGINE NDB; +ALTER TABLE teller ENGINE NDB; +ALTER TABLE history ENGINE NDB; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000001' from <start_pos> limit 6,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +** Test 3 ** + +FLUSH LOGS; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000002' from <start_pos> limit 6,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000002 # Xid 1 <end_pos> COMMIT /* XID */ + +** Test 4 ** + +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; + +select @log_name:=log_name, @start_pos:=start_pos, @end_pos:=end_pos +from mysql.ndb_apply_status; +@log_name:=log_name @start_pos:=start_pos @end_pos:=end_pos +<log_name> <start_pos> <end_pos> + +show binlog events in 'master-bin.000001' from <start_pos> limit 6,1; +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Xid 1 <end_pos> COMMIT /* XID */ + +*** DUMP MASTER & SLAVE FOR COMPARE ******** +DROP DATABASE tpcb; +****** Do dumps compare ************ diff --git a/mysql-test/t/rpl_ndb_dd_advance.test b/mysql-test/t/rpl_ndb_dd_advance.test index 5e346d5fe3b..723672931ab 100644 --- a/mysql-test/t/rpl_ndb_dd_advance.test +++ b/mysql-test/t/rpl_ndb_dd_advance.test @@ -9,7 +9,7 @@ --source include/have_binlog_format_row.inc --source include/ndb_default_cluster.inc --source include/not_embedded.inc ---source include/big_test.inc +#--source include/big_test.inc #--source include/have_ndb_extra.inc --source include/ndb_master-slave.inc @@ -98,7 +98,7 @@ SELECT DISTINCT FILE_NAME, FILE_TYPE, TABLESPACE_NAME, LOGFILE_GROUP_NAME connection master; CREATE INDEX t1_i ON t1(c2, c3); #Bug 18039 -#CREATE UNIQUE INDEX t1_i2 ON t1(c2); +CREATE UNIQUE INDEX t1_i2 ON t1(c2); ALTER TABLE t1 ADD c4 TIMESTAMP; ALTER TABLE t1 ADD c5 DOUBLE; ALTER TABLE t1 ADD INDEX (c5); @@ -170,7 +170,7 @@ SELECT * FROM t1 ORDER BY c1 LIMIT 5; connection slave; SHOW CREATE TABLE t1; # Bug 18094 -#SELECT * FROM t1 ORDER BY c1 LIMIT 5; +SELECT * FROM t1 ORDER BY c1 LIMIT 5; SELECT * FROM t1 where c1 = 1; connection master; @@ -198,163 +198,19 @@ connection master; CREATE TABLESPACE ts2 ADD DATAFILE 'datafile03.dat' USE LOGFILE GROUP lg1 -INITIAL_SIZE 12M +INITIAL_SIZE 10M ENGINE=NDB; ALTER TABLESPACE ts2 ADD DATAFILE 'datafile04.dat' -INITIAL_SIZE 12M +INITIAL_SIZE 5M ENGINE=NDB; -###### CLEAN UP SECTION ############## -DROP DATABASE IF EXISTS tpcb; -CREATE DATABASE tpcb; -######## Creat Table Section ######### ---echo *********** 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; - ---echo ********* Create Procedures and Functions ************ -delimiter |; -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| -delimiter ;| +let engine_type=NDBCLUSTER; +let table_space=ts2; +let format='RBR'; + +--source include/tpcb_disk_data.inc --echo ****** TEST 2 test time ********************************* USE tpcb; @@ -372,15 +228,16 @@ SELECT COUNT(*) FROM account; --echo ******** Run in some transactions *************** connection master; -let $j= 1000; +let $j= 100; --disable_query_log while ($j) { - CALL tpcb.trans(); + eval CALL tpcb.trans($format); dec $j; } --enable_query_log + --echo ***** Time to try slave sync *********** --echo **** Must make sure slave is clean ***** --connection slave @@ -421,11 +278,11 @@ connection master; SELECT COUNT(*) FROM history; -let $j= 1000; +let $j= 100; --disable_query_log while ($j) { - CALL tpcb.trans(); + eval CALL tpcb.trans($format); dec $j; } --enable_query_log @@ -464,11 +321,11 @@ SELECT COUNT(*) FROM account; --echo ***** Add some more records to master ********* connection master; -let $j= 1000; +let $j= 100; --disable_query_log while ($j) { - CALL tpcb.trans(); + eval CALL tpcb.trans($format); dec $j; } --enable_query_log @@ -484,17 +341,18 @@ while ($j) --echo ***** Finsh the slave sync process ******* --disable_query_log # 1. 2. 3. +--sync_slave_with_master --source include/ndb_setup_slave.inc --enable_query_log # 4. --echo * 4. * connection master; -let $j= 1000; +let $j= 100; --disable_query_log while ($j) { - CALL tpcb.trans(); + eval CALL tpcb.trans($format); dec $j; } --enable_query_log @@ -507,15 +365,6 @@ START SLAVE; --echo **** We should be ready to continue on ************* connection master; -let $j= 50; ---disable_query_log -while ($j) -{ - CALL tpcb.trans(); - dec $j; -} ---enable_query_log - --echo ****** Let's make sure we match ******* --echo ***** MASTER ******* USE tpcb; diff --git a/mysql-test/t/rpl_ndb_mix_innodb-master.opt b/mysql-test/t/rpl_ndb_mix_innodb-master.opt new file mode 100644 index 00000000000..3596fc4d3bd --- /dev/null +++ b/mysql-test/t/rpl_ndb_mix_innodb-master.opt @@ -0,0 +1 @@ +--innodb --default-storage-engine=innodb diff --git a/mysql-test/t/rpl_ndb_mix_innodb.test b/mysql-test/t/rpl_ndb_mix_innodb.test new file mode 100644 index 00000000000..386aa81d7b7 --- /dev/null +++ b/mysql-test/t/rpl_ndb_mix_innodb.test @@ -0,0 +1,36 @@ +############################################# +#Authors: TU and Jeb +#Date: 2007/04 +#Purpose: Generic replication to cluster +# and ensuring that the ndb_apply_status +# table is updated. +############################################# +# Notes: +# include/select_ndb_apply_status.inc +# Selects out the log name, start & end pos +# from the ndb_apply_status table +# +# include/show_binlog_using_logname.inc +# To select out 1 row from offset 1 +# from the start position in the binlog whose +# name is = log_name +# +# include/tpcb.inc +# Creates DATABASE tpcb, the tables and +# stored procedures for loading the DB +# and for running transactions against DB. +############################################## + + +## Includes ## + +--disable_query_log +--source include/have_ndb.inc +--source include/have_innodb.inc +--source include/have_binlog_format_mixed.inc +--source include/master-slave.inc +--enable_query_log +let $off_set = 9; +let $rpl_format = 'MIX'; +--source extra/rpl_tests/rpl_ndb_apply_status.test + diff --git a/mysql-test/t/rpl_ndb_stm_innodb-master.opt b/mysql-test/t/rpl_ndb_stm_innodb-master.opt index 627becdbfb5..3596fc4d3bd 100644 --- a/mysql-test/t/rpl_ndb_stm_innodb-master.opt +++ b/mysql-test/t/rpl_ndb_stm_innodb-master.opt @@ -1 +1 @@ ---innodb +--innodb --default-storage-engine=innodb diff --git a/mysql-test/t/rpl_ndb_stm_innodb.test b/mysql-test/t/rpl_ndb_stm_innodb.test index d772a3372b9..15f85c059f6 100644 --- a/mysql-test/t/rpl_ndb_stm_innodb.test +++ b/mysql-test/t/rpl_ndb_stm_innodb.test @@ -1,62 +1,35 @@ +############################################# +#Authors: TU and Jeb +#Date: 2007/04 +#Purpose: Generic replication to cluster +# and ensuring that the ndb_apply_status +# table is updated. +############################################# +# Notes: +# include/select_ndb_apply_status.inc +# Selects out the log name, start & end pos +# from the ndb_apply_status table +# +# include/show_binlog_using_logname.inc +# To select out 1 row from offset 1 +# from the start position in the binlog whose +# name is = log_name +# +# include/tpcb.inc +# Creates DATABASE tpcb, the tables and +# stored procedures for loading the DB +# and for running transactions against DB. +############################################## + + +## Includes ## + +--disable_query_log --source include/have_ndb.inc --source include/have_innodb.inc ---source include/have_binlog_format_mixed_or_statement.inc ---source include/ndb_master-slave.inc - ---connection master -create table t1 (a int key, b int) engine innodb; -create table t2 (a int key, b int) engine innodb; - ---sync_slave_with_master ---connection slave -alter table t1 engine ndb; -alter table t2 engine ndb; - -# check binlog position without begin ---connection master -insert into t1 values (1,2); - ---sync_slave_with_master ---connection slave ---replace_column 1 <start_pos> 2 <end_pos> -select @start_pos:=start_pos, @end_pos:=end_pos from mysql.ndb_apply_status; ---let $start_pos = `select @start_pos` ---let $end_pos = `select @end_pos` - ---connection master -# here is actually a bug, since there is no begin statement, the -# query is autocommitted, and end_pos shows end of the insert and not -# end of the commit ---replace_result $start_pos <start_pos> ---replace_column 5 # ---eval show binlog events from $start_pos limit 1 ---replace_result $start_pos <start_pos> $end_pos <end_pos> ---replace_column 2 # ---replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ ---eval show binlog events from $start_pos limit 1,1 - -# check binlog position with begin ---connection master -begin; -insert into t1 values (2,3); -insert into t2 values (3,4); -commit; - ---sync_slave_with_master ---connection slave ---replace_column 1 <start_pos> 2 <end_pos> -select @start_pos:=start_pos, @end_pos:=end_pos from mysql.ndb_apply_status; ---let $start_pos = `select @start_pos` ---let $end_pos = `select @end_pos` - ---connection master ---replace_result $start_pos <start_pos> ---replace_column 5 # ---eval show binlog events from $start_pos limit 1 ---replace_result $start_pos <start_pos> ---replace_column 2 # 4 # 5 # ---eval show binlog events from $start_pos limit 1,2 ---replace_result $start_pos <start_pos> $end_pos <end_pos> ---replace_column 2 # ---replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ ---eval show binlog events from $start_pos limit 3,1 +--source include/have_binlog_format_statement.inc +--source include/master-slave.inc +--enable_query_log +let $off_set = 6; +let $rpl_format = 'SBR'; +--source extra/rpl_tests/rpl_ndb_apply_status.test |