#At file:///Users/mattiasj/clones/bzrroot/b46866-51-bugteam/ based on revid:li-bing.song@stripped
3116 Mattias Jonsson 2009-09-08
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Archive does not support decreasing AUTO_INCREMENT,
since it does not support indexes (the PRIMARY KEY on
AUTO_INCREMENT is enforced by only allowing higher values than
the current max.)
Bug was that negative AUTO_INCREMENT was converted to unsigned
and become very large positive values instead.
Solution was to treat all negative AUTO_INCREMENT values as
(unsigned) 0. This means that one cannot insert negative values
into an AUTO_INCREMENT column in a Archive table.
@ mysql-test/suite/parts/inc/partition_auto_increment.inc
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Added verification tests for negative values on non partitioned engines
and fixed the test to be used by both Archive and Blackhole.
@ mysql-test/suite/parts/r/partition_auto_increment_archive.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/r/partition_auto_increment_memory.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/r/partition_auto_increment_ndb.result
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test result.
@ mysql-test/suite/parts/t/partition_auto_increment_archive.test
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test case to run test with negative auto_increments.
@ mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Updated test case to run test with negative auto_increments.
@ storage/archive/ha_archive.cc
Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
Do not allow negative auto_increment (treat them as 0, which is
never greater than the current auto_increment value.)
modified:
mysql-test/suite/parts/inc/partition_auto_increment.inc
mysql-test/suite/parts/r/partition_auto_increment_archive.result
mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
mysql-test/suite/parts/r/partition_auto_increment_innodb.result
mysql-test/suite/parts/r/partition_auto_increment_memory.result
mysql-test/suite/parts/r/partition_auto_increment_myisam.result
mysql-test/suite/parts/r/partition_auto_increment_ndb.result
mysql-test/suite/parts/t/partition_auto_increment_archive.test
mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
storage/archive/ha_archive.cc
=== modified file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2009-09-08 08:23:00 +0000
@@ -49,6 +49,7 @@ if (!$skip_update)
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+ UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
@@ -75,6 +76,14 @@ if (!$skip_delete)
DELETE FROM t1;
}
INSERT INTO t1 VALUES (NULL);
+--error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (-1);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
SELECT * FROM t1 ORDER BY c1;
if (!$skip_truncate)
@@ -637,7 +646,12 @@ eval CREATE TABLE t (c1 INT NOT NULL AUT
INSERT INTO t(c2) VALUES (10);
INSERT INTO t(c2) VALUES (20);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-1,-10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (30);
INSERT INTO t(c2) VALUES (40);
@@ -652,7 +666,12 @@ DROP TABLE t;
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-2,-20);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (30);
SELECT * FROM t ORDER BY c1 ASC;
@@ -665,7 +684,12 @@ DROP TABLE t;
eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2;
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-4,-20);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (30);
INSERT INTO t(c2) VALUES (40);
@@ -680,7 +704,12 @@ eval CREATE TABLE t (c1 INT NOT NULL AUT
INSERT INTO t(c2) VALUES (10);
INSERT INTO t(c2) VALUES (20);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-1,-10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
SELECT * FROM t ORDER BY c1 ASC;
@@ -697,9 +726,19 @@ eval CREATE TABLE t (c1 INT NOT NULL AUT
INSERT INTO t(c2) VALUES (10);
INSERT INTO t(c2) VALUES (20);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-1,-10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (30);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-3,-20);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (40);
SELECT * FROM t ORDER BY c1 ASC;
@@ -724,10 +763,20 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (126,30);
INSERT INTO t VALUES (127,40);
---error ER_DUP_ENTRY
+--error 0, ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (128,50);
---error ER_DUP_ENTRY
+if (!$mysql_errno)
+{
+ echo # ERROR (Only OK if Blackhole);
+ echo # expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: $mysql_errno;
+}
+--error 0, ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (129,60);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole);
+ echo # expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: $mysql_errno;
+}
SELECT * FROM t ORDER BY c1 ASC;
@@ -741,13 +790,33 @@ eval CREATE TABLE t (c1 TINYINT NOT NULL
INSERT INTO t(c2) VALUES (10);
INSERT INTO t(c2) VALUES (20);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-127,30);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-128,40);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
---error ER_DUP_ENTRY
+--error 0, ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (-129,50);
---error ER_DUP_ENTRY
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole)
+ echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: $mysql_errno;
+}
+--error 0, ER_DUP_ENTRY, ER_DUP_KEY
INSERT INTO t VALUES (-130,60);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole)
+ echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: $mysql_errno;
+}
SELECT * FROM t ORDER BY c1 ASC;
@@ -760,7 +829,12 @@ eval CREATE TABLE t (c1 INT NOT NULL AUT
INSERT INTO t(c2) VALUES (10);
INSERT INTO t(c2) VALUES (20);
+--error 0, ER_DUP_KEY
INSERT INTO t VALUES (-1,-10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
INSERT INTO t(c2) VALUES (30);
SELECT * FROM t ORDER BY c1 ASC;
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_archive.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_archive.result 2008-11-05 20:13:54 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_archive.result 2009-09-08 08:23:00 +0000
@@ -81,30 +81,38 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`c1`)
) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+) ENGINE=ARCHIVE AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
4
5
6
+7
+8
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+) ENGINE=ARCHIVE AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
4
5
6
7
+8
+9
INSERT INTO t1 VALUES (100);
INSERT INTO t1 VALUES (NULL);
OPTIMIZE TABLE t1;
@@ -805,3 +813,179 @@ c1
4
5
DROP TABLE t1;
+#############################################################################
+# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
+# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
+##############################################################################
+# Inserting negative autoincrement values into a partition table (partitions >= 4)
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+4 40
+DROP TABLE t;
+# Reading from a partition table (partitions >= 2 ) after inserting a negative
+# value into the auto increment column
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 30
+DROP TABLE t;
+# Inserting negative auto increment value into a partition table (partitions >= 2)
+# auto increment value > 2.
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-4,-20);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 30
+2 40
+DROP TABLE t;
+# Inserting -1 into autoincrement column of a partition table (partition >= 4)
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+# ERROR (only OK if Archive), mysql_errno: 1022
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+DROP TABLE t;
+# Deleting from an auto increment table after inserting negative values
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t VALUES (-3,-20);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+4 40
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+4 40
+DROP TABLE t;
+# Inserting a positive value that exceeds maximum allowed value for an
+# Auto Increment column (positive maximum)
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (126,30);
+INSERT INTO t VALUES (127,40);
+INSERT INTO t VALUES (128,50);
+INSERT INTO t VALUES (129,60);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+DROP TABLE t;
+# Inserting a negative value that goes below minimum allowed value for an
+# Auto Increment column (negative minimum)
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-127,30);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t VALUES (-128,40);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t VALUES (-129,50);
+INSERT INTO t VALUES (-130,60);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+DROP TABLE t;
+# Updating the partition table with a negative Auto Increment value
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+# ERROR (only OK if Archive), mysql_errno: 1022
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+3 30
+4 40
+5 50
+DROP TABLE t;
+# Updating the partition table with a value that crosses the upper limits
+# on both the positive and the negative side.
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Archive' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (126,30);
+INSERT INTO t VALUES (127,40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2009-02-18 21:35:28 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2009-09-08 08:23:00 +0000
@@ -39,6 +39,7 @@ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
@@ -84,6 +85,9 @@ t1 CREATE TABLE `t1` (
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
@@ -647,3 +651,142 @@ PARTITIONS 2 */
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
+#############################################################################
+# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
+# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
+##############################################################################
+# Inserting negative autoincrement values into a partition table (partitions >= 4)
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Reading from a partition table (partitions >= 2 ) after inserting a negative
+# value into the auto increment column
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Inserting negative auto increment value into a partition table (partitions >= 2)
+# auto increment value > 2.
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-4,-20);
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Inserting -1 into autoincrement column of a partition table (partition >= 4)
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Deleting from an auto increment table after inserting negative values
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+INSERT INTO t(c2) VALUES (30);
+INSERT INTO t VALUES (-3,-20);
+INSERT INTO t(c2) VALUES (40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Inserting a positive value that exceeds maximum allowed value for an
+# Auto Increment column (positive maximum)
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (126,30);
+INSERT INTO t VALUES (127,40);
+INSERT INTO t VALUES (128,50);
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+# ERROR (Only OK if Blackhole)
+# expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: 0
+INSERT INTO t VALUES (129,60);
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+# ERROR (only OK if Blackhole)
+# expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: 0
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Inserting a negative value that goes below minimum allowed value for an
+# Auto Increment column (negative minimum)
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-127,30);
+INSERT INTO t VALUES (-128,40);
+INSERT INTO t VALUES (-129,50);
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+# ERROR (only OK if Blackhole)
+echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: 0
+INSERT INTO t VALUES (-130,60);
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+# ERROR (only OK if Blackhole)
+echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: 0
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Updating the partition table with a negative Auto Increment value
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (-1,-10);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+# Updating the partition table with a value that crosses the upper limits
+# on both the positive and the negative side.
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4;
+INSERT INTO t(c2) VALUES (10);
+INSERT INTO t(c2) VALUES (20);
+INSERT INTO t VALUES (126,30);
+INSERT INTO t VALUES (127,40);
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2009-09-08 08:23:00 +0000
@@ -37,6 +37,7 @@ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
@@ -48,6 +49,7 @@ INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
+-1
0
2
5
@@ -60,7 +62,6 @@ c1
30
31
32
-40
51
DROP TABLE t1;
CREATE TABLE t1 (
@@ -98,15 +99,21 @@ t1 CREATE TABLE `t1` (
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
+-1
6
+7
+8
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
@@ -921,9 +928,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (126,30);
INSERT INTO t VALUES (127,40);
INSERT INTO t VALUES (128,50);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
INSERT INTO t VALUES (129,60);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
1 10
@@ -940,9 +945,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (-127,30);
INSERT INTO t VALUES (-128,40);
INSERT INTO t VALUES (-129,50);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
INSERT INTO t VALUES (-130,60);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
-128 40
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-09-08 08:23:00 +0000
@@ -37,6 +37,7 @@ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
@@ -48,6 +49,7 @@ INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
+-1
0
2
5
@@ -58,7 +60,6 @@ c1
22
23
30
-40
51
52
53
@@ -98,15 +99,21 @@ t1 CREATE TABLE `t1` (
) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+) ENGINE=MEMORY AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
+-1
6
+7
+8
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
@@ -947,9 +954,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (126,30);
INSERT INTO t VALUES (127,40);
INSERT INTO t VALUES (128,50);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
INSERT INTO t VALUES (129,60);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
1 10
@@ -966,9 +971,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (-127,30);
INSERT INTO t VALUES (-128,40);
INSERT INTO t VALUES (-129,50);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
INSERT INTO t VALUES (-130,60);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
-128 40
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-09-08 08:23:00 +0000
@@ -37,6 +37,7 @@ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
@@ -48,6 +49,7 @@ INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
+-1
0
2
5
@@ -58,7 +60,6 @@ c1
22
23
30
-40
51
52
53
@@ -98,15 +99,21 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
+-1
6
+7
+8
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
@@ -966,9 +973,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (126,30);
INSERT INTO t VALUES (127,40);
INSERT INTO t VALUES (128,50);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
INSERT INTO t VALUES (129,60);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
1 10
@@ -985,9 +990,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (-127,30);
INSERT INTO t VALUES (-128,40);
INSERT INTO t VALUES (-129,50);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
INSERT INTO t VALUES (-130,60);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
-128 40
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2009-09-08 08:23:00 +0000
@@ -38,6 +38,7 @@ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+UPDATE t1 SET c1 = -1 WHERE c1 = 40;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
@@ -49,6 +50,7 @@ INSERT INTO t1 VALUES (NULL);
INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
+-1
0
2
5
@@ -59,7 +61,6 @@ c1
22
23
30
-40
51
52
53
@@ -99,6 +100,9 @@ t1 CREATE TABLE `t1` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
DELETE FROM t1;
INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (-1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
@@ -107,7 +111,10 @@ t1 CREATE TABLE `t1` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
SELECT * FROM t1 ORDER BY c1;
c1
+-1
6
+7
+8
TRUNCATE TABLE t1;
INSERT INTO t1 VALUES (NULL);
SHOW CREATE TABLE t1;
@@ -942,9 +949,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (126,30);
INSERT INTO t VALUES (127,40);
INSERT INTO t VALUES (128,50);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
INSERT INTO t VALUES (129,60);
-ERROR 23000: Duplicate entry '127' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
1 10
@@ -961,9 +966,7 @@ INSERT INTO t(c2) VALUES (20);
INSERT INTO t VALUES (-127,30);
INSERT INTO t VALUES (-128,40);
INSERT INTO t VALUES (-129,50);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
INSERT INTO t VALUES (-130,60);
-ERROR 23000: Duplicate entry '-128' for key 'PRIMARY'
SELECT * FROM t ORDER BY c1 ASC;
c1 c2
-128 40
=== modified file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2009-09-08 08:23:00 +0000
@@ -30,9 +30,6 @@ let $skip_delete= 1;
let $skip_truncate= 1;
let $skip_update= 1;
let $only_ai_pk= 1;
-# Bug#45823 Assertion failure in file row/row0mysql.c line 1386
-# Archive does not handle negative autoincrement values correctly
-let $skip_negative_auto_inc= 1;
##### Storage engine to be tested
let $engine= 'Archive';
=== modified file 'mysql-test/suite/parts/t/partition_auto_increment_blackhole.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 2009-09-04 03:57:11 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 2009-09-08 08:23:00 +0000
@@ -25,9 +25,6 @@
#------------------------------------------------------------------------------#
# Engine specific settings and requirements
--source include/have_blackhole.inc
-# Bug#45823 Assertion failure in file row/row0mysql.c line 1386
-# Blackhole does not handle negative autoincrement values correctly
-let $skip_negative_auto_inc= 1;
##### Storage engine to be tested
let $engine= 'Blackhole';
=== modified file 'storage/archive/ha_archive.cc'
--- a/storage/archive/ha_archive.cc 2009-05-15 13:03:22 +0000
+++ b/storage/archive/ha_archive.cc 2009-09-08 08:23:00 +0000
@@ -810,7 +810,9 @@ int ha_archive::write_row(uchar *buf)
{
KEY *mkey= &table->s->key_info[0]; // We only support one key right now
update_auto_increment();
- temp_auto= table->next_number_field->val_int();
+ temp_auto= (((Field_num*) table->next_number_field)->unsigned_flag ||
+ table->next_number_field->val_int() > 0 ?
+ table->next_number_field->val_int() : 0);
/*
We don't support decremening auto_increment. They make the performance
Attachment: [text/bzr-bundle]
