1
--source include/have_log_bin.inc
2
--source include/have_binlog_format_row.inc
3
--source include/have_ucs2.inc
6
--echo # Preparatory cleanup.
9
DROP TABLE IF EXISTS t1;
13
--echo # We need a fixed timestamp to avoid varying results.
15
SET timestamp=1000000000;
18
--echo # Delete all existing binary logs.
23
CREATE TABLE t1 (c01 BIT);
24
INSERT INTO t1 VALUES (0);
25
INSERT INTO t1 VALUES (1);
28
CREATE TABLE t1 (c01 BIT(7));
29
INSERT INTO t1 VALUES (1);
30
INSERT INTO t1 VALUES (2);
31
INSERT INTO t1 VALUES (4);
32
INSERT INTO t1 VALUES (8);
33
INSERT INTO t1 VALUES (16);
34
INSERT INTO t1 VALUES (32);
35
INSERT INTO t1 VALUES (64);
36
INSERT INTO t1 VALUES (127);
37
DELETE FROM t1 WHERE c01=127;
38
UPDATE t1 SET c01=15 WHERE c01=16;
41
CREATE TABLE t1 (a BIT(20), b CHAR(2));
42
INSERT INTO t1 VALUES (b'00010010010010001001', 'ab');
45
CREATE TABLE t1 (c02 BIT(64));
46
INSERT INTO t1 VALUES (1);
47
INSERT INTO t1 VALUES (2);
48
INSERT INTO t1 VALUES (128);
49
INSERT INTO t1 VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');
53
CREATE TABLE t1 (c03 TINYINT);
54
INSERT INTO t1 VALUES (1),(2),(3);
55
INSERT INTO t1 VALUES (-128);
56
UPDATE t1 SET c03=2 WHERE c03=1;
57
DELETE FROM t1 WHERE c03=-128;
60
CREATE TABLE t1 (c04 TINYINT UNSIGNED);
61
INSERT INTO t1 VALUES (128), (255);
62
UPDATE t1 SET c04=2 WHERE c04=1;
63
DELETE FROM t1 WHERE c04=255;
66
CREATE TABLE t1 (c06 BOOL);
67
INSERT INTO t1 VALUES (TRUE);
68
DELETE FROM t1 WHERE c06=TRUE;
71
CREATE TABLE t1 (c07 SMALLINT);
72
INSERT INTO t1 VALUES (1234);
73
DELETE FROM t1 WHERE c07=1234;
76
CREATE TABLE t1 (c08 SMALLINT UNSIGNED);
77
INSERT INTO t1 VALUES (32768), (65535);
78
UPDATE t1 SET c08=2 WHERE c08=32768;
79
DELETE FROM t1 WHERE c08=65535;
82
CREATE TABLE t1 (c10 MEDIUMINT);
83
INSERT INTO t1 VALUES (12345);
84
DELETE FROM t1 WHERE c10=12345;
87
CREATE TABLE t1 (c11 MEDIUMINT UNSIGNED);
88
INSERT INTO t1 VALUES (8388608), (16777215);
89
UPDATE t1 SET c11=2 WHERE c11=8388608;
90
DELETE FROM t1 WHERE c11=16777215;
93
CREATE TABLE t1 (c13 INT);
94
INSERT INTO t1 VALUES (123456);
95
DELETE FROM t1 WHERE c13=123456;
98
CREATE TABLE t1 (c14 INT UNSIGNED);
99
INSERT INTO t1 VALUES (2147483648), (4294967295);
100
UPDATE t1 SET c14=2 WHERE c14=2147483648;
101
DELETE FROM t1 WHERE c14=4294967295;
104
CREATE TABLE t1 (c16 BIGINT);
105
INSERT INTO t1 VALUES (1234567890);
106
DELETE FROM t1 WHERE c16=1234567890;
109
CREATE TABLE t1 (c17 BIGINT UNSIGNED);
110
INSERT INTO t1 VALUES (9223372036854775808), (18446744073709551615);
111
UPDATE t1 SET c17=2 WHERE c17=9223372036854775808;
112
DELETE FROM t1 WHERE c17=18446744073709551615;
115
CREATE TABLE t1 (c19 FLOAT);
116
INSERT INTO t1 VALUES (123.2234);
117
DELETE FROM t1 WHERE c19>123;
120
CREATE TABLE t1 (c22 DOUBLE);
121
INSERT INTO t1 VALUES (123434.22344545);
122
DELETE FROM t1 WHERE c22>123434;
127
CREATE TABLE t1 (c25 DECIMAL(10,5));
128
INSERT INTO t1 VALUES (124.45);
129
INSERT INTO t1 VALUES (-543.21);
130
DELETE FROM t1 WHERE c25=124.45;
135
CREATE TABLE t1 (c28 DATE);
136
INSERT INTO t1 VALUES ('2001-02-03');
137
DELETE FROM t1 WHERE c28='2001-02-03';
140
CREATE TABLE t1 (c29 DATETIME);
141
INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
142
DELETE FROM t1 WHERE c29='2001-02-03 10:20:30';
145
CREATE TABLE t1 (c30 TIMESTAMP);
146
INSERT INTO t1 VALUES ('2001-02-03 10:20:30');
147
DELETE FROM t1 WHERE c30='2001-02-03 10:20:30';
150
CREATE TABLE t1 (c31 TIME);
151
INSERT INTO t1 VALUES ('11:22:33');
152
DELETE FROM t1 WHERE c31='11:22:33';
155
CREATE TABLE t1 (c32 YEAR);
156
INSERT INTO t1 VALUES ('2001');
157
DELETE FROM t1 WHERE c32=2001;
162
CREATE TABLE t1 (c33 CHAR);
163
INSERT INTO t1 VALUES ('a');
164
DELETE FROM t1 WHERE c33='a';
167
CREATE TABLE t1 (c34 CHAR(0));
168
INSERT INTO t1 VALUES ('');
169
DELETE FROM t1 WHERE c34='';
172
CREATE TABLE t1 (c35 CHAR(1));
173
INSERT INTO t1 VALUES ('b');
174
DELETE FROM t1 WHERE c35='b';
177
CREATE TABLE t1 (c36 CHAR(255));
178
INSERT INTO t1 VALUES (repeat('c',255));
179
DELETE FROM t1 WHERE c36>'c';
184
CREATE TABLE t1 (c37 NATIONAL CHAR);
185
INSERT INTO t1 VALUES ('a');
186
DELETE FROM t1 WHERE c37='a';
189
CREATE TABLE t1 (c38 NATIONAL CHAR(0));
190
INSERT INTO t1 VALUES ('');
191
DELETE FROM t1 WHERE c38='';
194
CREATE TABLE t1 (c39 NATIONAL CHAR(1));
195
INSERT INTO t1 VALUES ('a');
196
DELETE FROM t1 WHERE c39='a';
199
CREATE TABLE t1 (c40 NATIONAL CHAR(255));
200
INSERT INTO t1 VALUES (repeat('a', 255));
201
INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
202
DELETE FROM t1 WHERE c40>'a';
207
CREATE TABLE t1 (c41 CHAR CHARACTER SET UCS2);
208
INSERT INTO t1 VALUES ('a');
209
DELETE FROM t1 WHERE c41='a';
212
CREATE TABLE t1 (c42 CHAR(0) CHARACTER SET UCS2);
213
INSERT INTO t1 VALUES ('');
214
DELETE FROM t1 WHERE c42='';
217
CREATE TABLE t1 (c43 CHAR(1) CHARACTER SET UCS2);
218
INSERT INTO t1 VALUES ('a');
219
DELETE FROM t1 WHERE c43='a';
222
CREATE TABLE t1 (c44 CHAR(255) CHARACTER SET UCS2);
223
INSERT INTO t1 VALUES (repeat('a', 255));
224
INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
225
DELETE FROM t1 WHERE c44>'a';
230
CREATE TABLE t1 (c45 VARCHAR(0));
231
INSERT INTO t1 VALUES ('');
232
DELETE FROM t1 WHERE c45='';
235
CREATE TABLE t1 (c46 VARCHAR(1));
236
INSERT INTO t1 VALUES ('a');
237
DELETE FROM t1 WHERE c46='a';
240
CREATE TABLE t1 (c47 VARCHAR(255));
241
INSERT INTO t1 VALUES (repeat('a',255));
242
DELETE FROM t1 WHERE c47>'a';
245
CREATE TABLE t1 (c48 VARCHAR(261));
246
INSERT INTO t1 VALUES (repeat('a',261));
247
DELETE FROM t1 WHERE c48>'a';
252
CREATE TABLE t1 (c49 NATIONAL VARCHAR(0));
253
INSERT INTO t1 VALUES ('');
254
DELETE FROM t1 WHERE c49='';
257
CREATE TABLE t1 (c50 NATIONAL VARCHAR(1));
258
INSERT INTO t1 VALUES ('a');
259
DELETE FROM t1 WHERE c50='a';
262
CREATE TABLE t1 (c51 NATIONAL VARCHAR(255));
263
INSERT INTO t1 VALUES (repeat('a',255));
264
INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 255));
265
DELETE FROM t1 WHERE c51>'a';
268
CREATE TABLE t1 (c52 NATIONAL VARCHAR(261));
269
INSERT INTO t1 VALUES (repeat('a',261));
270
INSERT INTO t1 VALUES (repeat(_latin1 0xDF, 261));
271
DELETE FROM t1 WHERE c52>'a';
276
CREATE TABLE t1 (c53 VARCHAR(0) CHARACTER SET ucs2);
277
INSERT INTO t1 VALUES ('');
278
DELETE FROM t1 WHERE c53='';
281
CREATE TABLE t1 (c54 VARCHAR(1) CHARACTER SET ucs2);
282
INSERT INTO t1 VALUES ('a');
283
DELETE FROM t1 WHERE c54='a';
286
CREATE TABLE t1 (c55 VARCHAR(255) CHARACTER SET ucs2);
287
INSERT INTO t1 VALUES (repeat('ab', 127));
288
DELETE FROM t1 WHERE c55>'a';
291
CREATE TABLE t1 (c56 VARCHAR(261) CHARACTER SET ucs2);
292
INSERT INTO t1 VALUES (repeat('ab', 130));
293
DELETE FROM t1 WHERE c56>'a';
298
CREATE TABLE t1 (c57 BINARY);
299
INSERT INTO t1 VALUES (0x00);
300
INSERT INTO t1 VALUES (0x02);
301
INSERT INTO t1 VALUES ('a');
302
DELETE FROM t1 WHERE c57='a';
305
CREATE TABLE t1 (c58 BINARY(0));
306
INSERT INTO t1 VALUES ('');
307
DELETE FROM t1 WHERE c58='';
310
CREATE TABLE t1 (c59 BINARY(1));
311
INSERT INTO t1 VALUES (0x00);
312
INSERT INTO t1 VALUES (0x02);
313
INSERT INTO t1 VALUES ('a');
314
DELETE FROM t1 WHERE c59='a';
317
CREATE TABLE t1 (c60 BINARY(255));
318
INSERT INTO t1 VALUES (0x00);
319
INSERT INTO t1 VALUES (0x02);
320
INSERT INTO t1 VALUES (repeat('a\0',120));
321
DELETE FROM t1 WHERE c60<0x02;
326
CREATE TABLE t1 (c61 VARBINARY(0));
327
INSERT INTO t1 VALUES ('');
328
DELETE FROM t1 WHERE c61='';
331
CREATE TABLE t1 (c62 VARBINARY(1));
332
INSERT INTO t1 VALUES (0x00);
333
INSERT INTO t1 VALUES (0x02);
334
INSERT INTO t1 VALUES ('a');
335
DELETE FROM t1 WHERE c62=0x02;
338
CREATE TABLE t1 (c63 VARBINARY(255));
339
INSERT INTO t1 VALUES (0x00);
340
INSERT INTO t1 VALUES (0x02);
341
INSERT INTO t1 VALUES (repeat('a\0',120));
342
DELETE FROM t1 WHERE c63=0x02;
347
CREATE TABLE t1 (c65 TINYBLOB);
348
INSERT INTO t1 VALUES ('tinyblob1');
349
DELETE FROM t1 WHERE c65='tinyblob1';
352
CREATE TABLE t1 (c68 BLOB);
353
INSERT INTO t1 VALUES ('blob1');
354
DELETE FROM t1 WHERE c68='blob1';
357
CREATE TABLE t1 (c71 MEDIUMBLOB);
358
INSERT INTO t1 VALUES ('mediumblob1');
359
DELETE FROM t1 WHERE c71='mediumblob1';
362
CREATE TABLE t1 (c74 LONGBLOB);
363
INSERT INTO t1 VALUES ('longblob1');
364
DELETE FROM t1 WHERE c74='longblob1';
367
CREATE TABLE t1 (c66 TINYTEXT);
368
INSERT INTO t1 VALUES ('tinytext1');
369
DELETE FROM t1 WHERE c66='tinytext1';
372
CREATE TABLE t1 (c69 TEXT);
373
INSERT INTO t1 VALUES ('text1');
374
DELETE FROM t1 WHERE c69='text1';
377
CREATE TABLE t1 (c72 MEDIUMTEXT);
378
INSERT INTO t1 VALUES ('mediumtext1');
379
DELETE FROM t1 WHERE c72='mediumtext1';
382
CREATE TABLE t1 (c75 LONGTEXT);
383
INSERT INTO t1 VALUES ('longtext1');
384
DELETE FROM t1 WHERE c75='longtext1';
389
CREATE TABLE t1 (c67 TINYTEXT CHARACTER SET UCS2);
390
INSERT INTO t1 VALUES ('tinytext1');
391
DELETE FROM t1 WHERE c67='tinytext1';
394
CREATE TABLE t1 (c70 TEXT CHARACTER SET UCS2);
395
INSERT INTO t1 VALUES ('text1');
396
DELETE FROM t1 WHERE c70='text1';
399
CREATE TABLE t1 (c73 MEDIUMTEXT CHARACTER SET UCS2);
400
INSERT INTO t1 VALUES ('mediumtext1');
401
DELETE FROM t1 WHERE c73='mediumtext1';
404
CREATE TABLE t1 (c76 LONGTEXT CHARACTER SET UCS2);
405
INSERT INTO t1 VALUES ('longtext1');
406
DELETE FROM t1 WHERE c76='longtext1';
411
CREATE TABLE t1 (c77 ENUM('a','b','c'));
412
INSERT INTO t1 VALUES ('b');
413
DELETE FROM t1 WHERE c77='b';
418
CREATE TABLE t1 (c78 SET('a','b','c','d','e','f'));
419
INSERT INTO t1 VALUES ('a,b');
420
INSERT INTO t1 VALUES ('a,c');
421
INSERT INTO t1 VALUES ('b,c');
422
INSERT INTO t1 VALUES ('a,b,c');
423
INSERT INTO t1 VALUES ('a,b,c,d');
424
INSERT INTO t1 VALUES ('a,b,c,d,e');
425
INSERT INTO t1 VALUES ('a,b,c,d,e,f');
426
DELETE FROM t1 WHERE c78='a,b';
430
# Check multi-table update
432
CREATE TABLE t1 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
433
CREATE TABLE t2 (a int NOT NULL DEFAULT 0, b int NOT NULL DEFAULT 0);
434
INSERT INTO t1 SET a=1;
435
INSERT INTO t1 SET b=1;
436
INSERT INTO t2 SET a=1;
437
INSERT INTO t2 SET b=1;
438
UPDATE t1, t2 SET t1.a=10, t2.a=20;
443
let $MYSQLD_DATADIR= `select @@datadir`;
444
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
445
--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/
446
--exec $MYSQL_BINLOG --base64-output=decode-rows -v -v $MYSQLD_DATADIR/master-bin.000001