tim laqua dot com Thoughts and Code from Tim Laqua


MySQL Error 1292 – Incorrect datetime value (Daylight Savings Time / DST)

So, you have a bunch of errors from some application between 2:00 AM and 3:00 AM on the day that Daylight Savings Time occured, huh? This year, it was between 2009-03-08 02:00:00 and 2009-03-08 02:59:59 - and we had one of those applications. As Urchin 6 was trying to parse IIS logs from 2:00 AM to 3:00 AM this past Sunday, it failed and continued to fail over and over again with the error "Incorrect datetime value" when it tried to insert the non-existant date in to the MySQL 5 ( to be exact) database. MySQL was configured with a timezone of SYSTEM:

SELECT @@time_zone

Running under Windows Server 2003 Web Edition, MySQL used the correct timezone and appropriately skipped ahead at 2AM to 3AM - those times don't exist here. Unfortunately, one of the IIS servers wasn't configured to adjust the time for Daylight Savings and made a big pile of log entries between 2:00 AM and 3:00 AM (again, a non-existant time).

ERROR: (...) Failed to query the database
DETAIL: Incorrect datetime value: '2009-03-08 02:56:15'
        for column '...' at row 1

First, properly configure IIS so it records time the same way the database expects it to. The only way to get around this situation where you have essentially corrupt IIS log files is to fix the time entries in the log file and associate them with a valid time (i.e. an hour before or an hour after). Technically, you also need to shift all of the subsequent logs forward one hour as well, as IIS was recording the wrong time (-1 hour). Then let 'er rip.