MySQL Bugs: #25397: Warning on timestamp ONLY for 2007-04-01 02:00:00, breaks replication

Bug #25397	Warning on timestamp ONLY for 2007-04-01 02:00:00, breaks replication
Submitted:	3 Jan 2007 20:39	Modified:	5 Feb 2007 9:14
Reporter:	John Cwikla	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.26-standard-log	OS:	Linux (Fedora Core 4 x64)
Assigned to:		CPU Architecture:	Any
Tags:	replication, timestamp

Description:

Maybe an April fools joke? Updating a timestamp to anytime between
2007-04-01 02:00 and 2007-04-01 02:59 generates a warning. All other dates and times seems to work. Due to warning, it breaks replication.

How to repeat:
mysql> create table t2(t timestamp);
Query OK, 0 rows affected (0.26 sec)

mysql> insert into t2 values (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> update t2 set t = "2007-04-1 02:00:00";
Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update t2 set t = "2007-04-1 03:00:00";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Hmm, ok it's basing this on trying to call the timestamp as invalid because of DST (I'm in California). DST moved this year, that's why I didn't knotice at first.

It's still a bummer that it breaks replication due to the warning.

I'll probably go add something to the doc page about DST since this isn't mentioned anywhere.

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with latest 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t2(t timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> update t2 set t = "2007-04-1 02:00:00";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+---------------------+
| t                   |
+---------------------+
| 2007-04-01 02:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.01 sec)

We are in different time zones, and April 1st will be a special day only for you, according to http://geography.about.com/cs/daylightsavings/a/dst.htm, for example.

Please, send the results of:

SHOW VARIABLES LIKE 'sql_mode';

from both your master and slave, if you think this is replication-related issue.

My sql_mode is the default for both master and slave:

mysql> show global variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | ALLOW_INVALID_DATES |
+---------------+---------------------+
1 row in set (0.00 sec)

The problem I was getting bitten by was that although the master accepted the date and time with a warning (due to daylight savings), on replication I was receiving a 1292 error "incorrect datetime" on the replicate host.

Woops, that last one was because I just set ALLOW_INVALID_DATES on the replicate to see if it would fix the replication problem.

It was the default before (nothing set) when I had the problem -- :) sorry about that.

So, this is not a bug.

Actually, it would seem that it is a bug since it's a warning on the master but then an error on the slave during replication. (Both with the default sql_mode).

That seems like a bug.

Do you have the same version, 5.0.26, on slave?

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