1 ##############################################################################
3 # Let's verify that multi-update with a subselect does not cause the slave to crash
7 SELECT '-------- Test for BUG#9361 --------' as "";
10 eval CREATE TABLE t1 (
11 a int unsigned not null auto_increment primary key,
13 ) ENGINE=$engine_type;
15 eval CREATE TABLE t2 (
16 a int unsigned not null auto_increment primary key,
18 ) ENGINE=$engine_type;
20 INSERT INTO t1 VALUES (NULL, 0);
21 INSERT INTO t1 SELECT NULL, 0 FROM t1;
23 INSERT INTO t2 VALUES (NULL, 0), (NULL,1);
25 SELECT * FROM t1 ORDER BY a;
26 SELECT * FROM t2 ORDER BY a;
28 UPDATE t2, (SELECT a FROM t1 ORDER BY a) AS t SET t2.b = t.a+5 ;
29 SELECT * FROM t1 ORDER BY a;
30 SELECT * FROM t2 ORDER BY a;
32 sync_slave_with_master;
34 SELECT * FROM t1 ORDER BY a;
35 SELECT * FROM t2 ORDER BY a;
40 ##############################################################################
43 # Subselects should work inside multi-updates
46 SELECT '-------- Test 1 for BUG#9361 --------' as "";
52 DROP TABLE IF EXISTS t1;
53 DROP TABLE IF EXISTS t2;
69 # Insert one row per table
70 INSERT INTO t1 VALUES ('Yes', 1, NULL, 'foo', 'bar');
71 INSERT INTO t2 VALUES (1, 'baz');
73 # This should update the row in t1
84 sync_slave_with_master;
92 ##############################################################################
94 # Second test for BUG#9361
98 SELECT '-------- Test 2 for BUG#9361 --------' as "";
104 DROP TABLE IF EXISTS t1;
105 DROP TABLE IF EXISTS t2;
106 DROP TABLE IF EXISTS t3;
133 INSERT INTO t1 VALUES ( 1, 2,13,14,15);
134 INSERT INTO t2 VALUES ( 1, 3,23,24,25);
135 INSERT INTO t3 VALUES ( 2, 3, 1,34,35), ( 2, 3, 1,34,36);
141 ON a.j = c.j AND b.k = c.k
151 sync_slave_with_master;
157 DROP TABLE t1, t2, t3;
159 ##############################################################################
163 # TEST: Replication of a statement containing a join in a multi-update.
165 DROP TABLE IF EXISTS t1;
166 DROP TABLE IF EXISTS t2;
169 idp int(11) NOT NULL default '0',
170 idpro int(11) default NULL,
171 price decimal(19,4) default NULL,
176 idpro int(11) NOT NULL default '0',
177 price decimal(19,4) default NULL,
178 nbprice int(11) default NULL,
182 INSERT INTO t1 VALUES
190 INSERT INTO t2 VALUES
195 # This update sets t2 to the minimal prices for each product
199 ( select idpro, min(price) as min_price, count(*) as nbr_price
201 where idpro>0 and price>0
204 on t2.idpro = table_price.idpro
205 set t2.price = table_price.min_price,
206 t2.nbprice = table_price.nbr_price;
208 select "-- MASTER AFTER JOIN --" as "";
212 sync_slave_with_master;
214 select "-- SLAVE AFTER JOIN --" as "";