summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorRahul Malik <rahul.m.malik@oracle.com>2019-05-02 10:43:17 +0530
committerMarko Mäkelä <marko.makela@mariadb.com>2019-07-23 11:26:11 +0300
commit739f5239f12904247d2a61f9880ea1fafbedc332 (patch)
treee9596e5132fcbf02b70aca779e63a4f02deb767c
parent7153e155424de4b42bc0f96ceb6433e4ed1f827a (diff)
downloadmariadb-git-739f5239f12904247d2a61f9880ea1fafbedc332.tar.gz
Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS
Problem: Clients running different values for auto_increment_increment and doing concurrent inserts leads to "Duplicate key error" in one of them. Analysis: When auto_increment_increment value is reduced in a session, InnoDB uses last auto_increment_increment value to recalculate the autoinc value. In case, some other session has inserted a value with different auto_increment_increment, InnoDB recalculate autoinc values based on current session previous auto_increment_increment instead of considering the auto_increment_increment used for last insert across all session Fix: revert 7acdf29cb4f90498af143430e3bf0e9fd3bd39f5 a.k.a. 7c12a9e5c3200688612d59160e8f45b1c8451635 as it causing the bug. Reviewed By: Bin <bin.x.su@oracle.com> Kevin <kevin.lewis@oracle.com> RB#21777 Note: In MariaDB Server, earlier changes in ae5bc059880c395ccf2cc51d5db1895dffc4f5f0 for MDEV-533 require that the original test in mysql/mysql-server@1ccd472d63a042d3237a55f5827239164219ef7e be adjusted for MariaDB. Also, ef47b62551b0f37770e5d174ea028150c5b71fd8 (MDEV-8827) had to be reverted after the upstream fix had been backported.
-rw-r--r--mysql-test/suite/innodb/r/innodb-autoinc.result132
-rw-r--r--mysql-test/suite/innodb/t/innodb-autoinc.test60
-rw-r--r--storage/innobase/handler/ha_innodb.cc18
-rw-r--r--storage/xtradb/handler/ha_innodb.cc18
4 files changed, 194 insertions, 34 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-autoinc.result b/mysql-test/suite/innodb/r/innodb-autoinc.result
index d6f7a930340..d98a539f968 100644
--- a/mysql-test/suite/innodb/r/innodb-autoinc.result
+++ b/mysql-test/suite/innodb/r/innodb-autoinc.result
@@ -1340,11 +1340,139 @@ SELECT * FROM t;
i
1
301
-351
+601
SHOW CREATE TABLE t;
Table Create Table
t CREATE TABLE `t` (
`i` int(11) NOT NULL AUTO_INCREMENT,
KEY `i` (`i`)
-) ENGINE=InnoDB AUTO_INCREMENT=401 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=651 DEFAULT CHARSET=latin1
+DROP TABLE t;
+#
+# Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS
+#
+# This test shows that the next record to be inserted is not affected
+# by a change in auto_increment_increment.
+# In addition, current value of auto_increment_increment by the client
+# that uses the existing autoinc value with be used to set next autoinc
+# value, which will be used by next client reguardless of its own session
+# setting for auto_increment_increment.
+#
+# Client 1: Insert a record with auto_increment_increment=2
+CREATE TABLE t(
+a SERIAL PRIMARY KEY,
+b VARCHAR(200)) ENGINE=InnoDB;
+SET SESSION auto_increment_increment=2;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S1');
+SELECT a,b FROM t;
+a b
+1 S1
+# Client 2: Insert records with auto_increment_increment 2,1
+SET SESSION auto_increment_increment=2;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S2');
+SELECT a,b FROM t;
+a b
+1 S1
+3 S2
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S2');
+SELECT a,b FROM t;
+a b
+1 S1
+3 S2
+5 S2
+# Client 1: Insert a record with auto_increment_increment=1
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`),
+ UNIQUE KEY `a` (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S1');
+SELECT a,b FROM t;
+a b
+1 S1
+3 S2
+5 S2
+6 S1
+DROP TABLE t;
+# Autoincrement behaviour with mixed insert.
+CREATE TABLE t(
+a TINYINT AUTO_INCREMENT PRIMARY KEY,
+b VARCHAR(200)) ENGINE=InnoDB;
+SET SESSION auto_increment_increment=10;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S0'),('S1');
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1
+INSERT INTO t(a,b) VALUES(28,'S2');
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S3');
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
+INSERT INTO t(b) VALUES('S4');
+SELECT * FROM t;
+a b
+1 S0
+11 S1
+28 S2
+31 S3
+32 S4
+SHOW CREATE TABLE t;
+Table Create Table
+t CREATE TABLE `t` (
+ `a` tinyint(4) NOT NULL AUTO_INCREMENT,
+ `b` varchar(200) DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1
DROP TABLE t;
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test
index 5e5a2b49a7d..b311e161a0f 100644
--- a/mysql-test/suite/innodb/t/innodb-autoinc.test
+++ b/mysql-test/suite/innodb/t/innodb-autoinc.test
@@ -680,3 +680,63 @@ INSERT INTO t VALUES (NULL);
SELECT * FROM t;
SHOW CREATE TABLE t;
DROP TABLE t;
+
+--echo #
+--echo # Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS
+--echo #
+--echo # This test shows that the next record to be inserted is not affected
+--echo # by a change in auto_increment_increment.
+--echo # In addition, current value of auto_increment_increment by the client
+--echo # that uses the existing autoinc value with be used to set next autoinc
+--echo # value, which will be used by next client reguardless of its own session
+--echo # setting for auto_increment_increment.
+--echo #
+
+--connection default
+--echo # Client 1: Insert a record with auto_increment_increment=2
+CREATE TABLE t(
+ a SERIAL PRIMARY KEY,
+ b VARCHAR(200)) ENGINE=InnoDB;
+SET SESSION auto_increment_increment=2;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S1');
+SELECT a,b FROM t;
+--connect(con1,localhost,root,,)
+
+--connection con1
+--echo # Client 2: Insert records with auto_increment_increment 2,1
+SET SESSION auto_increment_increment=2;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S2');
+SELECT a,b FROM t;
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S2');
+SELECT a,b FROM t;
+disconnect con1;
+
+--connection default
+--echo # Client 1: Insert a record with auto_increment_increment=1
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S1');
+SELECT a,b FROM t;
+DROP TABLE t;
+
+--echo # Autoincrement behaviour with mixed insert.
+CREATE TABLE t(
+ a TINYINT AUTO_INCREMENT PRIMARY KEY,
+ b VARCHAR(200)) ENGINE=InnoDB;
+SET SESSION auto_increment_increment=10;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S0'),('S1');
+SHOW CREATE TABLE t;
+INSERT INTO t(a,b) VALUES(28,'S2');
+SET SESSION auto_increment_increment=1;
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S3');
+SHOW CREATE TABLE t;
+INSERT INTO t(b) VALUES('S4');
+SELECT * FROM t;
+SHOW CREATE TABLE t;
+DROP TABLE t;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index dab43efbf94..5b9c9dc9123 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -1,8 +1,9 @@
/*****************************************************************************
-Copyright (c) 2000, 2018, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 2000, 2019, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2008, 2009 Google Inc.
Copyright (c) 2009, Percona Inc.
+Copyright (c) 2010, 2019, MariaDB Corporation.
Portions of this file contain modifications contributed and copyrighted by
Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -10529,21 +10530,6 @@ ha_innobase::get_auto_increment(
current = *first_value;
- /* If the increment step of the auto increment column
- decreases then it is not affecting the immediate
- next value in the series. */
- if (prebuilt->autoinc_increment > increment) {
-
- current = autoinc - prebuilt->autoinc_increment;
-
- current = innobase_next_autoinc(
- current, 1, increment, 1, col_max_value);
-
- dict_table_autoinc_initialize(prebuilt->table, current);
-
- *first_value = current;
- }
-
/* Compute the last value in the interval */
next_value = innobase_next_autoinc(
current, *nb_reserved_values, increment, offset,
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index 5f3639a8ea9..18d4bd5b4d5 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -1,8 +1,9 @@
/*****************************************************************************
-Copyright (c) 2000, 2018, Oracle and/or its affiliates. All Rights Reserved.
+Copyright (c) 2000, 2019, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2008, 2009 Google Inc.
Copyright (c) 2009, Percona Inc.
+Copyright (c) 2010, 2019, MariaDB Corporation.
Portions of this file contain modifications contributed and copyrighted by
Google, Inc. Those modifications are gratefully acknowledged and are described
@@ -11798,21 +11799,6 @@ ha_innobase::get_auto_increment(
current = *first_value;
- /* If the increment step of the auto increment column
- decreases then it is not affecting the immediate
- next value in the series. */
- if (prebuilt->autoinc_increment > increment) {
-
- current = autoinc - prebuilt->autoinc_increment;
-
- current = innobase_next_autoinc(
- current, 1, increment, 1, col_max_value);
-
- dict_table_autoinc_initialize(prebuilt->table, current);
-
- *first_value = current;
- }
-
/* Compute the last value in the interval */
next_value = innobase_next_autoinc(
current, *nb_reserved_values, increment, offset,