List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:September 8 2009 8:23am
Subject:bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3116)
Bug#46866
View as plain text  
#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]
Thread
bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3116)Bug#46866Mattias Jonsson8 Sep
  • Re: bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3116)Bug#46866V Narayanan9 Sep
    • Re: bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3116)Bug#46866Mattias Jonsson9 Sep
      • Re: bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3116)Bug#46866V Narayanan10 Sep