Replication: replication generates incorrect update commands when where clause uses a field contained in set clause

[Eric Lambert]

When replicating update statements who's where clause includes a field that is used in the set clause, the update command that is placed into the replication stream is not correct. Specifically, the value of the field in the replicated statement's where clause uses the new value of the where field as opposed to the original value.

Here is an example of this problem

On the master server, create a database and table as shown below

drizzle> create database testdb;
Query OK, 1 row affected (0 sec)

drizzle> create table testdb.t1(id int AUTO_INCREMENT NOT NULL PRIMARY KEY, name varchar(1024), alias varchar(1024));
Query OK, 0 rows affected (0 sec)

drizzle> insert into testdb.t1 (name,alias) values ("jeff lebowski","dude");
Query OK, 1 row affected (0 sec)

drizzle> select * from testdb.t1;
+----+---------------+-------+
| id | name | alias |
+----+---------------+-------+
| 1 | jeff lebowski | dude |
+----+---------------+-------+

Now still on the master server, update a record

drizzle> update testdb.t1 set alias="the dude" where alias="dude";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

drizzle> select * from testdb.t1;
+----+---------------+----------+
| id | name | alias |
+----+---------------+----------+
| 1 | jeff lebowski | the dude |
+----+---------------+----------+
1 row in set (0 sec)

if after issuing the update command on the original, we look at the value of the table on the replica, we see the update statement has not been applied (note that alias should be 'the dude' but is still set to 'dude')

drizzle> select * from testdb.t1;
+----+---------------+-------+
| id | name | alias |
+----+---------------+-------+
| 1 | jeff lebowski | dude |
+----+---------------+-------+
1 row in set (0 sec)

When we examine the contents of the update command that was placed in the replication stream, the reason for this becomes clear. Below we see the sql that was generated from the update command in the replication stream

 UPDATE `testdb`.`t1` SET `alias`='the dude' WHERE `id`='1' AND `alias`='the dude'

As you can see the where clause uses the new value of alias ('the dude') when it should be using the old value of alias ('dude');

If we change the update statement so that it does not use the same field in the where and set clause (as seen below) the update is correctly processed on the replica

drizzle> update testdb.t1 set alias="big lebowski" where name="jeff lebowski";
Query OK, 1 row affected (0 sec)
Rows matched: 1 Changed: 1 Warnings: 0

[NOTE, the query above is executed on the Master and the query below is executed on the slave]

drizzle> select * from testdb.t1;
+----+---------------+--------------+
| id | name | alias |
+----+---------------+--------------+
| 1 | jeff lebowski | big lebowski |
+----+---------------+--------------+
1 row in set (0 sec)

[Jay Pipes (jaypipes)]

There is a fix now in the replication-group-commit branch.

Currently, replication is only guaranteed to work on tables that have a primary key set. UPDATE statements will be generated to only contain the key value for the primary key in the WHERE clause now.