MySQL Bugs: #35681: mysqld slave fails to replicate timestamps after daylight saving change

Bug #35681	mysqld slave fails to replicate timestamps after daylight saving change
Submitted:	30 Mar 2008 11:10	Modified:	5 May 2008 19:29
Reporter:	Raúl García	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.45-community-log	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	daylight saving, replication, slave, timestamp

Description:
Windows Mysql slave stops discarding millisecond information giving an error message right after spring's daylight saving starts. No changes were made to any configuration.

We have 2 Windows slaves, and both have stopped. We haven't slept at all tonight due to alarms ;-)

How to repeat:
1.- Create a Linux Mysql master and a Windows Mysql slave (5.0.45-community-log and 5.0.45-community-nt-log)
2.- Set the time on both servers for 2008-03-30 01:58:00
3.- Enable daylight saving changes at least on the Windows server
4.- Insert a record on master including millisecond information such as INSERT INTO table1 SET ts="2008-03-30 02:01:13.473" (Mysql says OK, and simply discards millisecond information)
5.- Check the slave and see that it did the same without problem.
6.- Wait until 02:01:00
7.- Repeat step 4
8.- Check the slave and see that it has stopped giving this error:
Incorrect datetime value: '2008-03-30 02:01:13.473' for column 'ts' at row 1' on query...

You probably do not need master-slave configuration to check this. Chances are Windows' Mysql will do the same from 2:00AM by itself.

Right now, Windows' Mysql server does not accept this sentence: INSERT INTO table1 SET ts="2008-03-30 02:01:13.473"

Suggested fix:
Updating the Windows slave to 5.0.51a did not solve the problem

Possibly disabling some strict mode on the Windows slave would be a workaround.

Not including millisecond information would be another workaround, but that would mean modifying the application, and re-creating all slaves with master's data. Just a last resort.

Edit:

Some Linux slaves fail as well, so the problem is somewhere else.

Master has ECT (USA's East Coast Time), and so do all working slaves. Non-working slaves have different time zones, such as CET (Central European Time), so what really happened is european slaves stopped working after time change in Europe (CET to CEST saving time).

Thank you for the report.

Please provide output of SELECT @@sql_mode on both master and slave.

"SELECT @@sql_mode;" returns, in all masters and slaves, an empty record:
mysql> SELECT @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

Tomorrow I will try to set the timezone on the master to CEST, just as the failing slaves. If it's a problem related to that, failing slaves should begin working and working slaves should fail.

I usually use INSERT with the function now() in them, such as "INSERT INTO table 1 now() AS ts;". Slaves always replicate this instruction, but even if they are hours slow from master, they still get the right Timestamp instead of setting it two hours later. Could this be related?

Please also update to current version 5.0.51a. At least one related bug #29536 was fixed after version 5.0.45 has been released.

I will do that this week, but although this may probably solve the problem in the future (current replication will stay wrong, so I'll have to change all timezones), there's still something else going on, why does the master let me insert with milliseconds (discarding millisecond information), but slaves don't?

I am, in almost all cases, using a standard MySQL installation, and in all cases I get a blank sql_mode record.

Problem is not milliseconds, but not existant date due daylight saving change. Strange why slave is affected. This is why I think you can be affected by bug #29536

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

Problem was fixed after updating to latest MySQL 5.

Thank you for your support. You may close this ticket.

Thank you for the feedback.

Closed regarding to last comment.