MySQL Bugs: #37403: datetime type data does not show up properly when data is transfered over

Bug #37403	datetime type data does not show up properly when data is transfered over
Submitted:	13 Jun 2008 19:06	Modified:	17 Jun 2008 13:43
Reporter:	d a	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	data files, data transfered, datetime, datetime type format

Description:

I used to be running a database on a server than ran on Darwin (unix like from Apple/Mac) and mysql version 4.0.14; the server has crashed and is therefore not usable but I was able to get hold of the mysql data files. I have copied them over under the data folder in a new mysql installation version 5.0.27 through easyphp on local Windows XP machine and luckily everything has worked fine and I have access to all databases, tables and data; the only strange thing is that the fields that were of type datetime do not show up properly. for instance "2007-03-21 12:35:51" shows up as "?/,(-*--)'' 95:86:56" 

is this a bug? any idea what the reason is and how it may be fixed !!?

Thanks in advance.

p.s. here is the show create table on the new installation:

CREATE TABLE `video_comments` (\n  `comment_id` int(10) NOT NULL auto_increment,\n  `clipId` int(9) NOT NULL default '0',\n  `comment` text NOT NULL,\n  `userId` int(9) NOT NULL default '0',\n  `date_posted` datetime NOT NULL default '0000-00-00 00:00:00',\n  PRIMARY KEY  (`comment_id`)\n) ENGINE=MyISAM AUTO_INCREMENT=563 DEFAULT CHARSET=latin1

How to repeat:
!

Suggested fix:
!

Thank you for the bug report. I assume that you did a simple copy/paste of your 4.1.XX crashed tables to the new data directory without performing the recommendation described at:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

My suggestion is if you still have the original 4.1 crashed tables to install a 4.1 server and then try to repair the tables according intructions:

http://dev.mysql.com/doc/refman/4.1/en/check-table.html
http://dev.mysql.com/doc/refman/4.1/en/table-maintenance.html

then apply the instructions for upgrading mentioned above. Your 5.0.27 server is quite older so install the newer 5.0.XX version. Thanks in advance.

Hi and thanks for the reply;

the problem is that it wasn't 4.1, it was 4.0.14 and 4.0 is no longer available for download. does it matter that it was previously on darwin which is unix like and now on win xp !?

Yes the upgrading process is important, please read carefully the Manual chapter I have pointed you. Try to perform the check tables and dumping process with 4.1 server. Thanks in advance.