MySQL Bugs: #4492: Wrong timestamp value after upgraded to 4.1.3-beta

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	CPU Architecture:	Any

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;

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.

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.

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.

Matthew, Dmitri,

is this the expected MySQL behavior in 4.1.3?

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

Regards,

Heikki

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)

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).

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 ?

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. :-/

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