Bug #46866 AUTO_INCREMENT errors with partitioned Archive tables
Submitted: 22 Aug 2009 20:31 Modified: 1 Oct 2010 12:43
Reporter: V Venkateswaran Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1, next bzr OS:Any
Assigned to: Mattias Jonsson CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[22 Aug 2009 20:31] V Venkateswaran
Description:
partitioned archive tables do not give expected
output when negative values are inserted in the
auto increment column.

How to repeat:
Reproducible
------------

USE test;

DROP TABLE IF EXISTS t;

CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
  c2 INT) ENGINE=Archive PARTITION BY HASH(c1) PARTITIONS 2;

INSERT INTO t VALUES (-4,-20);
INSERT INTO t(c2) VALUES (30);
INSERT INTO t(c2) VALUES (40);

SELECT * FROM t ORDER BY c1 ASC;

DROP TABLE t;

O/P with the relevant error
---------------------------

mysql> INSERT INTO t VALUES (-4,-20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t(c2) VALUES (30);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t(c2) VALUES (40);
ERROR 1022 (23000): Can't write; duplicate key in table 't'
[23 Aug 2009 13:12] Peter Laursen
I find lots of weird inconsistencies here (5.1.37 64-bit wondows server) also with an unpartitioned MyISAM table

USE test;

-- using partitions + Archive

DROP TABLE IF EXISTS t;

CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
  c2 INT) ENGINE=Archive PARTITION BY HASH(c1) PARTITIONS 2;

INSERT INTO t VALUES (-4,-20);
INSERT INTO t(c2) VALUES (30);
SHOW WARNINGS;
/*
Level      Code  Message                                    
-------  ------  -------------------------------------------
Warning    1264  Out of range value for column 'c1' at row 1
*/
SELECT * FROM t;
/*
        c1    c2  
----------  ------
        -4     -20
2147483647      30
*/
INSERT INTO t(c2) VALUES (40); -- Error Code : 1022 Can't write; duplicate key in table 't'

-- no partitions + MyISAM

DROP TABLE IF EXISTS tt;

CREATE TABLE tt (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1),
  c2 INT) ENGINE=MYISAM;

INSERT INTO tt VALUES (-4,-20);
INSERT INTO tt(c2) VALUES (30);
SHOW WARNINGS; -- empty result set

SELECT * FROM t;
/*
        c1    c2  
----------  ------
        -4     -20
2147483647      30
*/
INSERT INTO t(c2) VALUES (40); -- Error Code : 1467 Failed to read auto-increment value from storage engine

-- inserting '1's in both tables
INSERT INTO t VALUES (1,-2); -- Error Code : 1022 Can't write; duplicate key in table 't'
INSERT INTO tt VALUES (1,-2); -- Error Code : 1062 Duplicate entry '1' for key 'PRIMARY'

-- inserting '10's in both tables
INSERT INTO t VALUES (10,-2); -- Error Code : 1022 Can't write; duplicate key in table 't'
INSERT INTO tt VALUES (10,-2); -- success

SELECT * FROM t;
/*
        c1    c2  
----------  ------
        -4     -20
2147483647      30
*/

SELECT * FROM tt;
/*
    c1    c2  
------  ------
    -4     -20
     1      30
    10      -2

look: now '2147483647' suddenly became '1' ????

*/

This is definitely more serious than 'S3' in my opinion!
[24 Aug 2009 6:14] Sveta Smirnova
Thank you for the report.

Verified as described.
[24 Aug 2009 6:18] Sveta Smirnova
Peter,

re your comment.

You insert into tt then check t table first. So no mysterious 2147483647->1 conversion happens, you just select from wrong table first time.
[8 Sep 2009 8:24] Bugs System
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/82639

3116 Mattias Jonsson	2009-09-08
      Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
      
      Archive does not support decreasing AUTO_INCREMENT,
      since it does not support indexes (the PRIMARY KEY on
      AUTO_INCREMENT is enforced by only allowing higher values than
      the current max.)
      
      Bug was that negative AUTO_INCREMENT was converted to unsigned
      and become very large positive values instead.
      
      Solution was to treat all negative AUTO_INCREMENT values as
      (unsigned) 0. This means that one cannot insert negative values
      into an AUTO_INCREMENT column in a Archive table.
     @ mysql-test/suite/parts/inc/partition_auto_increment.inc
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Added verification tests for negative values on non partitioned engines
        and fixed the test to be used by both Archive and Blackhole.
     @ mysql-test/suite/parts/r/partition_auto_increment_archive.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/r/partition_auto_increment_memory.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/r/partition_auto_increment_ndb.result
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test result.
     @ mysql-test/suite/parts/t/partition_auto_increment_archive.test
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test case to run test with negative auto_increments.
     @ mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Updated test case to run test with negative auto_increments.
     @ storage/archive/ha_archive.cc
        Bug#46866: AUTO_INCREMENT errors with partitioned Archive tables
        
        Do not allow negative auto_increment (treat them as 0, which is
        never greater than the current auto_increment value.)
[26 May 2010 8:30] Mattias Jonsson
pushed to mysql-next-mr-bugfixing
[15 Jun 2010 8:31] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[4 Aug 2010 8:07] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)
[4 Aug 2010 8:23] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:18)
[6 Aug 2010 9:09] Jon Stephens
No 5.6 changelog entry required, since this doesn't appear in any 5.6 release.

Set NM status, waiting for pushes to 5.1/5.5.
[1 Oct 2010 12:43] Jon Stephens
Mattias confirms no new merges expected, closing.