summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/innodb-autoinc.result221
-rw-r--r--mysql-test/t/innodb-autoinc.test42
-rw-r--r--storage/innobase/handler/ha_innodb.cc8
3 files changed, 270 insertions, 1 deletions
diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result
index c49405f5175..f400c8e9f16 100644
--- a/mysql-test/r/innodb-autoinc.result
+++ b/mysql-test/r/innodb-autoinc.result
@@ -627,3 +627,224 @@ c1 c2
3 3
4 4
DROP TABLE t1;
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note 1051 Unknown table 't1'
+DROP TABLE IF EXISTS t2;
+Warnings:
+Note 1051 Unknown table 't2'
+CREATE TABLE t1 (
+a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+b INT(10) UNSIGNED NOT NULL,
+c ENUM('FALSE','TRUE') DEFAULT NULL,
+PRIMARY KEY (a)) ENGINE = InnoDB;
+CREATE TABLE t2 (
+m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+n INT(10) UNSIGNED NOT NULL,
+o enum('FALSE','TRUE') DEFAULT NULL,
+PRIMARY KEY (m)) ENGINE = InnoDB;
+INSERT INTO t2 (n,o) VALUES
+(1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
+(3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `m` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `n` int(10) unsigned NOT NULL,
+ `o` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`m`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+30 1 FALSE
+31 2 FALSE
+32 3 FALSE
+33 4 FALSE
+34 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+a b c
+1 1 TRUE
+2 1 FALSE
+3 2 TRUE
+4 2 FALSE
+5 3 TRUE
+6 3 FALSE
+7 4 TRUE
+8 4 FALSE
+9 5 TRUE
+10 5 FALSE
+13 1 TRUE
+14 1 FALSE
+15 2 TRUE
+16 2 FALSE
+17 3 TRUE
+18 3 FALSE
+19 4 TRUE
+20 4 FALSE
+21 5 TRUE
+22 5 FALSE
+23 1 FALSE
+24 2 FALSE
+25 3 FALSE
+26 4 FALSE
+27 5 FALSE
+30 1 FALSE
+31 2 FALSE
+32 3 FALSE
+33 4 FALSE
+34 5 FALSE
+37 1 FALSE
+38 2 FALSE
+39 3 FALSE
+40 4 FALSE
+41 5 FALSE
+44 1 FALSE
+45 2 FALSE
+46 3 FALSE
+47 4 FALSE
+48 5 FALSE
+51 1 FALSE
+52 2 FALSE
+53 3 FALSE
+54 4 FALSE
+55 5 FALSE
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
+ `b` int(10) unsigned NOT NULL,
+ `c` enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (`a`)
+) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test
index 5f9d4bfdae9..6be2e5ac67e 100644
--- a/mysql-test/t/innodb-autoinc.test
+++ b/mysql-test/t/innodb-autoinc.test
@@ -417,3 +417,45 @@ INSERT INTO t1 VALUES(NULL, 3);
INSERT INTO t1 VALUES(NULL, 4);
SELECT * FROM t1;
DROP TABLE t1;
+
+#
+# Bug# 42714: AUTOINC column calculated next value not greater than highest
+# value stored in table.
+#
+SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (
+ a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+ b INT(10) UNSIGNED NOT NULL,
+ c ENUM('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (a)) ENGINE = InnoDB;
+CREATE TABLE t2 (
+ m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
+ n INT(10) UNSIGNED NOT NULL,
+ o enum('FALSE','TRUE') DEFAULT NULL,
+ PRIMARY KEY (m)) ENGINE = InnoDB;
+INSERT INTO t2 (n,o) VALUES
+ (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'),
+ (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
+SHOW CREATE TABLE t2;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 ;
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false';
+SELECT * FROM t1;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 5c4e111b67d..b125c731228 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -953,6 +953,12 @@ innobase_next_autoinc(
/* Should never be 0. */
ut_a(increment > 0);
+ /* According to MySQL documentation, if the offset is greater than
+ the increment then the offset is ignored. */
+ if (offset > increment) {
+ offset = 0;
+ }
+
if (max_value <= current) {
next_value = max_value;
} else if (offset <= 1) {
@@ -3780,7 +3786,7 @@ no_commit:
will be 0 if get_auto_increment() was not called.*/
if (auto_inc <= col_max_value
- && auto_inc > prebuilt->autoinc_last_value) {
+ && auto_inc >= prebuilt->autoinc_last_value) {
set_max_autoinc:
ut_a(prebuilt->autoinc_increment > 0);
View Lorry Controller Status