Bug #42714 AUTO_INCREMENT errors in 5.1.31
Submitted: 9 Feb 2009 19:51 Modified: 19 Jun 2010 17:41
Reporter: Singer Wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.31, 5.1, 6.0 bzr OS:Linux (i386 and x86_64 verified)
Assigned to:
Tags: regression
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

[9 Feb 2009 19:51] Singer Wang
Description:
Erroneous Duplicate Key Error in MySQL 5.1.31 when we are inserting into AUTO_INCREMENT columns..

How to repeat:
<pre>
create table test_a (
  `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(10) unsigned NOT NULL,
  `c` enum('FALSE','TRUE') DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE = InnoDB;

create table test_b (
  `m` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n` int(10) unsigned NOT NULL,
  `o` enum('FALSE','TRUE') DEFAULT NULL,
  PRIMARY KEY (`m`)
) ENGINE = InnoDB;

Note that you must use a 5.1.31 Client and 5.1.31 Server
<pre>
INSERT INTO test_b (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');

INSERT INTO test_a (b,c) select n,o FROM test_b ;

INSERT INTO test_a (b,c) select n,o FROM test_b ;

SELECT * FROM test_a;

INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';

SELECT * FROM test_a;

INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
</pre>

Suggested fix:
No Idea
[9 Feb 2009 20:04] Sveta Smirnova
Thank you for the report.

I can not repeat any AUTO_INCREMENT error using test case provided. Please send us output from your environment.
[9 Feb 2009 20:18] Singer Wang
mysql> create table test_a (
    ->   `a` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `b` int(10) unsigned NOT NULL,
    ->   `c` enum('FALSE','TRUE') DEFAULT NULL,
    ->   PRIMARY KEY (`a`)
    -> ) ENGINE = InnoDB;

create table test_b (
  `m` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `n` int(10) unsigned NOT NULL,
  `o` enum('FALSE','TRUE') DEFAULT NULL,
  PRIMARY KEY (`m`)
Query OK, 0 rows affected (0.00 sec)

) ENGINE = InnoDB;
mysql>
mysql> create table test_b (
    ->   `m` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `n` int(10) unsigned NOT NULL,
    ->   `o` enum('FALSE','TRUE') DEFAULT NULL,
    ->   PRIMARY KEY (`m`)
    -> ) ENGINE = InnoDB;
INSERT INTO test_a (b,c) select n,o FROM test_b ;

SELECT * FROM test_a;

INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';

SELECT * FROM test_a;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> INSERT INTO test_b (n,o) VALUES (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO test_a (b,c) select n,o FROM test_b ;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> INSERT INTO test_a (b,c) select n,o FROM test_b ;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test_a;
+----+---+-------+
| a  | b | c     |
+----+---+-------+
|  3 | 1 | TRUE  |
|  8 | 1 | FALSE |
| 13 | 2 | TRUE  |
| 18 | 2 | FALSE |
| 23 | 3 | TRUE  |
| 28 | 3 | FALSE |
| 33 | 4 | TRUE  |
| 38 | 4 | FALSE |
| 43 | 5 | TRUE  |
| 48 | 5 | FALSE |
| 53 | 1 | TRUE  |
| 58 | 1 | FALSE |
| 63 | 2 | TRUE  |
| 68 | 2 | FALSE |
| 73 | 3 | TRUE  |
| 78 | 3 | FALSE |
| 83 | 4 | TRUE  |
| 88 | 4 | FALSE |
| 93 | 5 | TRUE  |
| 98 | 5 | FALSE |
+----+---+-------+
20 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT * FROM test_a;
+-----+---+-------+
| a   | b | c     |
+-----+---+-------+
|   3 | 1 | TRUE  |
|   8 | 1 | FALSE |
|  13 | 2 | TRUE  |
|  18 | 2 | FALSE |
|  23 | 3 | TRUE  |
|  28 | 3 | FALSE |
|  33 | 4 | TRUE  |
|  38 | 4 | FALSE |
|  43 | 5 | TRUE  |
|  48 | 5 | FALSE |
|  53 | 1 | TRUE  |
|  58 | 1 | FALSE |
|  63 | 2 | TRUE  |
|  68 | 2 | FALSE |
|  73 | 3 | TRUE  |
|  78 | 3 | FALSE |
|  83 | 4 | TRUE  |
|  88 | 4 | FALSE |
|  93 | 5 | TRUE  |
|  98 | 5 | FALSE |
| 123 | 1 | FALSE |
| 128 | 2 | FALSE |
| 133 | 3 | FALSE |
| 138 | 4 | FALSE |
| 143 | 5 | FALSE |
+-----+---+-------+
25 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
ERROR 1062 (23000): Duplicate entry '143' for key 'PRIMARY'
mysql> SELECT * FROM test_a;
+-----+---+-------+
| a   | b | c     |
+-----+---+-------+
|   3 | 1 | TRUE  |
|   8 | 1 | FALSE |
|  13 | 2 | TRUE  |
|  18 | 2 | FALSE |
|  23 | 3 | TRUE  |
|  28 | 3 | FALSE |
|  33 | 4 | TRUE  |
|  38 | 4 | FALSE |
|  43 | 5 | TRUE  |
|  48 | 5 | FALSE |
|  53 | 1 | TRUE  |
|  58 | 1 | FALSE |
|  63 | 2 | TRUE  |
|  68 | 2 | FALSE |
|  73 | 3 | TRUE  |
|  78 | 3 | FALSE |
|  83 | 4 | TRUE  |
|  88 | 4 | FALSE |
|  93 | 5 | TRUE  |
|  98 | 5 | FALSE |
| 123 | 1 | FALSE |
| 128 | 2 | FALSE |
| 133 | 3 | FALSE |
| 138 | 4 | FALSE |
| 143 | 5 | FALSE |
+-----+---+-------+
25 rows in set (0.00 sec)

mysql> INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test_a (b,c) SELECT n,o FROM test_b WHERE o = 'false';
ERROR 1062 (23000): Duplicate entry '203' for key 'PRIMARY'
mysql> SELECT * FROM test_a;
+-----+---+-------+
| a   | b | c     |
+-----+---+-------+
|   3 | 1 | TRUE  |
|   8 | 1 | FALSE |
|  13 | 2 | TRUE  |
|  18 | 2 | FALSE |
|  23 | 3 | TRUE  |
|  28 | 3 | FALSE |
|  33 | 4 | TRUE  |
|  38 | 4 | FALSE |
|  43 | 5 | TRUE  |
|  48 | 5 | FALSE |
|  53 | 1 | TRUE  |
|  58 | 1 | FALSE |
|  63 | 2 | TRUE  |
|  68 | 2 | FALSE |
|  73 | 3 | TRUE  |
|  78 | 3 | FALSE |
|  83 | 4 | TRUE  |
|  88 | 4 | FALSE |
|  93 | 5 | TRUE  |
|  98 | 5 | FALSE |
| 123 | 1 | FALSE |
| 128 | 2 | FALSE |
| 133 | 3 | FALSE |
| 138 | 4 | FALSE |
| 143 | 5 | FALSE |
| 148 | 1 | FALSE |
| 153 | 2 | FALSE |
| 158 | 3 | FALSE |
| 163 | 4 | FALSE |
| 168 | 5 | FALSE |
| 183 | 1 | FALSE |
| 188 | 2 | FALSE |
| 193 | 3 | FALSE |
| 198 | 4 | FALSE |
| 203 | 5 | FALSE |
+-----+---+-------+
35 rows in set (0.00 sec)

mysql>
[9 Feb 2009 20:18] Singer Wang
also...

mysql> \s
--------------
/usr/local/mysql-5.1.31-linux-x86_64-glibc23/bin/mysql  Ver 14.14 Distrib 5.1.31, for unknown-linux-gnu (x86_64) using readline 5.1

Connection id:          12
Current database:       fark2
Current user:           root@
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.31 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Insert id:              183
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql_sandbox5131.sock
Uptime:                 1 hour 7 min 24 sec

Threads: 1  Questions: 698  Slow queries: 0  Opens: 108  Flush tables: 1  Open tables: 12  Queries per second avg: 0.172
--------------

mysql>
[9 Feb 2009 20:33] Singer Wang
Note that this only occurs if both the server and client is 5.1.31; if I use a 5.1.30 client this doesn't seem to occur..
[9 Feb 2009 20:38] Sveta Smirnova
Thank you for the feedback.

Verified as described with one note: server should be started with option --auto-increment-offset=5.

Bug is not repeatable with MyISAM storage engine. Bug was introduced in version 5.1.31
[9 Feb 2009 21:35] Singer Wang
acutally, auto_increment_offset was 3 and auto_increment_increment was 5..

mysql>
mysql>
mysql> show global variables like '%increment%';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| auto_increment_increment    | 5     |
| auto_increment_offset       | 3     |
| div_precision_increment     | 4     |
| innodb_autoextend_increment | 8     |
+-----------------------------+-------+
4 rows in set (0.01 sec)

mysql>

I've also attached system.txt (a full dump of show global variables, show global status)...
[9 Feb 2009 21:35] Singer Wang
a dump of show global variables and show global status

Attachment: system.txt (text/plain), 0 bytes.

[9 Feb 2009 21:51] Sveta Smirnova
Thank you for the additional information.

Yes, I really meant any auto-increment-offset >1 :)
[13 Mar 2009 19:48] Timothy Smith
Pushed to 5.1.33; Docs please return to "Patch approved" waiting for a 6.0 snapshot.

  Applying InnoDB snashot 5.1-ss4350, part 4.  Fixes
  
  Bug #42714    AUTO_INCREMENT errors in 5.1.31
  
  Detailed revision comments:
  
  r4287 | sunny | 2009-02-25 05:32:01 +0200 (Wed, 25 Feb 2009) | 10 lines
  branches/5.1: Fix Bug#42714 AUTO_INCREMENT errors in 5.1.31. There are two
  changes to the autoinc handling.
  
  1. To fix the immediate problem from the bug report, we must ensure that the
     value written to the table is always less than the max value stored in
     dict_table_t.
  
  2. The second related change is that according to MySQL documentation when
     the offset is greater than the increment, we should ignore the offset.
[15 Mar 2009 0:14] Paul Dubois
Noted in 5.1.33 changelog.

For InnoDB tables, spurious duplicate-key errors could occur when
inserting into an AUTO_INCREMENT column.  

Setting report to Patch Approved pending push into 6.0.x.
[4 Sep 2009 1:52] Roel Van de Paar
See bug #42994 (dup)
See bug #47118 (5.1.37)
[14 Sep 2009 19:43] Paul Dubois
Noted in 5.4.2 changelog.
[5 May 2010 15:18] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 17:55] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:03] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:31] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:59] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:36] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[15 Jun 2010 8:16] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:33] 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)
[17 Jun 2010 11:47] Bugs System
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)
[17 Jun 2010 12:24] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:12] Bugs System
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)