diff options
| -rw-r--r-- | mysql-test/r/innodb-autoinc.result | 27 | ||||
| -rw-r--r-- | mysql-test/t/innodb-autoinc.test | 16 | ||||
| -rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 26 |
3 files changed, 56 insertions, 13 deletions
diff --git a/mysql-test/r/innodb-autoinc.result b/mysql-test/r/innodb-autoinc.result index ce96baf11c1..e662a6772c7 100644 --- a/mysql-test/r/innodb-autoinc.result +++ b/mysql-test/r/innodb-autoinc.result @@ -867,6 +867,7 @@ INSERT INTO t2 SELECT NULL FROM t1; Got one of the listed errors DROP TABLE t1; DROP TABLE t2; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (null); INSERT INTO t1 VALUES (null); @@ -874,18 +875,25 @@ ALTER TABLE t1 CHANGE c1 d1 INT NOT NULL AUTO_INCREMENT; SELECT * FROM t1; d1 1 -3 +2 SELECT * FROM t1; d1 1 -3 +2 INSERT INTO t1 VALUES(null); Got one of the listed errors ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `d1` int(11) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`d1`) +) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES(null); SELECT * FROM t1; d1 1 +2 3 4 DROP TABLE t1; @@ -1104,3 +1112,18 @@ c1 c2 3 innodb 4 NULL DROP TABLE t1; +CREATE TABLE T1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on T1(c2); +SHOW CREATE TABLE T1; +Table Create Table +T1 CREATE TABLE `T1` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `i1` (`c2`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +INSERT INTO T1 (c2) values (0); +SELECT * FROM T1; +c1 c2 +10 0 +DROP TABLE T1; diff --git a/mysql-test/t/innodb-autoinc.test b/mysql-test/t/innodb-autoinc.test index a4ffc64c99f..218484d440c 100644 --- a/mysql-test/t/innodb-autoinc.test +++ b/mysql-test/t/innodb-autoinc.test @@ -482,6 +482,7 @@ DROP TABLE t2; # 44030: Error: (1500) Couldn't read the MAX(ID) autoinc value from # the index (PRIMARY) # This test requires a restart of the server +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; INSERT INTO t1 VALUES (null); INSERT INTO t1 VALUES (null); @@ -491,10 +492,12 @@ SELECT * FROM t1; -- source include/restart_mysqld.inc # The MySQL and InnoDB data dictionaries should now be out of sync. # The select should print message to the error log +#-- error ER_AUTOINC_READ_FAILED,1467 SELECT * FROM t1; -- error ER_AUTOINC_READ_FAILED,1467 INSERT INTO t1 VALUES(null); ALTER TABLE t1 AUTO_INCREMENT = 3; +SHOW CREATE TABLE t1; INSERT INTO t1 VALUES(null); SELECT * FROM t1; DROP TABLE t1; @@ -601,5 +604,16 @@ INSERT INTO t1 VALUES (NULL, NULL); SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; - +# # End negative number check + +## +# 47125: auto_increment start value is ignored if an index is created +# and engine=innodb +# +CREATE TABLE T1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on T1(c2); +SHOW CREATE TABLE T1; +INSERT INTO T1 (c2) values (0); +SELECT * FROM T1; +DROP TABLE T1; diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 3b096d9bab3..84bf5a89d83 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -2218,6 +2218,8 @@ innobase_rollback( innobase_release_stat_resources(trx); + trx->n_autoinc_rows = 0; /* Reset the number AUTO-INC rows required */ + /* If we had reserved the auto-inc lock for some table (if we come here to roll back the latest SQL statement) we release it now before a possibly lengthy rollback */ @@ -5603,18 +5605,22 @@ ha_innobase::create( setup at this stage and so we use thd. */ /* We need to copy the AUTOINC value from the old table if - this is an ALTER TABLE. */ + this is an ALTER TABLE or CREATE INDEX because CREATE INDEX + does a table copy too. */ if (((create_info->used_fields & HA_CREATE_USED_AUTO) - || thd_sql_command(thd) == SQLCOM_ALTER_TABLE) - && create_info->auto_increment_value != 0) { - - /* Query was ALTER TABLE...AUTO_INCREMENT = x; or - CREATE TABLE ...AUTO_INCREMENT = x; Find out a table - definition from the dictionary and get the current value - of the auto increment field. Set a new value to the - auto increment field if the value is greater than the - maximum value in the column. */ + || thd_sql_command(thd) == SQLCOM_ALTER_TABLE + || thd_sql_command(thd) == SQLCOM_CREATE_INDEX) + && create_info->auto_increment_value > 0) { + + /* Query was one of : + CREATE TABLE ...AUTO_INCREMENT = x; or + ALTER TABLE...AUTO_INCREMENT = x; or + CREATE INDEX x on t(...); + Find out a table definition from the dictionary and get + the current value of the auto increment field. Set a new + value to the auto increment field if the value is greater + than the maximum value in the column. */ auto_inc_value = create_info->auto_increment_value; |