#At file:///home/narayanan/Work/mysql_checkouts/shared_repository_directory/mysql-5.1-bugteam-45823-02/ based on revid:joro@stripped
3074 V Narayanan 2009-08-20
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Inserting a negative value in the autoincrement column of a
partitioned innodb table was causing the value of the auto
increment column to wrap around into a very large positive
value. This was causing unexpected behaviour.
The current patch ensures that before calculating the next
auto increment value, the current value is within the positive
maximum allowed limit.
@ mysql-test/suite/parts/inc/partition_auto_increment.inc
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds tests for performing insert,update and delete on a partition
table with negative auto_increment values.
@ mysql-test/suite/parts/r/partition_auto_increment_archive.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the archive engine.
@ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the blackhole engine.
@ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the innodb engine.
@ mysql-test/suite/parts/r/partition_auto_increment_memory.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the memory engine.
@ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the myisam engine.
@ mysql-test/suite/parts/r/partition_auto_increment_ndb.result
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Result file for the ndb engine.
@ sql/field.h
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds a function that returns the upper limits of the mysql
integral and floating point types.
@ sql/ha_partition.cc
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Ensures that the current value is lesser than the upper limit
for the type of the field before setting the next auto increment
value to be calculated.
@ sql/ha_partition.h
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Modifies the set_auto_increment_if_higher function to
accept the upper limit of the auto_increment column
and uses it to ensure that the auto_increment value is
within limits before incrementing it.
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
sql/field.h
sql/ha_partition.cc
sql/ha_partition.h
=== modified file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 2009-02-18 21:35:28 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2009-08-19 19:58:29 +0000
@@ -623,3 +623,189 @@ SHOW CREATE TABLE t1;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
+--echo #############################################################################
+--echo # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386
+--echo # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31
+--echo ##############################################################################
+
+--echo # Inserting negative autoincrement values into a partition table (partitions >= 4)
+
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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;
+
+DROP TABLE t;
+
+--echo # Reading from a partition table (partitions >= 2 ) after inserting a negative
+--echo # value into the auto increment column
+
+
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) PARTITION BY HASH(c1) PARTITIONS 2;
+
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Inserting negative auto increment value into a partition table (partitions >= 2)
+--echo # auto increment value > 2.
+
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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;
+
+DROP TABLE t;
+
+--echo # 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) 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;
+
+INSERT INTO t(c2) VALUES (30);
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Deleting from an auto increment table after inserting negative values
+
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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;
+
+DELETE FROM t WHERE c1 > 1;
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Inserting a positive value that exceeds maximum allowed value for an
+--echo # Auto Increment column (positive maximum)
+
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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);
+
+--echo # disable warnings that indicate that value greater than limit is being inserted
+--disable_warnings
+--error 1062
+INSERT INTO t VALUES (128,50);
+--error 1062
+INSERT INTO t VALUES (129,60);
+--enable_warnings
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Inserting a negative value that goes below minimum allowed value for an
+--echo # Auto Increment column (negative minimum)
+
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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);
+
+--echo # disable warnings that indicate that value greater than limit is being inserted
+--disable_warnings
+--error 1062
+INSERT INTO t VALUES (-129,50);
+--error 1062
+INSERT INTO t VALUES (-130,60);
+--enable_warnings
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Updating the partition table with a negative Auto Increment value
+
+CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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;
+
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+
+SELECT * FROM t;
+
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo # Updating the partition table with a value that crosses the upper limits
+--echo # on both the positive and the negative side.
+
+CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) 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;
+
+--echo # disable warnings that indicate that value greater than limit is being inserted
+--disable_warnings
+UPDATE t SET c1 = 130 where c1 = 127;
+--enable_warnings
+
+SELECT * FROM t;
+
+--echo # disable warnings that indicate that value greater than limit is being inserted
+--disable_warnings
+UPDATE t SET c1 = -140 where c1 = 126;
+--enable_warnings
+
+SELECT * FROM t;
+
+DROP TABLE t;
+
+--echo ##############################################################################
=== 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-08-19 19:58:29 +0000
@@ -805,3 +805,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+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-08-19 19:58:29 +0000
@@ -647,3 +647,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+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-02-05 17:47:24 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2009-08-19 19:58:29 +0000
@@ -825,3 +825,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-07-08 12:11:34 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2009-08-19 19:58:29 +0000
@@ -851,3 +851,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-07-08 12:11:34 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2009-08-19 19:58:29 +0000
@@ -870,3 +870,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2009-02-18 21:35:28 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2009-08-19 19:58:29 +0000
@@ -846,3 +846,194 @@ 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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+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) PARTITION BY HASH(c1) PARTITIONS 2;
+INSERT INTO t VALUES (-2,-20);
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+-2 -20
+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) 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;
+c1 c2
+-4 -20
+2 40
+1 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t;
+c1 c2
+1 10
+-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) 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;
+c1 c2
+4 40
+1 10
+-1 -10
+2 20
+3 30
+-3 -20
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-3 -20
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+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) 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);
+# disable warnings that indicate that value greater than limit is being inserted
+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;
+c1 c2
+-128 40
+1 10
+2 20
+-127 30
+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) 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;
+c1 c2
+1 10
+-1 -10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+1 10
+-1 -10
+-6 20
+3 30
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t;
+c1 c2
+4 40
+1 10
+-1 -10
+5 50
+-6 20
+3 30
+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) 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;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = 130 where c1 = 127;
+SELECT * FROM t;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+# disable warnings that indicate that value greater than limit is being inserted
+UPDATE t SET c1 = -140 where c1 = 126;
+SELECT * FROM t;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'sql/field.h'
--- a/sql/field.h 2009-06-09 16:44:26 +0000
+++ b/sql/field.h 2009-08-19 19:58:29 +0000
@@ -53,6 +53,68 @@ public:
static void *operator new(size_t size) throw ()
{ return sql_alloc(size); }
static void operator delete(void *ptr_arg, size_t size) { TRASH(ptr_arg, size); }
+ /**
+ Return the upper limit of the mysql integral and floating point
+ type.
+
+ @param[in] The Field object whose maximum value needs to be returned.
+
+ @return upper limit of Field object type.
+ */
+ static ulonglong get_int_col_max_value(const Field* field)
+ {
+ ulonglong max_value= 0;
+
+ switch(field->key_type()) {
+ case HA_KEYTYPE_BINARY:
+ max_value= UINT_MAX8;
+ break;
+ case HA_KEYTYPE_INT8:
+ max_value= INT_MAX8;
+ break;
+ case HA_KEYTYPE_USHORT_INT:
+ max_value= UINT_MAX16;
+ break;
+ case HA_KEYTYPE_SHORT_INT:
+ max_value= INT_MAX16;
+ break;
+ case HA_KEYTYPE_UINT24:
+ max_value= UINT_MAX24;
+ break;
+ case HA_KEYTYPE_INT24:
+ max_value= INT_MAX24;
+ break;
+ case HA_KEYTYPE_ULONG_INT:
+ max_value= UINT_MAX32;
+ break;
+ case HA_KEYTYPE_LONG_INT:
+ max_value= INT_MAX32;
+ break;
+ case HA_KEYTYPE_ULONGLONG:
+ max_value= ULONGLONG_MAX;
+ break;
+ case HA_KEYTYPE_LONGLONG:
+ max_value= LONGLONG_MAX;
+ break;
+ /*
+ The auto increment value is computed using a ulonglong
+ datatype in the server. The upper limits of float and
+ double defined in my_global.h exceed the range of a
+ ulonglong and the auto increment value will overflow
+ before this limit is hit. Hence we use the upper limit
+ of ulonglong for both float and double.
+ */
+ case HA_KEYTYPE_FLOAT:
+ max_value= ULONGLONG_MAX;
+ break;
+ case HA_KEYTYPE_DOUBLE:
+ max_value= ULONGLONG_MAX;
+ break;
+ default:
+ max_value= 0;
+ }
+ return(max_value);
+}
uchar *ptr; // Position to field in record
uchar *null_ptr; // Byte where null_bit is
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2009-08-06 11:31:26 +0000
+++ b/sql/ha_partition.cc 2009-08-19 19:58:29 +0000
@@ -3024,7 +3024,8 @@ int ha_partition::write_row(uchar * buf)
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
error= m_file[part_id]->ha_write_row(buf);
if (have_auto_increment && !table->s->next_number_keypart)
- set_auto_increment_if_higher(table->next_number_field->val_int());
+ set_auto_increment_if_higher(table->next_number_field->val_int(),
+ Field::get_int_col_max_value(table->next_number_field));
reenable_binlog(thd);
exit:
table->timestamp_field_type= orig_timestamp_type;
@@ -3128,7 +3129,8 @@ exit:
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
if (!ha_data->auto_inc_initialized)
info(HA_STATUS_AUTO);
- set_auto_increment_if_higher(table->found_next_number_field->val_int());
+ set_auto_increment_if_higher(table->found_next_number_field->val_int(),
+ Field::get_int_col_max_value(table->found_next_number_field));
}
table->timestamp_field_type= orig_timestamp_type;
DBUG_RETURN(error);
=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h 2009-01-05 16:10:20 +0000
+++ b/sql/ha_partition.h 2009-08-19 19:58:29 +0000
@@ -936,13 +936,14 @@ private:
auto_increment_lock= FALSE;
}
}
- virtual void set_auto_increment_if_higher(const ulonglong nr)
+ virtual void set_auto_increment_if_higher(const ulonglong nr,
+ const ulonglong col_max_value)
{
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
lock_auto_increment();
DBUG_ASSERT(ha_data->auto_inc_initialized == TRUE);
/* must check when the mutex is taken */
- if (nr >= ha_data->next_auto_inc_val)
+ if (nr >= ha_data->next_auto_inc_val && nr < col_max_value)
ha_data->next_auto_inc_val= nr + 1;
unlock_auto_increment();
}
Attachment: [text/bzr-bundle] bzr/v.narayanan@sun.com-20090819195829-ltj8an2vw3n1trrd.bundle
