In early July the admin statistics page of my search engine started reporting zero: zero pages crawled in the last hour, zero in the last 24 hours, on a pipeline that was visibly busy. At the same time, re-crawls were being scheduled two hours early. Both had the same cause, and none of the data was wrong. The database and the application disagreed about what time it was.
Two clocks, two hours apart
The PHP application wrote its timestamps, like the time a page was visited or indexed, in UTC. The MySQL server ran in a Docker container on the data pipeline server, and that container inherited TZ=Europe/Copenhagen from the project's .env file.
MySQL's documentation explains what that does. When the server starts, it determines the host's time zone, and the TZ environment variable is the way to set it explicitly; that becomes system_time_zone. The server's own time_zone starts as SYSTEM, meaning "the same as the system time zone". And the session time zone affects the values shown by functions like NOW().
So NOW() returned Copenhagen time. In July, Copenhagen is on summer time, two hours ahead of UTC.
Why "last hour" read zero
The statistics queries had the usual shape:
WHERE indexed_at >= NOW() - INTERVAL 1 HOUR
Take a page indexed at 18:00 UTC, stored as 18:00. At that moment, NOW() says 20:00, so the query asks for rows from 19:00 onwards. A row from 18:00 doesn't qualify, and nothing written in the last hour does either, because every stored value is two hours behind the clock it's compared with. The commit that fixed it records that the 24-hour counter read zero as well; the last-hour case is the easiest to follow. Re-crawl scheduling compared against the same clock and ran two hours early.
Nothing errored. The queries were valid, the data was correct, and the numbers were wrong.
The fix: tell MySQL to use UTC
The fix was one line in the compose file for the MySQL container:
--default-time-zone=+00:00
That sets the server's time zone to UTC, matching the application. The value is an offset rather than the name UTC on purpose. MySQL's documentation says named time zones only work if the time zone tables in the mysql system database have been created and populated; otherwise SET time_zone = 'UTC' fails with "Unknown or incorrect time zone". An offset needs no tables.
The same setting went into the production compose overlay for the frontend server a minute later, so that a rollback to the older single-server MySQL setup wouldn't bring the skew back.
The caveat: DATETIME and TIMESTAMP aren't the same
The commit notes that no data had to change, because the stored values were DATETIMEs that were already in UTC. That reasoning depends on the column type, and it's the part to check before doing the same thing elsewhere.
MySQL's documentation draws the line clearly. The session time zone doesn't affect values in DATE, TIME or DATETIME columns; those are stored and returned as written. TIMESTAMP columns are different: values are converted from the session time zone to UTC when stored, and back to the session time zone when read. So changing the server's time zone doesn't touch a DATETIME, but it changes how every existing TIMESTAMP value reads back. If your tables use TIMESTAMP and you change the zone, work out what that does to the rows written under the old setting first.
What generalises
Pick one time zone for storage and comparison, use UTC, and make every component agree, including the ones that inherit their environment from somewhere you didn't look. In this case the database's idea of the time came from an .env file meant for the application, through the container's environment, and nothing complained.
It's the same kind of bug as a GNU date gotcha I hit in a shell script: the tool did exactly what it was documented to do, with a time zone I hadn't realised it was using.
Leave a comment
Comments are reviewed before they appear.