MySQL Bugs: #50392: insert_id is not reset for partitioned tables auto

Bug #50392	insert_id is not reset for partitioned tables auto_increment on duplicate entry
Submitted:	17 Jan 2010 0:28	Modified:	21 Jun 2010 0:40
Reporter:	Mattias Jonsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
in myisam, insert_id is reset after failing auto_increment insert, but on partitioned myisam it is not.

How to repeat:
create table t1 (a int auto_increment primary key);
SET insert_id= 13;
insert into t1 values (NULL);
insert into t1 values (NULL);
SET insert_id= 13;
insert into t1 values (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
insert into t1 values (NULL);
select * from t1;
a
13
14
15
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);
insert into t1 values (NULL);
SET insert_id= 13;
insert into t1 values (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
insert into t1 values (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
insert into t1 values (NULL);
ERROR 23000: Duplicate entry '13' for key 'PRIMARY'
select * from t1;
a
13
14
drop table t1;

Suggested fix:
reset insert_id also in case of failure for the partitioning engine.

Verified just as described:

77-52-1-11:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.43-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int auto_increment primary key);
Query OK, 0 rows affected (0.43 sec)

mysql> SET insert_id= 13;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SET insert_id= 13;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (NULL);
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> insert into t1 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+
| a  |
+----+
| 13 |
| 14 |
| 15 |
+----+
3 rows in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (a int auto_increment primary key) partition by key(a);
Query OK, 0 rows affected (0.05 sec)

mysql> SET insert_id= 13;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SET insert_id= 13;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (NULL);
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> insert into t1 values (NULL);
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> insert into t1 values (NULL);
ERROR 1062 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> select * from t1;
+----+
| a  |
+----+
| 13 |
| 14 |
+----+
2 rows in set (0.00 sec)

The bug was that it lowered the auto_increment value on the table (i.e. it did reset the insert_id, but when releasing the auto_increment it did lower the auto_increment value of the partitioned table to the value of insert_id.)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/102358

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.

pushed into mysql-5.1-bugteam and mysql-pe

Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)

Documented in the 5.5.4 and 6.0.14 changelogs as follows:

        The insert_id server system variable was not reset following an
        insert that failed on a partitioned MyISAM table having an
        AUTO_INCREMENT column.

Set to NM, waiting for 5.1.

5.5.4 changelog entry was moved to 5.5.5.

Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:martin.hansson@sun.com-20100316162138-u9724fhm54cj3or0) (merge vers: 5.1.46) (pib:16)

Also documented in the 5.1.46 changelog (see prev comments).

Closed.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)