Debian Codegraph @

mysql-test/suite/rpl/t/rpl_misc_functions.test

raw
1 #
2 # Test of replicating some difficult functions
3 #
4 source include/master-slave.inc;
5
6 CALL mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT');
7
8 create table t1(id int, i int, r1 int, r2 int, p varchar(100));
9 insert into t1 values(1, connection_id(), 0, 0, "");
10 # don't put rand and password in the same query, to see if they replicate
11 # independently
12 # Pure rand test
13 --disable_warnings
14 insert into t1 values(2, 0, rand()*1000, rand()*1000, "");
15 --enable_warnings
16 # change the rand suite on the master (we do this because otherwise password()
17 # benefits from the fact that the above rand() is well replicated :
18 # it picks the same sequence element, which hides a possible bug in password() replication.
19 set sql_log_bin=0;
20 insert into t1 values(6, 0, rand(), rand(), "");
21 delete from t1 where id=6;
22 set sql_log_bin=1;
23 # Pure password test
24 insert into t1 values(3, 0, 0, 0, password('does_this_work?'));
25 # "altogether now"
26 --disable_warnings
27 insert into t1 values(4, connection_id(), rand()*1000, rand()*1000, password('does_this_still_work?'));
28 --enable_warnings
29 select * into outfile 'rpl_misc_functions.outfile' from t1;
30 let $MYSQLD_DATADIR= `select @@datadir`;
31 sync_slave_with_master;
32 create temporary table t2 like t1;
33 # read the values from the master table
34 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
35 eval load data local infile '$MYSQLD_DATADIR/test/rpl_misc_functions.outfile' into table t2;
36 # compare them with the replica; the SELECT below should return no row
37 select * from t1, t2 where (t1.id=t2.id) and not(t1.i=t2.i and t1.r1=t2.r1 and t1.r2=t2.r2 and t1.p=t2.p);
38
39 connection master;
40 drop table t1;
41
42 # End of 4.1 tests
43
44 #
45 # BUG#25543 test calling rand() multiple times on the master in
46 # a stored procedure.
47 #
48
49 --disable_warnings
50 DROP TABLE IF EXISTS t1;
51 --enable_warnings
52
53 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
54 col_a DOUBLE DEFAULT NULL);
55
56 DELIMITER |;
57
58 # Use a SP that calls rand() multiple times
59 CREATE PROCEDURE test_replication_sp1()
60 BEGIN
61 INSERT INTO t1 (col_a) VALUES (rand()), (rand());
62 INSERT INTO t1 (col_a) VALUES (rand());
63 END|
64
65 # Use a SP that calls another SP to call rand() multiple times
66 CREATE PROCEDURE test_replication_sp2()
67 BEGIN
68 CALL test_replication_sp1();
69 CALL test_replication_sp1();
70 END|
71
72 # Use a SF that calls rand() multiple times
73 CREATE FUNCTION test_replication_sf() RETURNS DOUBLE DETERMINISTIC
74 BEGIN
75 RETURN (rand() + rand());
76 END|
77
78 DELIMITER ;|
79
80 # Exercise the functions and procedures then compare the results on
81 # the master to those on the slave.
82 --disable_warnings
83 CALL test_replication_sp1();
84 CALL test_replication_sp2();
85 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
86 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
87 INSERT INTO t1 (col_a) VALUES (test_replication_sf());
88 --enable_warnings
89
90 --sync_slave_with_master
91
92 --enable_prepare_warnings
93 # Dump table on slave
94 select * from t1 into outfile "../../tmp/t1_slave.txt";
95 --disable_prepare_warnings
96
97 # Load data from slave into temp table on master
98 connection master;
99 --disable_warnings
100 create temporary table t1_slave select * from t1 where 1=0;
101 --enable_warnings
102 load data infile '../../tmp/t1_slave.txt' into table t1_slave;
103 --remove_file $MYSQLTEST_VARDIR/tmp/t1_slave.txt
104
105 # Compare master and slave temp table, use subtraction
106 # for floating point comparison of "double"
107 --disable_cursor_protocol
108 select count(*) into @aux from t1 join t1_slave using (id)
109 where ABS(t1.col_a - t1_slave.col_a) < 0.0000001 ;
110 --enable_cursor_protocol
111 SELECT @aux;
112 if (`SELECT @aux <> 12 OR @aux IS NULL`)
113 {
114 --echo # ERROR: We expected to get count(*) = 12.
115 SELECT id, col_a FROM t1;
116 SELECT id, col_a FROM t1_slave;
117 --echo # abort
118 exit;
119 }
120
121 # Cleanup
122 connection master;
123 DROP TABLE t1, t1_slave;
124 DROP PROCEDURE test_replication_sp1;
125 DROP PROCEDURE test_replication_sp2;
126 DROP FUNCTION test_replication_sf;
127 --remove_file $MYSQLD_DATADIR/test/rpl_misc_functions.outfile
128 --sync_slave_with_master
129
130
131 --source include/rpl_end.inc