MySQL Bugs: #50429: ASCII function in WHERE clause is causing incorrect updates/deletes on slave

Bug #50429	ASCII function in WHERE clause is causing incorrect updates/deletes on slave
Submitted:	18 Jan 2010 23:08	Modified:	19 Feb 2010 6:19
Reporter:	Victor Kirkebo	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.43	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ascii function, delete, UPDATE

Description:
I've tested this on a pre-release of 5.1.43 with RBR.
The incorrect results on slave happen for both myisam and innodb tables with columns of types varchar, text and blob.

How to repeat:
This doesn't happen every time - but after trying a few times it always happens:

Example 1: (update)
-------------------
master:
mysql> create database mydb;
mysql> use mydb;
mysql> create table tb1(f1 varchar(10), f2 char(10));
mysql> insert into tb1 values('Agnes',''),('abcde',''),('Annie','');
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| Annie |      |
| abcde |      |
| Agnes |      |
+-------+------+
3 rows in set (0.00 sec)

slave:
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| Annie |      |
| abcde |      |
| Agnes |      |
+-------+------+
3 rows in set (0.00 sec)

master:
mysql> update tb1 set f2='Hello' where ascii(f1)=65;
mysql> select * from tb1;
+-------+-------+
| f1    | f2    |
+-------+-------+
| Annie | Hello |
| abcde |       |
| Agnes | Hello |
+-------+-------+
3 rows in set (0.00 sec)

slave:
mysql> select * from tb1;
+-------+-------+
| f1    | f2    |
+-------+-------+
| Annie | Hello |
| Agnes | Hello |
| Agnes |       |
+-------+-------+
3 rows in set (0.00 sec)

Example 2: (delete)
-------------------
master:
mysql> create database mydb;
mysql> use mydb;
mysql> create table tb1(f1 varchar(10), f2 char(10));
mysql> insert into tb1 values('Agnes',''),('abcde',''),('Annie','');
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| Annie |      |
| abcde |      |
| Agnes |      |
+-------+------+
3 rows in set (0.00 sec)

slave:
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| abcde |      |
| Agnes |      |
| Annie |      |
+-------+------+
3 rows in set (0.00 sec)

master:
mysql> delete from tb1 where ascii(f1)=65;
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| abcde |      |
+-------+------+
1 row in set (0.00 sec)

slave:
mysql> select * from tb1;
+-------+------+
| f1    | f2   |
+-------+------+
| Annie |      |
+-------+------+
1 row in set (0.01 sec)

Thank you for the report.

I can not reepat described behavior. Please provide output of SHOW VARIABLES LIKE 'char%' on both master and slave.

On both master and slave:

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+---------------------------------------------------------------------------------+
| Variable_name            | Value                                                                           |
+--------------------------+---------------------------------------------------------------------------------+
| character_set_client     | latin1                                                                          |
| character_set_connection | latin1                                                                          |
| character_set_database   | latin1                                                                          |
| character_set_filesystem | binary                                                                          |
| character_set_results    | latin1                                                                          |
| character_set_server     | latin1                                                                          |
| character_set_system     | utf8                                                                            |
| character_sets_dir       | /export/home/lroot/vk136562/mysql-5.1.43-solaris10-x86_64/share/mysql/charsets/ |
+--------------------------+---------------------------------------------------------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.1.43-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | pc-solaris2.10      |
+-------------------------+---------------------+
4 rows in set (0.00 sec)

I just tried again with the latest 5.1.43 build but now I cannot recreate the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".