title: Out of sync auto increment MYSQL
body: <p>I have a weird behavior on one of the tables I am working with, I am not sure if it is my php code or some setting on the database that is causing auto incremented id's to fall out of sync.</p>
  
  <p>If I run the following code without any indexes on name&amp;cars I get:</p>
  
  <pre><code>    $cars = array("Volvo","BMW","Toyota");
      $name = "John Smith";
  
          foreach($cars as $value)
          {
                  try 
                  {
  
                   //insert into database with a prepared statement
  
                     $query = $db-&gt;prepare(
                     'INSERT INTO cars (name,cars) 
                     VALUES (:name,:cars) 
                     ');
                     $query-&gt;execute(array(           
                     ':name' =&gt; $name,
                     ':cars' =&gt; $value
                     ));
  
                  }
                  //else catch the exception and show the error.
                  catch(PDOException $e) 
                  {
                      $error[] = $e-&gt;getMessage();
                  }   
          }
  
  ///Results
  id || name       || cars
  1  || John Smith || Volvo
  2  || John Smith || BMW
  3  || John Smith || Toyota
  </code></pre>
  
  <p>But if I put an unique index on name&amp;cars, the auto increment gets out of sync and I can't understand why because I can't see anything wrong with my PHP code?</p>
  
  <pre><code>$cars = array("Volvo","BMW","Toyota");
      $name = "John Smith";
  
          foreach($cars as $value)
          {
                  try 
                  {
  
                   //insert into database with a prepared statement
  
                     $query = $db-&gt;prepare(
                     'INSERT INTO cars (name,cars) 
                     VALUES (:name,:cars) 
                     ');
                     $query-&gt;execute(array(           
                     ':name' =&gt; $name,
                     ':cars' =&gt; $value
                     ));
  
                  }
                  //else catch the exception and show the error.
                  catch(PDOException $e) 
                  {
                      $error[] = $e-&gt;getMessage();
                  }   
          }
  
  ///Results
  id || name       || cars
  3  || John Smith || Toyota
  1  || John Smith || Volvo
  2  || John Smith || BMW
  </code></pre>