sync-diff-inspector比对timestamp类型异常

🪐 TiDB 技术问题
,
1

【TiDB 使用环境】生产环境
【TiDB 版本】5.3.0
【遇到的问题：问题现象及影响】
sync-diff-inspector比对timestamp类型DEFAULT ‘1970-01-01 08:00:01’ 异常

image
image756×269 21.5 KB

【复制黏贴 ERROR 报错的日志】
[2025/04/21 15:46:43.528 +08:00] [FATAL] [main.go:95] [“failed to initialize diff process”] [error=“get table xxx.xx_bill’s information error [types:1067]Invalid default value for ‘receive_time’\ngithub.com/pingcap/errors.AddStack\n\t/nfs/cache/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\t/nfs/cache/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/normalize.go:159\ngithub.com/pingcap/tidb/ddl.checkDefaultValue\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1045\ngithub.com/pingcap/tidb/ddl.processAndCheckDefaultValueAndColumn\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:4035\ngithub.com/pingcap/tidb/ddl.columnDefToCol\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:794\ngithub.com/pingcap/tidb/ddl.buildColumnAndConstraint\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:546\ngithub.com/pingcap/tidb/ddl.buildColumnsAndConstraints\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:413\ngithub.com/pingcap/tidb/ddl.buildTableInfoWithStmt\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1889\ngithub.com/pingcap/tidb/ddl.buildTableInfoWithCheck\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1833\ngithub.com/pingcap/tidb/ddl.BuildTableInfoFromAST\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1827\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfoBySQL\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/pkg/dbutil/table.go:53\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfo\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/pkg/dbutil/table.go:41\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.initTables\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:245\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.NewSources\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:113\nmain.(*Diff).init\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:139\nmain.NewDiff\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:97\nmain.checkSyncState\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:92\nmain.main\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:79\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:225\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1371”] [errorVerbose=“get table xxxx.xxx_bill’s information error [types:1067]Invalid default value for ‘receive_time’\ngithub.com/pingcap/errors.AddStack\n\t/nfs/cache/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/errors.go:174\ngithub.com/pingcap/errors.(*Error).GenWithStackByArgs\n\t/nfs/cache/mod/github.com/pingcap/errors@v0.11.5-0.20210513014640-40f9a1999b3b/normalize.go:159\ngithub.com/pingcap/tidb/ddl.checkDefaultValue\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1045\ngithub.com/pingcap/tidb/ddl.processAndCheckDefaultValueAndColumn\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:4035\ngithub.com/pingcap/tidb/ddl.columnDefToCol\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:794\ngithub.com/pingcap/tidb/ddl.buildColumnAndConstraint\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:546\ngithub.com/pingcap/tidb/ddl.buildColumnsAndConstraints\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:413\ngithub.com/pingcap/tidb/ddl.buildTableInfoWithStmt\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1889\ngithub.com/pingcap/tidb/ddl.buildTableInfoWithCheck\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1833\ngithub.com/pingcap/tidb/ddl.BuildTableInfoFromAST\n\t/nfs/cache/mod/github.com/pingcap/tidb@v1.1.0-beta.0.20211025024448-36e694bfc536/ddl/ddl_api.go:1827\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfoBySQL\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/pkg/dbutil/table.go:53\ngithub.com/pingcap/tidb-tools/pkg/dbutil.GetTableInfo\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/pkg/dbutil/table.go:41\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.initTables\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:245\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.NewSources\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:113\nmain.(*Diff).init\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:139\nmain.NewDiff\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:97\nmain.checkSyncState\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:92\nmain.main\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:79\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:225\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1371\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.initTables\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:247\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.NewSources\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:113\nmain.(*Diff).init\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:139\nmain.NewDiff\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:97\nmain.checkSyncState\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:92\nmain.main\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:79\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:225\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1371”] [stack=“main.checkSyncState\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:95\nmain.main\n\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:79\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:225”]

2

上游和下游的时区设置一致吗？

3

上下游 tidb 搜索一下 types 1067 error code 看看有没有内容。
没有的话应该是 sync diff 本身的问题，可以试试用最新版本的 sync diff 跑跑看。报错上来看可能和 sql_mode 有关联，估计需要改下 sql_mode 配置。不过 sync diff 我没看到相关配置，可能要在 tidb 上 global 修改。

1 个赞
4

sql_mode全部为空

5

一样的
±-----------------±--------------+
| Variable_name | Value |
±-----------------±--------------+
| system_time_zone | Asia/Shanghai |
| time_zone | SYSTEM |
±-----------------±--------------+

1 个赞
6

用新版本 sync diff 可以的话就用新版本 sync diff 吧。

1 个赞
7

应该就是这个bug。报告的时候说是上下游的时区不一致。

不过修复的pr里面提到，根因是

Because sync_diff_inspector currently set time_zone to UTC +0:00 as default, it is necessary to use an sessionCtx with UTC time_zone to parse the table info.

所以应该和上下游不一致没太大关系，是根本没读取时区。

晚于2023.1.6的版本都会包含这个修复。所以上面军哥说需要新版本的sync diff应该是靠谱的。

1 个赞