Description:
For a table that contains a timestamp column type, replication puts in the Binary Log File a SET TIMESTAMP=XXXXX. Yet SHOW BINLOG EVENTS does not have this information.
For example:
mysqlbinlog -j 835 dbshard-bin.000023 |more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 835
#080302 18:57:48 server id 142214196 end_log_pos 215 Query thread_id=26941859 exec_time=0 error_code=0
use DB;
SET TIMESTAMP=1204484268;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C utf8 */;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8;
DELETE FROM TABLE WHERE photo_id='X';
mysql> SHOW BINLOG EVENTS IN 'dbshard-bin.000023' FROM 835 LIMIT 1;
+--------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbshard-bin.000023 | 835 | Query | 142214196 | 215 | use `DB`; DELETE FROM TABLE WHERE photo_id='X' |
+--------------------+-----+------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
How to repeat:
Create a table with a timestamp, do a write, look at SHOW BIN LOG EVENTS notice no SET timestamp statement. Execute mysqlbinlog for and notice that SET TIMESTAMP is there.
Suggested fix:
Please add it to the same line of SHOW BIN LOG EVENTS where the use DB statement is located.