Out of sync auto increment MYSQL

Question

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.

If I run the following code without any indexes on name&cars I get:

    $cars = array("Volvo","BMW","Toyota");
    $name = "John Smith";

        foreach($cars as $value)
        {
                try 
                {

                 //insert into database with a prepared statement

                   $query = $db->prepare(
                   'INSERT INTO cars (name,cars) 
                   VALUES (:name,:cars) 
                   ');
                   $query->execute(array(           
                   ':name' => $name,
                   ':cars' => $value
                   ));

                }
                //else catch the exception and show the error.
                catch(PDOException $e) 
                {
                    $error[] = $e->getMessage();
                }   
        }

///Results
id || name       || cars
1  || John Smith || Volvo
2  || John Smith || BMW
3  || John Smith || Toyota

But if I put an unique index on name&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?

$cars = array("Volvo","BMW","Toyota");
    $name = "John Smith";

        foreach($cars as $value)
        {
                try 
                {

                 //insert into database with a prepared statement

                   $query = $db->prepare(
                   'INSERT INTO cars (name,cars) 
                   VALUES (:name,:cars) 
                   ');
                   $query->execute(array(           
                   ':name' => $name,
                   ':cars' => $value
                   ));

                }
                //else catch the exception and show the error.
                catch(PDOException $e) 
                {
                    $error[] = $e->getMessage();
                }   
        }

///Results
id || name       || cars
3  || John Smith || Toyota
1  || John Smith || Volvo
2  || John Smith || BMW

Answer 1

Two things.

First, auto increment will usually end up "out of sync" in one way or another. Take a look at MySQL AUTO_INCREMENT does not ROLLBACK for a very good example of why you can't expect auto increment primary key ids to be "in sync" with your data. The id is unique and it increments. That's really all you can say about auto increment.

Which leads to the second point. You can certainly not say an auto increment field won't have gaps or will be returned to you in sorted order unless you insist that it is (which both @omeinusch and Mark Baker pointed out above, and both of them are right). Here, what I'd like to point out is that your auto-increment is behaving exactly as expected. Insert another row. It'll have an id of four. Begin an insert transaction, roll it back, then insert another row. It'll have an id of six. This is on purpose. Insert 100 rows, roll them back, then insert 100 more. That batch of 100 will have auto increment ids > 100. And they won't be returned to you in sorted order unless you add ORDER BY id to your query. This is intentional. And there's really nothing "out of sync" about it.

Answer 2

Why do you think, it's out of sync? John's Toyota has still id 3 and his Volvo is 1.

How do you get your results? You are just SELECTing your cars, right? The behaviour is fine, because there is no need to order the data by the id without any ORDER BY statement.

You should query with SELECT id, name, cars FROM cars ORDER BY id ASC.

It's totally okay with mysql that an unordered list is returned. The "unorder" is just the result of some internal optimization.