处理timestamp(3) 类型时会出问题 #90

chaigang0 · 2021-10-31T01:31:10Z

复现过程如下（mysql 5.7）：
create database ceshi;
use ceshi;
create table t1(id int primary key,sj timestamp(3));
insert into t1 select 1,'2021-10-31 10:00:00.123';
insert into t1 select 2,'2021-10-31 11:00:00.456';
insert into t1 select 3,'2021-10-31 12:00:00.789';
update t1 set id=300 where id=3;

mysql> select * from t1;
+-----+-------------------------+
| id | sj |
+-----+-------------------------+
| 1 | 2021-10-31 10:00:00.123 |
| 2 | 2021-10-31 11:00:00.456 |
| 300 | 2021-10-31 09:28:00.381 |
+-----+-------------------------+
3 rows in set (0.00 sec)

使用binlog2sql生成回滚语句
python3 binlog2sql.py -h127.0.0.1 -P3357 -uroot -p'c123456' --flashback -dceshi -t t1 --start-file='mysql-bin.000013'
结果：
UPDATE ceshi.t1 SET id=3, sj='2021-10-31 04:00:00.000789' WHERE id=300 AND sj='2021-10-31 01:28:00.000381' LIMIT 1; #start 5335 end 5518 time 2021-10-31 01:28:00
DELETE FROM ceshi.t1 WHERE id=3 AND sj='2021-10-31 04:00:00.000789' LIMIT 1; #start 5064 end 5239 time 2021-10-31 01:24:56
DELETE FROM ceshi.t1 WHERE id=2 AND sj='2021-10-31 03:00:00.000456' LIMIT 1; #start 4793 end 4968 time 2021-10-31 01:24:41
DELETE FROM ceshi.t1 WHERE id=1 AND sj='2021-10-31 02:00:00.000123' LIMIT 1; #start 4522 end 4697 time 2021-10-31 01:24:26

在毫秒的精度上，多了三个0

The text was updated successfully, but these errors were encountered:

michael-liumh · 2021-12-29T10:12:00Z

升级一下 mysql-replication==0.23 就ok

chaigang0 · 2021-12-30T03:25:23Z

非常感谢您的回复，我将安装 mysql-replication==0.23 版本，
可问题还是没有解决 /笑哭

create table t1(id int primary key,sj timestamp(3));
INSERT INTO ceshi.t1(id, sj) VALUES (1, '2021-12-29 17:02:03.456');
update t1 set sj='2021-12-29 17:02:03.789';

使用binlog2sql解析

UPDATE ceshi.t1 SET id=1, sj='2021-12-29 09:02:03.456000' WHERE id=1 AND sj='2021-12-29 09:02:03.789000' LIMIT 1; #start 570 end 757 time 2021-12-30 03:22:12
DELETE FROM ceshi.t1 WHERE id=1 AND sj='2021-12-29 09:02:03.456000' LIMIT 1; #start 299 end 474 time 2021-12-30 03:22:09

解析出来是【09:02:03.456000】、【09:02:03.789000】
我定义的表结构 timestamp 精度是3，但解析出来还是6位精度，导致解析出来的SQL语句还是没办法用

michael-liumh · 2021-12-30T04:39:57Z

你看看数据库原来存的值？我测试看，里面存的值就是6位小数的，哪怕你建表的时候定义的3位精度

…

---原始邮件--- 发件人: ***@***.***> 发送时间: 2021年12月30日(周四) 中午11:25 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [danfengcao/binlog2sql] 处理timestamp(3) 类型时会出问题 (Issue #90) 非常感谢您的回复，我将安装 mysql-replication==0.23 版本，可问题还是没有解决 /笑哭 create table t1(id int primary key,sj timestamp(3)); INSERT INTO ceshi.t1(id, sj) VALUES (1, '2021-12-29 17:02:03.456'); update t1 set sj='2021-12-29 17:02:03.789'; 使用binlog2sql解析 UPDATE ceshi.t1 SET id=1, sj='2021-12-29 09:02:03.456000' WHERE id=1 AND sj='2021-12-29 09:02:03.789000' LIMIT 1; #start 570 end 757 time 2021-12-30 03:22:12 DELETE FROM ceshi.t1 WHERE id=1 AND sj='2021-12-29 09:02:03.456000' LIMIT 1; #start 299 end 474 time 2021-12-30 03:22:09 解析出来是【09:02:03.456000】、【09:02:03.789000】我定义的表结构 timestamp 精度是3，但解析出来还是6位精度，导致解析出来的SQL语句还是没办法用 — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you commented.Message ID: ***@***.***>

michael-liumh · 2021-12-30T04:41:12Z

我测试的版本是mysql 5.7.32

…

---原始邮件--- 发件人: ***@***.***> 发送时间: 2021年12月30日(周四) 中午11:25 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [danfengcao/binlog2sql] 处理timestamp(3) 类型时会出问题 (Issue #90) 非常感谢您的回复，我将安装 mysql-replication==0.23 版本，可问题还是没有解决 /笑哭 create table t1(id int primary key,sj timestamp(3)); INSERT INTO ceshi.t1(id, sj) VALUES (1, '2021-12-29 17:02:03.456'); update t1 set sj='2021-12-29 17:02:03.789'; 使用binlog2sql解析 UPDATE ceshi.t1 SET id=1, sj='2021-12-29 09:02:03.456000' WHERE id=1 AND sj='2021-12-29 09:02:03.789000' LIMIT 1; #start 570 end 757 time 2021-12-30 03:22:12 DELETE FROM ceshi.t1 WHERE id=1 AND sj='2021-12-29 09:02:03.456000' LIMIT 1; #start 299 end 474 time 2021-12-30 03:22:09 解析出来是【09:02:03.456000】、【09:02:03.789000】我定义的表结构 timestamp 精度是3，但解析出来还是6位精度，导致解析出来的SQL语句还是没办法用 — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you commented.Message ID: ***@***.***>

chaigang0 · 2021-12-30T06:31:36Z

mysql> \s

mysql Ver 14.14 Distrib 5.7.34, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 55
Current database: ceshi
Current user: [email protected]
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.34-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3357
Uptime: 21 days 4 hours 18 min 34 sec

Threads: 4 Questions: 939 Slow queries: 0 Opens: 431 Flush tables: 1 Open tables: 362 Queries per second avg: 0.000

mysql> select * from t1;
+----+-------------------------+
| id | sj |
+----+-------------------------+
| 1 | 2021-12-29 17:02:03.789 |
+----+-------------------------+
1 row in set (0.00 sec)

我使用的mysql 5.7.34版本，正常查询出来的结果是3位小数点。
同时我分析了下binlog，写入和更新记录的都是3位小数

INSERT binlog:

INSERT INTO `ceshi`.`t1`

SET

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.456 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

UPDATE binlog:

UPDATE `ceshi`.`t1`

WHERE

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.456 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

SET

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.789 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

michael-liumh · 2021-12-31T08:29:16Z

直接插入6位小数就行，数据库会自动删除多余的精度

chaigang0 · 2021-12-31T08:33:47Z

嗯，感谢回复，
确实如你实验那样，insert into 可以忽略掉这个影响，

但是如果是生成逆向 update 语句，就会有问题了，因为指定【sj='2021-12-29 09:02:03.789000'】，时间多了三个零，数据库会找不到这条记录。

UPDATE ceshi.t1 SET id=1, sj='2021-12-29 09:02:03.456000' WHERE id=1 AND sj='2021-12-29 09:02:03.789000' LIMIT 1;

michael-liumh · 2021-12-31T11:12:29Z

可以根据主键去更新，其它条件忽略，你可以用我改过的那一版试试 https://github.com/michael-liumh/binlog2sql 使用的时候，加-KK参数，生成的update语句的条件就只有主键了

…

---原始邮件--- 发件人: ***@***.***> 发送时间: 2021年12月31日(周五) 下午4:33 收件人: ***@***.***>; 抄送: ***@***.******@***.***>; 主题: Re: [danfengcao/binlog2sql] 处理timestamp(3) 类型时会出问题 (Issue #90) 嗯，感谢回复，确实如你实验那样，insert into 可以忽略掉这个影响，但是如果是生成逆向 update 语句，就会有问题了，因为指定【sj='2021-12-29 09:02:03.789000'】，时间多了三个零，数据库会找不到这条记录。 UPDATE ceshi.t1 SET id=1, sj='2021-12-29 09:02:03.456000' WHERE id=1 AND sj='2021-12-29 09:02:03.789000' LIMIT 1; — Reply to this email directly, view it on GitHub, or unsubscribe. Triage notifications on the go with GitHub Mobile for iOS or Android. You are receiving this because you commented.Message ID: ***@***.***>

chaigang0 · 2021-12-31T15:28:57Z

感谢你的分享，我感觉你的那一版本会满足我们的需求。
同时也祝你元旦快乐~

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

处理timestamp(3) 类型时会出问题 #90

处理timestamp(3) 类型时会出问题 #90

chaigang0 commented Oct 31, 2021

michael-liumh commented Dec 29, 2021 •

edited

Loading

chaigang0 commented Dec 30, 2021

michael-liumh commented Dec 30, 2021 via email

michael-liumh commented Dec 30, 2021 via email

chaigang0 commented Dec 30, 2021

michael-liumh commented Dec 31, 2021

chaigang0 commented Dec 31, 2021

michael-liumh commented Dec 31, 2021 via email

chaigang0 commented Dec 31, 2021

处理timestamp(3) 类型时会出问题 #90

处理timestamp(3) 类型时会出问题 #90

Comments

chaigang0 commented Oct 31, 2021

michael-liumh commented Dec 29, 2021 • edited Loading

chaigang0 commented Dec 30, 2021

michael-liumh commented Dec 30, 2021 via email

michael-liumh commented Dec 30, 2021 via email

chaigang0 commented Dec 30, 2021

mysql> \s

Threads: 4 Questions: 939 Slow queries: 0 Opens: 431 Flush tables: 1 Open tables: 362 Queries per second avg: 0.000

mysql> select * from t1; +----+-------------------------+ | id | sj | +----+-------------------------+ | 1 | 2021-12-29 17:02:03.789 | +----+-------------------------+ 1 row in set (0.00 sec)

我使用的mysql 5.7.34版本，正常查询出来的结果是3位小数点。 同时我分析了下binlog，写入和更新记录的都是3位小数

INSERT INTO ceshi.t1

SET

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.456 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

UPDATE ceshi.t1

WHERE

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.456 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

SET

@1=1 /* INT meta=0 nullable=0 is_null=0 */

@2=1640768523.789 /* TIMESTAMP(3) meta=3 nullable=0 is_null=0 */

michael-liumh commented Dec 31, 2021

chaigang0 commented Dec 31, 2021

michael-liumh commented Dec 31, 2021 via email

chaigang0 commented Dec 31, 2021

michael-liumh commented Dec 29, 2021 •

edited

Loading

mysql> select * from t1;
+----+-------------------------+
| id | sj |
+----+-------------------------+
| 1 | 2021-12-29 17:02:03.789 |
+----+-------------------------+
1 row in set (0.00 sec)

我使用的mysql 5.7.34版本，正常查询出来的结果是3位小数点。
同时我分析了下binlog，写入和更新记录的都是3位小数

INSERT INTO `ceshi`.`t1`

UPDATE `ceshi`.`t1`