Debian Codegraph @

mysql-test/suite/rpl/r/rpl_stm_user_variables.result

raw
1 include/master-slave.inc
2 [connection master]
3 SET @positive= 18446744073709551615;
4 SET @negative= -9223372036854775808;
5 CREATE TABLE t1 (`tinyint` TINYINT,
6 `smallint` SMALLINT,
7 `mediumint` MEDIUMINT,
8 `integer` INTEGER,
9 `bigint` BIGINT,
10 `utinyint` TINYINT UNSIGNED,
11 `usmallint` SMALLINT UNSIGNED,
12 `umediumint` MEDIUMINT UNSIGNED,
13 `uinteger` INTEGER UNSIGNED,
14 `ubigint` BIGINT UNSIGNED,
15 `double` DOUBLE,
16 `float` FLOAT,
17 `real` REAL(30,2),
18 `decimal` DECIMAL(30,2)) ENGINE = MyISAM;
19 ### insert max unsigned
20 ### a) declarative
21 INSERT IGNORE INTO t1 VALUES (18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615,18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615, 18446744073709551615);;
22 #########################################
23 SELECT * FROM t1;
24 tinyint 127
25 smallint 32767
26 mediumint 8388607
27 integer 2147483647
28 bigint 9223372036854775807
29 utinyint 255
30 usmallint 65535
31 umediumint 16777215
32 uinteger 4294967295
33 ubigint 18446744073709551615
34 double 1.8446744073709552e19
35 float 1.84467e19
36 real 18446744073709552000.00
37 decimal 18446744073709551615.00
38 connection slave;
39 SELECT * FROM t1;
40 tinyint 127
41 smallint 32767
42 mediumint 8388607
43 integer 2147483647
44 bigint 9223372036854775807
45 utinyint 255
46 usmallint 65535
47 umediumint 16777215
48 uinteger 4294967295
49 ubigint 18446744073709551615
50 double 1.8446744073709552e19
51 float 1.84467e19
52 real 18446744073709552000.00
53 decimal 18446744073709551615.00
54 #########################################
55 connection master;
56 ## assertion: master and slave tables are in sync
57 include/diff_tables.inc [master:t1,slave:t1]
58 connection master;
59 TRUNCATE t1;
60 ### b) user var
61 INSERT IGNORE INTO t1 VALUES (@positive,
62 @positive,
63 @positive,
64 @positive,
65 @positive,
66 @positive,
67 @positive,
68 @positive,
69 @positive,
70 @positive,
71 @positive,
72 @positive,
73 @positive,
74 @positive);
75 #########################################
76 SELECT * FROM t1;
77 tinyint 127
78 smallint 32767
79 mediumint 8388607
80 integer 2147483647
81 bigint 9223372036854775807
82 utinyint 255
83 usmallint 65535
84 umediumint 16777215
85 uinteger 4294967295
86 ubigint 18446744073709551615
87 double 1.8446744073709552e19
88 float 1.84467e19
89 real 18446744073709552000.00
90 decimal 18446744073709551615.00
91 connection slave;
92 SELECT * FROM t1;
93 tinyint 127
94 smallint 32767
95 mediumint 8388607
96 integer 2147483647
97 bigint 9223372036854775807
98 utinyint 255
99 usmallint 65535
100 umediumint 16777215
101 uinteger 4294967295
102 ubigint 18446744073709551615
103 double 1.8446744073709552e19
104 float 1.84467e19
105 real 18446744073709552000.00
106 decimal 18446744073709551615.00
107 #########################################
108 connection master;
109 ## assertion: master and slave tables are in sync
110 include/diff_tables.inc [master:t1,slave:t1]
111 connection master;
112 TRUNCATE t1;
113 ### insert min signed
114 ### a) declarative
115 INSERT IGNORE INTO t1 VALUES (-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808,-9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808, -9223372036854775808);;
116 #########################################
117 SELECT * FROM t1;
118 tinyint -128
119 smallint -32768
120 mediumint -8388608
121 integer -2147483648
122 bigint -9223372036854775808
123 utinyint 0
124 usmallint 0
125 umediumint 0
126 uinteger 0
127 ubigint 0
128 double -9.223372036854776e18
129 float -9.22337e18
130 real -9223372036854776000.00
131 decimal -9223372036854775808.00
132 connection slave;
133 SELECT * FROM t1;
134 tinyint -128
135 smallint -32768
136 mediumint -8388608
137 integer -2147483648
138 bigint -9223372036854775808
139 utinyint 0
140 usmallint 0
141 umediumint 0
142 uinteger 0
143 ubigint 0
144 double -9.223372036854776e18
145 float -9.22337e18
146 real -9223372036854776000.00
147 decimal -9223372036854775808.00
148 #########################################
149 connection master;
150 ## assertion: master and slave tables are in sync
151 include/diff_tables.inc [master:t1,slave:t1]
152 connection master;
153 TRUNCATE t1;
154 ### b) user var
155 INSERT IGNORE INTO t1 VALUES (@negative,
156 @negative,
157 @negative,
158 @negative,
159 @negative,
160 @negative,
161 @negative,
162 @negative,
163 @negative,
164 @negative,
165 @negative,
166 @negative,
167 @negative,
168 @negative);
169 #########################################
170 SELECT * FROM t1;
171 tinyint -128
172 smallint -32768
173 mediumint -8388608
174 integer -2147483648
175 bigint -9223372036854775808
176 utinyint 0
177 usmallint 0
178 umediumint 0
179 uinteger 0
180 ubigint 0
181 double -9.223372036854776e18
182 float -9.22337e18
183 real -9223372036854776000.00
184 decimal -9223372036854775808.00
185 connection slave;
186 SELECT * FROM t1;
187 tinyint -128
188 smallint -32768
189 mediumint -8388608
190 integer -2147483648
191 bigint -9223372036854775808
192 utinyint 0
193 usmallint 0
194 umediumint 0
195 uinteger 0
196 ubigint 0
197 double -9.223372036854776e18
198 float -9.22337e18
199 real -9223372036854776000.00
200 decimal -9223372036854775808.00
201 #########################################
202 connection master;
203 ## assertion: master and slave tables are in sync
204 include/diff_tables.inc [master:t1,slave:t1]
205 connection master;
206 TRUNCATE t1;
207 ## check: contents of both tables master's and slave's
208 connection master;
209 DROP TABLE t1;
210 connection slave;
211 include/rpl_reset.inc
212 connection master;
213 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
214 CREATE TABLE t1 ( c INT, PRIMARY KEY (c)) Engine=MyISAM;
215 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW SET @aux = -1 ;
216 SET @aux = 10294947273192243200;
217 SET @aux1= @aux;
218 INSERT INTO t1 VALUES (@aux) , (@aux1);
219 ERROR 23000: Duplicate entry '2147483647' for key 'PRIMARY'
220 SET sql_mode = DEFAULT;
221 connection slave;
222 ## assertion: master and slave tables are in sync
223 include/diff_tables.inc [master:t1,slave:t1]
224 connection master;
225 DROP TRIGGER tr1;
226 DROP TABLE t1;
227 connection slave;
228
229 # The GET DIAGNOSTICS itself is not replicated, but it can set
230 # variables which can be used in statements that are replicated.
231
232 include/rpl_reset.inc
233 connection master;
234 CREATE TABLE t1 (a INT, b INT);
235 GET DIAGNOSTICS @var1 = NUMBER;
236 INSERT INTO t1 VALUES (@var1, 0), (@var1, 0);
237 CREATE PROCEDURE p1()
238 LANGUAGE SQL
239 BEGIN
240 DECLARE count INT;
241 UPDATE t1 SET b = 2 WHERE a = 0;
242 GET DIAGNOSTICS count = ROW_COUNT;
243 INSERT INTO t1 VALUES (1, count);
244 END|
245 CALL p1();
246 connection slave;
247 connection slave;
248 # check if the statement was replicated.
249 SELECT * FROM t1 ORDER BY a;
250 a b
251 0 2
252 0 2
253 1 2
254 connection master;
255 # Show events and cleanup
256 include/show_binlog_events.inc
257 Log_name Pos Event_type Server_id End_log_pos Info
258 master-bin.000001 # Gtid # # GTID #-#-#
259 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT)
260 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
261 master-bin.000001 # User var # # @`var1`=0
262 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (@var1, 0), (@var1, 0)
263 master-bin.000001 # Query # # COMMIT
264 master-bin.000001 # Gtid # # GTID #-#-#
265 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
266 BEGIN
267 DECLARE count INT;
268 UPDATE t1 SET b = 2 WHERE a = 0;
269 GET DIAGNOSTICS count = ROW_COUNT;
270 INSERT INTO t1 VALUES (1, count);
271 END
272 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
273 master-bin.000001 # Query # # use `test`; UPDATE t1 SET b = 2 WHERE a = 0
274 master-bin.000001 # Query # # COMMIT
275 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
276 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1, NAME_CONST('count',2))
277 master-bin.000001 # Query # # COMMIT
278 DROP TABLE t1;
279 DROP PROCEDURE p1;
280 connection slave;
281 include/rpl_end.inc