#At file:///home/narayanan/Work/mysql_checkouts/shared_repository_directory/mysql-5.1-bugteam-45823-04/ based on revid:anurag.shekhar@stripped
3094 V Narayanan 2009-09-01
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 counter to wrap around into a very large positive
value. The consequences are the same as if a very large positive
value was inserted into a column, e.g. reduced autoincrement
range, failure to read autoincrement counter.
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_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.
@ mysql-test/suite/parts/t/partition_auto_increment_archive.test
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds a variable that allows the Archive engine to skip tests
that involve insertion of negative auto increment values.
@ mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
Bug#45823 Assertion failure in file row/row0mysql.c line 1386
Adds a variable that allows the Blackhole engine to skip tests
that involve insertion of negative auto increment values.
@ 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 take
into account negative auto increment values when doing a
comparison.
modified:
mysql-test/suite/parts/inc/partition_auto_increment.inc
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
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-09-01 12:02:21 +0000
@@ -623,3 +623,195 @@ SHOW CREATE TABLE t1;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
+if (!$skip_negative_auto_inc)
+{
+--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)
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+DROP TABLE t;
+
+--echo # Reading from a partition table (partitions >= 2 ) after inserting a negative
+--echo # value into the auto increment column
+
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+DROP TABLE t;
+
+--echo # Inserting negative auto increment value into a partition table (partitions >= 2)
+--echo # auto increment value > 2.
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+DROP TABLE t;
+
+--echo # Inserting -1 into autoincrement column of a partition table (partition >= 4)
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+INSERT INTO t(c2) VALUES (30);
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+DROP TABLE t;
+
+--echo # Deleting from an auto increment table after inserting negative values
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+if (!$skip_delete)
+{
+DELETE FROM t WHERE c1 > 1;
+}
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+DROP TABLE t;
+
+--echo # Inserting a positive value that exceeds maximum allowed value for an
+--echo # Auto Increment column (positive maximum)
+
+eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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);
+
+--error ER_DUP_ENTRY
+INSERT INTO t VALUES (128,50);
+--error ER_DUP_ENTRY
+INSERT INTO t VALUES (129,60);
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+DROP TABLE t;
+
+--echo # Inserting a negative value that goes below minimum allowed value for an
+--echo # Auto Increment column (negative minimum)
+
+eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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);
+
+--error ER_DUP_ENTRY
+INSERT INTO t VALUES (-129,50);
+--error ER_DUP_ENTRY
+INSERT INTO t VALUES (-130,60);
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+DROP TABLE t;
+
+--echo # Updating the partition table with a negative Auto Increment value
+
+eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+if (!$skip_update)
+{
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+}
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+INSERT INTO t(c2) VALUES (40);
+INSERT INTO t(c2) VALUES (50);
+
+if (!$skip_update)
+{
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+}
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+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.
+
+eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
+ c2 INT) ENGINE=$engine 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;
+
+if (!$skip_update)
+{
+UPDATE t SET c1 = 130 where c1 = 127;
+}
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+if (!$skip_update)
+{
+UPDATE t SET c1 = -140 where c1 = 126;
+}
+
+SELECT * FROM t ORDER BY c1 ASC;
+
+DROP TABLE t;
+
+--echo ##############################################################################
+}
=== 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-09-01 12:02:21 +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) ENGINE='InnoDB' 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
+-1 -10
+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='InnoDB' 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
+-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) ENGINE='InnoDB' 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
+-4 -20
+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='InnoDB' 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
+-1 -10
+1 10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+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) ENGINE='InnoDB' 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
+-3 -20
+-1 -10
+1 10
+2 20
+3 30
+4 40
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-3 -20
+-1 -10
+1 10
+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='InnoDB' 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);
+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
+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='InnoDB' 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);
+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
+-127 30
+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='InnoDB' 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
+-1 -10
+1 10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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 ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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='InnoDB' 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
+UPDATE t SET c1 = 130 where c1 = 127;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+UPDATE t SET c1 = -140 where c1 = 126;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+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-09-01 12:02:21 +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) ENGINE='Memory' 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
+-1 -10
+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='Memory' 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
+-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) ENGINE='Memory' 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
+-4 -20
+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='Memory' 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
+-1 -10
+1 10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+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) ENGINE='Memory' 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
+-3 -20
+-1 -10
+1 10
+2 20
+3 30
+4 40
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-3 -20
+-1 -10
+1 10
+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='Memory' 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);
+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
+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='Memory' 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);
+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
+-127 30
+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='Memory' 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
+-1 -10
+1 10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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 ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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='Memory' 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
+UPDATE t SET c1 = 130 where c1 = 127;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+UPDATE t SET c1 = -140 where c1 = 126;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+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-09-01 12:02:21 +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) ENGINE='MyISAM' 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
+-1 -10
+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='MyISAM' 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
+-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) ENGINE='MyISAM' 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
+-4 -20
+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='MyISAM' 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
+-1 -10
+1 10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+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) ENGINE='MyISAM' 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
+-3 -20
+-1 -10
+1 10
+2 20
+3 30
+4 40
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-3 -20
+-1 -10
+1 10
+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='MyISAM' 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);
+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
+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='MyISAM' 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);
+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
+-127 30
+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='MyISAM' 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
+-1 -10
+1 10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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 ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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='MyISAM' 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
+UPDATE t SET c1 = 130 where c1 = 127;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+UPDATE t SET c1 = -140 where c1 = 126;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+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-09-01 12:02:21 +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) ENGINE='NDB' 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
+-1 -10
+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='NDB' 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
+-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) ENGINE='NDB' 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
+-4 -20
+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='NDB' 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
+-1 -10
+1 10
+2 20
+INSERT INTO t(c2) VALUES (30);
+SELECT * FROM t ORDER BY c1 ASC;
+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) ENGINE='NDB' 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
+-3 -20
+-1 -10
+1 10
+2 20
+3 30
+4 40
+DELETE FROM t WHERE c1 > 1;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-3 -20
+-1 -10
+1 10
+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='NDB' 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);
+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
+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='NDB' 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);
+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
+-127 30
+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='NDB' 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
+-1 -10
+1 10
+2 20
+3 30
+UPDATE t SET c1 = -6 WHERE c1 = 2;
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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 ORDER BY c1 ASC;
+c1 c2
+-6 20
+-1 -10
+1 10
+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='NDB' 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
+UPDATE t SET c1 = 130 where c1 = 127;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+1 10
+2 20
+126 30
+127 40
+UPDATE t SET c1 = -140 where c1 = 126;
+Warnings:
+Warning 1264 Out of range value for column 'c1' at row 1
+SELECT * FROM t ORDER BY c1 ASC;
+c1 c2
+-128 30
+1 10
+2 20
+127 40
+DROP TABLE t;
+##############################################################################
=== modified file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2008-09-08 13:30:01 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2009-09-01 12:02:21 +0000
@@ -30,6 +30,9 @@ 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 2008-09-08 13:30:01 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 2009-09-01 12:02:21 +0000
@@ -25,6 +25,9 @@
#------------------------------------------------------------------------------#
# 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 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2009-08-21 15:38:29 +0000
+++ b/sql/ha_partition.cc 2009-09-01 12:02:21 +0000
@@ -3024,7 +3024,7 @@ 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);
reenable_binlog(thd);
exit:
table->timestamp_field_type= orig_timestamp_type;
@@ -3128,7 +3128,7 @@ 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);
}
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-09-01 12:02:21 +0000
@@ -936,9 +936,11 @@ private:
auto_increment_lock= FALSE;
}
}
- virtual void set_auto_increment_if_higher(const ulonglong nr)
+ virtual void set_auto_increment_if_higher(Field *field)
{
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ ulonglong nr= (((Field_num *)field)->unsigned_flag ||
+ field->val_int() > 0) ? field->val_int() : 0;
lock_auto_increment();
DBUG_ASSERT(ha_data->auto_inc_initialized == TRUE);
/* must check when the mutex is taken */
Attachment: [text/bzr-bundle] bzr/v.narayanan@sun.com-20090901120221-k9jffiuedbb8pwq8.bundle
