3
# Show that mysqlbinlog displays human readable comments to
4
# row-based log events.
7
# Create a table that represents all-known types in 5.1.
8
# Write rows that contain the mins, maxes, and NULL for each type.
9
# Write a random or "problematic" value (i.e. one that might require
10
# escaping if it's represented as a string-y type) for each type.
11
# Update each of these rows.
12
# Delete each of these rows.
15
# Bug#31455 - mysqlbinlog don't print user readable info about RBR events
18
--source include/have_log_bin.inc
21
#SHOW VARIABLES LIKE 'character_set%';
25
--echo # Preparatory cleanup.
28
DROP TABLE IF EXISTS t1, t2, t3;
32
--echo # We need a fixed timestamp to avoid varying results.
34
SET timestamp=1000000000;
37
--echo # ===================================================
38
--echo # Test #1 - Insert/update/delete with all data types.
39
--echo # ===================================================
41
--echo # Delete all existing binary logs.
46
--echo # Create a test table with all data types.
48
eval CREATE TABLE t1 (
56
c08 SMALLINT UNSIGNED,
57
c09 SMALLINT ZEROFILL,
59
c11 MEDIUMINT UNSIGNED,
60
c12 MEDIUMINT ZEROFILL,
90
c40 NATIONAL CHAR(255),
91
c41 CHAR CHARACTER SET UCS2,
92
c42 CHAR(0) CHARACTER SET UCS2,
93
c43 CHAR(1) CHARACTER SET UCS2,
94
c44 CHAR(255) CHARACTER SET UCS2,
100
c49 NATIONAL VARCHAR(0),
101
c50 NATIONAL VARCHAR(1),
102
c51 NATIONAL VARCHAR(255),
103
c52 NATIONAL VARCHAR(261),
104
c53 VARCHAR(0) CHARACTER SET UCS2,
105
c54 VARCHAR(1) CHARACTER SET UCS2,
106
c55 VARCHAR(255) CHARACTER SET UCS2,
107
c56 VARCHAR(261) CHARACTER SET UCS2,
121
c67 TINYTEXT CHARACTER SET UCS2,
124
c70 TEXT CHARACTER SET UCS2,
127
c73 MEDIUMTEXT CHARACTER SET UCS2,
130
c76 LONGTEXT CHARACTER SET UCS2,
132
c77 ENUM('a','b','c'),
133
c78 SET('a','b','c'),
135
crn INT -- row number
136
) ENGINE=$engine_type DEFAULT CHARSET latin1;
139
--echo # Insert minimum values.
141
INSERT INTO t1 VALUES (
143
b'0000000000000000000000000000000000000000000000000000000000000000', -- c02
157
-9223372036854775808, -- c16
159
00000000000000000000, -- c18
160
-3.402823466E+38, -- c19
161
1.175494351E-38, -- c20
163
-1.7976931348623E+308, -- c22 three digits cut for ps-protocol
164
2.2250738585072E-308, -- c23 three digits cut for ps-protocol
165
0000000000000000000000, -- c24
171
'1000-01-01 00:00:00', -- c29
172
'1970-01-02 00:00:01', -- c30 one day later due to timezone issues
228
1 -- crn -- row number
232
--echo # Insert maximum values.
234
INSERT INTO t1 VALUES (
236
b'1111111111111111111111111111111111111111111111111111111111111111', -- c02
250
9223372036854775807, -- c16
251
18446744073709551615, -- c17
252
18446744073709551615, -- c18
253
3.402823466E+38, -- c19
254
3.402823466E+38, -- c20
255
3.402823466E+38, -- c21
256
1.7976931348623E+308, -- c22 three digits cut for ps-protocol
257
1.7976931348623E+308, -- c23 three digits cut for ps-protocol
258
1.7976931348623E+308, -- c24 three digits cut for ps-protocol
264
'9999-12-31 23:59:59', -- c29
265
'2038-01-08 03:14:07', -- c30 one day earlier due to timezone issues
272
REPEAT(x'ff',255), -- c36
273
_utf8 x'efbfbf', -- c37
275
_utf8 x'efbfbf', -- c39
276
REPEAT(_utf8 x'efbfbf',255), -- c40
277
_ucs2 x'ffff', -- c41
279
_ucs2 x'ffff', -- c43
280
REPEAT(_ucs2 x'ffff',255), -- c44
284
REPEAT(x'ff',255), -- c47
285
REPEAT(x'ff',261), -- c48
287
_utf8 x'efbfbf', -- c50
288
REPEAT(_utf8 x'efbfbf',255), -- c51
289
REPEAT(_utf8 x'efbfbf',261), -- c52
291
_ucs2 x'ffff', -- c54
292
REPEAT(_ucs2 x'ffff',255), -- c55
293
REPEAT(_ucs2 x'ffff',261), -- c56
298
REPEAT(x'ff',255), -- c60
302
REPEAT(x'ff',255), -- c63
303
REPEAT(x'ff',261), -- c64
305
'tinyblob', -- c65 not using maximum value here
306
'tinytext', -- c66 not using maximum value here
307
'tinytext-ucs2', -- c67 not using maximum value here
308
'blob', -- c68 not using maximum value here
309
'text', -- c69 not using maximum value here
310
'text-ucs2', -- c70 not using maximum value here
311
'mediumblob', -- c71 not using maximum value here
312
'mediumtext', -- c72 not using maximum value here
313
'mediumtext-ucs2', -- c73 not using maximum value here
314
'longblob', -- c74 not using maximum value here
315
'longtext', -- c75 not using maximum value here
316
'longtext-ucs2', -- c76 not using maximum value here
321
2 -- crn -- row number
325
--echo # Insert a row with NULL values and one with arbitrary values.
327
INSERT INTO t1 VALUES (
414
3 -- crn -- row number
417
b'1111111111111111111111111111111111111111111111111111111111111111', -- c02
431
9223372036854775807, -- c16
433
00000000000000000001, -- c18
434
-1.175494351E-38, -- c19
435
1.175494351E-38, -- c20
436
000000000000001, -- c21
437
-2.2250738585072E-308, -- c22
438
2.2250738585072E-308, -- c23
439
00000000000000000000001, -- c24
445
'2008-08-04 16:18:06', -- c29
446
'2008-08-04 16:18:24', -- c30
453
REPEAT('i',255), -- c36
454
_utf8 x'c3a4', -- c37
456
_utf8 x'c3b6', -- c39
457
REPEAT(_utf8 x'c3bc',255), -- c40
458
_ucs2 x'00e4', -- c41
460
_ucs2 x'00f6', -- c43
461
REPEAT(_ucs2 x'00fc',255), -- c44
465
REPEAT('e',255), -- c47
466
REPEAT('i',261), -- c48
468
_utf8 x'c3a4', -- c50
469
REPEAT(_utf8 x'c3b6',255), -- c51
470
REPEAT(_utf8 x'c3bc',261), -- c52
472
_ucs2 x'00e4', -- c54
473
REPEAT(_ucs2 x'00f6',255), -- c55
474
REPEAT(_ucs2 x'00fc',261), -- c56
479
REPEAT('1',255), -- c60
483
REPEAT('c',255), -- c63
484
REPEAT('\'',261), -- c64
488
'tinytext-ucs2', -- c67
494
'mediumtext-ucs2', -- c73
497
'longtext-ucs2', -- c76
502
4 -- crn -- row number
506
--echo # Show what we have in the table.
507
--echo # Do not display bit type output. It's binary and confuses diff.
508
--echo # Also BINARY with nul-bytes should be avoided.
510
--replace_column 1 # 2 # 57 # 58 # 59 # 60 #
511
query_vertical SELECT * FROM t1;
514
--echo # NOTE: For matching FLOAT and DOUBLE values in WHERE conditions,
515
--echo # don't use exact match, but < or > and tweak the numbers a bit.
517
--echo # Show how much rows are affected by each statement.
522
--echo # Update min values to max values.
526
c02 = b'1111111111111111111111111111111111111111111111111111111111111111',
540
c16 = 9223372036854775807,
541
c17 = 18446744073709551615,
542
c18 = 18446744073709551615,
543
c19 = 3.402823466E+38,
544
c20 = 3.402823466E+38,
545
c21 = 3.402823466E+38,
546
c22 = 1.7976931348623E+308,
547
c23 = 1.7976931348623E+308,
548
c24 = 1.7976931348623E+308,
554
c29 = '9999-12-31 23:59:59',
555
c30 = '2038-01-08 03:14:07',
562
c36 = REPEAT(x'ff',255),
563
c37 = _utf8 x'efbfbf',
565
c39 = _utf8 x'efbfbf',
566
c40 = REPEAT(_utf8 x'efbfbf',255),
570
c44 = REPEAT(_ucs2 x'ffff',255),
574
c47 = REPEAT(x'ff',255),
575
c48 = REPEAT(x'ff',261),
577
c50 = _utf8 x'efbfbf',
578
c51 = REPEAT(_utf8 x'efbfbf',255),
579
c52 = REPEAT(_utf8 x'efbfbf',261),
582
c55 = REPEAT(_ucs2 x'ffff',255),
583
c56 = REPEAT(_ucs2 x'ffff',261),
588
c60 = REPEAT(x'ff',255),
592
c63 = REPEAT(x'ff',255),
593
c64 = REPEAT(x'ff',261),
597
c67 = 'tinytext-ucs2',
603
c73 = 'mediumtext-ucs2',
606
c76 = 'longtext-ucs2',
616
c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND
627
c13 = -2147483648 AND
630
c16 = -9223372036854775808 AND
632
c18 = 00000000000000000000 AND
633
c19 < -3.402823465E+38 AND
634
c20 < 1.175494352E-38 AND
635
c21 = 000000000000 AND
636
c22 < -1.7976931348622E+308 AND
637
c23 < 2.2250738585073E-308 AND
638
c24 = 0000000000000000000000 AND
639
c25 = -9999999999 AND
643
c28 = '1000-01-01' AND
644
c29 = '1000-01-01 00:00:00' AND
645
c30 = '1970-01-02 00:00:01' AND
646
c31 = '-838:59:59' AND
675
# this does not reproduce the inserted value: c57 = '' AND
677
# this does not reproduce the inserted value: c59 = '' AND
678
# this does not reproduce the inserted value: c60 = '' AND
704
--echo # Update max values to min values.
708
c02 = b'0000000000000000000000000000000000000000000000000000000000000000',
722
c16 = -9223372036854775808,
724
c18 = 00000000000000000000,
725
c19 = -3.402823466E+38,
726
c20 = 1.175494351E-38,
728
c22 = -1.7976931348623E+308,
729
c23 = 2.2250738585072E-308,
730
c24 = 0000000000000000000000,
736
c29 = '1000-01-01 00:00:00',
737
c30 = '1970-01-02 00:00:01',
798
# the below does not reproduce the inserted value:
799
#c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
813
c16 = 9223372036854775807 AND
814
c17 = 18446744073709551615 AND
815
c18 = 18446744073709551615 AND
816
c19 > 3.402823465E+38 AND
817
c20 > 3.402823465E+38 AND
818
c21 > 3.402823465E+38 AND
819
c22 > 1.7976931348622E+308 AND
820
c23 > 1.7976931348622E+308 AND
821
c24 > 1.7976931348622E+308 AND
826
c28 = '9999-12-31' AND
827
c29 = '9999-12-31 23:59:59' AND
828
c30 = '2038-01-08 03:14:07' AND
829
c31 = '838:59:59' AND
835
c36 = REPEAT(x'ff',255) AND
836
c37 = _utf8 x'efbfbf' AND
838
c39 = _utf8 x'efbfbf' AND
839
c40 = REPEAT(_utf8 x'efbfbf',255) AND
840
c41 = _ucs2 x'ffff' AND
842
c43 = _ucs2 x'ffff' AND
843
c44 = REPEAT(_ucs2 x'ffff',255) AND
847
c47 = REPEAT(x'ff',255) AND
848
c48 = REPEAT(x'ff',261) AND
850
c50 = _utf8 x'efbfbf' AND
851
c51 = REPEAT(_utf8 x'efbfbf',255) AND
852
c52 = REPEAT(_utf8 x'efbfbf',261) AND
854
c54 = _ucs2 x'ffff' AND
855
c55 = REPEAT(_ucs2 x'ffff',255) AND
856
c56 = REPEAT(_ucs2 x'ffff',261) AND
861
c60 = REPEAT(x'ff',255) AND
865
c63 = REPEAT(x'ff',255) AND
866
c64 = REPEAT(x'ff',261) AND
870
c67 = 'tinytext-ucs2' AND
873
c70 = 'text-ucs2' AND
874
c71 = 'mediumblob' AND
875
c72 = 'mediumtext' AND
876
c73 = 'mediumtext-ucs2' AND
879
c76 = 'longtext-ucs2' AND
887
--echo # Update NULL values to arbitrary values.
891
c02 = b'1111111111111111111111111111111111111111111111111111111111111111',
905
c16 = 9223372036854775807,
907
c18 = 00000000000000000001,
908
c19 = -1.175494351E-38,
909
c20 = 1.175494351E-38,
910
c21 = 000000000000001,
911
c22 = -2.2250738585072E-308,
912
c23 = 2.2250738585072E-308,
913
c24 = 00000000000000000000001,
919
c29 = '2008-08-04 16:18:06',
920
c30 = '2008-08-04 16:18:24',
927
c36 = REPEAT('i',255),
931
c40 = REPEAT(_utf8 x'c3bc',255),
935
c44 = REPEAT(_ucs2 x'00fc',255),
939
c47 = REPEAT('e',255),
940
c48 = REPEAT('i',261),
943
c51 = REPEAT(_utf8 x'c3b6',255),
944
c52 = REPEAT(_utf8 x'c3bc',261),
947
c55 = REPEAT(_ucs2 x'00f6',255),
948
c56 = REPEAT(_ucs2 x'00fc',261),
953
c60 = REPEAT('1',255),
957
c63 = REPEAT('c',255),
958
c64 = REPEAT('\'',261),
962
c67 = 'tinytext-ucs2',
968
c73 = 'mediumtext-ucs2',
971
c76 = 'longtext-ucs2',
1010
# this got a timestamp instead of NULL: c30 IS NULL AND
1069
--echo # Update arbitrary values to NULL values.
1163
# the below does not reproduce the inserted value:
1164
#c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1175
c13 = 2147483647 AND
1177
c15 = 0000000001 AND
1178
c16 = 9223372036854775807 AND
1180
c18 = 00000000000000000001 AND
1181
c19 > -1.175494352E-38 AND
1182
c20 < 1.175494352E-38 AND
1183
c21 = 000000000000001 AND
1184
c22 > -2.2250738585073E-308 AND
1185
c23 < 2.2250738585073E-308 AND
1186
c24 = 00000000000000000000001 AND
1187
c25 = -9999999999 AND
1188
c26 = 9999999999 AND
1189
c27 = 0000000001 AND
1191
c28 = '2008-08-04' AND
1192
c29 = '2008-08-04 16:18:06' AND
1193
c30 = '2008-08-04 16:18:24' AND
1194
c31 = '16:18:47' AND
1200
c36 = REPEAT('i',255) AND
1201
c37 = _utf8 x'c3a4' AND
1203
c39 = _utf8 x'c3b6' AND
1204
c40 = REPEAT(_utf8 x'c3bc',255) AND
1205
c41 = _ucs2 x'00e4' AND
1207
c43 = _ucs2 x'00f6' AND
1208
c44 = REPEAT(_ucs2 x'00fc',255) AND
1212
c47 = REPEAT('e',255) AND
1213
c48 = REPEAT('i',261) AND
1215
c50 = _utf8 x'c3a4' AND
1216
c51 = REPEAT(_utf8 x'c3b6',255) AND
1217
c52 = REPEAT(_utf8 x'c3bc',261) AND
1219
c54 = _ucs2 x'00e4' AND
1220
c55 = REPEAT(_ucs2 x'00f6',255) AND
1221
c56 = REPEAT(_ucs2 x'00fc',261) AND
1226
c60 = REPEAT('1',255) AND
1230
c63 = REPEAT('c',255) AND
1231
c64 = REPEAT('\'',261) AND
1233
c65 = 'tinyblob' AND
1234
c66 = 'tinytext' AND
1235
c67 = 'tinytext-ucs2' AND
1238
c70 = 'text-ucs2' AND
1239
c71 = 'mediumblob' AND
1240
c72 = 'mediumtext' AND
1241
c73 = 'mediumtext-ucs2' AND
1242
c74 = 'longblob' AND
1243
c75 = 'longtext' AND
1244
c76 = 'longtext-ucs2' AND
1252
--echo # Show what we have in the table.
1253
--echo # Do not display bit type output. It's binary and confuses diff.
1254
--echo # Also BINARY with nul-bytes should be avoided.
1256
--replace_column 1 # 2 # 57 # 58 # 59 # 60 #
1257
query_vertical SELECT * FROM t1;
1260
--echo # Delete the row that has max values now.
1262
DELETE FROM t1 WHERE
1265
# the below does not reproduce the inserted value:
1266
#c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1277
c13 = 2147483647 AND
1278
c14 = 4294967295 AND
1279
c15 = 4294967295 AND
1280
c16 = 9223372036854775807 AND
1281
c17 = 18446744073709551615 AND
1282
c18 = 18446744073709551615 AND
1283
c19 > 3.402823465E+38 AND
1284
c20 > 3.402823465E+38 AND
1285
c21 > 3.402823465E+38 AND
1286
c22 > 1.7976931348622E+308 AND
1287
c23 > 1.7976931348622E+308 AND
1288
c24 > 1.7976931348622E+308 AND
1289
c25 = 9999999999 AND
1290
c26 = 9999999999 AND
1291
c27 = 9999999999 AND
1293
c28 = '9999-12-31' AND
1294
c29 = '9999-12-31 23:59:59' AND
1295
c30 = '2038-01-08 03:14:07' AND
1296
c31 = '838:59:59' AND
1302
c36 = REPEAT(x'ff',255) AND
1303
c37 = _utf8 x'efbfbf' AND
1305
c39 = _utf8 x'efbfbf' AND
1306
c40 = REPEAT(_utf8 x'efbfbf',255) AND
1307
c41 = _ucs2 x'ffff' AND
1309
c43 = _ucs2 x'ffff' AND
1310
c44 = REPEAT(_ucs2 x'ffff',255) AND
1314
c47 = REPEAT(x'ff',255) AND
1315
c48 = REPEAT(x'ff',261) AND
1317
c50 = _utf8 x'efbfbf' AND
1318
c51 = REPEAT(_utf8 x'efbfbf',255) AND
1319
c52 = REPEAT(_utf8 x'efbfbf',261) AND
1321
c54 = _ucs2 x'ffff' AND
1322
c55 = REPEAT(_ucs2 x'ffff',255) AND
1323
c56 = REPEAT(_ucs2 x'ffff',261) AND
1328
c60 = REPEAT(x'ff',255) AND
1332
c63 = REPEAT(x'ff',255) AND
1333
c64 = REPEAT(x'ff',261) AND
1335
c65 = 'tinyblob' AND
1336
c66 = 'tinytext' AND
1337
c67 = 'tinytext-ucs2' AND
1340
c70 = 'text-ucs2' AND
1341
c71 = 'mediumblob' AND
1342
c72 = 'mediumtext' AND
1343
c73 = 'mediumtext-ucs2' AND
1344
c74 = 'longblob' AND
1345
c75 = 'longtext' AND
1346
c76 = 'longtext-ucs2' AND
1354
--echo # Delete the row that has min values now.
1356
DELETE FROM t1 WHERE
1359
c02 = b'0000000000000000000000000000000000000000000000000000000000000000' AND
1370
c13 = -2147483648 AND
1372
c15 = 0000000000 AND
1373
c16 = -9223372036854775808 AND
1375
c18 = 00000000000000000000 AND
1376
c19 < -3.402823465E+38 AND
1377
c20 < 1.175494352E-38 AND
1378
c21 = 000000000000 AND
1379
c22 < -1.7976931348622E+308 AND
1380
c23 < 2.2250738585073E-308 AND
1381
c24 = 0000000000000000000000 AND
1382
c25 = -9999999999 AND
1384
c27 = 0000000000 AND
1386
c28 = '1000-01-01' AND
1387
c29 = '1000-01-01 00:00:00' AND
1388
c30 = '1970-01-02 00:00:01' AND
1389
c31 = '-838:59:59' AND
1418
# this does not reproduce the inserted value: c57 = '' AND
1420
# this does not reproduce the inserted value: c59 = '' AND
1421
# this does not reproduce the inserted value: c60 = '' AND
1447
--echo # Delete the row that has arbitrary values now.
1449
DELETE FROM t1 WHERE
1452
# the below does not reproduce the inserted value:
1453
#c02 = b'1111111111111111111111111111111111111111111111111111111111111111' AND
1464
c13 = 2147483647 AND
1466
c15 = 0000000001 AND
1467
c16 = 9223372036854775807 AND
1469
c18 = 00000000000000000001 AND
1470
c19 > -1.175494352E-38 AND
1471
c20 < 1.175494352E-38 AND
1472
c21 = 000000000000001 AND
1473
c22 > -2.2250738585073E-308 AND
1474
c23 < 2.2250738585073E-308 AND
1475
c24 = 00000000000000000000001 AND
1476
c25 = -9999999999 AND
1477
c26 = 9999999999 AND
1478
c27 = 0000000001 AND
1480
c28 = '2008-08-04' AND
1481
c29 = '2008-08-04 16:18:06' AND
1482
c30 = '2008-08-04 16:18:24' AND
1483
c31 = '16:18:47' AND
1489
c36 = REPEAT('i',255) AND
1490
c37 = _utf8 x'c3a4' AND
1492
c39 = _utf8 x'c3b6' AND
1493
c40 = REPEAT(_utf8 x'c3bc',255) AND
1494
c41 = _ucs2 x'00e4' AND
1496
c43 = _ucs2 x'00f6' AND
1497
c44 = REPEAT(_ucs2 x'00fc',255) AND
1501
c47 = REPEAT('e',255) AND
1502
c48 = REPEAT('i',261) AND
1504
c50 = _utf8 x'c3a4' AND
1505
c51 = REPEAT(_utf8 x'c3b6',255) AND
1506
c52 = REPEAT(_utf8 x'c3bc',261) AND
1508
c54 = _ucs2 x'00e4' AND
1509
c55 = REPEAT(_ucs2 x'00f6',255) AND
1510
c56 = REPEAT(_ucs2 x'00fc',261) AND
1515
c60 = REPEAT('1',255) AND
1519
c63 = REPEAT('c',255) AND
1520
c64 = REPEAT('\'',261) AND
1522
c65 = 'tinyblob' AND
1523
c66 = 'tinytext' AND
1524
c67 = 'tinytext-ucs2' AND
1527
c70 = 'text-ucs2' AND
1528
c71 = 'mediumblob' AND
1529
c72 = 'mediumtext' AND
1530
c73 = 'mediumtext-ucs2' AND
1531
c74 = 'longblob' AND
1532
c75 = 'longtext' AND
1533
c76 = 'longtext-ucs2' AND
1541
--echo # Delete the row that has NULL values now.
1543
DELETE FROM t1 WHERE
1575
# this got a timestamp instead of NULL: c30 IS NULL AND
1634
--echo # Show what we have in the table. Should be empty now.
1636
query_vertical SELECT * FROM t1;
1639
--echo # Hide how much rows are affected by each statement.
1644
--echo # Flush all log buffers to the log file.
1649
--echo # Call mysqlbinlog to display the log file contents.
1651
let $MYSQLD_DATADIR= `select @@datadir`;
1652
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1653
--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 #/ /Xid = [0-9]*/Xid = #/ /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/
1654
--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1662
--echo # =========================================
1663
--echo # Test #2 - Multi-row insert/update/delete.
1664
--echo # =========================================
1666
--echo # Delete all existing binary logs.
1671
--echo # Create a test table with selected data types.
1673
eval CREATE TABLE t1 (
1676
crn INT -- row number
1677
) ENGINE=$engine_type DEFAULT CHARSET latin1;
1680
--echo # Show how much rows are affected by each statement.
1685
--echo # Multi-row insert.
1687
INSERT INTO t1 VALUES
1688
('2008-08-01','VARCHAR-01',1),
1689
('2008-08-02','VARCHAR-02',2),
1690
('2008-08-03','VARCHAR-03',3),
1691
('2008-08-04','VARCHAR-04',4),
1692
('2008-08-05','VARCHAR-05',5),
1693
('2008-08-06','VARCHAR-06',6),
1694
('2008-08-07','VARCHAR-07',7),
1695
('2008-08-08','VARCHAR-08',8),
1696
('2008-08-09','VARCHAR-09',9);
1699
--echo # Multi-row update.
1701
UPDATE t1 SET c28 = ADDDATE(c28,10) WHERE crn < 8;
1704
--echo # Show what we have in the table.
1709
--echo # Multi-row delete.
1711
DELETE FROM t1 WHERE crn < 8;
1714
--echo # Show what we have in the table.
1719
--echo # Hide how much rows are affected by each statement.
1724
--echo # Flush all log buffers to the log file.
1729
--echo # Call mysqlbinlog to display the log file contents.
1731
let $MYSQLD_DATADIR= `select @@datadir`;
1732
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1733
--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 #/ /Xid = [0-9]*/Xid = #/ /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/
1734
--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1742
--echo # ====================================
1743
--echo # Test #3 - Multi-table update/delete.
1744
--echo # ====================================
1746
--echo # Delete all existing binary logs.
1751
--echo # Create test tables with selected data types.
1753
eval CREATE TABLE t1 (
1756
c_1_n INT -- row number
1757
) ENGINE=$engine_type DEFAULT CHARSET latin1;
1759
eval CREATE TABLE t2 (
1762
c_2_n INT -- row number
1763
) ENGINE=$engine_type DEFAULT CHARSET latin1;
1765
eval CREATE TABLE t3 (
1768
c_3_n INT -- row number
1769
) ENGINE=$engine_type DEFAULT CHARSET latin1;
1772
--echo # Show how much rows are affected by each statement.
1777
--echo # Insert data.
1779
INSERT INTO t1 VALUES
1780
('2008-01-01','VARCHAR-01-01',11),
1781
('2008-01-02','VARCHAR-01-02',2),
1782
('2008-01-03','VARCHAR-01-03',3),
1783
('2008-01-04','VARCHAR-01-04',4),
1784
('2008-01-05','VARCHAR-01-05',5),
1785
('2008-01-06','VARCHAR-01-06',6),
1786
('2008-01-07','VARCHAR-01-07',7),
1787
('2008-01-08','VARCHAR-01-08',18),
1788
('2008-01-09','VARCHAR-01-09',19);
1790
INSERT INTO t2 VALUES
1791
('2008-02-01','VARCHAR-02-01',21),
1792
('2008-02-02','VARCHAR-02-02',2),
1793
('2008-02-03','VARCHAR-02-03',3),
1794
('2008-02-04','VARCHAR-02-04',4),
1795
('2008-02-05','VARCHAR-02-05',5),
1796
('2008-02-06','VARCHAR-02-06',6),
1797
('2008-02-07','VARCHAR-02-07',7),
1798
('2008-02-08','VARCHAR-02-08',28),
1799
('2008-02-09','VARCHAR-02-09',29);
1801
INSERT INTO t3 VALUES
1802
('2008-03-01','VARCHAR-03-01',31),
1803
('2008-03-02','VARCHAR-03-02',2),
1804
('2008-03-03','VARCHAR-03-03',3),
1805
('2008-03-04','VARCHAR-03-04',4),
1806
('2008-03-05','VARCHAR-03-05',5),
1807
('2008-03-06','VARCHAR-03-06',6),
1808
('2008-03-07','VARCHAR-03-07',7),
1809
('2008-03-08','VARCHAR-03-08',38),
1810
('2008-03-09','VARCHAR-03-09',39);
1813
--echo # Multi-table update.
1816
c_1_1 = ADDDATE(c_1_1,INTERVAL 10 YEAR),
1817
c_2_1 = ADDDATE(c_2_1,INTERVAL 20 YEAR),
1818
c_3_1 = ADDDATE(c_3_1,INTERVAL 30 YEAR)
1819
WHERE c_1_n = c_2_n AND c_2_n = c_3_n;
1822
--echo # Show what we have in the tables.
1829
--echo # Multi-table delete.
1831
DELETE FROM t1,t2,t3 USING t1 INNER JOIN t2 INNER JOIN t3
1832
WHERE c_1_n = c_2_n AND c_2_n = c_3_n;
1835
--echo # Show what we have in the tables.
1842
--echo # Hide how much rows are affected by each statement.
1847
--echo # Flush all log buffers to the log file.
1852
--echo # Call mysqlbinlog to display the log file contents.
1854
let $MYSQLD_DATADIR= `select @@datadir`;
1855
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1856
--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 #/ /Xid = [0-9]*/Xid = #/ /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/
1857
--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
1862
DROP TABLE t1, t2, t3;
1865
--echo # ===========================
1866
--echo # Test #4 - LOAD DATA INFILE.
1867
--echo # ===========================
1869
--echo # Delete all existing binary logs.
1874
--echo # Create a test table with selected data types.
1876
eval CREATE TABLE t1 (
1880
) ENGINE=$engine_type DEFAULT CHARSET latin1;
1883
--echo # Show how much rows are affected by each statement.
1890
LOAD DATA INFILE '../../std_data/loaddata5.dat'
1891
INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (c1,c2)
1895
--echo # Show what we have in the table.
1900
--echo # Hide how much rows are affected by each statement.
1905
--echo # Flush all log buffers to the log file.
1910
--echo # Call mysqlbinlog to display the log file contents.
1912
let $MYSQLD_DATADIR= `select @@datadir`;
1913
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
1914
--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 #/ /Xid = [0-9]*/Xid = #/ /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/
1915
--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001
added
removed
mysql-test/extra/binlog_tests/mysqlbinlog_row_engine.inc
