Description:
The table AUTO_INCREMENT value is not kept in synch between the master and the slave when using INSERT ON DUPLICATE KEY UPDATE.
How to repeat:
--source include/master-slave.inc
--echo # Scenario 1: Test OJKU operations.
connection master;
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '0',
`data` varchar(100) not null DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB;
INSERT INTO t1(name, data) VALUES
('user1', 'data1'),
('user2', 'data2'),
('user3', 'data3'),
('user4', 'data4'),
('user5', 'data5');
INSERT INTO t1(name, data)
VALUES
('user2', 'new data2'),
('user3', 'new data3'),
('user4', 'new data4')
ON DUPLICATE KEY UPDATE data = VALUES(data);
--sync_slave_with_master
connection master;
SELECT * FROM t1;
select table_name,auto_increment from information_schema.tables where table_name='t1';
connection slave;
SELECT * FROM t1;
select table_name,auto_increment from information_schema.tables where table_name='t1';
--echo # clean-up
connection master;
DROP TABLE t1;
--sync_slave_with_master
--source include/rpl_end.inc
The results are as follows. By comparison, it is found that the auto-increment column of slave is 6, and the master is 9.
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 connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
[connection master]
# Scenario 1: Test OJKU operations.
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '0',
`data` varchar(100) not null DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY uniq_name(name)
) ENGINE=InnoDB;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
INSERT INTO t1(name, data) VALUES
('user1', 'data1'),
('user2', 'data2'),
('user3', 'data3'),
('user4', 'data4'),
('user5', 'data5');
INSERT INTO t1(name, data)
VALUES
('user2', 'new data2'),
('user3', 'new data3'),
('user4', 'new data4')
ON DUPLICATE KEY UPDATE data = VALUES(data);
Warnings:
Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SELECT * FROM t1;
id name data
1 user1 data1
2 user2 new data2
3 user3 new data3
4 user4 new data4
5 user5 data5
select table_name,auto_increment from information_schema.tables where table_name='t1';
TABLE_NAME AUTO_INCREMENT
t1 9
SELECT * FROM t1;
id name data
1 user1 data1
2 user2 new data2
3 user3 new data3
4 user4 new data4
5 user5 data5
select table_name,auto_increment from information_schema.tables where table_name='t1';
TABLE_NAME AUTO_INCREMENT
t1 6
# clean-up
DROP TABLE t1;
include/rpl_end.inc
Suggested fix:
Observing the binlog, it was found that the auto-incrementing id was not updated. However, the binlog of 'replace into' is different and will update the auto-incrementing id.
INSERT INTO t1(name, data)
VALUES
('user2', 'new data2'),
('user3', 'new data3'),
('user4', 'new data4')
ON DUPLICATE KEY UPDATE data = VALUES(data);
### UPDATE `test`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
# at 2505
REPLACE INTO t1(name, data) VALUES
('user2', 'new data2'),
('user3', 'new data3'),
('user4', 'new data4');
### UPDATE `test`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### UPDATE `test`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
### SET
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### @3='new data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
Description: The table AUTO_INCREMENT value is not kept in synch between the master and the slave when using INSERT ON DUPLICATE KEY UPDATE. How to repeat: --source include/master-slave.inc --echo # Scenario 1: Test OJKU operations. connection master; CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL DEFAULT '0', `data` varchar(100) not null DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY uniq_name(name) ) ENGINE=InnoDB; INSERT INTO t1(name, data) VALUES ('user1', 'data1'), ('user2', 'data2'), ('user3', 'data3'), ('user4', 'data4'), ('user5', 'data5'); INSERT INTO t1(name, data) VALUES ('user2', 'new data2'), ('user3', 'new data3'), ('user4', 'new data4') ON DUPLICATE KEY UPDATE data = VALUES(data); --sync_slave_with_master connection master; SELECT * FROM t1; select table_name,auto_increment from information_schema.tables where table_name='t1'; connection slave; SELECT * FROM t1; select table_name,auto_increment from information_schema.tables where table_name='t1'; --echo # clean-up connection master; DROP TABLE t1; --sync_slave_with_master --source include/rpl_end.inc The results are as follows. By comparison, it is found that the auto-increment column of slave is 6, and the master is 9. 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 connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information. [connection master] # Scenario 1: Test OJKU operations. CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL DEFAULT '0', `data` varchar(100) not null DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY uniq_name(name) ) ENGINE=InnoDB; Warnings: Warning 1681 Integer display width is deprecated and will be removed in a future release. INSERT INTO t1(name, data) VALUES ('user1', 'data1'), ('user2', 'data2'), ('user3', 'data3'), ('user4', 'data4'), ('user5', 'data5'); INSERT INTO t1(name, data) VALUES ('user2', 'new data2'), ('user3', 'new data3'), ('user4', 'new data4') ON DUPLICATE KEY UPDATE data = VALUES(data); Warnings: Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead SELECT * FROM t1; id name data 1 user1 data1 2 user2 new data2 3 user3 new data3 4 user4 new data4 5 user5 data5 select table_name,auto_increment from information_schema.tables where table_name='t1'; TABLE_NAME AUTO_INCREMENT t1 9 SELECT * FROM t1; id name data 1 user1 data1 2 user2 new data2 3 user3 new data3 4 user4 new data4 5 user5 data5 select table_name,auto_increment from information_schema.tables where table_name='t1'; TABLE_NAME AUTO_INCREMENT t1 6 # clean-up DROP TABLE t1; include/rpl_end.inc Suggested fix: Observing the binlog, it was found that the auto-incrementing id was not updated. However, the binlog of 'replace into' is different and will update the auto-incrementing id. INSERT INTO t1(name, data) VALUES ('user2', 'new data2'), ('user3', 'new data3'), ('user4', 'new data4') ON DUPLICATE KEY UPDATE data = VALUES(data); ### UPDATE `test`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ # at 2505 REPLACE INTO t1(name, data) VALUES ('user2', 'new data2'), ('user3', 'new data3'), ('user4', 'new data4'); ### UPDATE `test`.`t1` ### WHERE ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user2' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data2' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user3' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data3' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### UPDATE `test`.`t1` ### WHERE ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */ ### SET ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ ### @2='user4' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */ ### @3='new data4' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */