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