my primary ids are no more consecutive, WEIRD

[Fking]

i have auto incrementing primary id in one of my tables
everything was ok few days ago
it was adding them like that, 1,2,3,4,5,
suddenly it started to make them
1,2,3,4,5,9,8,6......
and it's not only in phpmyadmin, cause it outputs them the same way in the scripts

and i'm using while($row=mysql_fetch_array($result, MYSQL_ASSOC)
to fetch them, so that's the order they are saved in the table, not just something with the phpmyadmin visual sorting options....

any ideas?

thanks

[Doug_G]

If mysql is like most relational databases, there is no inherent ordering of any columns within a table, you must order resultsets with an ORDER BY clause in your sql.

I know MS databases might do the same thing. Internally the dbms has it's own rules about what record comes where in a table.

[Sxooter]

This is actually VERY common in MVCC databases, like MySQL with innodb and PostgreSQL and Oracle. What happens is that each update is actually an insert, followed by a delete when the original version of the row is no longer visible.

This means that internal ordering gets mixed around.

Makes it kind of funny when someone writes an app to edit the rows in a database, and it has paging without an order by. You page down so far, edit the page, hit save, and a different set of row data pops up for that page as everything shifts underneath the application.

If you need order in your data, use order by. 🙂