danfengcao
MySQL 5.7.20 timestamp(3)回滚以后，毫秒部分右移三位
表结构如下：
CREATE TABLE recover_test (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
name varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
create_by varchar(50) NOT NULL DEFAULT 'system',
create_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
modify_by varchar(50) NOT NULL DEFAULT 'system',
modifytime timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
disabled tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB;
binlog日志如下：
at 14464
#180412 15:28:38 server id 736 end_log_pos 14548 CRC32 0x1693107f Query thread_id=81 exec_time=0 error_code=0 SET TIMESTAMP=1523518118.115372/!/; BEGIN /!/;
at 14548
#180412 15:28:38 server id 736 end_log_pos 14669 CRC32 0x5769e4de Rows_query
UPDATE ygop_o2o_sop.recover_test SET id=6, name='小吴' WHERE id=3 AND name='小孙'
at 14669
#180412 15:28:38 server id 736 end_log_pos 14746 CRC32 0xf3e74488 Table_map: ygop_o2o_sop.recover_test mapped to number 246
at 14746
#180412 15:28:38 server id 736 end_log_pos 14860 CRC32 0x09380bfa Update_rows: table id 246 flags: STMT_END_F
UPDATE ygop_o2o_sop.recover_test
WHERE
@1=3 /* INT meta=0 nullable=0 is_null=0 */
@2='小孙' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
@3='system' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
@4=1523517247.276 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */
@5='system' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
@6=1523517247.276 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */
@7=0 /* TINYINT meta=0 nullable=0 is_null=0 */
SET
@1=6 /* INT meta=0 nullable=0 is_null=0 */
@2='小吴' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
@3='system' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
@4=1523517247.276 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */
@5='system' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
@6=1523518118.115 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */
@7=0 /* TINYINT meta=0 nullable=0 is_null=0 */
at 14860
#180412 15:28:38 server id 736 end_log_pos 14891 CRC32 0xcff37532 Xid = 681 COMMIT/!/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog / /!*/; DELIMITER ;
End of log file
通过binlog2sql解析出来的sql如下：
python binlog2sql.py -hXXX -PXXX -uXXX -pXXX -dygop_o2o_sop -trecover_test --start-file='mysql-bin.000051' --start-datetime='2018-04-12 15:27:00'
UPDATE ygop_o2o_sop.recover_test SET name='小吴', create_by='system', disabled=0, modify_by='system', create_time='2018-04-12 15:14:07.000276', modifytime='2018-04-12 15:28:38.000115', id=6 WHERE name='小孙' AND create_by='system' AND disabled=0 AND modify_by='system' AND create_time='2018-04-12 15:14:07.000276' AND modifytime='2018-04-12 15:14:07.000276' AND id=3 LIMIT 1; #start 14464 end 14860 time 2018-04-12 15:28:38
谢谢反馈，是python-mysql-replication的一个bug 已提交pr，https://github.com/noplay/python-mysql-replication/pull/269 在等待管理员合并
