Bug #12618 update with join clause is not replicated
Submitted: 17 Aug 2005 9:26 Modified: 14 Oct 2005 13:14
Reporter: Jean Dupond Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:4.1.13 OS:Linux (linux mandrake 10.2)
Assigned to: Lars Thalmann CPU Architecture:Any

[17 Aug 2005 9:26] Jean Dupond
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.
[17 Aug 2005 9:27] Jean Dupond
database dump to reproduce the bug

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

[18 Aug 2005 13:33] Victoria Reznichenko
Hello Jean,

Do you use any replicate-* options on the slave?
[18 Aug 2005 15:11] Jean Dupond
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.
[22 Aug 2005 16:25] Victoria Reznichenko
Verified with 4.1.14-debug-log
[30 Aug 2005 17:02] Jean Dupond
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
[6 Oct 2005 12:05] Bugs System
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
[10 Oct 2005 13:10] Bugs System
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
[10 Oct 2005 20:07] Lars Thalmann
Fixed in 4.1.16, 5.0.15.
Bug#13305 is a duplicate.
[14 Oct 2005 13:14] Jon Stephens
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.