OGG同步关于timestamp类型数据无法写入问题

TidbH · 2019 年12 月 5 日 03:04

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

【TiDB 版本】： 5.7.25-TiDB-v3.0.4
【问题描述】: 因Oracle 源头存在timestamp类型数据在使用ogg同步到tidb的过程中发现丢失数据，经过排查初步定位到时由于timestamp类型数据有关，导致无法写入，sql_mode为严格模式，测试如下：
表结构：

show create table t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `CREATE_TIME_UTC` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

时区：

show variables like '%time_zone%';                                                                            
 +------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| time_zone        | SYSTEM |
| system_time_zone | CST    |
+------------------+--------+
2 rows in set (0.00 sec)

sql_mode模式：

select @@sql_mode;                 
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

插入数据：

insert into t1 values(1,'04-DEC-19 05.21.24.875000 PM');
ERROR 1292 (22007): Incorrect datetime value: '04-DEC-19 05.21.24.875000 PM'

修改sql_mode模式：

set sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.01 sec)

select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.01 sec)

重新插入数据：

insert into t1 values(1,'04-DEC-19 05.21.24.875000 PM');
Query OK, 1 row affected, 1 warning (0.17 sec)

 insert into t1 values(2,'04-DEC-19 03.58.43.873000 PM');
Query OK, 1 row affected, 1 warning (0.09 sec)

select* from t1;
+------+---------------------+
| id   | CREATE_TIME_UTC     |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
|    2 | 0000-00-00 00:00:00 |
+------+---------------------+
2 rows in set (0.01 sec)

插入后时间变成“0000-00-00 00:00:00”，请问这个情况如何正确写入数据和不丢失数据呢？

QBin · 2019 年12 月 5 日 05:40

目前 TiDB 支持的日期格式为 YYYY-MM-DD HH:MM:SS ( MySQL 也是) 。因此如果要插入的日期类型的字段的话建议先转成兼容的格式再进行插入。

TidbH · 2019 年12 月 5 日 06:20

其实觉得有点奇怪， 1：在严格模式下，通过ogg实时同步大部分的数据都是正常同步的（没有转换任何格式），少部分数据却不能正常同步。 2：如何不设置严格模式担心两边数据对不上。 3：根据同步的数据来看oracle和tidb 都是timestamp类型的情况，在tidb是会自动转成 YYYY-MM-DD HH:MM:SS格式，不需要单独进行转换。

QBin · 2019 年12 月 5 日 06:39

严格模式下是不允许插入不合法的日期的。
具体 SQL_mode 的设置。以及对应的行为可以参考 https://pingcap.com/docs-cn/stable/reference/sql/sql-mode/#sql-模式
麻烦提供一行类似可以插入成功的数据用来做相应的测试。

TidbH · 2019 年12 月 5 日 07:24

这个只能提供同步的数据，手工insert经过测试插入后就是0000-00-00 00:00:00 。
同步数据示例如下：
oracle端：

  ID	CREATE_TIME_UTC	UPDATE_TIME_UTC
  39229|05-DEC-19 09.45.44.997000 AM | 05-DEC-19 09.45.44.997000 AM|
  39230|05-DEC-19 09.51.36.624000 AM | 05-DEC-19 09.51.36.624000 AM|

使用ogg 同步到tidb后数据如下：

  *************************** 1. row ***************************
               id: 39229
  CREATE_TIME_UTC: 2019-12-05 09:45:44
  UPDATE_TIME_UTC: 2019-12-05 09:45:44
  *************************** 2. row ***************************
               id: 39230
  CREATE_TIME_UTC: 2019-12-05 09:51:36
  UPDATE_TIME_UTC: 2019-12-05 09:51:36
  2 rows in set (0.00 sec)

说明：两边表结构完全一致，ogg配置文件也没有对字段进行特殊转换处理。

QBin · 2019 年12 月 5 日 09:08

我这边先测试一下。

TidbH · 2019 年12 月 5 日 09:09

好的，辛苦了，有结果了，麻烦回复下。

miaoqingli-PingCAP · 2019 年12 月 5 日 14:44

这块正常 ogg 同步已经做了格式转换，所以你正常 sql_mode 格式是可以插入 12 小时制的数据的。出现数据同步问题错误，可以从两个方向入手：（1）确定是不是 Oracle 源端 extract 抽取不到，对比下丢失数据（2）开启 tidb general log 看一下 TIDB 插入报的错误。还有 TIDB timestamp 默认并且最大支持 timestamp(6),Oracle 可以支持 timestmap(9)，这块也可以看下。

TidbH · 2019 年12 月 6 日 01:50

（1）确定是不是 Oracle 源端 extract 抽取不到，对比下丢失数据关闭严格模式之后，观察了两天两边count数据是一致的，没有出现丢失情况，timestamp数据也是正常转换（数据状态是否一致，待考证）
（2）开启 tidb general log 看一下 TIDB 插入报的错误。现已开启，待观察。
（3）还有 TIDB timestamp 默认并且最大支持 timestamp(6),Oracle 可以支持 timestmap(9) 数据能够正常进行转换到TIDB，源的数据格式都是一致的，应该可以排除这个问题。
辛苦各位大佬耐心的回复

zhenjiaogao · 2019 年12 月 6 日 03:02

好的，那先观察下，如果有问题，后面继续沟通。

sanbron · 2019 年12 月 15 日 13:19

请教ogg 怎么配置replicate 进程的。我无法使用dblogin 登录 tidb

GGSCI (zy-y8-kk-test-yace07) 16> dblogin sourcedb test@172.18.3.208:4000 userid oggadmin password xxx

2019-12-15 20:36:36 WARNING OGG-00769 MySQL Login failed: . SQL error (1045). Access denied for user ‘oggadmin’@‘172.18.3.208’ (using password: NO). ERROR: Failed to connect to MySQL database engine for HOST 172.18.3.208, DATABASE test, USER oggadmin, PORT 4000.

TidbH · 2019 年12 月 17 日 03:21

检查下你的密码和权限是否正确。

张鱼小丸子-PingCAP · 2019 年12 月 17 日 04:01

bennett · 2020 年7 月 13 日 02:00

请问这个问题最后是如何解决的呢？

TidbH · 2020 年7 月 16 日 09:18

关闭数据库严格模式。

来了老弟 · 2020 年7 月 16 日 09:24

@TidbH 感谢回复
@bennett tidb 中执行 set @@global.sql_mode=’’; 也可

system · 2022 年10 月 31 日 19:04

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。