Bug #35059 SHOW BINLOG EVENTS does not contain SET TIMESTAMP
Submitted: 5 Mar 2008 0:36 Modified: 5 Mar 2008 20:52
Reporter: Dathan Pattishall Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.51 OS:Any (Linux 2.6.9-22.12.y1-64 #5 SMP Fri Nov 4 13:39:56 PST 2005 x86_64 x86_64 x)
Assigned to: CPU Architecture:Any
Tags: mysqlbinlog, replication, SHOW BINLOG EVENTS

[5 Mar 2008 0:36] Dathan Pattishall
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.
[5 Mar 2008 20:52] Sveta Smirnova
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #4270