Bug #97421 Replace into affected row count not match with events in binlog
Submitted: 30 Oct 2019 9:28 Modified: 30 Oct 2019 13:22
Reporter: Ke Lu Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.23, 5.7.28, 5.6.46 OS:CentOS
Assigned to: CPU Architecture:Any

[30 Oct 2019 9:28] Ke Lu
Description:
Replace reports affected rows is 2, but in binary log, only an update row event found but not delete plus insert.

How to repeat:
mysql> select @@version;   
+------------+
| @@version  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` varchar(32) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4

mysql> select * from t;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  2 | dd   |    2 |
|  3 | bbdd |    3 |
|  4 | cc   |    4 |
| 14 | cc   |    5 |
| 17 | dd   |    6 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> replace into t (c1,c2) values('dd',6);
Query OK, 2 rows affected (0.00 sec)

Yes, it reports 2 rows affected, but in the binary log, the replace is converted to an update instead of delete plus insert.

BEGIN
/*!*/;
# at 123154213
#191030 17:23:14 server id 100  end_log_pos 123154260 CRC32 0x22617e7b  Table_map: `aaa`.`t` mapped to number 681
# at 123154260
#191030 17:23:14 server id 100  end_log_pos 123154320 CRC32 0x6716d677  Update_rows: table id 681 flags: STMT_END_F
### UPDATE `aaa`.`t`
### WHERE
###   @1=17 /* INT meta=0 nullable=0 is_null=0 */
###   @2='dd' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3=6 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=18 /* INT meta=0 nullable=0 is_null=0 */
###   @2='dd' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3=6 /* INT meta=0 nullable=1 is_null=0 */
# at 123154320
#191030 17:23:14 server id 100  end_log_pos 123154351 CRC32 0x22df72d7  Xid = 2227414
COMMIT/*!*/;

Suggested fix:
make the affected rows reported match with the events in the binary log.
[30 Oct 2019 13:22] Umesh Shastry
Hello Ke Lu,

Thank you for the report.

regards,
Umesh
[30 Oct 2019 13:24] Umesh Shastry
Related - Bug #74354