Afternoon all,
This is an interesting problem that we ran into and have subsequently fixed (well it
fixed
itself, but more on that later).
I posted a question to the list a couple days ago quoting this as a TIMESTAMP(14)
column type
issue, but as I investigated more, that turned out to be incorrect.
About 3 days ago, four of our tables started reporting incorrect date/times, they were
all out by 1 day in the future.
These errors were localised to the fields with a column type of TIMESTAMP(14), our
only TIMESTAMP(14) columns
in our database (save for a few in tables where they are used as a date/time marker,
i.e., using the INSERT with NULL
to set it to now).
As it turns out the issue seemed to be this:
MySQL added 1 day (24 hours) when converting from character data "YYYYMMDDHHMMSS" to
Unixtime
(seconds since epoch).
The effects were thus:
* Doing an explicit INSERT INTO <Table> SET TimeStampField = '20031210235959'
Would put '20031211235959' into the table.
* Doing a SELECT UNIX_TIMESTAMP('20031210233150') would return the unixtime of
20031211233150,
In the example below, our time offset is UTC + 13hrs. The unixtime for
'20031210233150' should be 1071052310:
mysql> SELECT UNIX_TIMESTAMP('2003-12-10 23:31:50');
+---------------------------------------+
| UNIX_TIMESTAMP('2003-12-10 23:31:50') |
+---------------------------------------+
| 1071138710 |
+---------------------------------------+
1 row in set (0.23 sec)
mysql> SELECT FROM_UNIXTIME( 1071138710 );
+-----------------------------+
| FROM_UNIXTIME( 1071138710 ) |
+-----------------------------+
| 2003-12-11 23:31:50 |
+-----------------------------+
1 row in set (0.20 sec)
* TIMESTAMP(14) fields where we never explicitly set the value, were
unaffected.
* There is no issue with mktime() at an OS level as alternative conversions
using Perl were correct.
* We are running v3.23.49a on Red Hat Linux v7.2
-----
While investigating this issue, the problem corrected itself!! We did nothing. Somehow
43 hours after the
problem arose, it suddenly went away, and we've spent the time since massaging the
affected records back to
normal.
Certainly a very strange problem and very disconcerting since it corrected itself
without any intervention on our
part, it's making us ask "if it has happened before" and "will it happen again".
Just putting this one out there :)
____________________________________________________________________________
Matthew M. Boulter
Software Engineer
Saab ITS Pty Ltd
Mobile: +61 (0)415 169 088
Phone: +61 (0)7 3854-4815
Fax: +61 (0)7 3854-4899
Email: [EMAIL PROTECTED]
WWW: www.saab-its.com.au
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
