#At file:///home/nirbhay/Project/mysql/repo/wl/mysql-5.1-bugteam-39828/ based on revid:georgi.kodinov@stripped
3520 Nirbhay Choubey 2010-10-03
Bug#39828 : Autoinc wraps around when offset and increment > 1
Auto increment value wraps when performing a bulk insert with
auto_increment_increment and auto_increment_offset greater than
one. Howerer, the initial investigation showed that same bug can
be reproduced even if just auto_increment_increment is greater
than one. The bug report also reports of an assertion failure on
a debug server.
The wrapping of autoinc column happend in update_auto_increment
function as it failed to check for overflow of next autoinc value
to be inserted into the column in case of bulk insert.
Fixed by placing checks for overflow.
@ mysql-test/suite/innodb/r/innodb-autoinc.result
Bug #39828 : Autoinc wraps around when offset and increment > 1
@ mysql-test/suite/innodb/t/innodb-autoinc.test
Bug #39828 : Autoinc wraps around when offset and increment > 1
@ mysql-test/suite/innodb_plugin/r/innodb-autoinc.result
Bug #39828 : Autoinc wraps around when offset and increment > 1
@ mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
Bug #39828 : Autoinc wraps around when offset and increment > 1
@ sql/handler.cc
Bug #39828 : Autoinc wraps around when offset and increment > 1
Added a condition to check for overflow of 'nr' returned from
compute_next_insert_id. Apart from that, added one more condition
towards the beginning of handler::update_auto_increment to check
if next_insert_id is lesser than insert_id_for_cur_row, which
inturn will avoid overflowing of autoinc value in case of
bulk (multi-valued) insert.
modified:
mysql-test/suite/innodb/r/innodb-autoinc.result
mysql-test/suite/innodb/t/innodb-autoinc.test
mysql-test/suite/innodb_plugin/r/innodb-autoinc.result
mysql-test/suite/innodb_plugin/t/innodb-autoinc.test
sql/handler.cc
=== modified file 'mysql-test/suite/innodb/r/innodb-autoinc.result'
--- a/mysql-test/suite/innodb/r/innodb-autoinc.result 2010-04-12 11:56:24 +0000
+++ b/mysql-test/suite/innodb/r/innodb-autoinc.result 2010-10-02 18:44:54 +0000
@@ -471,17 +471,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 2
auto_increment_offset 10
-INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551603
-18446744073709551604
-18446744073709551606
-18446744073709551608
-18446744073709551610
-18446744073709551612
-18446744073709551614
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
@@ -504,13 +499,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 5
auto_increment_offset 7
-INSERT INTO t1 VALUES (NULL),(NULL);
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551603
-18446744073709551607
-18446744073709551612
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
@@ -572,12 +566,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 65535
auto_increment_offset 65535
-INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551610
-18446744073709551615
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
=== modified file 'mysql-test/suite/innodb/t/innodb-autoinc.test'
--- a/mysql-test/suite/innodb/t/innodb-autoinc.test 2010-04-12 11:56:24 +0000
+++ b/mysql-test/suite/innodb/t/innodb-autoinc.test 2010-10-02 18:44:54 +0000
@@ -279,6 +279,9 @@ DROP TABLE t1;
#
# Check for overflow handling when increment and offser are > 1
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -291,26 +294,16 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't, it seems to be
-# a MySQL server bug. It wraps around to 0 for the last value.
-# See MySQL Bug# 39828
-#
-# Instead of wrapping around, it asserts when MySQL is compiled --with-debug
-# (see sql/handler.cc:handler::update_auto_increment()). Don't test for
-# overflow until Bug #39828 is fixed.
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-#INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
-#else
-INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
#
# Check for overflow handling when increment and offset are odd numbers
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -323,20 +316,8 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't. It fails with
-# a duplicate entry message because of a MySQL server bug, it wraps
-# around. See MySQL Bug# 39828, once MySQL fix the bug we can replace
-# the ER_DUP_ENTRY, 1062 below with the appropriate error message
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-# Still need to fix this error code, error should mention overflow
-#-- error ER_DUP_ENTRY,1062
-#INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
-#else
-INSERT INTO t1 VALUES (NULL),(NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
@@ -362,6 +343,9 @@ DROP TABLE t1;
#
# Check for overflow handling when increment and offset are very
# large numbers 2^60
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -374,20 +358,8 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't. It wraps around
-# and the autoinc values look bogus too.
-# See MySQL Bug# 39828, once MySQL fix the bug we can enable the error
-# code expected test.
-# -- error ER_AUTOINC_READ_FAILED,1467
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-#-- error ER_AUTOINC_READ_FAILED,1467
-#INSERT INTO t1 VALUES (NULL),(NULL);
-#else
-INSERT INTO t1 VALUES (NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
=== modified file 'mysql-test/suite/innodb_plugin/r/innodb-autoinc.result'
--- a/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result 2010-04-09 12:56:51 +0000
+++ b/mysql-test/suite/innodb_plugin/r/innodb-autoinc.result 2010-10-02 18:44:54 +0000
@@ -471,17 +471,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 2
auto_increment_offset 10
-INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551603
-18446744073709551604
-18446744073709551606
-18446744073709551608
-18446744073709551610
-18446744073709551612
-18446744073709551614
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
@@ -504,13 +499,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 5
auto_increment_offset 7
-INSERT INTO t1 VALUES (NULL),(NULL);
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551603
-18446744073709551607
-18446744073709551612
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
@@ -572,12 +566,12 @@ SHOW VARIABLES LIKE "%auto_inc%";
Variable_name Value
auto_increment_increment 65535
auto_increment_offset 65535
-INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL),(NULL);
+ERROR HY000: Failed to read auto-increment value from storage engine
SELECT * FROM t1;
c1
1
18446744073709551610
-18446744073709551615
DROP TABLE t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
=== modified file 'mysql-test/suite/innodb_plugin/t/innodb-autoinc.test'
--- a/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test 2010-08-06 09:35:17 +0000
+++ b/mysql-test/suite/innodb_plugin/t/innodb-autoinc.test 2010-10-02 18:44:54 +0000
@@ -281,6 +281,9 @@ DROP TABLE t1;
#
# Check for overflow handling when increment and offser are > 1
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -293,26 +296,16 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't, it seems to be
-# a MySQL server bug. It wraps around to 0 for the last value.
-# See MySQL Bug# 39828
-#
-# Instead of wrapping around, it asserts when MySQL is compiled --with-debug
-# (see sql/handler.cc:handler::update_auto_increment()). Don't test for
-# overflow until Bug #39828 is fixed.
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-#INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
-#else
-INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
#
# Check for overflow handling when increment and offset are odd numbers
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -325,20 +318,8 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't. It fails with
-# a duplicate entry message because of a MySQL server bug, it wraps
-# around. See MySQL Bug# 39828, once MySQL fix the bug we can replace
-# the ER_DUP_ENTRY, 1062 below with the appropriate error message
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-# Still need to fix this error code, error should mention overflow
-#-- error ER_DUP_ENTRY,1062
-#INSERT INTO t1 VALUES (NULL),(NULL), (NULL);
-#else
-INSERT INTO t1 VALUES (NULL),(NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
@@ -364,6 +345,9 @@ DROP TABLE t1;
#
# Check for overflow handling when increment and offset are very
# large numbers 2^60
+#
+# Bug #39828 : Autoinc wraps around when offset and increment > 1
+#
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1;
SET @@INSERT_ID=1;
SHOW VARIABLES LIKE "%auto_inc%";
@@ -376,20 +360,8 @@ INSERT INTO t1 VALUES (18446744073709551
SELECT * FROM t1;
SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976;
SHOW VARIABLES LIKE "%auto_inc%";
-# This should fail because of overflow but it doesn't. It wraps around
-# and the autoinc values look bogus too.
-# See MySQL Bug# 39828, once MySQL fix the bug we can enable the error
-# code expected test.
-# -- error ER_AUTOINC_READ_FAILED,1467
-#
-# Since this asserts when compiled --with-debug, we can't properly test this
-# until Bug #39828 is fixed. For now, this test is meaningless.
-#if Bug #39828 is fixed
-#-- error ER_AUTOINC_READ_FAILED,1467
-#INSERT INTO t1 VALUES (NULL),(NULL);
-#else
-INSERT INTO t1 VALUES (NULL);
-#endif
+-- error ER_AUTOINC_READ_FAILED
+INSERT INTO t1 VALUES (NULL),(NULL);
SELECT * FROM t1;
DROP TABLE t1;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2010-04-14 09:53:59 +0000
+++ b/sql/handler.cc 2010-10-02 18:44:54 +0000
@@ -2317,11 +2317,17 @@ prev_insert_id(ulonglong nr, struct syst
int handler::update_auto_increment()
{
- ulonglong nr, nb_reserved_values;
+ ulonglong nr, nb_reserved_values, nr_old;
bool append= FALSE;
THD *thd= table->in_use;
struct system_variables *variables= &thd->variables;
DBUG_ENTER("handler::update_auto_increment");
+
+ /*
+ Perform an overflow check on next_insert_id.
+ */
+ if ((insert_id_for_cur_row) && (insert_id_for_cur_row > next_insert_id))
+ DBUG_RETURN(HA_ERR_AUTOINC_READ_FAILED);
/*
next_insert_id is a "cursor" into the reserved interval, it may go greater
@@ -2341,6 +2347,12 @@ int handler::update_auto_increment()
*/
adjust_next_insert_id_after_explicit_value(nr);
insert_id_for_cur_row= 0; // didn't generate anything
+
+ /*
+ Check for the overflow of next_insert_id.
+ */
+ if (next_insert_id < nr)
+ auto_inc_interval_for_cur_row.replace(0, 0, 0);
DBUG_RETURN(0);
}
@@ -2396,6 +2408,12 @@ int handler::update_auto_increment()
DBUG_RETURN(HA_ERR_AUTOINC_READ_FAILED); // Mark failure
/*
+ nr_old will be used to store nr's value, which inturn will be
+ compared with nr's value returned from compute_next_insert_id().
+ */
+ nr_old= nr;
+
+ /*
That rounding below should not be needed when all engines actually
respect offset and increment in get_auto_increment(). But they don't
so we still do it. Wonder if for the not-first-in-index we should do
@@ -2404,6 +2422,9 @@ int handler::update_auto_increment()
will not help as we inserted no row).
*/
nr= compute_next_insert_id(nr-1, variables);
+
+ if (nr < nr_old)
+ DBUG_RETURN(HA_ERR_AUTOINC_READ_FAILED);
}
if (table->s->next_number_keypart == 0)
Attachment: [text/bzr-bundle]
