MySQL Bugs: #49970: Datetime on MySQL Server goes back automatically;

Bug #49970	Datetime on MySQL Server goes back automatically;
Submitted:	29 Dec 2009 8:22	Modified:	29 Jan 2010 16:48
Reporter:	fahad khalil	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.41-community for x64	OS:	Windows (Windows Server 2008 R2 (64bit))
Assigned to:		CPU Architecture:	Any
Tags:	mysql datetime

Description:
This is the strange issue which has occured to me for the second time.
At unknown instance of time; mysql server goes back by a span of 12 Hours.
When I restart the service, date/time goes back to normal state.

First time this happened couple of months ago on fresh installation of windows.

How to repeat:
I have no clue; the only thing which comes to my mind is that this happened only fresh installation of Windows 2008 Server.

Which version of MySQL you are using ?

Thank you for the report.

How did you see datetime goes back? Do you have wrong data in timestamp columns? NOW() and other date/time functions return wrong result? Is it possible what somebody changed timestamp session variable or time_zone variable, then inserted row or used date/time functions?

Please send us full error log file.

I used:

select now();

> It gave me time which was exactly 12 hour behind from server's current date/time

When I restarted the service;

select now();

> this gave me exactly the same date/time which was on the server.

Following is the [SERVER].err file.

091226 14:18:58 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
091226 14:18:59  InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
091226 14:18:59  InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 343 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300
091226 14:19:01  InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 343 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
091226 14:19:04  InnoDB: Started; log sequence number 0 0
091226 14:19:04 [Note] Event Scheduler: Loaded 0 events
091226 14:19:04 [Note] F:\MySQL\bin\mysqld: ready for connections.
Version: '5.1.41-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
091229  0:06:14 [Note] F:\MySQL\bin\mysqld: Normal shutdown

091229  0:06:14 [Note] Event Scheduler: Purging the queue. 0 events
091229  0:06:16 [Warning] F:\MySQL\bin\mysqld: Forcing close of thread 29716  user: 'root'

091229 13:06:16  InnoDB: Starting shutdown...
091229 13:06:17  InnoDB: Shutdown completed; log sequence number 0 44233
091229  0:06:17 [Note] F:\MySQL\bin\mysqld: Shutdown complete

091229 13:06:17 [Note] Plugin 'FEDERATED' is disabled.
091229 13:06:18  InnoDB: Started; log sequence number 0 44233
091229 13:06:18 [Note] Event Scheduler: Loaded 0 events
091229 13:06:18 [Note] F:\MySQL\bin\mysqld: ready for connections.
Version: '5.1.41-community'  socket: ''  port: 3306  MySQL Community Server (GPL)

I couldn't repeat this issue on Vista Ultimate and Home Premium X64. Looks to me is something related to the environment and not MySQL issue.

Thank you for the feedback.

This can happen in 2 scenarios:

1. MySQL server started, then OS changes time, then user issues SELECT NOW()
2. One set value of timestamp or time_zone variable, then issue query.

Please turn general log to ON, so when this happens next time we can find what caused such behavior. Also please compare OS time and time which shows SELECT NOW() after server is started.

@Sveta Smirnova

turning general log on

I am certain that neither server date/time nor timezone got changed after installation.

Will report along with general log if I face this issue again.

Thanks for the assistance.

Thank you for the feedback.

Will wait news from you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".