MySQL Bugs: #12618: update with join clause is not replicated

Bug #12618	update with join clause is not replicated
Submitted:	17 Aug 2005 11:26	Modified:	14 Oct 2005 15:14
Reporter:	Jean Dupond
Status:	Closed
Category:	Server: Replication	Severity:	S1 (Critical)
Version:	4.1.13	OS:	Linux (linux mandrake 10.2)
Assigned to:	Lars Thalmann	Target Version:

Description:
On a simple master / slave environnement, the following update with join clause is not
replicated

// Query : 
update product join (select idpro ,min(price) as min_price,count(*) as nbr_price from
price where idpro>0 and price>0 group by idpro) as table_price on
product.idpro=table_price.idpro set
product.price=table_price.min_price,product.nbprice=table_price.nbr_price;

here is the database definition (a dump is attached to the bug too)
CREATE TABLE `price` (
  `idp` int(11) NOT NULL default '0',
  `idpro` int(11) default NULL,
  `price` decimal(19,4) default NULL,
  PRIMARY KEY  (`idp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `price` VALUES
(1,1,'5.0000'),(2,2,'3.0000'),(3,1,'3.0000'),(4,1,'6.0000'),(5,3,'2.0000'),(6,2,'6.0000');

CREATE TABLE `product` (
  `idpro` int(11) NOT NULL default '0',
  `price` decimal(19,4) default NULL,
  `nbprice` int(11) default NULL,
  PRIMARY KEY  (`idpro`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `product` VALUES (1,'0.0000',0),(2,'0.0000',0),(3,'0.0000',0);

After the query, the master contains the right values 
mysql> select * from product;
+-------+--------+---------+
| idpro | price  | nbprice |
+-------+--------+---------+
|     1 | 3.0000 |       3 |
|     2 | 3.0000 |       2 |
|     3 | 2.0000 |       1 |
+-------+--------+---------+

the values on the slave are eroneous
mysql> select * from product;
+-------+--------+---------+
| idpro | price  | nbprice |
+-------+--------+---------+
|     1 | 0.0000 |       0 |
|     2 | 6.0000 |       6 |
|     3 | 0.0000 |       0 |
+-------+--------+---------+

If the above query is executed on the slave, we get the good results.

How to repeat:
Install mysql server from officiel 4.1.13 rpm (MySQL-server-4.1.13-0.i386.rpm)

Create a master / slave environnement with : 

// master my.cnf : 
[mysqld]
log-bin
server-id       = 1

// slave my.cnf :
[mysqld]
server-id       = 2

Create table structure with attached dump.

execute the following query :
update product join (select idpro ,min(price) as min_price,count(*) as nbr_price from
price where idpro>0 and price>0 group by idpro) as table_price on
product.idpro=table_price.idpro set
product.price=table_price.min_price,product.nbprice=table_price.nbr_price;

compare a "select * from product;" on slave and master.

database dump to reproduce the bug

Attachment: test2.sql (application/octet-stream, text), 2.18 KiB.

Hello Jean,

Do you use any replicate-* options on the slave?

Initialy we use some but i have deleted them to verify that it was not the source of the
problem but the result is the same.

Verified with 4.1.14-debug-log

This bug seems to have the same behavior as this one :
http://bugs.mysql.com/bug.php?id=11723 and this one :
http://bugs.mysql.com/bug.php?id=6888

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30755

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30858

Fixed in 4.1.16, 5.0.15.
Bug#13305 is a duplicate.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 4.1.16 and 5.0.15 changelogs.