####################################### # Author: JBM # # Date: 8/23/2005 # # Purpose: Setup for system level # # testing of RBR # # Updated: HH 7/25/2006 partitioning # # Updated: OBN 5/08/2007 MBR specific # ####################################### --let $engine_type = Falcon --source include/master-slave.inc --connection slave set global binlog_format = 'mixed'; --connection master set global binlog_format = 'mixed'; ###### CLEAN UP SECTION ############## DROP DATABASE IF EXISTS tpcb; CREATE DATABASE tpcb; DROP DATABASE IF EXISTS test; CREATE DATABASE test; ######## Creat Table Section ######### eval CREATE TABLE test.insdel1_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id))ENGINE=$engine_type; eval CREATE TABLE test.insdel1_sub (tid MEDIUMINT AUTO_INCREMENT,fkid MEDIUMINT, filler VARCHAR(255), FOREIGN KEY(tid) REFERENCES test.insdel1_tbl(id) ON DELETE CASCADE, PRIMARY KEY(tid))ENGINE=$engine_type; eval CREATE TABLE test.insdel2_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id))ENGINE=$engine_type; eval CREATE TABLE test.insdel2_sub (tid MEDIUMINT AUTO_INCREMENT,fkid MEDIUMINT, filler VARCHAR(255), FOREIGN KEY(tid) REFERENCES test.insdel2_tbl(id) ON DELETE CASCADE, PRIMARY KEY(tid))ENGINE=$engine_type; eval CREATE TABLE test.insdel2_tracker (trid MEDIUMINT AUTO_INCREMENT, d DATETIME, u CHAR(20), PRIMARY KEY(trid)) ENGINE=$engine_type; eval CREATE TABLE test.insdel2_tracker2 (trid MEDIUMINT AUTO_INCREMENT, d DATETIME, u CHAR(20), r FLOAT, PRIMARY KEY(trid)) ENGINE=$engine_type; eval CREATE TABLE test.trans_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, b BIT(8), filler VARCHAR(255), PRIMARY KEY(id))ENGINE=$engine_type; eval CREATE TABLE test.viewer_tbl1 (a MEDIUMINT NOT NULL AUTO_INCREMENT, qty INT, price INT,PRIMARY KEY(a)) ENGINE=$engine_type; eval CREATE TABLE test.viewer_tbl2 (a MEDIUMINT, qty INT, price INT, total INT, PRIMARY KEY(a)) ENGINE=$engine_type; eval CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), filler CHAR(255), PRIMARY KEY(id))ENGINE=$engine_type; eval CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(bid))ENGINE=$engine_type; eval CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(tid))ENGINE=$engine_type; 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))ENGINE=$engine_type; ########### tables with partitions ########################### # DATA and INDEX DIRECTORY cannot be used on the same machine, as not only the master, # but also the slave tries to write into that directories, that causes an error (files # are locked). If you have 2 different machines, then on the slave the directories must # exist! eval CREATE TABLE test.pinsdel1_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id))ENGINE=$engine_type PARTITION BY KEY(id) partitions 5; eval CREATE TABLE test.pinsdel2_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id))ENGINE=$engine_type PARTITION BY RANGE(id) SUBPARTITION BY hash(id) subpartitions 2 (PARTITION pa1 values less than (10), PARTITION pa2 values less than (20), PARTITION pa3 values less than (30), PARTITION pa4 values less than (40), PARTITION pa5 values less than (50), PARTITION pa6 values less than (60), PARTITION pa7 values less than (70), PARTITION pa8 values less than (80), PARTITION pa9 values less than (90), PARTITION pa10 values less than (100), PARTITION pa11 values less than MAXVALUE); ######## Create SPs, Functions, Views and Triggers Section ############## delimiter |; CREATE PROCEDURE tpcb.load() BEGIN DECLARE acct INT DEFAULT 100000; DECLARE brch INT DEFAULT 1000; DECLARE tell INT DEFAULT 10000; 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; ELSEIF (ran < 6) THEN SELECT RAND() * 1000 INTO num; ELSEIF (ran < 8) THEN SELECT RAND() * 10000 INTO num; ELSE SELECT RAND() * 100000 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; ELSEIF (ran < 7) THEN SELECT RAND() * 1000 INTO num; ELSE SELECT RAND() * 10000 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; INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, NOW(), USER(), UUID(),'completed trans'); END| CREATE PROCEDURE test.insdel1() BEGIN DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DECLARE del_count INT; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user= CURRENT_USER(); SET local_uuid= UUID(); WHILE ins_count > 0 DO INSERT INTO test.insdel1_tbl VALUES (NULL, NOW(), USER() , UUID(), ins_count,'Going to test MBR for MySQL'); INSERT INTO test.insdel1_sub VALUES (NULL, ins_count, 'More text to test with'); SET ins_count = ins_count - 1; END WHILE; SELECT MAX(id) FROM test.insdel1_tbl INTO del_count; WHILE del_count > 0 DO DELETE FROM test.insdel1_tbl WHERE id = del_count; SET del_count = del_count - 2; END WHILE; END| CREATE PROCEDURE test.insdel2() BEGIN DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DECLARE del_count INT; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user = CURRENT_USER(); SET local_uuid=UUID(); WHILE ins_count > 0 DO INSERT INTO test.insdel2_tbl VALUES (NULL, NOW(), USER(), UUID(), ins_count,'Going to test MBR for MySQL'); INSERT INTO test.insdel2_sub VALUES (NULL,ins_count, 'More text to test with'); SET ins_count = ins_count - 1; END WHILE; SELECT MAX(id) FROM test.insdel2_tbl INTO del_count; WHILE del_count > 0 DO DELETE FROM test.insdel2_tbl WHERE id = del_count; SET del_count = del_count - 2; END WHILE; END| CREATE TRIGGER test.insdel2_bd BEFORE DELETE ON test.insdel2_tbl FOR EACH ROW BEGIN INSERT INTO test.insdel2_tracker VALUES (NULL, NOW(), USER()); END| CREATE TRIGGER test.insdel2_bi BEFORE INSERT ON test.insdel2_sub FOR EACH ROW BEGIN INSERT INTO test.insdel2_tracker2 VALUES (NULL, NOW(), USER(), RAND()); END| CREATE PROCEDURE test.trans() BEGIN DECLARE ran INT DEFAULT 0; DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user = CURRENT_USER(); SET local_uuid=UUID(); SET AUTOCOMMIT=0; START TRANSACTION; WHILE ins_count > 0 DO INSERT INTO test.trans_tbl VALUES (NULL, NOW(), USER() , UUID(), 0,'Going to test MBR for MySQL'); SET ins_count = ins_count - 1; END WHILE; SELECT RAND() * 10 INTO ran; IF (ran > 5) THEN COMMIT; ELSE ROLLBACK; END IF; SET AUTOCOMMIT=1; END| CREATE VIEW test.viewer AS SELECT a, qty, price, qty*price AS value FROM test.viewer_tbl1| CREATE PROCEDURE test.viewer_sp() BEGIN DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DELETE FROM test.viewer_tbl2; START TRANSACTION; WHILE ins_count > 0 DO INSERT INTO test.viewer_tbl1 VALUES (NULL,RAND() * 10, RAND() * 100); SET ins_count = ins_count - 1; END WHILE; INSERT INTO test.viewer_tbl2 SELECT * FROM test.viewer; END| CREATE PROCEDURE test.pinsdel1() BEGIN DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DECLARE del_count INT; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user= CURRENT_USER(); SET local_uuid= UUID(); WHILE ins_count > 0 DO INSERT INTO test.pinsdel1_tbl VALUES (NULL, NOW(), USER() , UUID(), ins_count,'Going to test MBR for MySQL'); SET ins_count = ins_count - 1; END WHILE; SELECT MAX(id) FROM test.pinsdel1_tbl INTO del_count; WHILE del_count > 0 DO DELETE FROM test.pinsdel1_tbl WHERE id = del_count; SET del_count = del_count - 2; END WHILE; END| CREATE PROCEDURE test.pinsdel2() BEGIN DECLARE ins_count INT DEFAULT 100; #<--This should be changed to 1,000,000 DECLARE del_count INT; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user = CURRENT_USER(); SET local_uuid=UUID(); WHILE ins_count > 0 DO INSERT INTO test.pinsdel2_tbl VALUES (NULL, NOW(), USER(), UUID(), ins_count,'Going to test MBR for MySQL'); SET ins_count = ins_count - 1; END WHILE; SELECT MAX(id) FROM test.pinsdel2_tbl INTO del_count; WHILE del_count > 0 DO DELETE FROM test.pinsdel2_tbl WHERE id = del_count; SET del_count = del_count - 2; END WHILE; END| delimiter ;| ############ Finish Setup Section ################### USE tpcb; SET AUTOCOMMIT=0; CALL tpcb.load(); SET AUTOCOMMIT=1; ########## Setup Script Test Section ############# # Just for testing and debugging this script. # ** Testing TPCB *** #CALL tpcb.trans(); #CALL tpcb.trans(); #SELECT COUNT(*) FROM tpcb.account WHERE filler like '%updated%'; #SELECT COUNT(*) FROM tpcb.teller WHERE filler like '%updated%'; #SELECT COUNT(*) FROM tpcb.branch WHERE filler like '%updated%'; #SELECT SUM(balance) FROM tpcb.account; #SELECT SUM(balance) FROM tpcb.branch; #SELECT SUM(balance) FROM tpcb.teller; # ** Testing Insert Delete 1 *** #CALL test.insdel1(); #SELECT * FROM test.insdel1_tbl; #SELECT * FROM test.insdel1_sub; # ** Testing Insert Delete 2 with triggers *** #CALL test.insdel2(); #SELECT * FROM test.insdel2_tbl; #SELECT * FROM test.insdel2_sub; #SELECT * FROM test.insdel2_tracker; #SELECT * FROM test.insdel2_tracker2; # *** Testing Transaction SP *** #CALL test.trans(); #CALL test.trans(); #CALL test.trans(); #SELECT * FROM test.trans_tbl; # *** Testing View *** #CALL test.viewer_sp(); #SELECT * FROM test.viewer_tbl1; #SELECT * FROM test.viewer_tbl2; # ** Testing partitioned Insert Delete 1 *** CALL test.pinsdel1(); SELECT * FROM test.pinsdel1_tbl; # ** Testing partitioned Insert Delete 2 with triggers *** CALL test.pinsdel2(); SELECT * FROM test.pinsdel2_tbl;