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.
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.