MySQL: can an AUTO_INCREMENT generate values out of order?

Question

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).

Answer 1

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.

Answer 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.