title: Why does mysql skip some auto increment ids?
body: <p>I have seen many explanations why mysql skips some auto increment values (and that it is not a bug as mysql never states that they should be consecutive). I am not sure why any of them would be applicable to this simple test case. I don't know if the results would be the same on all recent versions of mysql or not. Also adding:</p>
  
  <pre><code>ALTER TABLE test_table2 AUTO_INCREMENT = 1;
  </code></pre>
  
  <p>between the 2 INSERT INTO test_table2 lines makes the order as expected.</p>
  
  <p>Does anyone know why this simple case would skip ids 6 and 7?</p>
  
  <pre><code>CREATE TABLE test_table1 (
    `id` INT NOT NULL AUTO_INCREMENT,
    `test` TEXT NOT NULL,
    PRIMARY KEY (`id`)
  );
  INSERT INTO test_table1(`test`) VALUES('value 1');
  INSERT INTO test_table1(`test`) VALUES('value 2');
  INSERT INTO test_table1(`test`) VALUES('value 3');
  INSERT INTO test_table1(`test`) VALUES('value 4');
  INSERT INTO test_table1(`test`) VALUES('value 5');
  CREATE TABLE test_table2 (
    `id` INT NOT NULL AUTO_INCREMENT,
    `test` TEXT NOT NULL,
    PRIMARY KEY (`id`)
  );
  INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
  INSERT INTO test_table2(`test`) SELECT `test` FROM test_table1;
  SELECT * FROM test_table2;
  </code></pre>
  
  <p>Results on my version of mysql:</p>
  
  <pre><code>'1', 'value 1'
  '2', 'value 2'
  '3', 'value 3'
  '4', 'value 4'
  '5', 'value 5'
  '8', 'value 1'
  '9', 'value 2'
  '10', 'value 3'
  '11', 'value 4'
  '12', 'value 5'
  </code></pre>
  
  <p>Thanks in advance.</p>