Bug #4492 Wrong timestamp value after upgraded to 4.1.3-beta
Submitted: 9 Jul 2004 21:44 Modified: 30 Jul 2004 9:39
Reporter: Phillip Qin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.3-beta OS:Linux (Debian 3.0-woody)
Assigned to: Dmitry Lenev

[9 Jul 2004 21:44] Phillip Qin
Description:
Created InnoDB database/tables in 4.0.18. MySQL Control Center displayed timestamp column value correctly, e.g. 20040512171235.

Upgraded to 4.1.3-beta and copied over data directory from 4.0.18 to 4.1.3-beta. Both MySQL Control Center and command line program mysql displays timestamp column value incorrectly, e.g. the above value is now 2036-04-24 12:58:27. When using INSERT statement to insert a new record, the displayed timestamp value is correct.

How to repeat:
Use 2004-05-12 17:12:35 as example.

1. Create a 4.0.x InnoDB table;
2. Create a column, type timestamp(14);
3. Insert a record using Java/JDBC PreparedStatement (pass Timestamp value) or simply use mysql command line SELECT statement;
4. SELECT column_name FROM table_name in control center or on command line, column value should be 20040512171235;
5. Copy database to 4.1 environment;
6. Repeat step 4, column value now reads 2036-04-24 12:58:27;
7. SELECT colmn_name FROM table_name WHERE column_name = '2036-04-24 12:58:27'; No row will be returned; WHERE column_name = '2036-04-24 12:58:27%' will work;
7.1 SELECT date_format(column_name, '%Y-%m-%d %T %f') FROM table_name WHERE column_name = '2036-04-24 12:58:27%'; 1936-04-24 12:58:27 000000 should display.
8. Repeat step 3, 2004-05-12 17:12:35 should display;
9. SELECT colmn_name FROM table_name WHERE column_name = '2004-05-12 17:12:35', 2004-05-12 17:12:35 should display;
[9 Jul 2004 22:41] Phillip Qin
Developers who need a copy of my 4.0.18 database, please email me. File is 12MB (after zip 1.2MB) backed up using MySQL Adminsitrator GUI. Problem is in webapps.audit_trail table.
[13 Jul 2004 3:02] Matt Lord
This should be documented, it's not easily found in the manual.

Phillip,

If you use mysqldump to backup and restore your tables you will be fine.
[13 Jul 2004 15:32] Phillip Qin
The upgrade 4.0 to 4.1 section should be updated so people know that timestamp would cause trouble if they don't use mysqldump to restore 4.0 DB to 4.1.
[27 Jul 2004 9:54] Heikki Tuuri
Matthew, Dmitri,

is this the expected MySQL behavior in 4.1.3?

Nothing has changed inside InnoDB. What has changed in MySQL?

Regards,

Heikki
[28 Jul 2004 8:53] Dmitry Lenev
Hi, Heikki and Qin!

At the same time when we have changed TIMESTAMP format (from 20030101000000 to '2003-01-01 00:00:00') we forgot to leave internal representation of this field unsigned... hence behavior you can observe.

I have proposed a patch fixing this.

ChangeSet
  1.1936 04/07/28 10:49:21 dlenev@brandersnatch.localdomain +6 -0
  Fix for bug #4492.
  TIMESTAMP columns should be unsigned to preserve compatibility with 4.0
  (Or else InnoDB will return different internal TIMESTAMP values when user upgrades to 4.1)
[30 Jul 2004 9:39] Dmitry Lenev
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

ChangeSet 1.1935.1.1 2004/07/28 10:49:21 dlenev@brandersnatch.localdomain
  Fix for bug #4492.
  TIMESTAMP columns should be unsigned to preserve compatibility with 4.0
  (Or else InnoDB will return different internal TIMESTAMP values when user upgrades to 4.1).
[12 Nov 2004 13:30] Miguel Dias
To fix a running table, would it not be possible to just execute something like 

update table set TimeField = from_unixtime(unix_timestamp(TimeField) - 1008272752);

What would be the sideeffects of this ?
[30 Dec 2004 5:59] Rob Beckett
In response to Miguel Dias regarding the quick query to fix the problem - tried it on a 500,000 row table that gained timestamps in 2036 after the upgrade... no dice, they all went to zeroed out dates.

Unfortunately the most recent backup I've found so far has half that many rows. :-/
[10 Mar 2005 15:59] Fabio Venuti
The fix suggested by Miguel Dias on running tables does work for me, but I need first to drop the constraint "on update CURRENT_TIMESTAMP", then also the amount of seconds I have to subtract is different (basically, one more hour = 3600 sec). So this is what I did:

ALTER TABLE table MODIFY TimeField timestamp;
UPDATE table set TimeField = from_unixtime(unix_timestamp(TimeField) -
1008276352) where Id<MaxWrongDateIndex;
ALTER TABLE table MODIFY TimeField timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;

Where MaxWrongDateIndex is the index of the last entry with wrong timestamp. This because after the upgrade the new entries added have the correct timestamp and don't want to change those.

Fabio