Bug #29536 timestamp inconsistent in replication around 1970
Submitted: 4 Jul 2007 1:30 Modified: 25 Aug 2007 15:43
Reporter: Adam Dixon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0.44 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: bfsm_2007_07_19

[4 Jul 2007 1:30] Adam Dixon
Description:
In replication, timestamp results are inconsistent between master and slave. See Below how to repeat.

How to repeat:
export TZ=PST8PDT
bin/mysqld_safe --user=mysql  --server-id=1 --log-bin  &
bin/mysqld_safe --user=mysql  --port=4406 --socket=/tmp/mysql4406.sock         --datadir=/usr/local/mysqlcluster/mysqldata1/ --server-id=2  --skip-slave-start &
(setup & start the slave)
On the master;

drop table if exists t2;
CREATE TABLE `t2` ( `login` varchar(24) default NULL, `date` timestamp NULL default NULL) ENGINE=MyISAM;

insert into t2 (login, date) values ('test1', now()), ('test2', NOW());

SET @@session.time_zone='Etc/GMT-9'/*!*/;
update t2 set date = '1970-01-01 09:59:59' where login = 'test1';
SET @@session.time_zone='Etc/GMT-10'/*!*/;
update t2 set date = '1970-01-01 09:59:59' where login = 'test2';
show warnings;

mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'date' at row 2 | 
+---------+------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from t2;
+-------+---------------------+
| login | date                |
+-------+---------------------+
| test1 | 1970-01-01 10:59:59 | 
| test2 | 0000-00-00 00:00:00 | 
+-------+---------------------+
2 rows in set (0.00 sec)

On the slave;
mysqlslave>

mysql> select * from t2;
+-------+---------------------+
| login | date                |
+-------+---------------------+
| test1 | 1969-12-31 16:59:59 | 
| test2 | 1969-12-31 16:59:59 | 
+-------+---------------------+
2 rows in set (0.00 sec)

Is fine if say, 1990 is used however.
[4 Jul 2007 1:30] Adam Dixon
Output from master binlog

Attachment: master.binlog.sql (text/x-sql), 2.83 KiB.

[4 Jul 2007 1:31] Adam Dixon
Output from slave relay log.

Attachment: slave.binlog.sql (text/x-sql), 1.58 KiB.

[6 Aug 2007 11:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32138

ChangeSet@1.2501, 2007-08-06 04:57:28-07:00, gkodinov@magare.gmz +4 -0
  Bug #29536: timestamp inconsistent in replication around 1970
  
  MySQL replicates the time zone only when operations that involve
  it are performed. This is controlled by a flag. But this flag
  is set only on successful operation.
  The flag must be set also when there is an error that involves
  a timezone (so the master would replicate the error to the slaves). 
  
  Fixed by moving the setting of the flag before the operation
  (so it apples to errors as well).
[15 Aug 2007 9:48] Bugs System
Pushed into 5.1.22-beta
[15 Aug 2007 9:51] Bugs System
Pushed into 5.0.48
[25 Aug 2007 15:43] Paul Dubois
Noted in 5.0.48, 5.1.22 changelogs.

Operations that used the time zone replicated the time zone only for
successful operations, but did not replicate the time zone for errors
that need to know it.