Why Does MySQL Suddenly Insert the Data NOT Chronological?

[chaseman]

It used to be chronological, and now suddenly it's not anymore.

 

It looks as follows:

ID |

2

3

4

9

8

7

 

The ID 9 was of course LATER inserted than 8, and 8 was later inserted than 7, the rows have a DATETIME column as well. The ID is auto_increment and primary key.

 

When I know fetch the data with a while loop, the fetched data will be printed in that order as above, since the while loop goes row by row.

 

I could of course do a ORDER BY, but I'd like to have it chronological ordered in the database itself, and I'd like to have it ordered when inserting already.

 

Did I accidentally change a setting?

[Pikachu2000]

The only reason I know of for that to happen is if the PK has been manipulated and changed, which there is almost never a good reason to do.

[chaseman]

How would I be able to manipulate or change the primary key?

 

I've done a describe table, and the ID field still has a primary key, which means I haven't removed the primary key.

 

What else settings could have been changed? Where can I look at to make sure?

[Pikachu2000]

I should have said 'PK field values', as in changing any of them from one number to another.

[chaseman]

oh that makes sense, well I didn't do that and I don't see a sense in doing that either.

 

Can I exclude a script sided problem totally? Because it was working fine since I set up the database, I was working on some code and suddenly the rows where out of order after a few new submissions.

 

Though I don't see how a query would make the database insert the row above older rows, so I think it must be database sided.

 

P.S. I just checked it and it is now submitting it normally again. I only have 3 rows backwards.

[trq]

It used to be chronological, and now suddenly it's not anymore.

 

This is normal functionality.

 

Databases do not store data in a particular order. You need to use ORDER BY to retrieve data in a specific order, and you should not rely upon an auto incrementing field for sorting.

[PFMaBiSmAd]

The position of the rows in the table can be anything (for example if you deleted row(s), new rows will be inserted into the space previously used by the deleted row(s).) The database doesn't care where any row is at and neither should you or your code.

 

Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

 

If you want the rows retrieved in a specific order, use ORDER BY to achieve that ordering.

[Pikachu2000]

It used to be chronological, and now suddenly it's not anymore.

 

This is normal functionality.

 

Databases do not store data in a particular order. You need to use ORDER BY to retrieve data in a specific order, and you should not rely upon an auto incrementing field for sorting.

 

That's good to know. I've never seen it happen before unless the PK field had been jacked with.

[chaseman]

So basically it's common practice. PF's suggestion seems to be right on the money, I did delete several rows before, the MySQL simply added those spaces with new auto_increment numbers.