Strange order in mysql table

Yii 1.1 General Discussion
1

Hi guys,

why in my tables ids (incremental primary key) are not consecutive?

thanx.

2

What engine do you use… MySQL?

If this is the case, then suppose that you have the last id = 7

Then you want to insert a new record, but the insertion fails. last id = 8, but no record inserted.

Then you wnat to insert other record, this is successfull, then this new record will have id = 9.

So in your table, you have the records …6, 7, 9, …

This happens to me when I use transactions and roll back on failure in InnoDB for MySQL!

3

just to add to PoL’s comment…

let’s say that last id is 8 even if you delete that record… when you insert a new record, the new record will have id=9

4

Oh… I forgot to mention that! Thanks mdomba!!!

5

When I look a table with a simple select, ids and rows do not follow the insertion order: I have 4,5,6,1,2,3,7,8,9,13,14,15,10,11,12.

Why ?

6

Sorry, I use mysql with MyISAM

7

cause other column is acting as the default order…

in phpMyAdmin, select the table that has that problem to see its structure…

then go to the "operations" tab.

In it, there is a section to define the default ORDER BY of the table, it must be setted to something else…