Bug #71035 | mysqlbinlog dumps out improperly-formatted TIMESTAMP values | ||
---|---|---|---|
Submitted: | 28 Nov 2013 15:53 | Modified: | 30 Nov 2013 22:32 |
Reporter: | Guy Paddock | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
Version: | mysqlbinlog Ver 3.4, 5.1.74, 5.5.35, 5.6.16 | OS: | Linux (CentOS release 6.4 2.6.32-358.23.2.el6.x86_64) |
Assigned to: | CPU Architecture: | Any | |
Tags: | bugs, mysqlbinlog, timestamp |
[28 Nov 2013 15:53]
Guy Paddock
[28 Nov 2013 18:09]
Sveta Smirnova
Thank you for the report. Verified as described. Not sure if we should consider this a s a bug though.
[30 Nov 2013 22:32]
Guy Paddock
How is this not considered a bug? It affects user's ability to replay SQL from the binary log.
[1 Dec 2013 8:38]
Hartmut Holzgraefe
Not a bug as the -v comments are not supposed to be real, replayable SQL statements anyway: -v, --verbose Reconstruct pseudo-SQL statements out of row events. So while it would be nice to have a more human readable form of output here this only qualifies as a feature request. If you really want to convert the -v generated pseudo-SQL into actual working SQL statements you'd need to put in quite a bit of parsing and post processing effort anyway, and having to convert raw timestamp values by e.g. wrapping them in FROM_UNIXTIME() calls looks like a rather minor issue with that ...
[30 Oct 2014 7:05]
ukits Byun
I'm sure that this is a bug. On replication, the timestamp column on WHERE cluase in DELETE or UPDATE use unix_timestamp format. the slave cannot find matched row. for example, binlog: DELETE FROM `db`.`table` WHERE @1 = 'xxx' @2 = 'yyy' @3 = 1414491155 // this is timestamp formatted column and @3 column on slave has value '2014-10-28 19:12:35'; as you know, timestamp '1414491155' is date '2014-10-28 19:12:35' the query CANNOT find rows on slave. an error occured, mysql> show slave status\G *************************** 1. row *************************** ... Replicate_Wild_Ignore_Table: Last_Errno: 1032 Last_Error: Could not execute Delete_rows event on table db.table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000021, end_log_pos 39357456 MySQL v5.6.21
[30 Oct 2014 8:47]
Hartmut Holzgraefe
Hello ukits, what you're experiencing might actually be a completely different problem, so allow me to ask for the timezone settings on your master and slave. If they both use SYSTEM timezone (the default) and master and slave operating systems are set to different timezones you may actually run into problems due to this. If that is the case the solution would be to explicitly set the timezone within mysql to the actual time zone instead of SYSTEM so that mysqld knows what conversions to apply. When a slave sees that master and slave time zone are the same it just assumes that no conversion is necessary, even in the case of SYSTEM, as it doesn't really know anything about the underlying system time zone ...