| 1 |
############################################################################# |
| 2 |
# This test is being created to test out the non deterministic items with # |
| 3 |
# row based replication. # |
| 4 |
# Original Author: JBM # |
| 5 |
# Original Date: Aug/10/2005 # |
| 6 |
# Update: 08/29/2005 Turn on diff # |
| 7 |
############################################################################# |
| 8 |
# Note: Many lines are commented out in this test case. These were used for # |
| 9 |
# creating the test case and debugging and are being left for # |
| 10 |
# debugging, but they can not be used for the regular testing as the # |
| 11 |
# Time changes and is not deteministic, so instead we dump both the # |
| 12 |
# master and slave and diff the dumps. If the dumps differ then the # |
| 13 |
# test case will fail. To run during diff failuers, comment out the # |
| 14 |
# diff. # |
| 15 |
############################################################################# |
| 16 |
|
| 17 |
# Includes |
| 18 |
-- source include/have_binlog_format_row.inc |
| 19 |
-- source include/set_privilege_checks_user_as_system_user.inc |
| 20 |
-- source include/master-slave.inc |
| 21 |
|
| 22 |
|
| 23 |
# Begin clean up test section |
| 24 |
connection master; |
| 25 |
--disable_warnings |
| 26 |
--error 0,1305 |
| 27 |
DROP FUNCTION test.f1; |
| 28 |
DROP TABLE IF EXISTS test.t1; |
| 29 |
DROP TABLE IF EXISTS test.t2; |
| 30 |
|
| 31 |
--enable_warnings |
| 32 |
|
| 33 |
# Section 1 test from Peter G. This test changes uses a stored function to update rows and return the timestamp. We change the value of the time stamp on the master to see what is replicated. |
| 34 |
SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; |
| 35 |
CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, t TIMESTAMP, t2 TIMESTAMP, PRIMARY KEY(a)); |
| 36 |
CREATE TABLE test.t2 (a INT NOT NULL AUTO_INCREMENT, t TIMESTAMP, t2 TIMESTAMP, PRIMARY KEY(a)); |
| 37 |
SET sql_mode = default; |
| 38 |
|
| 39 |
delimiter //; |
| 40 |
create function test.f1() RETURNS TIMESTAMP |
| 41 |
BEGIN |
| 42 |
UPDATE test.t1 SET t = CURRENT_TIMESTAMP; |
| 43 |
RETURN CURRENT_TIMESTAMP; |
| 44 |
END// |
| 45 |
delimiter ;// |
| 46 |
|
| 47 |
INSERT INTO test.t2 VALUES (null,f1(),CURRENT_TIMESTAMP); |
| 48 |
#select * from test.t1; |
| 49 |
#save_master_pos; |
| 50 |
#--source include/sync_slave_sql_with_master.inc |
| 51 |
#connection slave; |
| 52 |
#select * from test.t1; |
| 53 |
#connection master; |
| 54 |
|
| 55 |
|
| 56 |
SET TIMESTAMP=2; |
| 57 |
INSERT INTO test.t2 VALUES (null,f1(),CURRENT_TIMESTAMP); |
| 58 |
#select * from test.t1; |
| 59 |
#save_master_pos; |
| 60 |
#--source include/sync_slave_sql_with_master.inc |
| 61 |
#connection slave; |
| 62 |
#select * from test.t1; |
| 63 |
#connection master; |
| 64 |
|
| 65 |
#sleep 3; |
| 66 |
SET TIMESTAMP=1; |
| 67 |
INSERT INTO test.t2 VALUES (null,f1(),CURRENT_TIMESTAMP); |
| 68 |
#select * from test.t1; |
| 69 |
#save_master_pos; |
| 70 |
#--source include/sync_slave_sql_with_master.inc |
| 71 |
#connection slave; |
| 72 |
#select * from test.t1; |
| 73 |
#connection master; |
| 74 |
|
| 75 |
|
| 76 |
SET TIMESTAMP=333300000; |
| 77 |
INSERT INTO test.t2 VALUES (null,f1(),CURRENT_TIMESTAMP); |
| 78 |
|
| 79 |
# We need a sync to ensure that the slave has caught up before |
| 80 |
# dumping the database. |
| 81 |
--source include/sync_slave_sql_with_master.inc |
| 82 |
|
| 83 |
connection master; |
| 84 |
#Used for debugging |
| 85 |
#show binlog events; |
| 86 |
|
| 87 |
# time to dump the databases and so we can see if they match |
| 88 |
|
| 89 |
--exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/func002_master.sql |
| 90 |
--exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLTEST_VARDIR/tmp/func002_slave.sql |
| 91 |
|
| 92 |
# Cleanup |
| 93 |
DROP FUNCTION test.f1; |
| 94 |
DROP TABLE test.t1; |
| 95 |
DROP TABLE test.t2; |
| 96 |
--source include/sync_slave_sql_with_master.inc |
| 97 |
|
| 98 |
# the test will show that the diff statement failed and no reject file |
| 99 |
# will be created. You will need to go to the mysql-test dir and diff |
| 100 |
# the files your self to see what is not matching :-). The files are located |
| 101 |
# in mysql-test/var/tmp |
| 102 |
|
| 103 |
diff_files $MYSQLTEST_VARDIR/tmp/func002_master.sql $MYSQLTEST_VARDIR/tmp/func002_slave.sql; |
| 104 |
|
| 105 |
--remove_file $MYSQLTEST_VARDIR/tmp/func002_master.sql |
| 106 |
--remove_file $MYSQLTEST_VARDIR/tmp/func002_slave.sql |
| 107 |
--source include/rpl_end.inc |
| 108 |
# End of 5.0 test case |