Debian Codegraph @

mysql-test/suite/rpl/include/rpl_multi_update3.test

raw
1 ##############################################################################
2 #
3 # Let's verify that multi-update with a subselect does not cause the slave to crash
4 # (BUG#10442)
5 #
6 --disable_query_log
7 SELECT '-------- Test for BUG#9361 --------' as "";
8 --enable_query_log
9
10 eval CREATE TABLE t1 (
11 a int unsigned not null auto_increment primary key,
12 b int unsigned
13 ) ENGINE=$engine_type;
14
15 eval CREATE TABLE t2 (
16 a int unsigned not null auto_increment primary key,
17 b int unsigned
18 ) ENGINE=$engine_type;
19
20 INSERT INTO t1 VALUES (NULL, 0);
21
22 --disable_warnings ONCE
23 INSERT INTO t1 SELECT NULL, 0 FROM t1;
24
25 INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
26
27 SELECT * FROM t1 ORDER BY a;
28 SELECT * FROM t2 ORDER BY a;
29
30 --disable_warnings ONCE
31 UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ;
32 SELECT * FROM t1 ORDER BY a;
33 SELECT * FROM t2 ORDER BY a;
34
35 sync_slave_with_master;
36 connection slave;
37 SELECT * FROM t1 ORDER BY a;
38 SELECT * FROM t2 ORDER BY a;
39
40 connection master;
41 drop table t1,t2;
42
43 ##############################################################################
44 #
45 # Test for BUG#9361:
46 # Subselects should work inside multi-updates
47 #
48 --disable_query_log
49 SELECT '-------- Test 1 for BUG#9361 --------' as "";
50 --enable_query_log
51
52 connection master;
53
54 --disable_warnings
55 DROP TABLE IF EXISTS t1;
56 DROP TABLE IF EXISTS t2;
57 --enable_warnings
58
59 CREATE TABLE t1 (
60 a1 char(30),
61 a2 int,
62 a3 int,
63 a4 char(30),
64 a5 char(30)
65 );
66
67 CREATE TABLE t2 (
68 b1 int,
69 b2 char(30)
70 );
71
72 # Insert one row per table
73 INSERT INTO t1 VALUES ('Yes', 1, NULL, 'foo', 'bar');
74 INSERT INTO t2 VALUES (1, 'baz');
75
76 # This should update the row in t1
77 UPDATE t1 a, t2
78 SET a.a1 = 'No'
79 WHERE a.a2 =
80 (SELECT b1
81 FROM t2
82 WHERE b2 = 'baz')
83 AND a.a3 IS NULL
84 AND a.a4 = 'foo'
85 AND a.a5 = 'bar';
86
87 sync_slave_with_master;
88 connection slave;
89 SELECT * FROM t1;
90 SELECT * FROM t2;
91
92 connection master;
93 DROP TABLE t1, t2;
94
95 ##############################################################################
96 #
97 # Second test for BUG#9361
98 #
99
100 --disable_query_log
101 SELECT '-------- Test 2 for BUG#9361 --------' as "";
102 --enable_query_log
103
104 connection master;
105
106 --disable_warnings
107 DROP TABLE IF EXISTS t1;
108 DROP TABLE IF EXISTS t2;
109 DROP TABLE IF EXISTS t3;
110 --enable_warnings
111
112 CREATE TABLE t1 (
113 i INT,
114 j INT,
115 x INT,
116 y INT,
117 z INT
118 );
119
120 CREATE TABLE t2 (
121 i INT,
122 k INT,
123 x INT,
124 y INT,
125 z INT
126 );
127
128 CREATE TABLE t3 (
129 j INT,
130 k INT,
131 x INT,
132 y INT,
133 z INT
134 );
135
136 INSERT INTO t1 VALUES ( 1, 2,13,14,15);
137 INSERT INTO t2 VALUES ( 1, 3,23,24,25);
138 INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);
139
140 UPDATE t1 AS a
141 INNER JOIN t2 AS b
142 ON a.i = b.i
143 INNER JOIN t3 AS c
144 ON a.j = c.j AND b.k = c.k
145 SET a.x = b.x,
146 a.y = b.y,
147 a.z = (
148 SELECT sum(z)
149 FROM t3
150 WHERE y = 34
151 )
152 WHERE b.x = 23;
153
154 sync_slave_with_master;
155 connection slave;
156
157 SELECT * FROM t1;
158
159 connection master;
160 DROP TABLE t1, t2, t3;
161
162 ##############################################################################
163 #
164 # BUG#12618
165 #
166 # TEST: Replication of a statement containing a join in a multi-update.
167
168 DROP TABLE IF EXISTS t1;
169 DROP TABLE IF EXISTS t2;
170
171 CREATE TABLE t1 (
172 idp int(11) NOT NULL default '0',
173 idpro int(11) default NULL,
174 price decimal(19,4) default NULL,
175 PRIMARY KEY (idp)
176 );
177
178 CREATE TABLE t2 (
179 idpro int(11) NOT NULL default '0',
180 price decimal(19,4) default NULL,
181 nbprice int(11) default NULL,
182 PRIMARY KEY (idpro)
183 );
184
185 INSERT INTO t1 VALUES
186 (1,1,'3.0000'),
187 (2,2,'1.0000'),
188 (3,1,'1.0000'),
189 (4,1,'4.0000'),
190 (5,3,'2.0000'),
191 (6,2,'4.0000');
192
193 INSERT INTO t2 VALUES
194 (1,'0.0000',0),
195 (2,'0.0000',0),
196 (3,'0.0000',0);
197
198 # This update sets t2 to the minimal prices for each product
199 update
200 t2
201 join
202 ( select idpro, min(price) as min_price, count(*) as nbr_price
203 from t1
204 where idpro>0 and price>0
205 group by idpro
206 ) as table_price
207 on t2.idpro = table_price.idpro
208 set t2.price = table_price.min_price,
209 t2.nbprice = table_price.nbr_price;
210
211 select "-- MASTER AFTER JOIN --" as "";
212 select * from t1;
213 select * from t2;
214
215 sync_slave_with_master;
216
217 select "-- SLAVE AFTER JOIN --" as "";
218 select * from t1;
219 select * from t2;
220
221 connection master;
222 DROP TABLE t1, t2;
223 # End of 4.1 tests