MySQL Bugs: #28153: On duplicate update in conjuction with char field doesn't replicate properly

Bug #28153	On duplicate update in conjuction with char field doesn't replicate properly
Submitted:	29 Apr 2007 14:10	Modified:	29 Apr 2007 15:49
Reporter:	Thomas van Gulick	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.37	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
INSERT INTO ON DUPLICATE KEY doesn't function properly when character fields are to be updated.
On the master, it says 0 rows changed, though the existing row actually _is_ updated.
On slaves replication the same table, the query isn't executed at all, no updates are done.

Note that when using MySQL 4.1 as master, the INSERT is executed fine on both 4.1.22 master and 5.0.37 slave.

How to repeat:
CREATE TABLE test (ID int unsigned not null primary key, BODY text not null);

INSERT INTO test SET ID=1,BODY="test";
INSERT INTO test SET ID=1,BODY="tost" ON DUPLICATE KEY UPDATE BODY=VALUES(BODY);

Second insert tells no rows have been updated, though SELECTing shows the row actually _has_ changed.

If you do above queries on a master and make sure table test is replicated, you will notice the row with ID 1 won't containt "tost" after second query. It seem to not have executed the query.
(Might be because the master told slave no updates were done, like it reported, though it actually dit update the row)

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

[miguel@light 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE test (ID int unsigned not null primary key, BODY text not null);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO test SET ID=1,BODY="test";
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test SET ID=1,BODY="tost" ON DUPLICATE KEY UPDATE
    -> BODY=VALUES(BODY);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;
+----+------+
| ID | BODY |
+----+------+
|  1 | tost | 
+----+------+
1 row in set (0.02 sec)