Debian Codegraph @

mysql-test/suite/binlog/t/binlog_mysqlbinlog_row.test

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