9

Assume I've got the table:

CREATE TABLE test (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    InsertTime DATETIME
) ENGINE = InnoDB;

And, through an Apache/PHP website, as a response to web requests, I keep doing this:

INSERT INTO test (InsertTime) values (NOW());

Is it safe to assume that if row1.ID > row2.ID then row1.InsertTime >= row2.InsertTime? Or perhaps through some unfortunate combination of factors (multi-CPU server in a replicated environment with the moons of Jupiter in the correct alignment etc.) this can fail?

Note: I don't have any issues. I'm writing a new piece of software and am wondering if I can rely on sorting by ID to also sort by dates (only NOW() will even be inserted into that column).

Vilx-
  • 104,512
  • 87
  • 279
  • 422
  • 1
    This does not seem to be a problem, but rather a discussion so -> more something for SE programmers? And for he record, I don't see how this can fail. – Derk Arts Dec 25 '11 at 15:05
  • 2
    @saratis - Don't see how this is a discussion? It is a specific question. Either it does work 100% guaranteed or it doesn't. – Martin Smith Dec 25 '11 at 15:08
  • Although I guess there are 2 different questions here. "Can an Id ever be generated out of insertion order" and "can the order ever fail to match the order of the datetime column?" – Martin Smith Dec 25 '11 at 15:24
  • Can the second condition ever be present without the first? – Vilx- Dec 25 '11 at 19:42

5 Answers5

2

I think you will have some inconsistency once a year because of DST (surely, it depends on server settings). Other than that I don't see why it may fail.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Unless you are doing string-comparison instead of date-comparison, DST will not cause a change of date order. Timestamps are stored as UTC under the hood. MySQL knows that 8:00pm in UTC-7 is smaller than 7:01PM in UTC-8. – Julius Musseau Dec 25 '11 at 15:22
  • In the above example I use MySQL's `datetime` type which does not use UTC or any kind of timestamps. Instead it stores the date as a string (well, almost). That's how you can achieve all those invalid dates like `00-00-0000`. It also doesn't say anything about timezones, so I have to keep track of that myself. All in all, I guess that TIMESTAMP would be a much better choice. – Vilx- Dec 25 '11 at 19:37
  • Oh, and that also means that due to DST there might be time confusion during transitions. One more reason to avoid that datatype. – Vilx- Dec 25 '11 at 19:39
2

I think most problems will come from the call to NOW() instead of the AUTO_INCREMENT. I suspect most computers at some point print NOW() dates out of order! This is usually either because a sysadmin changed the clock, or because NTP changed the clock.

Julius Musseau
  • 4,037
  • 23
  • 27
  • Wow, I had not thought about the NTP. It's so obvious now! And it also means that sorting my ID is not only possible - it's strongly recommended! Because it's the only way to ensure that the records are retrieved in the same order they were inserted. – Vilx- Dec 25 '11 at 19:35
1

Best as I can tell, the autoincrement ID will never fail to return records in the wrong order. However, there is one other case you need to be aware of when ordering by ID.

If your client ever holds on to records to be inserted later, then when records are read, they will not be read in the same order created. I've run into this a number of times, for example, when building clients for mobile apps that have intermittent network access.

Chris Henry
  • 11,914
  • 3
  • 30
  • 31
0

It is possible if somebody manually resets the auto_increment on the table.

blockhead
  • 9,655
  • 3
  • 43
  • 69
  • That's not part of normal operation. You might as well enter manual date too instead of `NOW()`. Obviously, if you do something like that, all bets are off. – Vilx- Dec 25 '11 at 19:29
0

Yes, delete the auto_increment column, then re-create a new one.

Ziad JAROUDI
  • 1