MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
mysqlbinlog_raw_mode.inc
1 # Tests for new raw mode features in mysqlbinlog, row mode
2 # TODO: Test --wait-for-data
3 
4 # Delete all the binary logs
5 reset master;
6 
7 # we need this for getting fixed timestamps inside of this test
8 set timestamp=1000000000;
9 
10 --disable_warnings
11 drop table if exists t1;
12 --enable_warnings
13 CREATE TABLE t1 (c01 BIT);
14 INSERT INTO t1 VALUES (0);
15 INSERT INTO t1 VALUES (1);
16 DROP TABLE t1;
17 
18 CREATE TABLE t1 (c01 BIT(7));
19 INSERT INTO t1 VALUES (1);
20 INSERT INTO t1 VALUES (2);
21 INSERT INTO t1 VALUES (4);
22 INSERT INTO t1 VALUES (8);
23 INSERT INTO t1 VALUES (16);
24 INSERT INTO t1 VALUES (32);
25 INSERT INTO t1 VALUES (64);
26 INSERT INTO t1 VALUES (127);
27 DELETE FROM t1 WHERE c01=127;
28 UPDATE t1 SET c01=15 WHERE c01=16;
29 DROP TABLE t1;
30 
31 CREATE TABLE t1 (a BIT(20), b CHAR(2));
32 INSERT INTO t1 VALUES (b'00010010010010001001', 'ab');
33 DROP TABLE t1;
34 
35 CREATE TABLE t1 (c02 BIT(64));
36 INSERT INTO t1 VALUES (1);
37 INSERT INTO t1 VALUES (2);
38 INSERT INTO t1 VALUES (128);
39 INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
40 DROP TABLE t1;
41 
42 
43 CREATE TABLE t1 (c03 TINYINT);
44 INSERT INTO t1 VALUES (1),(2),(3);
45 INSERT INTO t1 VALUES (-128);
46 UPDATE t1 SET c03=2 WHERE c03=1;
47 DELETE FROM t1 WHERE c03=-128;
48 DROP TABLE t1;
49 
50 CREATE TABLE t1 (c04 TINYINT UNSIGNED);
51 INSERT INTO t1 VALUES (128), (255);
52 UPDATE t1 SET c04=2 WHERE c04=1;
53 DELETE FROM t1 WHERE c04=255;
54 DROP TABLE t1;
55 
56 CREATE TABLE t1 (c06 BOOL);
57 INSERT INTO t1 VALUES (TRUE);
58 DELETE FROM t1 WHERE c06=TRUE;
59 DROP TABLE t1;
60 
61 CREATE TABLE t1 (c07 SMALLINT);
62 INSERT INTO t1 VALUES (1234);
63 DELETE FROM t1 WHERE c07=1234;
64 DROP TABLE t1;
65 
66 CREATE TABLE t1 (c08 SMALLINT UNSIGNED);
67 INSERT INTO t1 VALUES (32768), (65535);
68 UPDATE t1 SET c08=2 WHERE c08=32768;
69 DELETE FROM t1 WHERE c08=65535;
70 DROP TABLE t1;
71 
72 CREATE TABLE t1 (c10 MEDIUMINT);
73 INSERT INTO t1 VALUES (12345);
74 DELETE FROM t1 WHERE c10=12345;
75 DROP TABLE t1;
76 
77 CREATE TABLE t1 (c11 MEDIUMINT UNSIGNED);
78 INSERT INTO t1 VALUES (8388608), (16777215);
79 UPDATE t1 SET c11=2 WHERE c11=8388608;
80 DELETE FROM t1 WHERE c11=16777215;
81 DROP TABLE t1;
82 
83 CREATE TABLE t1 (c13 INT);
84 INSERT INTO t1 VALUES (123456);
85 DELETE FROM t1 WHERE c13=123456;
86 DROP TABLE t1;
87 
88 CREATE TABLE t1 (c14 INT UNSIGNED);
89 INSERT INTO t1 VALUES (2147483648), (4294967295);
90 UPDATE t1 SET c14=2 WHERE c14=2147483648;
91 DELETE FROM t1 WHERE c14=4294967295;
92 DROP TABLE t1;
93 
94 CREATE TABLE t1 (c16 BIGINT);
95 INSERT INTO t1 VALUES (1234567890);
96 DELETE FROM t1 WHERE c16=1234567890;
97 DROP TABLE t1;
98 
99 CREATE TABLE t1 (c17 BIGINT UNSIGNED);
100 INSERT INTO t1 VALUES (9223372036854775808), (18446744073709551615);
101 UPDATE t1 SET c17=2 WHERE c17=9223372036854775808;
102 DELETE FROM t1 WHERE c17=18446744073709551615;
103 DROP TABLE t1;
104 
105 CREATE TABLE t1 (c19 FLOAT);
106 INSERT INTO t1 VALUES (123.2234);
107 DELETE FROM t1 WHERE c19>123;
108 DROP TABLE t1;
109 
110 CREATE TABLE t1 (c22 DOUBLE);
111 INSERT INTO t1 VALUES (123434.22344545);
112 DELETE FROM t1 WHERE c22>123434;
113 DROP TABLE t1;
114 
115 #
116 
117 CREATE TABLE t1 (c25 DECIMAL(10,5));
118 INSERT INTO t1 VALUES (124.45);
119 INSERT INTO t1 VALUES (-543.21);
120 DELETE FROM t1 WHERE c25=124.45;
121 DROP TABLE t1;
122 
123 #
124 
125 CREATE TABLE t1 (c28 DATE);
126 INSERT INTO t1 VALUES ('2001-02-03');
127 DELETE FROM t1 WHERE c28='2001-02-03';
128 DROP TABLE t1;
129 
130 CREATE TABLE t1 (c29 DATETIME);
131 INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
132 DELETE FROM t1 WHERE c29='2001-02-03 10:20:30';
133 DROP TABLE t1;
134 
135 CREATE TABLE t1 (c30 TIMESTAMP);
136 INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
137 DELETE FROM t1 WHERE c30='2001-02-03 10:20:30';
138 DROP TABLE t1;
139 
140 CREATE TABLE t1 (c31 TIME);
141 INSERT INTO t1 VALUES ('11:22:33');
142 DELETE FROM t1 WHERE c31='11:22:33';
143 DROP TABLE t1;
144 
145 CREATE TABLE t1 (c32 YEAR);
146 INSERT INTO t1 VALUES ('2001');
147 DELETE FROM t1 WHERE c32=2001;
148 DROP TABLE t1;
149 
150 #
151 
152 CREATE TABLE t1 (c33 CHAR);
153 INSERT INTO t1 VALUES ('a');
154 DELETE FROM t1 WHERE c33='a';
155 DROP TABLE t1;
156 
157 CREATE TABLE t1 (c34 CHAR(0));
158 INSERT INTO t1 VALUES ('');
159 DELETE FROM t1 WHERE c34='';
160 DROP TABLE t1;
161 
162 CREATE TABLE t1 (c35 CHAR(1));
163 INSERT INTO t1 VALUES ('b');
164 DELETE FROM t1 WHERE c35='b';
165 DROP TABLE t1;
166 
167 CREATE TABLE t1 (c36 CHAR(255));
168 INSERT INTO t1 VALUES (repeat('c',255));
169 DELETE FROM t1 WHERE c36>'c';
170 DROP TABLE t1;
171 
172 #
173 
174 CREATE TABLE t1 (c37 NATIONAL CHAR);
175 INSERT INTO t1 VALUES ('a');
176 DELETE FROM t1 WHERE c37='a';
177 DROP TABLE t1;
178 
179 CREATE TABLE t1 (c38 NATIONAL CHAR(0));
180 INSERT INTO t1 VALUES ('');
181 DELETE FROM t1 WHERE c38='';
182 DROP TABLE t1;
183 
184 CREATE TABLE t1 (c39 NATIONAL CHAR(1));
185 INSERT INTO t1 VALUES ('a');
186 DELETE FROM t1 WHERE c39='a';
187 DROP TABLE t1;
188 
189 CREATE TABLE t1 (c40 NATIONAL CHAR(255));
190 INSERT INTO t1 VALUES (repeat('a', 255));
191 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
192 DELETE FROM t1 WHERE c40>'a';
193 DROP TABLE t1;
194 
195 #
196 
197 CREATE TABLE t1 (c45 VARCHAR(0));
198 INSERT INTO t1 VALUES ('');
199 DELETE FROM t1 WHERE c45='';
200 DROP TABLE t1;
201 
202 CREATE TABLE t1 (c46 VARCHAR(1));
203 INSERT INTO t1 VALUES ('a');
204 DELETE FROM t1 WHERE c46='a';
205 DROP TABLE t1;
206 
207 CREATE TABLE t1 (c47 VARCHAR(255));
208 INSERT INTO t1 VALUES (repeat('a',255));
209 DELETE FROM t1 WHERE c47>'a';
210 DROP TABLE t1;
211 
212 CREATE TABLE t1 (c48 VARCHAR(261));
213 INSERT INTO t1 VALUES (repeat('a',261));
214 DELETE FROM t1 WHERE c48>'a';
215 DROP TABLE t1;
216 
217 #
218 
219 CREATE TABLE t1 (c49 NATIONAL VARCHAR(0));
220 INSERT INTO t1 VALUES ('');
221 DELETE FROM t1 WHERE c49='';
222 DROP TABLE t1;
223 
224 CREATE TABLE t1 (c50 NATIONAL VARCHAR(1));
225 INSERT INTO t1 VALUES ('a');
226 DELETE FROM t1 WHERE c50='a';
227 DROP TABLE t1;
228 
229 CREATE TABLE t1 (c51 NATIONAL VARCHAR(255));
230 INSERT INTO t1 VALUES (repeat('a',255));
231 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
232 DELETE FROM t1 WHERE c51>'a';
233 DROP TABLE t1;
234 
235 CREATE TABLE t1 (c52 NATIONAL VARCHAR(261));
236 INSERT INTO t1 VALUES (repeat('a',261));
237 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 261));
238 DELETE FROM t1 WHERE c52>'a';
239 DROP TABLE t1;
240 
241 #
242 
243 CREATE TABLE t1 (c57 BINARY);
244 INSERT INTO t1 VALUES (0x00);
245 INSERT INTO t1 VALUES (0x02);
246 INSERT INTO t1 VALUES ('a');
247 DELETE FROM t1 WHERE c57='a';
248 DROP TABLE t1;
249 
250 CREATE TABLE t1 (c58 BINARY(0));
251 INSERT INTO t1 VALUES ('');
252 DELETE FROM t1 WHERE c58='';
253 DROP TABLE t1;
254 
255 CREATE TABLE t1 (c59 BINARY(1));
256 INSERT INTO t1 VALUES (0x00);
257 INSERT INTO t1 VALUES (0x02);
258 INSERT INTO t1 VALUES ('a');
259 DELETE FROM t1 WHERE c59='a';
260 DROP TABLE t1;
261 
262 CREATE TABLE t1 (c60 BINARY(255));
263 INSERT INTO t1 VALUES (0x00);
264 INSERT INTO t1 VALUES (0x02);
265 INSERT INTO t1 VALUES (repeat('a\0',120));
266 DELETE FROM t1 WHERE c60<0x02;
267 DROP TABLE t1;
268 
269 #
270 
271 CREATE TABLE t1 (c61 VARBINARY(0));
272 INSERT INTO t1 VALUES ('');
273 DELETE FROM t1 WHERE c61='';
274 DROP TABLE t1;
275 
276 CREATE TABLE t1 (c62 VARBINARY(1));
277 INSERT INTO t1 VALUES (0x00);
278 INSERT INTO t1 VALUES (0x02);
279 INSERT INTO t1 VALUES ('a');
280 DELETE FROM t1 WHERE c62=0x02;
281 DROP TABLE t1;
282 
283 CREATE TABLE t1 (c63 VARBINARY(255));
284 INSERT INTO t1 VALUES (0x00);
285 INSERT INTO t1 VALUES (0x02);
286 INSERT INTO t1 VALUES (repeat('a\0',120));
287 DELETE FROM t1 WHERE c63=0x02;
288 DROP TABLE t1;
289 
290 #
291 flush logs;
292 
293 CREATE TABLE t1 (c65 TINYBLOB);
294 INSERT INTO t1 VALUES ('tinyblob1');
295 DELETE FROM t1 WHERE c65='tinyblob1';
296 DROP TABLE t1;
297 
298 CREATE TABLE t1 (c68 BLOB);
299 INSERT INTO t1 VALUES ('blob1');
300 DELETE FROM t1 WHERE c68='blob1';
301 DROP TABLE t1;
302 
303 CREATE TABLE t1 (c71 MEDIUMBLOB);
304 INSERT INTO t1 VALUES ('mediumblob1');
305 DELETE FROM t1 WHERE c71='mediumblob1';
306 DROP TABLE t1;
307 
308 CREATE TABLE t1 (c74 LONGBLOB);
309 INSERT INTO t1 VALUES ('longblob1');
310 DELETE FROM t1 WHERE c74='longblob1';
311 DROP TABLE t1;
312 
313 CREATE TABLE t1 (c66 TINYTEXT);
314 INSERT INTO t1 VALUES ('tinytext1');
315 DELETE FROM t1 WHERE c66='tinytext1';
316 DROP TABLE t1;
317 
318 CREATE TABLE t1 (c69 TEXT);
319 INSERT INTO t1 VALUES ('text1');
320 DELETE FROM t1 WHERE c69='text1';
321 DROP TABLE t1;
322 
323 CREATE TABLE t1 (c72 MEDIUMTEXT);
324 INSERT INTO t1 VALUES ('mediumtext1');
325 DELETE FROM t1 WHERE c72='mediumtext1';
326 DROP TABLE t1;
327 
328 CREATE TABLE t1 (c75 LONGTEXT);
329 INSERT INTO t1 VALUES ('longtext1');
330 DELETE FROM t1 WHERE c75='longtext1';
331 DROP TABLE t1;
332 
333 #
334 
335 CREATE TABLE t1 (c77 ENUM('a','b','c'));
336 INSERT INTO t1 VALUES ('b');
337 DELETE FROM t1 WHERE c77='b';
338 DROP TABLE t1;
339 
340 #
341 
342 CREATE TABLE t1 (c78 SET('a','b','c','d','e','f'));
343 INSERT INTO t1 VALUES ('a,b');
344 INSERT INTO t1 VALUES ('a,c');
345 INSERT INTO t1 VALUES ('b,c');
346 INSERT INTO t1 VALUES ('a,b,c');
347 INSERT INTO t1 VALUES ('a,b,c,d');
348 INSERT INTO t1 VALUES ('a,b,c,d,e');
349 INSERT INTO t1 VALUES ('a,b,c,d,e,f');
350 DELETE FROM t1 WHERE c78='a,b';
351 DROP TABLE t1;
352 
353 #
354 # Check multi-table update
355 #
356 CREATE TABLE t1 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
357 CREATE TABLE t2 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
358 INSERT INTO t1 SET a=1;
359 INSERT INTO t1 SET b=1;
360 INSERT INTO t2 SET a=1;
361 INSERT INTO t2 SET b=1;
362 UPDATE t1, t2 SET t1.a=10, t2.a=20;
363 DROP TABLE t1,t2;
364 
365 flush logs;
366 
367 let $MYSQLD_DATADIR= `select @@datadir`;
368 
369 # Test reading one file in raw mode
370 --exec $MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --result-file=$MYSQLTEST_VARDIR/tmp/ master-bin.000001
371 --diff_files $MYSQLTEST_VARDIR/tmp/master-bin.000001 $MYSQLD_DATADIR/master-bin.000001
372 --remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000001
373 
374 # Test reading all files in raw mode
375 # Don't test the end file since this is still open with mysqld so will be different
376 --exec $MYSQL_BINLOG --raw --read-from-remote-server --to-last-log --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --result-file=$MYSQLTEST_VARDIR/tmp/ master-bin.000001
377 --diff_files $MYSQLTEST_VARDIR/tmp/master-bin.000001 $MYSQLD_DATADIR/master-bin.000001
378 --diff_files $MYSQLTEST_VARDIR/tmp/master-bin.000002 $MYSQLD_DATADIR/master-bin.000002
379 
380 --remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000001
381 --remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000002
382 --remove_file $MYSQLTEST_VARDIR/tmp/master-bin.000003
383 
384 
385 # Test output to different filename
386 --exec $MYSQL_BINLOG --raw --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT --result-file=$MYSQLTEST_VARDIR/tmp/server1- master-bin.000001
387 --diff_files $MYSQLTEST_VARDIR/tmp/server1-master-bin.000001 $MYSQLD_DATADIR/master-bin.000001
388 --remove_file $MYSQLTEST_VARDIR/tmp/server1-master-bin.000001