Debian Codegraph @

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

raw
1 #
2 # BUG#49562: SBR out of sync when using numeric data types + user variable
3 #
4
5 -- source include/have_binlog_format_statement.inc
6 -- source include/master-slave.inc
7
8 ## Setup user variables for several numeric types, so that we get
9 ## coverage on the User_var_log_event different val types
10
11 -- let $max_unsigned_long= 18446744073709551615
12 -- let $min_signed_long= -9223372036854775808
13 -- eval SET @positive= $max_unsigned_long
14 -- eval SET @negative= $min_signed_long
15
16 CREATE TABLE t1 (`tinyint` TINYINT,
17 `smallint` SMALLINT,
18 `mediumint` MEDIUMINT,
19 `integer` INTEGER,
20 `bigint` BIGINT,
21 `utinyint` TINYINT UNSIGNED,
22 `usmallint` SMALLINT UNSIGNED,
23 `umediumint` MEDIUMINT UNSIGNED,
24 `uinteger` INTEGER UNSIGNED,
25 `ubigint` BIGINT UNSIGNED,
26 `double` DOUBLE,
27 `float` FLOAT,
28 `real` REAL(30,2),
29 `decimal` DECIMAL(30,2)) ENGINE = MyISAM;
30
31 -- disable_warnings
32
33 -- echo ### insert max unsigned
34 -- echo ### a) declarative
35 -- eval INSERT IGNORE INTO t1 VALUES ($max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long,$max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long, $max_unsigned_long);
36
37 -- echo #########################################
38 -- query_vertical SELECT * FROM t1
39 -- sync_slave_with_master
40 -- query_vertical SELECT * FROM t1
41 -- echo #########################################
42 -- connection master
43 -- echo ## assertion: master and slave tables are in sync
44 -- let $diff_tables=master:t1,slave:t1
45 -- source include/diff_tables.inc
46 -- connection master
47 TRUNCATE t1;
48
49 -- echo ### b) user var
50 INSERT IGNORE INTO t1 VALUES (@positive,
51 @positive,
52 @positive,
53 @positive,
54 @positive,
55 @positive,
56 @positive,
57 @positive,
58 @positive,
59 @positive,
60 @positive,
61 @positive,
62 @positive,
63 @positive);
64
65 -- echo #########################################
66 -- query_vertical SELECT * FROM t1
67 -- sync_slave_with_master
68 -- query_vertical SELECT * FROM t1
69 -- echo #########################################
70 -- connection master
71 -- echo ## assertion: master and slave tables are in sync
72 -- let $diff_tables=master:t1,slave:t1
73 -- source include/diff_tables.inc
74 -- connection master
75 TRUNCATE t1;
76
77
78 -- echo ### insert min signed
79 -- echo ### a) declarative
80 -- eval INSERT IGNORE INTO t1 VALUES ($min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long,$min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long, $min_signed_long);
81
82 -- echo #########################################
83 -- query_vertical SELECT * FROM t1
84 -- sync_slave_with_master
85 -- query_vertical SELECT * FROM t1
86 -- echo #########################################
87 -- connection master
88 -- echo ## assertion: master and slave tables are in sync
89 -- let $diff_tables=master:t1,slave:t1
90 -- source include/diff_tables.inc
91 -- connection master
92 TRUNCATE t1;
93
94 -- echo ### b) user var
95 INSERT IGNORE INTO t1 VALUES (@negative,
96 @negative,
97 @negative,
98 @negative,
99 @negative,
100 @negative,
101 @negative,
102 @negative,
103 @negative,
104 @negative,
105 @negative,
106 @negative,
107 @negative,
108 @negative);
109
110 -- echo #########################################
111 -- query_vertical SELECT * FROM t1
112 -- sync_slave_with_master
113 -- query_vertical SELECT * FROM t1
114 -- echo #########################################
115 -- connection master
116
117 -- echo ## assertion: master and slave tables are in sync
118 -- let $diff_tables=master:t1,slave:t1
119 -- source include/diff_tables.inc
120 -- connection master
121 TRUNCATE t1;
122
123 -- echo ## check: contents of both tables master's and slave's
124 -- enable_warnings
125
126 ## cleanup
127 -- connection master
128 DROP TABLE t1;
129 -- sync_slave_with_master
130
131 #####################################################################
132 #
133 # BUG#51426
134 #
135 #####################################################################
136 --source include/rpl_reset.inc
137 -- connection master
138 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
139 CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM;
140
141 # offending trigger that would reset the unsigned flag for aux before
142 # binlogging of User_var_log_event would take place.
143 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ;
144
145 SET @aux = 10294947273192243200;
146 SET @aux1= @aux;
147 -- error ER_DUP_ENTRY
148 INSERT INTO t1 VALUES (@aux) , (@aux1);
149 SET sql_mode = DEFAULT;
150
151 -- sync_slave_with_master
152
153 -- echo ## assertion: master and slave tables are in sync
154 -- let $diff_tables=master:t1,slave:t1
155 -- source include/diff_tables.inc
156
157 --connection master
158 DROP TRIGGER tr1;
159 DROP TABLE t1;
160
161 -- sync_slave_with_master
162
163 --echo
164 --echo # The GET DIAGNOSTICS itself is not replicated, but it can set
165 --echo # variables which can be used in statements that are replicated.
166 --echo
167
168 --source include/rpl_reset.inc
169 connection master;
170
171 CREATE TABLE t1 (a INT, b INT);
172 GET DIAGNOSTICS @var1 = NUMBER;
173 INSERT INTO t1 VALUES (@var1, 0), (@var1, 0);
174
175 DELIMITER |;
176 CREATE PROCEDURE p1()
177 LANGUAGE SQL
178 BEGIN
179 DECLARE count INT;
180 UPDATE t1 SET b = 2 WHERE a = 0;
181 GET DIAGNOSTICS count = ROW_COUNT;
182 INSERT INTO t1 VALUES (1, count);
183 END|
184 DELIMITER ;|
185
186 CALL p1();
187
188 -- sync_slave_with_master
189
190 connection slave;
191 --echo # check if the statement was replicated.
192 SELECT * FROM t1 ORDER BY a;
193
194 connection master;
195 --echo # Show events and cleanup
196 --source include/show_binlog_events.inc
197 DROP TABLE t1;
198 DROP PROCEDURE p1;
199
200 -- sync_slave_with_master
201
202 --source include/rpl_end.inc