Skip to content

Commit b02b79a

Browse files
author
Sujatha Sivakumar
committed
Bug#17588419: INSERT ON DUPLICATE KEY UPDATE FAILING AFTER
MYSQL 5.6 UPGRADE. Problem: ======== After upgrading a MySQL 5.5.23 slave to 5.6.14 (the master still runs 5.5.23) on executing INSERT ON DUPLICATE KEY following error is reported in SBR. [ERROR] Slave SQL: Error 'Auto-increment value in UPDATE conflicts with internally generated values' on query. Analysis: ======== On master when user specifies an autoincrement value in multi insert statement the user given value is compared with current auto increment value and current + number of rows affected value. Whenever the user specified value falls within the given range ER_AUTO_INCREMENT_CONFLICT error is generated. On the slave the range is set to the value ULONGULONG_MAX by default. Whenever user specifies any value it will always fall within the above range and error gets generated. On slave each DML operation will result in n number of rows being manipulated. When number of manipulated rows is known the value range can be reset to currect value to current+n. Fix: === On slave identify the number of rows being manipulated. Reset the value ranage from currnet value to current value + number of maninupated rows. If the number is not known go ahead with default values.
1 parent 670a876 commit b02b79a

3 files changed

Lines changed: 61 additions & 2 deletions

File tree

‎mysql-test/suite/rpl/r/rpl_insert_on_update.result‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
include/master-slave.inc
2+
Warnings:
3+
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4+
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.
5+
[connection master]
6+
CREATE TABLE t(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,k INT,UNIQUE KEY(k)) ENGINE=InnoDB;
7+
INSERT INTO t(k) VALUES (1), (2), (3);
8+
INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10;
9+
include/sync_slave_sql_with_master.inc
10+
include/diff_tables.inc [master:t,slave:t]
11+
DROP TABLE t;
12+
include/rpl_end.inc

‎mysql-test/suite/rpl/t/rpl_insert_on_update.test‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
###############################################################################
2+
# Bug#17588419: INSERT ON DUPLICATE KEY UPDATE FAILING AFTER MYSQL 5.6
3+
# UPGRADE.
4+
#
5+
# Problem:
6+
# ========
7+
# After upgrading a MySQL 5.5.23 slave to 5.6.14 (the master still runs 5.5.23)
8+
# on executing INSERT ON DUPLICATE KEY following error is reported in SBR.
9+
#
10+
# [ERROR] Slave SQL: Error 'Auto-increment value in UPDATE conflicts with
11+
# internally generated values' on query.
12+
#
13+
# Test:
14+
# =====
15+
# Execute INSERT ON DUPLICATE KEY UPDATE statements on master and try to sync
16+
# master with slave. With the bug sync operation will fail with
17+
# ER_AUTO_INCREMENT_CONFLICT. Post fix sync operation should succeed.
18+
# Note:
19+
# As part of the bug fix the issue is fixed only with innodb storage engine.
20+
###############################################################################
21+
--source include/master-slave.inc
22+
23+
--disable_query_log
24+
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format*");
25+
--enable_query_log
26+
27+
--connection master
28+
CREATE TABLE t(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,k INT,UNIQUE KEY(k)) ENGINE=InnoDB;
29+
--disable_warnings
30+
INSERT INTO t(k) VALUES (1), (2), (3);
31+
INSERT INTO t(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE id=10;
32+
--enable_warnings
33+
--source include/sync_slave_sql_with_master.inc
34+
35+
--connection master
36+
-- let $diff_tables=master:t,slave:t
37+
-- source include/diff_tables.inc
38+
39+
DROP TABLE t;
40+
--source include/rpl_end.inc

‎sql/handler.cc‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
1+
/* Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
22
33
This program is free software; you can redistribute it and/or modify
44
it under the terms of the GNU General Public License as published by
@@ -3216,7 +3216,14 @@ int handler::update_auto_increment()
32163216
if (forced != NULL)
32173217
{
32183218
nr= forced->minimum();
3219-
nb_reserved_values= forced->values();
3219+
/*
3220+
In a multi insert statement when the number of affected rows is known
3221+
then reserve those many number of auto increment values. So that
3222+
interval will be starting value to starting value + number of affected
3223+
rows * increment of auto increment.
3224+
*/
3225+
nb_reserved_values= (estimation_rows_to_insert > 0) ?
3226+
estimation_rows_to_insert : forced->values();
32203227
}
32213228
else
32223229
{

0 commit comments

Comments
 (0)