MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
binlog_mysqlbinlog_row.inc
1 --echo #
2 --echo # Preparatory cleanup.
3 --echo #
4 --disable_warnings
5 DROP TABLE IF EXISTS t1;
6 --enable_warnings
7 
8 --echo #
9 --echo # We need a fixed timestamp to avoid varying results.
10 --echo #
11 SET timestamp=1000000000;
12 
13 --echo #
14 --echo # Delete all existing binary logs.
15 --echo #
16 RESET MASTER;
17 
18 
19 CREATE TABLE t1 (c01 BIT);
20 INSERT INTO t1 VALUES (0);
21 INSERT INTO t1 VALUES (1);
22 DROP TABLE t1;
23 
24 CREATE TABLE t1 (c01 BIT(7));
25 INSERT INTO t1 VALUES (1);
26 INSERT INTO t1 VALUES (2);
27 INSERT INTO t1 VALUES (4);
28 INSERT INTO t1 VALUES (8);
29 INSERT INTO t1 VALUES (16);
30 INSERT INTO t1 VALUES (32);
31 INSERT INTO t1 VALUES (64);
32 INSERT INTO t1 VALUES (127);
33 DELETE FROM t1 WHERE c01=127;
34 UPDATE t1 SET c01=15 WHERE c01=16;
35 DROP TABLE t1;
36 
37 CREATE TABLE t1 (a BIT(20), b CHAR(2));
38 INSERT INTO t1 VALUES (b'00010010010010001001', 'ab');
39 DROP TABLE t1;
40 
41 CREATE TABLE t1 (c02 BIT(64));
42 INSERT INTO t1 VALUES (1);
43 INSERT INTO t1 VALUES (2);
44 INSERT INTO t1 VALUES (128);
45 INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
46 DROP TABLE t1;
47 
48 
49 CREATE TABLE t1 (c03 TINYINT);
50 INSERT INTO t1 VALUES (1),(2),(3);
51 INSERT INTO t1 VALUES (-128);
52 UPDATE t1 SET c03=2 WHERE c03=1;
53 DELETE FROM t1 WHERE c03=-128;
54 DROP TABLE t1;
55 
56 CREATE TABLE t1 (c04 TINYINT UNSIGNED);
57 INSERT INTO t1 VALUES (128), (255);
58 UPDATE t1 SET c04=2 WHERE c04=1;
59 DELETE FROM t1 WHERE c04=255;
60 DROP TABLE t1;
61 
62 CREATE TABLE t1 (c06 BOOL);
63 INSERT INTO t1 VALUES (TRUE);
64 DELETE FROM t1 WHERE c06=TRUE;
65 DROP TABLE t1;
66 
67 CREATE TABLE t1 (c07 SMALLINT);
68 INSERT INTO t1 VALUES (1234);
69 DELETE FROM t1 WHERE c07=1234;
70 DROP TABLE t1;
71 
72 CREATE TABLE t1 (c08 SMALLINT UNSIGNED);
73 INSERT INTO t1 VALUES (32768), (65535);
74 UPDATE t1 SET c08=2 WHERE c08=32768;
75 DELETE FROM t1 WHERE c08=65535;
76 DROP TABLE t1;
77 
78 CREATE TABLE t1 (c10 MEDIUMINT);
79 INSERT INTO t1 VALUES (12345);
80 DELETE FROM t1 WHERE c10=12345;
81 DROP TABLE t1;
82 
83 CREATE TABLE t1 (c11 MEDIUMINT UNSIGNED);
84 INSERT INTO t1 VALUES (8388608), (16777215);
85 UPDATE t1 SET c11=2 WHERE c11=8388608;
86 DELETE FROM t1 WHERE c11=16777215;
87 DROP TABLE t1;
88 
89 CREATE TABLE t1 (c13 INT);
90 INSERT INTO t1 VALUES (123456);
91 DELETE FROM t1 WHERE c13=123456;
92 DROP TABLE t1;
93 
94 CREATE TABLE t1 (c14 INT UNSIGNED);
95 INSERT INTO t1 VALUES (2147483648), (4294967295);
96 UPDATE t1 SET c14=2 WHERE c14=2147483648;
97 DELETE FROM t1 WHERE c14=4294967295;
98 DROP TABLE t1;
99 
100 CREATE TABLE t1 (c16 BIGINT);
101 INSERT INTO t1 VALUES (1234567890);
102 DELETE FROM t1 WHERE c16=1234567890;
103 DROP TABLE t1;
104 
105 CREATE TABLE t1 (c17 BIGINT UNSIGNED);
106 INSERT INTO t1 VALUES (9223372036854775808), (18446744073709551615);
107 UPDATE t1 SET c17=2 WHERE c17=9223372036854775808;
108 DELETE FROM t1 WHERE c17=18446744073709551615;
109 DROP TABLE t1;
110 
111 CREATE TABLE t1 (c19 FLOAT);
112 INSERT INTO t1 VALUES (123.2234);
113 DELETE FROM t1 WHERE c19>123;
114 DROP TABLE t1;
115 
116 CREATE TABLE t1 (c22 DOUBLE);
117 INSERT INTO t1 VALUES (123434.22344545);
118 DELETE FROM t1 WHERE c22>123434;
119 DROP TABLE t1;
120 
121 #
122 
123 CREATE TABLE t1 (c25 DECIMAL(10,5));
124 INSERT INTO t1 VALUES (124.45);
125 INSERT INTO t1 VALUES (-543.21);
126 DELETE FROM t1 WHERE c25=124.45;
127 DROP TABLE t1;
128 
129 #
130 
131 CREATE TABLE t1 (c28 DATE);
132 INSERT INTO t1 VALUES ('2001-02-03');
133 DELETE FROM t1 WHERE c28='2001-02-03';
134 DROP TABLE t1;
135 
136 CREATE TABLE t1 (c29 DATETIME);
137 INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
138 DELETE FROM t1 WHERE c29='2001-02-03 10:20:30';
139 DROP TABLE t1;
140 
141 CREATE TABLE t1 (c30 TIMESTAMP);
142 INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
143 DELETE FROM t1 WHERE c30='2001-02-03 10:20:30';
144 DROP TABLE t1;
145 
146 CREATE TABLE t1 (c31 TIME);
147 INSERT INTO t1 VALUES ('11:22:33');
148 DELETE FROM t1 WHERE c31='11:22:33';
149 DROP TABLE t1;
150 
151 CREATE TABLE t1 (c32 YEAR);
152 INSERT INTO t1 VALUES ('2001');
153 DELETE FROM t1 WHERE c32=2001;
154 DROP TABLE t1;
155 
156 #
157 
158 CREATE TABLE t1 (c33 CHAR);
159 INSERT INTO t1 VALUES ('a');
160 DELETE FROM t1 WHERE c33='a';
161 DROP TABLE t1;
162 
163 CREATE TABLE t1 (c34 CHAR(0));
164 INSERT INTO t1 VALUES ('');
165 DELETE FROM t1 WHERE c34='';
166 DROP TABLE t1;
167 
168 CREATE TABLE t1 (c35 CHAR(1));
169 INSERT INTO t1 VALUES ('b');
170 DELETE FROM t1 WHERE c35='b';
171 DROP TABLE t1;
172 
173 CREATE TABLE t1 (c36 CHAR(255));
174 INSERT INTO t1 VALUES (repeat('c',255));
175 DELETE FROM t1 WHERE c36>'c';
176 DROP TABLE t1;
177 
178 #
179 
180 CREATE TABLE t1 (c37 NATIONAL CHAR);
181 INSERT INTO t1 VALUES ('a');
182 DELETE FROM t1 WHERE c37='a';
183 DROP TABLE t1;
184 
185 CREATE TABLE t1 (c38 NATIONAL CHAR(0));
186 INSERT INTO t1 VALUES ('');
187 DELETE FROM t1 WHERE c38='';
188 DROP TABLE t1;
189 
190 CREATE TABLE t1 (c39 NATIONAL CHAR(1));
191 INSERT INTO t1 VALUES ('a');
192 DELETE FROM t1 WHERE c39='a';
193 DROP TABLE t1;
194 
195 CREATE TABLE t1 (c40 NATIONAL CHAR(255));
196 INSERT INTO t1 VALUES (repeat('a', 255));
197 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
198 DELETE FROM t1 WHERE c40>'a';
199 DROP TABLE t1;
200 
201 #
202 
203 CREATE TABLE t1 (c41 CHAR CHARACTER SET UCS2);
204 INSERT INTO t1 VALUES ('a');
205 DELETE FROM t1 WHERE c41='a';
206 DROP TABLE t1;
207 
208 CREATE TABLE t1 (c42 CHAR(0) CHARACTER SET UCS2);
209 INSERT INTO t1 VALUES ('');
210 DELETE FROM t1 WHERE c42='';
211 DROP TABLE t1;
212 
213 CREATE TABLE t1 (c43 CHAR(1) CHARACTER SET UCS2);
214 INSERT INTO t1 VALUES ('a');
215 DELETE FROM t1 WHERE c43='a';
216 DROP TABLE t1;
217 
218 CREATE TABLE t1 (c44 CHAR(255) CHARACTER SET UCS2);
219 INSERT INTO t1 VALUES (repeat('a', 255));
220 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
221 DELETE FROM t1 WHERE c44>'a';
222 DROP TABLE t1;
223 
224 #
225 
226 CREATE TABLE t1 (c45 VARCHAR(0));
227 INSERT INTO t1 VALUES ('');
228 DELETE FROM t1 WHERE c45='';
229 DROP TABLE t1;
230 
231 CREATE TABLE t1 (c46 VARCHAR(1));
232 INSERT INTO t1 VALUES ('a');
233 DELETE FROM t1 WHERE c46='a';
234 DROP TABLE t1;
235 
236 CREATE TABLE t1 (c47 VARCHAR(255));
237 INSERT INTO t1 VALUES (repeat('a',255));
238 DELETE FROM t1 WHERE c47>'a';
239 DROP TABLE t1;
240 
241 CREATE TABLE t1 (c48 VARCHAR(261));
242 INSERT INTO t1 VALUES (repeat('a',261));
243 DELETE FROM t1 WHERE c48>'a';
244 DROP TABLE t1;
245 
246 #
247 
248 CREATE TABLE t1 (c49 NATIONAL VARCHAR(0));
249 INSERT INTO t1 VALUES ('');
250 DELETE FROM t1 WHERE c49='';
251 DROP TABLE t1;
252 
253 CREATE TABLE t1 (c50 NATIONAL VARCHAR(1));
254 INSERT INTO t1 VALUES ('a');
255 DELETE FROM t1 WHERE c50='a';
256 DROP TABLE t1;
257 
258 CREATE TABLE t1 (c51 NATIONAL VARCHAR(255));
259 INSERT INTO t1 VALUES (repeat('a',255));
260 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
261 DELETE FROM t1 WHERE c51>'a';
262 DROP TABLE t1;
263 
264 CREATE TABLE t1 (c52 NATIONAL VARCHAR(261));
265 INSERT INTO t1 VALUES (repeat('a',261));
266 INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 261));
267 DELETE FROM t1 WHERE c52>'a';
268 DROP TABLE t1;
269 
270 #
271 
272 CREATE TABLE t1 (c53 VARCHAR(0) CHARACTER SET ucs2);
273 INSERT INTO t1 VALUES ('');
274 DELETE FROM t1 WHERE c53='';
275 DROP TABLE t1;
276 
277 CREATE TABLE t1 (c54 VARCHAR(1) CHARACTER SET ucs2);
278 INSERT INTO t1 VALUES ('a');
279 DELETE FROM t1 WHERE c54='a';
280 DROP TABLE t1;
281 
282 CREATE TABLE t1 (c55 VARCHAR(255) CHARACTER SET ucs2);
283 INSERT INTO t1 VALUES (repeat('ab', 127));
284 DELETE FROM t1 WHERE c55>'a';
285 DROP TABLE t1;
286 
287 CREATE TABLE t1 (c56 VARCHAR(261) CHARACTER SET ucs2);
288 INSERT INTO t1 VALUES (repeat('ab', 130));
289 DELETE FROM t1 WHERE c56>'a';
290 DROP TABLE t1;
291 
292 #
293 
294 CREATE TABLE t1 (c57 BINARY);
295 INSERT INTO t1 VALUES (0x00);
296 INSERT INTO t1 VALUES (0x02);
297 INSERT INTO t1 VALUES ('a');
298 DELETE FROM t1 WHERE c57='a';
299 DROP TABLE t1;
300 
301 CREATE TABLE t1 (c58 BINARY(0));
302 INSERT INTO t1 VALUES ('');
303 DELETE FROM t1 WHERE c58='';
304 DROP TABLE t1;
305 
306 CREATE TABLE t1 (c59 BINARY(1));
307 INSERT INTO t1 VALUES (0x00);
308 INSERT INTO t1 VALUES (0x02);
309 INSERT INTO t1 VALUES ('a');
310 DELETE FROM t1 WHERE c59='a';
311 DROP TABLE t1;
312 
313 CREATE TABLE t1 (c60 BINARY(255));
314 INSERT INTO t1 VALUES (0x00);
315 INSERT INTO t1 VALUES (0x02);
316 INSERT INTO t1 VALUES (repeat('a\0',120));
317 DELETE FROM t1 WHERE c60<0x02;
318 DROP TABLE t1;
319 
320 #
321 
322 CREATE TABLE t1 (c61 VARBINARY(0));
323 INSERT INTO t1 VALUES ('');
324 DELETE FROM t1 WHERE c61='';
325 DROP TABLE t1;
326 
327 CREATE TABLE t1 (c62 VARBINARY(1));
328 INSERT INTO t1 VALUES (0x00);
329 INSERT INTO t1 VALUES (0x02);
330 INSERT INTO t1 VALUES ('a');
331 DELETE FROM t1 WHERE c62=0x02;
332 DROP TABLE t1;
333 
334 CREATE TABLE t1 (c63 VARBINARY(255));
335 INSERT INTO t1 VALUES (0x00);
336 INSERT INTO t1 VALUES (0x02);
337 INSERT INTO t1 VALUES (repeat('a\0',120));
338 DELETE FROM t1 WHERE c63=0x02;
339 DROP TABLE t1;
340 
341 #
342 
343 CREATE TABLE t1 (c65 TINYBLOB);
344 INSERT INTO t1 VALUES ('tinyblob1');
345 DELETE FROM t1 WHERE c65='tinyblob1';
346 DROP TABLE t1;
347 
348 CREATE TABLE t1 (c68 BLOB);
349 INSERT INTO t1 VALUES ('blob1');
350 DELETE FROM t1 WHERE c68='blob1';
351 DROP TABLE t1;
352 
353 CREATE TABLE t1 (c71 MEDIUMBLOB);
354 INSERT INTO t1 VALUES ('mediumblob1');
355 DELETE FROM t1 WHERE c71='mediumblob1';
356 DROP TABLE t1;
357 
358 CREATE TABLE t1 (c74 LONGBLOB);
359 INSERT INTO t1 VALUES ('longblob1');
360 DELETE FROM t1 WHERE c74='longblob1';
361 DROP TABLE t1;
362 
363 CREATE TABLE t1 (c66 TINYTEXT);
364 INSERT INTO t1 VALUES ('tinytext1');
365 DELETE FROM t1 WHERE c66='tinytext1';
366 DROP TABLE t1;
367 
368 CREATE TABLE t1 (c69 TEXT);
369 INSERT INTO t1 VALUES ('text1');
370 DELETE FROM t1 WHERE c69='text1';
371 DROP TABLE t1;
372 
373 CREATE TABLE t1 (c72 MEDIUMTEXT);
374 INSERT INTO t1 VALUES ('mediumtext1');
375 DELETE FROM t1 WHERE c72='mediumtext1';
376 DROP TABLE t1;
377 
378 CREATE TABLE t1 (c75 LONGTEXT);
379 INSERT INTO t1 VALUES ('longtext1');
380 DELETE FROM t1 WHERE c75='longtext1';
381 DROP TABLE t1;
382 
383 #
384 
385 CREATE TABLE t1 (c67 TINYTEXT CHARACTER SET UCS2);
386 INSERT INTO t1 VALUES ('tinytext1');
387 DELETE FROM t1 WHERE c67='tinytext1';
388 DROP TABLE t1;
389 
390 CREATE TABLE t1 (c70 TEXT CHARACTER SET UCS2);
391 INSERT INTO t1 VALUES ('text1');
392 DELETE FROM t1 WHERE c70='text1';
393 DROP TABLE t1;
394 
395 CREATE TABLE t1 (c73 MEDIUMTEXT CHARACTER SET UCS2);
396 INSERT INTO t1 VALUES ('mediumtext1');
397 DELETE FROM t1 WHERE c73='mediumtext1';
398 DROP TABLE t1;
399 
400 CREATE TABLE t1 (c76 LONGTEXT CHARACTER SET UCS2);
401 INSERT INTO t1 VALUES ('longtext1');
402 DELETE FROM t1 WHERE c76='longtext1';
403 DROP TABLE t1;
404 
405 #
406 
407 CREATE TABLE t1 (c77 ENUM('a','b','c'));
408 INSERT INTO t1 VALUES ('b');
409 DELETE FROM t1 WHERE c77='b';
410 DROP TABLE t1;
411 
412 #
413 
414 CREATE TABLE t1 (c78 SET('a','b','c','d','e','f'));
415 INSERT INTO t1 VALUES ('a,b');
416 INSERT INTO t1 VALUES ('a,c');
417 INSERT INTO t1 VALUES ('b,c');
418 INSERT INTO t1 VALUES ('a,b,c');
419 INSERT INTO t1 VALUES ('a,b,c,d');
420 INSERT INTO t1 VALUES ('a,b,c,d,e');
421 INSERT INTO t1 VALUES ('a,b,c,d,e,f');
422 DELETE FROM t1 WHERE c78='a,b';
423 DROP TABLE t1;
424 
425 #
426 # Start of FSP-enabled data types
427 #
428 SET time_zone='+00:00';
429 SET timestamp=UNIX_TIMESTAMP('2002-02-02 20:20:20') + 0.123456;
430 SET @t='00:00:01.123456';
431 CREATE TABLE t1 (
432  c79 TIME(6), c80 TIME(5), c81 TIME(4),
433  c82 TIME(3), c83 TIME(2), c84 TIME(1)
434 );
435 INSERT INTO t1 VALUES (@t, @t, @t, @t, @t, @t);
436 INSERT INTO t1 VALUES (NOW(), NOW(), NOW(), NOW(), NOW(), NOW());
437 INSERT INTO t1 VALUES (NOW(6), NOW(6), NOW(6), NOW(6), NOW(6), NOW(6));
438 DROP TABLE t1;
439 
440 SET @ts='2001-01-01 00:00:00.123456';
441 CREATE TABLE t1 (
442  c85 DATETIME(6), c86 DATETIME(5), c87 DATETIME(4),
443  c88 DATETIME(3), c89 DATETIME(2), c90 DATETIME(1)
444 );
445 INSERT INTO t1 VALUES (@ts, @ts, @ts, @ts, @ts, @ts);
446 INSERT INTO t1 VALUES (NOW(), NOW(), NOW(), NOW(), NOW(), NOW());
447 INSERT INTO t1 VALUES (NOW(6), NOW(6), NOW(6), NOW(6), NOW(6), NOW(6));
448 DROP TABLE t1;
449 
450 SET @ts='2001-01-01 00:00:00.123456';
451 CREATE TABLE t1 (
452  c85 TIMESTAMP(6), c86 TIMESTAMP(5), c87 TIMESTAMP(4),
453  c88 TIMESTAMP(3), c89 TIMESTAMP(2), c90 TIMESTAMP(1)
454 );
455 INSERT INTO t1 VALUES (@ts, @ts, @ts, @ts, @ts, @ts);
456 INSERT INTO t1 VALUES (NOW(), NOW(), NOW(), NOW(), NOW(), NOW());
457 INSERT INTO t1 VALUES (NOW(6), NOW(6), NOW(6), NOW(6), NOW(6), NOW(6));
458 DROP TABLE t1;
459 
460 SET timestamp=1000000000;
461 SET time_zone=default;
462 #
463 # End of FSP-enabled data types
464 #
465 
466 
467 #
468 # Check multi-table update
469 #
470 CREATE TABLE t1 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
471 CREATE TABLE t2 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
472 INSERT INTO t1 SET a=1;
473 INSERT INTO t1 SET b=1;
474 INSERT INTO t2 SET a=1;
475 INSERT INTO t2 SET b=1;
476 UPDATE t1, t2 SET t1.a=10, t2.a=20;
477 DROP TABLE t1,t2;
478 
479 flush logs;
480 
481 let $MYSQLD_DATADIR= `select @@datadir`;
482 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
483 --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /(@[0-9]*=[0-9]*[.][0-9]{1,3})[0-9e+-]*[^ ]*(.*(FLOAT|DOUBLE).*[*].)/\1...\2/ /SET @@SESSION.GTID_NEXT= '.*'/SET @@SESSION.GTID_NEXT= 'GTID'/ /CRC32 0x[0-9a-f]{8}/CRC32 #/
484 --exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001