MySQL Bugs: #46866: AUTO_INCREMENT errors with partitioned Archive tables

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

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'

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!

Thank you for the report.

Verified as described.

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.

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.)

pushed to mysql-next-mr-bugfixing

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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.

Mattias confirms no new merges expected, closing.