title: Mysql auto increment jumps when insert-select
body: <p>I am testing insert-select query and noticed an weird result.</p>
  
  <pre><code>CREATE TABLE `test` (
    `cnt` int(11) NOT NULL AUTO_INCREMENT,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`cnt`)
  )
  
  CREATE TABLE `test_current` (
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL
  )
  </code></pre>
  
  <p>First I created two tables, and insert some values into test_current</p>
  
  <pre><code>mysql&gt; insert into test_current (a,b) values (1,1),(2,2);
  Query OK, 2 rows affected (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 0
  </code></pre>
  
  <p>And I did this query</p>
  
  <pre><code>mysql&gt; INSERT INTO test (a,b) SELECT a,b FROM test_current;
  Query OK, 2 rows affected, 1 warning (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 1
  
  mysql&gt; select * from test;
  +-----+------+------+
  | cnt | a    | b    |
  +-----+------+------+
  |   1 |    1 |    1 |
  |   2 |    2 |    2 |
  +-----+------+------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>But when I did the query again</p>
  
  <pre><code>mysql&gt; INSERT INTO test (a,b) SELECT a,b FROM test_current;
  Query OK, 2 rows affected, 1 warning (0.00 sec)
  Records: 2  Duplicates: 0  Warnings: 1
  
  mysql&gt; select * from test;
  +-----+------+------+
  | cnt | a    | b    |
  +-----+------+------+
  |   1 |    1 |    1 |
  |   2 |    2 |    2 |
  |   4 |    1 |    1 |
  |   5 |    2 |    2 |
  +-----+------+------+
  </code></pre>
  
  <p>The auto increment just skipped cnt for 3.  I want to know what is this about.</p>