UPDATE multiple rows with different values in a single SQL leads to inconsistency

[GoogleCodeExporter]

What steps will reproduce the problem?

0. Setup direct slave topology (though it might not matter).
1. CREATE TABLE t (id int, a text);
2. INSERT INTO t VALUES (1, 'A'); INSERT INTO t VALUES (2, 'B');
3. UPDATE t SET a = CASE id WHEN 1 THEN 'a' WHEN 2 THEN 'b' END; /* OK: */ 
4. UPDATE t SET a = CASE id WHEN 1 THEN 'A' WHEN 2 THEN NULL END; /* OK: */
5. UPDATE t SET a = CASE id WHEN 1 THEN 'a' WHEN 2 THEN 'b' END; /* OK: */
6. UPDATE t SET a = CASE id WHEN 1 THEN NULL WHEN 2 THEN 'B' END; /* NOT OK! */

What is the expected output?

On a slave:

SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
|    2 | B    |
+------+------+

What do you see instead?

Inconsistent apply:

Master:

SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
|    2 | B    |
+------+------+

Slave:

SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+

The SQL (6) is applied incorrectly. On the other hand, SQL (4) is applied 
correctly.

What is the possible cause?

Apparently, we have a "memory effect" in the loop that is applying the row 
changes. Most likely, there is some variable (I'm guessing it's 
columnSpec.getType()), which is checked only once per ReplDBMSEvent, while, in 
reality, it should have be checked on each row. When this variable is of NULL 
type, the code calls setNull(...) for current and subsequent rows, while it 
should only call that for current row and repeat the check during the next one.

As mentioned above, note that this happens only when NULL is at the first row.

Alternatively, it might be a bug in the way THL log is generated, or even in 
the way MySQL binary log is generated.

Additional information

The case like this might be reproducible not only via this SQL, but also by a 
prepared statement which updates more than one row with different values in the 
same statement.

Original issue reported on code.google.com by linas.vi...@gmail.com on 17 Jun 2011 at 5:01

[GoogleCodeExporter]

I was just able to confirm that this doesn't happen under pure MySQL->MySQL 
replication:

SELECT * FROM t;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
|    2 | B    |
+------+------+

Original comment by linas.vi...@gmail.com on 17 Jun 2011 at 5:47

[GoogleCodeExporter]

This issue was closed by revision r232.

Original comment by stephane...@gtempaccount.com on 20 Jun 2011 at 10:29

• Changed state: Fixed