Debian Codegraph @

mysql-test/suite/rpl/r/rpl_mdev_17614.result

raw
1 include/master-slave.inc
2 [connection master]
3 # Case 1: UNSAFE
4 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
5 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT,
6 UNIQUE(b), c int) engine=innodb;
7 connection slave;
8 connection master;
9 INSERT INTO t1 VALUES (1, 1, 1);
10 BEGIN;
11 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
12 Warnings:
13 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
14 connection master1;
15 INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
16 Warnings:
17 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
18 connection master;
19 COMMIT;
20 SELECT * FROM t1;
21 a b c
22 1 1 2
23 2 2 3
24 connection slave;
25 include/wait_for_slave_sql_error.inc [errno=1062]
26 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)''
27 #Different value from server
28 SELECT * FROM t1;
29 a b c
30 1 1 1
31 2 2 3
32 include/stop_slave_io.inc
33 include/reset_slave.inc
34 connection master;
35 reset master;
36 drop table t1;
37 connection slave;
38 start slave;
39 include/wait_for_slave_to_start.inc
40 # Case 2: UNSAFE
41 connection master;
42 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
43 UNIQUE(b), c int) engine=innodb;
44 connection slave;
45 connection master;
46 INSERT INTO t1 VALUES (default, 1, 1);
47 BEGIN;
48 INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
49 Warnings:
50 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
51 connection master1;
52 INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
53 Warnings:
54 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
55 connection master;
56 COMMIT;
57 SELECT * FROM t1;
58 a b c
59 1 1 2
60 3 2 3
61 connection slave;
62 #same data as master
63 SELECT * FROM t1;
64 a b c
65 1 1 2
66 3 2 3
67 connection master;
68 drop table t1;
69 connection slave;
70 # Case 3A: UNSAFE
71 connection master;
72 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
73 UNIQUE(b), c int, d int ) engine=innodb;
74 connection slave;
75 connection master;
76 INSERT INTO t1 VALUES (1, 1, 1, 1);
77 BEGIN;
78 INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
79 Warnings:
80 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
81 connection master1;
82 INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
83 Warnings:
84 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
85 connection master;
86 COMMIT;
87 SELECT * FROM t1;
88 a b c d
89 1 1 1 1
90 2 NULL 2 2
91 3 NULL 2 3
92 connection slave;
93 #same data as master
94 SELECT * FROM t1;
95 a b c d
96 1 1 1 1
97 2 NULL 2 2
98 3 NULL 2 3
99 connection master;
100 drop table t1;
101 connection slave;
102 # Case 3B: UNSAFE - all column specified.
103 connection master;
104 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
105 UNIQUE(b), c int, d int ) engine=innodb;
106 connection slave;
107 connection master;
108 INSERT INTO t1 VALUES (1, 1, 1, 1);
109 BEGIN;
110 INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c);
111 Warnings:
112 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
113 connection master1;
114 INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c);
115 Warnings:
116 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
117 connection master;
118 COMMIT;
119 SELECT * FROM t1;
120 a b c d
121 1 1 1 1
122 2 NULL 2 2
123 3 NULL 2 3
124 connection slave;
125 #same data as master
126 SELECT * FROM t1;
127 a b c d
128 1 1 1 1
129 2 NULL 2 2
130 3 NULL 2 3
131 connection master;
132 drop table t1;
133 connection slave;
134 # Case 3C: SAFE - only one unique key (PK) specified.
135 connection master;
136 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT,
137 UNIQUE(b), c int, d int ) engine=innodb;
138 connection slave;
139 connection master;
140 INSERT INTO t1 VALUES (1, 1, 1, 1);
141 BEGIN;
142 INSERT INTO t1 (`a`, `c`, `d`) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c=99;
143 connection master1;
144 INSERT INTO t1 (`a`, `c`, `d`) VALUES(3, 2, 3) ON DUPLICATE KEY UPDATE c=100;
145 connection master;
146 COMMIT;
147 SELECT * FROM t1;
148 a b c d
149 1 1 1 1
150 2 NULL 2 2
151 3 NULL 2 3
152 connection slave;
153 #same data as master
154 SELECT * FROM t1;
155 a b c d
156 1 1 1 1
157 2 NULL 2 2
158 3 NULL 2 3
159 connection master;
160 drop table t1;
161 connection slave;
162 # Case 4: UNSAFE
163 connection master;
164 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT,
165 UNIQUE(b), c int) engine=innodb;
166 connection slave;
167 connection master;
168 INSERT INTO t1 VALUES (1, 1, 1);
169 BEGIN;
170 INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
171 Warnings:
172 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
173 connection master1;
174 INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c);
175 Warnings:
176 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
177 connection master;
178 COMMIT;
179 SELECT * FROM t1;
180 a b c
181 1 1 2
182 2 2 3
183 connection slave;
184 include/wait_for_slave_sql_error.inc [errno=1062]
185 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)''
186 #Different value from server
187 SELECT * FROM t1;
188 a b c
189 1 1 1
190 2 2 3
191 include/stop_slave_io.inc
192 include/reset_slave.inc
193 connection master;
194 reset master;
195 drop table t1;
196 connection slave;
197 start slave;
198 include/wait_for_slave_to_start.inc
199 include/rpl_end.inc