Description:
Close to any ISP running more than a few customer databases with MySQL 4.0 (and below) is affected by thi s issue, so please don't discard this after just taking a glance at it.
With MySQL 4.1, the value of the TIMESTAMP field is being returned in a different format, e.g.
4.0 and below: "20060606120641"
4.1 and up: "2006-06-06 12:06:41"
This change has been made to improve compatibility with the SQL standard.
If an application relies on the older format string, one can select the field name "+0" in order to get the old string. This does also work for date and time functions:
mysql> select now(), now()+0;
+---------------------+----------------+
| now() | now()+0 |
+---------------------+----------------+
| 2006-06-06 12:06:41 | 20060606120641 |
+---------------------+----------------+
1 row in set (0.00 sec)
Support for 4.0 is being droppd by MySQL and there will be some time where MySQL developers cannot reliably support the old code base, so
users of MySQL should upgrade to a newer release (4.1 or 5.0).
Even the recently announced 4.0.27 has been removed from the download page and is only available from the "previous versions" archive, so I expect such a time of more or less forced migration to be soon.
However, a lot of ISPs (including ourselves) have a large base of customers with 4.0-based databases, but these ISPs cannot support the migration of thousands of customer databases on their own (especially not as most customers are using some year-old sql application and don't know how to enable usage of newer timestamp fields).
As most applications use quite strange functions to disassemble the old timestamp string and so do either rely on the old string or need some major rewrite in at least a few functions, it is usually essential to change those things BEFORE an upgrade can be done.
However, at the large number of shared hosting databases, this is simply a close to impossible issue, so ISPs currently running MySQL 4.0 are stuck to 4.0 and cannot run 4.1. Even the attempt to enable "--new" in 4.0 would create a lot of trouble.
How to repeat:
Become an ISP, host > 100 databases on a server and a few years later, discover that most applications cannot be migrated without a lot of trouble.
Especially as you've got to explain the same issues to > 100 different people with a highly varying level of technical knowledge and you see that at least a third don't know who installed that scripts some years ago, don't know who could "future-proof" those scripts or otherwise work on this issue.
The migration from 3.23 to 4.0 was simple, as most behaviour is compatible and merely a few keywords have been added (only clashing with field names used in an unquoted context). Those few added keywords can be investigated in advance (check existing tables for those keywords, inform customers on the expected changes) and the impact is quite low (simply quote/escape those fields), so we were able to do so.
With the large number of timestamp-disassembling scripts, advices cannot be given as a general rule of thumb, but each script would've to be checked on its own. This is an amount of consulting work most customers won't (and can't) expect from their ISP.
Suggested fix:
Add an "old-timestamp" function to MySQL 4.1 and 5.0, which always return the "compatible" timestamp string.
