MySQL 5.7.20 timestamp(3)回滚以后，毫秒部分右移三位

[DBA-jane]

表结构如下： 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

[danfengcao]

谢谢反馈，是python-mysql-replication的一个bug 已提交pr，https://github.com/noplay/python-mysql-replication/pull/269 在等待管理员合并

[danfengcao]

提的pr已经被合并到master，下个版本会修复

[DBA-jane]

thanks！那新版本大概什么时候发布呢？

[JiaYifei]

我也遇到了datetime(3)也会有同样问题