Hi Mattiasj,
Thank you for the patch,
OK to push.
Pls find a few request below (These do not require
any additional review)
General Request
----------------
This behaviour is different from that of other storage engines.
Other engines allow a negative value to be inserted. Can I request
you to please ensure that this is documented?
modified file 'storage/archive/ha_archive.cc'
----------------------------------------------
Can you please explain the change here by saying
something like
"Archive engine 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/r/partition_auto_increment_archive.result
----------------------------------------------------------------
Since you are printing a comment here for the error that occurs
in the archive engine
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive), mysql_errno: $mysql_errno;
+}
Can you add a echo line here that references this behaviour to
Bug#46866 AUTO_INCREMENT errors with partitioned Archive tables.
So that it would imply to the something like - This is expected
behaviour for the Archive engine and you can read more in this bug.
You would not have to redo much of the patch for this, you would just
need to change the .inc file and the result file for the archive engine.
Narayanan
Mattias Jonsson wrote:
> #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
>
>
> ------------------------------------------------------------------------
>
>
