MySQL Bugs: #73563: Replace result in auto_incrment value less or equal than max value in row-based

Bug #73563	Replace result in auto_incrment value less or equal than max value in row-based
Submitted:	13 Aug 2014 12:45	Modified:	13 Jul 2018 11:47
Reporter:	zhang yingqiang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.5.18 5.6.16, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, duplicate key, replace into

Description:
Replace into a table which has a auto_increment primary key and a unique key, without specific the primary key column value, result to the max value greater or equal than the auto_incrment value  in slave with row-based replication. If we change slave to master for  HA, ER_DUP_ENTRY occur in the new master (original slave) when now row is inserted.

  See the test case for detail.

How to repeat:
source include/master-slave.inc;

#set binlog_format=row in master and slave
connection master;
set global binlog_format = 'ROW';
set session binlog_format = 'ROW';
show global variables like '%binlog%';
flush logs;
connection slave;
source include/stop_slave.inc;
set global binlog_format = 'ROW';
set session binlog_format = 'ROW';
show global variables like '%binlog%';
source include/start_slave.inc;

#init data
connection master;
create table t1(a int auto_increment primary key, b int not null, c int not null, unique key uk_b(b)) engine=innodb;

insert into t1 values(1,1,1),(2,2,2),(3,3,3);
sync_slave_with_master;

#before replace into 
connection master;
show create table t1;
select * from t1;
connection slave;
show create table t1;
select * from t1;

#replace into
connection master;
replace into t1(b,c) values(1,10);
replace into t1(b,c) values(2,11);
sync_slave_with_master;

#after replace into 
connection master;
show create table t1;
select * from t1;
connection slave;
show create table t1;
select * from t1;
echo The auto_increment(4) is less then the max value(5).

#now if master is crash, we consider slave as master, and write on slave(new master)
connection slave;
set global read_only=0;
#!!! here receive err ER_DUP_ENTRY
--error ER_DUP_ENTRY
replace into t1(b,c) values(9,12);

#clear
connection master;
drop table t1;
set global binlog_format = 'STATEMENT';
set session binlog_format = 'STATEMENT';
connection slave;
set global binlog_format = 'STATEMENT';
set session binlog_format = 'STATEMENT';

connection slave;
source include/stop_slave.inc;

Suggested fix:

The reason for this case is that: the replace statement in master is consider as a delete and a insert, so it increase the auto_incrment value. But in row-based replication, this replace is consider as a update event. Update is not update the auto_incrment value and Result in this bug. 

1st suggested fix is: use a delete event and a insert event  to record this replace.
2nd suggested fix is: update the auto_incrment in update statements.

It's a known issue for the effect of auto_increment in replace into. Though it's suggested to bypass with insert into ... on duplicate key, but why not just solve it if not too hard?

Hello Zhang,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

// with 5.6.21

mysql-test]$ ./mysql-test-run.pl 73563
Logging: ./mysql-test-run.pl  73563
2014-08-16 05:42:33 13770 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2014-08-16 05:42:33 13770 [Warning] Buffered warning: Changed limits: table_cache: 431 (requested 2000)

2014-08-16 05:42:33 13770 [Note] Plugin 'FEDERATED' is disabled.
2014-08-16 05:42:33 13770 [Note] Binlog end
2014-08-16 05:42:33 13770 [Note] Shutting down plugin 'CSV'
2014-08-16 05:42:33 13770 [Note] Shutting down plugin 'MyISAM'
MySQL Version 5.6.21
Checking supported features...
 - SSL connections supported
Collecting tests...
Removing old var directory...
Creating var directory '/data/ushastry/server/mysql-advanced-5.6.21/mysql-test/var'...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
include/master-slave.inc
Warnings:
Note    ####    Sending passwords in plain text without SSL/TLS is extremely insecure.
Note    ####    Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
[connection master]
set global binlog_format = 'ROW';
set session binlog_format = 'ROW';
show global variables like '%binlog%';
Variable_name   Value
binlog_cache_size       32768
binlog_checksum CRC32
binlog_direct_non_transactional_updates ON
binlog_format   ROW
binlog_max_flush_queue_time     0
binlog_order_commits    ON
binlog_row_image        FULL
binlog_rows_query_log_events    OFF
binlog_stmt_cache_size  32768
binlogging_impossible_mode      IGNORE_ERROR
innodb_api_enable_binlog        OFF
innodb_locks_unsafe_for_binlog  OFF
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size      18446744073709547520
sync_binlog     0
flush logs;
include/stop_slave.inc
set global binlog_format = 'ROW';
set session binlog_format = 'ROW';
show global variables like '%binlog%';
Variable_name   Value
binlog_cache_size       32768
binlog_checksum CRC32
binlog_direct_non_transactional_updates ON
binlog_format   ROW
binlog_max_flush_queue_time     0
binlog_order_commits    ON
binlog_row_image        FULL
binlog_rows_query_log_events    OFF
binlog_stmt_cache_size  32768
binlogging_impossible_mode      IGNORE_ERROR
innodb_api_enable_binlog        OFF
innodb_locks_unsafe_for_binlog  OFF
max_binlog_cache_size   18446744073709547520
max_binlog_size 1073741824
max_binlog_stmt_cache_size      18446744073709547520
sync_binlog     0
include/start_slave.inc
create table t1(a int auto_increment primary key, b int not null, c int not null, unique key uk_b(b)) engine=innodb;
insert into t1 values(1,1,1),(2,2,2),(3,3,3);
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
select * from t1;
a       b       c
1       1       1
2       2       2
3       3       3
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
select * from t1;
a       b       c
1       1       1
2       2       2
3       3       3
replace into t1(b,c) values(1,10);
replace into t1(b,c) values(2,11);
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
select * from t1;
a       b       c
3       3       3
4       1       10
5       2       11
show create table t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `uk_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
select * from t1;
a       b       c
3       3       3
4       1       10
5       2       11
The auto_increment(4) is less then the max value(5).
#now if master is crash, we consider slave as master, and write on slave(new master)
connection slave
set global read_only=0;
replace into t1(b,c) values(9,12);
ERROR 23000: Duplicate entry '4' for key 'PRIMARY'

Posted by developer:
 
The following page was updated:
https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Changes should appear online soon.

Thank you for the bug report.

Posted by developer:
 
Reopening for additional revisions.

What was changed in the documentation?

> The following page was updated:
> https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html

Posted by developer:
 
The following behavior applies in MySQL 5.5, 5.6, and 5.7:

"When you insert any other value into an AUTO_INCREMENT column, the column
is set to that value and the sequence is reset so that the next
automatically generated value follows sequentially from the largest column
value. Updating an existing AUTO_INCREMENT column value in an InnoDB table
does not reset the AUTO_INCREMENT sequence as it does for MyISAM and NDB
tables."

In MySQL 8.0, updating an existing AUTO_INCREMENT column value in an InnoDB table
also resets the AUTO_INCREMENT sequence.

https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.7/en/example-auto-increment.html

The revised documentation should appear online soon.

Bug#87861 and Bug #65116 have been marked as duplicates of this bug.

The documentation change appears to be unrelated to the REPLACE INTO replication bug.

See more details in the bug #87861 and bug #65116.

Please re-open this bug.

Thanks.

Hi Monty,

The replace statement on master is considered as delete and insert.
So, it increases the auto_incrment value. But in row-based replication,
this replace is considered as an update event.

The root cause for the bug is that InnoDB doesn't update the
auto_increment value when the largest value in the auto_increment
column was changed and this was against the documentation which said

"When you insert any other value into an AUTO_INCREMENT column,
the column is set to that value and the sequence is reset so
that the next automatically generated value follows
sequentially from the largest column value".

The same can be observed in the below case.

CREATE TABLE t1 (c1 INT NOT NULL DEFAULT '0') ENGINE=InnoDB;
INSERT INTO t1(c1) VALUES (1), (2), (3);
ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t1(c1) VALUES (1000);

SELECT * FROM t1;
c1	c2
1	1
2	2
3	3
1000	4

UPDATE t1 SET c2= 1000 WHERE c1=1000;
INSERT INTO t1(c1) VALUES (2000);

# Since the largest value in the autoinc column was updated,
# We expect c2=1001 when c1=2000, but it is 5.
SELECT * FROM t1;
c1	c2
1	1
2	2
3	3
2000	5
1000	1000
DROP TABLE t1;

Fixing this may cause behavioral change for autoincrement in GA versions and
may cause problems with replication between minor versions.

However, the same has already been fixed in 8.0 by
WL#6204 - InnoDB persistent max value for autoinc columns.
Upgrading to 8.0 will fix the issue.

Regards,
Venkatesh Venugopal

A possible workaround for this bug for MySQL releases prior to 8 would be making the AUTO_INCREMENT sequence only UNIQUE, while making the previously UNIQUE field the new Primary Key of the table. So in the original:

> create table t1(a int auto_increment primary key, b int not null, c int not null, unique key uk_b(b)) engine=innodb;

`b` would be made the Primary Key while `a` would become UNIQUE. Trey and I blogged about this today.