#At file:///Users/mattiasj/clones/bzrroot/b50392-51-bugteam/ based on revid:joro@stripped
3369 Mattias Jonsson 2010-03-04
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
The bug was that when INSERT_ID was used and the storage
engine was told to release any reserved but not used
auto_increment values, it set the highest auto_increment
value to INSERT_ID.
The fix was to check if the auto_increment value was forced
by user (INSERT_ID) or by slave-thread, i.e. not auto-
generated. So that it is only allowed to release generated
values.
@ mysql-test/r/partition_error.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
updated result
@ mysql-test/suite/parts/inc/partition_auto_increment.inc
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added test
@ mysql-test/suite/parts/r/partition_auto_increment_archive.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that archive does only allow increasing
auto_increment values
@ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that blackhole accepts all inserts :)
@ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that innodb rolls back inserts on error,
but keeps the auto_increment value.
@ mysql-test/suite/parts/r/partition_auto_increment_memory.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that memory and myisam inserts all rows
before the error.
@ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that memory and myisam inserts all rows
before the error.
@ mysql-test/suite/parts/r/partition_auto_increment_ndb.result
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added result, note that NDB does not seem to handle
INSERT_ID as other engines. (Martin will look into it).
@ mysql-test/t/partition_error.test
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
Added test
@ sql/ha_partition.cc
Bug#50392: insert_id is not reset for partitioned tables
auto_increment on duplicate entry
If the next_insert_id comes from non generated (i.e
forced by INSERT_ID or slave-thread) then we cannot
lower the reserved auto_increment value, since it have
not reserved any values.
modified:
mysql-test/r/partition_error.result
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/t/partition_error.test
sql/ha_partition.cc
=== modified file 'mysql-test/r/partition_error.result'
--- a/mysql-test/r/partition_error.result 2009-12-13 20:29:50 +0000
+++ b/mysql-test/r/partition_error.result 2010-03-04 17:16:10 +0000
@@ -1,4 +1,46 @@
drop table if exists t1;
+#
+# Bug#50392: insert_id is not reset for partitioned tables
+# auto_increment on duplicate entry
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+a
+12
+13
+14
+DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`a`)
+) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
+/*!50100 PARTITION BY KEY (a) */
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+a
+12
+13
+14
+DROP TABLE t1;
CREATE TABLE t1 (a INTEGER NOT NULL, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1),(1);
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
=== 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 2010-03-04 17:16:10 +0000
@@ -42,6 +42,15 @@ if ($mysql_errno)
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+ echo # mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
if (!$skip_update)
{
# InnoDB Does not handle this correctly, see bug#14793, bug#21641
@@ -601,6 +610,15 @@ SET INSERT_ID = 23;
SHOW CREATE TABLE t1;
INSERT INTO t1 (c1) VALUES (NULL);
SHOW CREATE TABLE t1;
+SET INSERT_ID = 22;
+-- error 0, ER_DUP_ENTRY, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+ echo # mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
=== 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 2010-03-04 17:16:10 +0000
@@ -34,6 +34,9 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
2
@@ -46,6 +49,7 @@ c1
21
22
30
+31
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -751,10 +755,15 @@ t1 CREATE TABLE `t1` (
) ENGINE=ARCHIVE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
+22
23
+24
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
=== 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 2010-03-04 17:16:10 +0000
@@ -35,6 +35,11 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+# mysql_errno: 0
+INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
@@ -597,6 +602,11 @@ t1 CREATE TABLE `t1` (
) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+# mysql_errno: 0
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
DROP TABLE t1;
=== 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 2010-03-04 17:16:10 +0000
@@ -33,6 +33,9 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
@@ -40,7 +43,7 @@ UPDATE t1 SET c1 = 40 WHERE c1 = 50;
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='t1';
AUTO_INCREMENT
-31
+32
UPDATE t1 SET c1 = NULL WHERE c1 = 4;
Warnings:
Warning 1048 Column 'c1' cannot be null
@@ -60,6 +63,7 @@ c1
30
31
32
+33
40
51
DROP TABLE t1;
@@ -771,10 +775,14 @@ t1 CREATE TABLE `t1` (
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
23
+24
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
=== 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 2010-03-04 17:16:10 +0000
@@ -33,6 +33,9 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
@@ -57,7 +60,9 @@ c1
21
22
23
+29
30
+31
40
51
52
@@ -797,10 +802,15 @@ t1 CREATE TABLE `t1` (
) ENGINE=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
+22
23
+24
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
=== 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 2010-03-04 17:16:10 +0000
@@ -33,6 +33,9 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
@@ -57,7 +60,9 @@ c1
21
22
23
+29
30
+31
40
51
52
@@ -816,10 +821,15 @@ t1 CREATE TABLE `t1` (
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
+22
23
+24
DROP TABLE t1;
# Testing with FLUSH TABLE
CREATE TABLE t1 (
=== 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 2010-03-04 17:16:10 +0000
@@ -34,6 +34,9 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 29;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
UPDATE t1 SET c1 = 50 WHERE c1 = 17;
UPDATE t1 SET c1 = 51 WHERE c1 = 19;
FLUSH TABLES;
@@ -58,6 +61,7 @@ c1
21
22
23
+24
30
40
51
@@ -792,9 +796,13 @@ t1 CREATE TABLE `t1` (
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (c1)
PARTITIONS 2 */
+SET INSERT_ID = 22;
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
1
+2
23
DROP TABLE t1;
# Testing with FLUSH TABLE
=== modified file 'mysql-test/t/partition_error.test'
--- a/mysql-test/t/partition_error.test 2009-12-13 20:29:50 +0000
+++ b/mysql-test/t/partition_error.test 2010-03-04 17:16:10 +0000
@@ -8,6 +8,30 @@
drop table if exists t1;
--enable_warnings
+--echo #
+--echo # Bug#50392: insert_id is not reset for partitioned tables
+--echo # auto_increment on duplicate entry
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) PARTITION BY KEY(a);
+SET INSERT_ID= 13;
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID= 12;
+--error ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1;
+DROP TABLE t1;
+
#
# Bug#38719: Partitioning returns a different error code for a
# duplicate key error
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2010-02-12 09:03:10 +0000
+++ b/sql/ha_partition.cc 2010-03-04 17:16:10 +0000
@@ -6454,9 +6454,22 @@ void ha_partition::release_auto_incremen
ulonglong next_auto_inc_val;
lock_auto_increment();
next_auto_inc_val= ha_data->next_auto_inc_val;
+ /*
+ If the current auto_increment values is lower than the reserved
+ value, and the reserved value was reserved by this thread,
+ we can lower the reserved value.
+ */
if (next_insert_id < next_auto_inc_val &&
auto_inc_interval_for_cur_row.maximum() >= next_auto_inc_val)
- ha_data->next_auto_inc_val= next_insert_id;
+ {
+ THD *thd= ha_thd();
+ /*
+ Check that we do not lower the value because of a failed insert
+ with SET INSERT_ID, i.e. forced/non generated values.
+ */
+ if (thd->auto_inc_intervals_forced.maximum() < next_insert_id)
+ ha_data->next_auto_inc_val= next_insert_id;
+ }
DBUG_PRINT("info", ("ha_data->next_auto_inc_val: %lu",
(ulong) ha_data->next_auto_inc_val));
Attachment: [text/bzr-bundle]
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3369)Bug#50392 | Mattias Jonsson | 4 Mar |
