diff options
author | Rahul Malik <rahul.m.malik@oracle.com> | 2019-05-02 10:43:17 +0530 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2019-07-23 11:26:11 +0300 |
commit | 739f5239f12904247d2a61f9880ea1fafbedc332 (patch) | |
tree | e9596e5132fcbf02b70aca779e63a4f02deb767c | |
parent | 7153e155424de4b42bc0f96ceb6433e4ed1f827a (diff) | |
download | mariadb-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.result | 132 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb-autoinc.test | 60 | ||||
-rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 18 | ||||
-rw-r--r-- | storage/xtradb/handler/ha_innodb.cc | 18 |
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, |