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