MySQL Bugs: #42714: AUTO_INCREMENT errors in 5.1.31

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:		CPU Architecture:	Any
Tags:	regression

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

Thank you for the report.

I can not repeat any AUTO_INCREMENT error using test case provided. Please send us output from your environment.

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>

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>

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

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

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

a dump of show global variables and show global status

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

Thank you for the additional information.

Yes, I really meant any auto-increment-offset >1 :)

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.

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.

See bug #42994 (dup)
See bug #47118 (5.1.37)

Noted in 5.4.2 changelog.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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 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:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (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)