1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
include/master-slave.inc
[connection master]
call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT,
UNIQUE(b), c int) engine=innodb;
INSERT INTO t1 VALUES (1, 1, 1);
BEGIN;
INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
COMMIT;
SELECT * FROM t1;
a b c
1 1 2
2 2 3
include/wait_for_slave_sql_error.inc [errno=1062]
Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)''
#Different value from server
SELECT * FROM t1;
a b c
1 1 1
2 2 3
stop slave;
include/wait_for_slave_to_stop.inc
reset slave;
reset master;
drop table t1;
start slave;
include/wait_for_slave_to_start.inc
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
UNIQUE(b), c int) engine=innodb;
INSERT INTO t1 VALUES (default, 1, 1);
BEGIN;
INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
COMMIT;
SELECT * FROM t1;
a b c
1 1 2
3 2 3
#same data as master
SELECT * FROM t1;
a b c
1 1 2
3 2 3
drop table t1;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
UNIQUE(b), c int, d int ) engine=innodb;
INSERT INTO t1 VALUES (1, 1, 1, 1);
BEGIN;
INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
Warnings:
Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
COMMIT;
SELECT * FROM t1;
a b c d
1 1 1 1
2 NULL 2 2
3 NULL 2 3
#same data as master
SELECT * FROM t1;
a b c d
1 1 1 1
2 NULL 2 2
3 NULL 2 3
drop table t1;
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
UNIQUE(b), c int) engine=innodb;
INSERT INTO t1 VALUES (1, 1, 1);
BEGIN;
INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
COMMIT;
SELECT * FROM t1;
a b c
1 1 2
2 2 3
include/wait_for_slave_sql_error.inc [errno=1062]
Last_SQL_Error = 'Error 'Duplicate entry '1' for key 'b'' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c)''
#Different value from server
SELECT * FROM t1;
a b c
1 1 1
2 2 3
stop slave;
include/wait_for_slave_to_stop.inc
reset slave;
reset master;
drop table t1;
start slave;
include/wait_for_slave_to_start.inc
include/rpl_end.inc
|
