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

Unless your SELECT statement has an ORDER BY clause, there is __no guarantee__ that your records will be returned in any particular order — Mark Baker, Oct 31 '14 at 14:22
I only want ever want one combination of name&cars. Anymore than that is no use to me as I will only obtain the same information that the person has this named car, everything else will be sat as duplicates in the database — user3882771, Oct 31 '14 at 14:36
Also thanks for letting me know, I always thought that records were inserted in consecutive order. So I should not be worried I am doing something wrong that is causing the order to fall out? — user3882771, Oct 31 '14 at 14:37

score 2 · Accepted Answer · answered Oct 31 '14 at 14:23

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.

answered Oct 31 '14 at 14:23

tjati

5,761
4
41
56

Ah right you sure? I got worried because whilst numbers were not out of sync the order was, this is the first time I was seeing it being inserted in a random order. Normally I am use to seeing 1,2,3,4,5 etc. Not 3,1,5,2,4. You are correct it is just a SELECT statement without any ORDER BY. So this is normal? And I should not be worried that I've done something wrong? – user3882771 Oct 31 '14 at 14:33
1

It's totally normal that the order changes while the database engine reorganized the tables. – tjati Oct 31 '14 at 14:40

score 0 · Answer 2 · edited May 23 '17 at 10:26

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.

Thanks for the clarification, I understood that when you delete a record you end up with gaps. But what I didn't understand was in my head, I thought foreach loops each iteration. so on first iteration it inserts the first ID, therefore should be 1 first, second iteration is the second insertion so 2 second and so forth. Hence I thought in the database I would expect to see 1,2,3 in order. When I seen the results above I was like eh! why is 3rd iteration going in first, not understanding that they are assigned unique id's but doesn't mean they will be un-ordered. I was expecting ordered list — user3882771, Oct 31 '14 at 14:45
Common misunderstanding. If it helps, SQL is relational, and that's relational in the mathematical sense of the word (eg http://en.wikipedia.org/wiki/Relational_algebra). Don't think of it like an ordered list. Think of it like a set. — Evan Volgas, Oct 31 '14 at 15:01

Out of sync auto increment MYSQL

2 Answers2