Does sync-diff-inspector support TiDB-to-TiDB synchronization with identical database and table names? If so, why am I getting an error? Is there an issue with the configuration file?

translator_bot · June 22, 2024, 3:23am

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sync-diff-inspector是否支持上下游均为tidb，而且库名与表名均相同的同步任务，如果支持的话，麻烦看下我这个为什么会报错，是配置文件有问题吗

| username: TiDBer_nrpaXrZw

Does sync-diff-inspector support synchronization tasks where both the upstream and downstream are TiDB, and the database and table names are the same? If it does, could you please check why I am getting an error? Is there an issue with my configuration file?

Configuration:

# Diff Configuration.

######################### Global config #########################

# Number of threads for checking data. The number of connections to the upstream and downstream databases will be slightly higher than this value.
check-thread-count = 4

# If enabled, SQL statements for fixing inconsistencies will be output if there are discrepancies in the tables.
export-fix-sql = true

# Only compare table structures without comparing data.
check-struct-only = false

######################### Datasource config #########################

[data-sources]

[data-sources.mysql1] # mysql1 is a custom ID uniquely identifying this database instance, used in task.source-instances/task.target-instance below.
host = "10.30.23.126"
port = 3306
user = "root"
password = "TiDB666=$"
route-rules = ["rule1"]

#[data-sources.mysql2] # mysql1 is a custom ID uniquely identifying this database instance, used in task.source-instances/task.target-instance below.
# host = "10.30.23.192"
# port = 3306
# user = "root"
# password = "TiDB666=$"
# (Optional) Use mapping rules to match multiple upstream sharded tables. Rule1 and rule2 are defined in the Routes configuration section below.
#route-rules = ["rule1", "rule2"]
# route-rules = ["rule1"]

[data-sources.tidb0]
host = "10.30.23.176"
port = 3306
user = "root"
password = "TiDB666=$"
# (Optional) Use TiDB's snapshot feature. If enabled, historical data will be used for comparison.
# snapshot = "442935976656896001"

########################### Routes ###########################

# If you need to compare data from a large number of tables with different database or table names, or to verify data from multiple upstream sharded tables against a downstream consolidated table, you can set up mapping relationships using table-rule.
# You can configure only the schema or table mapping relationships, or both.

[routes]

[routes.rule1] # rule1 is a custom ID uniquely identifying this configuration, used in data-sources.route-rules above.
schema-pattern = "SJZX" # Matches the database name in the data source. Supports wildcards "*" and "?".
table-pattern = "TAB_110_FKB" # Matches the table name in the data source. Supports wildcards "*" and "?".
#table-pattern = "*"
target-schema = "SJZX" # Target database name.
target-table = "TAB_110_FKB" # Target table name.
#target-table = "new"

#[routes.rule2]
#schema-pattern = "test2_*" # Matches the database name in the data source. Supports wildcards "*" and "?".
#table-pattern = "t2_*" # Matches the table name in the data source. Supports wildcards "*" and "?".
#target-schema = "test2" # Target database name.
#target-table = "t2" # Target table name.

######################### Task config #########################

# Configure the tables in the *target database* that need to be compared.

[task]

# output-dir will save the following information:
# 1 sql: SQL files generated to fix errors, with one chunk corresponding to one file.
# 2 log: sync-diff.log saves log information.
# 3 summary: summary.txt saves the summary.
# 4 checkpoint: a directory saves checkpoint information for resuming interrupted tasks.

output-dir = "./output"

# Upstream database, the content is the unique ID declared in data-sources.
source-instances = ["mysql1"]

# Downstream database, the content is the unique ID declared in data-sources.
target-instance = "tidb0"

# Tables in the downstream database that need to be compared. Each table must include the database name and table name, separated by a `.`.
# Use ? to match any single character; use * to match any sequence of characters. For detailed matching rules, refer to the golang regexp package: https://github.com/google/re2/wiki/Syntax
target-check-tables = ["SJZX.TAB_110_FKB"]

# (Optional) Additional configuration for some tables, where config1 is defined in the Table config section below.
# target-configs = ["config1"]

######################### Table config #########################

# Special configuration for some tables. The configured tables must be included in task.target-check-tables.
#[table-configs.config1] # config1 is a custom ID uniquely identifying this configuration, used in task.target-configs above.
# Target table name. You can use regex to match multiple tables, but a table cannot be matched by multiple special configurations.
#target-tables = ["SJZX.TAB_110_FKB"]
# (Optional) Specify the range of data to be checked. It must conform to the syntax of the WHERE clause in SQL.
#range = "age > 10 AND age < 20"
# (Optional) Specify the columns used to divide chunks. If not configured, sync-diff-inspector will select some appropriate columns (primary key/unique key/index).
#index-fields = ["col1","col2"]
# (Optional) Ignore the check for certain columns, such as some types (json, bit, blob, etc.) that sync-diff-inspector currently does not support, or floating-point data that may differ between TiDB and MySQL. You can use ignore-columns to ignore these columns.
#ignore-columns = ["",""]
# (Optional) Specify the chunk size for dividing this table. If not specified, you can delete it or set it to 0.
#chunk-size = 0
# (Optional) Specify the collation for this table. If not specified, you can delete it or set it to an empty string.
#collation = ""

Error log:

[2023/07/18 16:32:16.918 +08:00] [INFO] [printer.go:46] ["Welcome to sync_diff_inspector"] ["Release Version"=v6.1.1-dirty] ["Git Commit Hash"=b505953aaa9ed289b71f3000cb7cb54fd09cd3e2] ["Git Branch"=heads/refs/tags/v6.1.1] ["UTC Build Time"="2022-08-23 08:25:33"] ["Go Version"=go1.18.5]
[2023/07/18 16:32:16.919 +08:00] [INFO] [main.go:101] [config="{\"check-thread-count\":4,\"split-thread-count\":5,\"export-fix-sql\":true,\"check-struct-only\":false,\"dm-addr\":\"\",\"dm-task\":\"\",\"data-sources\":{\"mysql1\":{\"host\":\"10.30.23.126\",\"port\":3306,\"user\":\"root\",\"password\":\"TiDB666=$\",\"sql-mode\":\"\",\"snapshot\":\"\",\"route-rules\":[\"rule1\"],\"Router\":{\"Selector\":{}},\"Conn\":null},\"tidb0\":{\"host\":\"10.30.23.176\",\"port\":3306,\"user\":\"root\",\"password\":\"TiDB666=$\",\"sql-mode\":\"\",\"snapshot\":\"\",\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null}},\"routes\":{\"rule1\":{\"schema-pattern\":\"sjzx\",\"table-pattern\":\"tab_110_fkb\",\"target-schema\":\"SJZX\",\"target-table\":\"TAB_110_FKB\"}},\"table-configs\":null,\"task\":{\"source-instances\":[\"mysql1\"],\"source-routes\":null,\"target-instance\":\"tidb0\",\"target-check-tables\":[\"SJZX.TAB_110_FKB\"],\"target-configs\":null,\"output-dir\":\"./output\",\"SourceInstances\":[{\"host\":\"10.30.23.126\",\"port\":3306,\"user\":\"root\",\"password\":\"TiDB666=$\",\"sql-mode\":\"\",\"snapshot\":\"\",\"route-rules\":[\"rule1\"],\"Router\":{\"Selector\":{}},\"Conn\":null}],\"TargetInstance\":{\"host\":\"10.30.23.176\",\"port\":3306,\"user\":\"root\",\"password\":\"TiDB666=$\",\"sql-mode\":\"\",\"snapshot\":\"\",\"route-rules\":null,\"Router\":{\"Selector\":{}},\"Conn\":null},\"TargetTableConfigs\":null,\"TargetCheckTables\":[{}],\"FixDir\":\"output/fix-on-tidb0\",\"CheckpointDir\":\"output/checkpoint\",\"HashFile\":\"\"},\"ConfigFile\":\"./sync.yaml\",\"PrintVersion\":false}"]
[2023/07/18 16:32:16.935 +08:00] [FATAL] [main.go:120] ["failed to initialize diff process"] [error="add shield rule failed [schema = SJZX] [table = TAB_110_FKB]"] [errorVerbose="add shield rule failed [schema = SJZX] [table = TAB_110_FKB]\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/source.NewSources\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/source/source.go:156\nmain.(*Diff).init\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:139\nmain.NewDiff\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/diff.go:97\nmain.checkSyncState\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:117\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:104\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1571"] [stack="main.checkSyncState\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:120\nmain.main\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb-tools/sync_diff_inspector/main.go:104\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"]

translator_bot · June 22, 2024, 3:23am

| username: tidb菜鸟一只 | Original post link

Are the database names and table names all in uppercase?

translator_bot · June 22, 2024, 3:23am

| username: 啦啦啦啦啦 | Original post link

It is definitely supported. I recently did an upstream and downstream comparison. The upstream and downstream database table names are the same, so try deleting all the routing-related ones and try again.

translator_bot · June 22, 2024, 3:23am

| username: 啦啦啦啦啦 | Original post link

This is a configuration file I used previously for comparing single tables, you can refer to it:

check-thread-count = 16
export-fix-sql = true
check-struct-only = false

[data-sources]
[data-sources.tidb1]

    host = "xx.xx.xx.xx"
    port = 4000
    user = "xxx"
    password = "xxxx"
    snapshot = "442818765228867661"
[data-sources.tidb2]

    host = "xx.xx.xx.x"
    port = 4000
    user = "xxxx"
    password = "xxxx"
    snapshot = "442818765259538435"
[task]

    output-dir = "./output"
    source-instances = ["tidb1"]
    target-instance = "tidb2"
    target-check-tables = ["xxx.xxx"]
    target-configs = ["config1"]
[table-configs.config1] # config1 is the unique identifier for this configuration, custom id, used in task.target-configs above
chunk-size = 10000000