Description:
When master uses row-based replication, replace statement will cause auto_increment column have different offset values because MySQL change the replace to update when log the statement to binary log.
How to repeat:
Make sure the master uses row-based replication.
on master:
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11),
`b` int(11),
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql>insert into t (a,b) values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
Now both master and slave look like:
mysql>show create table t\G
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
Issue a replace into statement on master:
mysql>replace into t (a,b) values(1,10);
Query OK, 2 rows affected (0.00 sec)
Now show create table on master and slave:
on master:
mysql>show create table t\G
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
on slave:
mysql>show create table t\G
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
We can see the next auto increment value for id on master is 5 but 4 on slave.
If we promote the slave to master and append rows to table t,
e.g.
insert into t values(a,b) values(4,4);
it will cause duplicated key error (id=4).
The reason for this is MySQL logs replace as an update on Row-based Replication:
binary log events for the above replace on master:
# at 129708629
#170926 11:31:57 server id 21 end_log_pos 129708673 CRC32 0x1564c394 Table_map: `m1`.`t` mapped to number 139
# at 129708673
#170926 11:31:57 server id 21 end_log_pos 129708735 CRC32 0x95a32e6e Update_rows: table id 139 flags: STMT_END_F
### UPDATE `m1`.`t`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### @3=10 /* INT meta=0 nullable=1 is_null=0 */
on master it generate value 4 for column id, so the next auto increment value is 5, but when the binary log replicated to slave, the SQL_THREAD will just treat it as a normal update and will not increment the auto_increment counter.
Suggested fix:
1，As the document says: replace into equals to delete + insert on duplicated key, maybe we should log delete + insert on the binary log.
2, log the auto_increment value in binary log,so when the slave SQL_THREAD applying this event will increase the auto increment counter.
Description: When master uses row-based replication, replace statement will cause auto_increment column have different offset values because MySQL change the replace to update when log the statement to binary log. How to repeat: Make sure the master uses row-based replication. on master: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11), `b` int(11), PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql>insert into t (a,b) values(1,1),(2,2),(3,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Now both master and slave look like: mysql>show create table t\G Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Issue a replace into statement on master: mysql>replace into t (a,b) values(1,10); Query OK, 2 rows affected (0.00 sec) Now show create table on master and slave: on master: mysql>show create table t\G Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 on slave: mysql>show create table t\G Table: t Create Table: CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 We can see the next auto increment value for id on master is 5 but 4 on slave. If we promote the slave to master and append rows to table t, e.g. insert into t values(a,b) values(4,4); it will cause duplicated key error (id=4). The reason for this is MySQL logs replace as an update on Row-based Replication: binary log events for the above replace on master: # at 129708629 #170926 11:31:57 server id 21 end_log_pos 129708673 CRC32 0x1564c394 Table_map: `m1`.`t` mapped to number 139 # at 129708673 #170926 11:31:57 server id 21 end_log_pos 129708735 CRC32 0x95a32e6e Update_rows: table id 139 flags: STMT_END_F ### UPDATE `m1`.`t` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### @3=10 /* INT meta=0 nullable=1 is_null=0 */ on master it generate value 4 for column id, so the next auto increment value is 5, but when the binary log replicated to slave, the SQL_THREAD will just treat it as a normal update and will not increment the auto_increment counter. Suggested fix: 1，As the document says: replace into equals to delete + insert on duplicated key, maybe we should log delete + insert on the binary log. 2, log the auto_increment value in binary log,so when the slave SQL_THREAD applying this event will increase the auto increment counter.