sync-diff-inspector: Error "context deadline exceeded"

[mengxian-li]

Bug Report

Please answer these questions before submitting your issue. Thanks!

We use sync-diff-inspector to compare the data of two Aurora clusters.
We have seen the following error in sync-diff-inspector when comparing table rows for large table, especially if there are massive mismatches.

[2025/02/23 23:21:32.208 +00:00] [ERROR] [report.go:404] ["Set table meet error"] [error="context deadline exceeded"] [stack="github.com/pingcap/tidb-tools/sync_diff_inspector/report.(*Report).SetTableMeetError\n\t/app/sync_diff_inspector/report/report.go:404\nmain.(*Diff).consume\n\t/app/sync_diff_inspector/diff.go:457\nmain.(*Diff).Equal.func2\n\t/app/sync_diff_inspector/diff.go:290\ngithub.com/pingcap/tidb-tools/sync_diff_inspector/utils.(*WorkerPool).Apply.func1\n\t/app/sync_diff_inspector/utils/utils.go:94"]

The context deadline error could happen in comparing checksum phase due to query taking too long.
We have tried increasing the query timeout and using larger instance types, which helped reduce the errors, but would not fix the essential problem.

We should consider improving the parallelism in comparing checksum and row data.

[joechenrh]

Hi, in sync-diff-inspector, we have already implemented concurrent data comparison. Here's an overview of the whole process:

1. Chunk Division: Each table is divided into multiple chunks. If the table has any index, we will use the index column to split chunks. If the chunk size (chunk-size) is not explicitly specified in the configuration file, we will calcuate # of chunks as max(rowCount/10000, 10000). However, if the table doesn't has any index, we will treat the whole table as one chunk.
2. Concurrent Chunk Checking: sync-diff-inspector will check all the chunks concurrently which can be specified by check-thread-count, which has a default value 4.
3. Mismatch Data Check: like the first step, if the table has indices, sync-diff-inspector will utilize it to do binary search, otherwise, it will compare the data in each chunk row by row.

Could you provide the configuration you used and table schema if possible?

[mengxian-li]

Hi, thanks for the information.
For the data comparison, yes the check division and concurrent chunk checking can help, I will share the configuration in the following comments.
But we also see query timeout for table checksum comparison which accumulates checksum for all rows in the table. I think this query can be improved with some parallelism?

[2025/04/07 23:07:14.858 +00:00] [WARN] [utils.go:807] ["execute checksum query fail"] [query="SELECT COUNT(*) as CNT, BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `col1`, `col2`, `col3`, `col4`, CONCAT(ISNULL(`col1`), ISNULL(`col2`), ISNULL(`col3`), ISNULL(`col4`)))), 1, 16), 16, 10) AS UNSIGNED) ^ CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `col1`, `col2`, `col3`, `col4`, CONCAT(ISNULL(`col1`), ISNULL(`col2`), ISNULL(`col3`), ISNULL(`col4`)))), 17, 16), 16, 10) AS UNSIGNED)) as CHECKSUM FROM `databaseName`.`tableName` WHERE ((TRUE) AND (TRUE));"] [args=null] [error="context deadline exceeded"]

[joechenrh]

But we also see query timeout for table checksum comparison which accumulates checksum for all rows in the table.

The checksum comparison is done on each chunk, so division should works.

[mengxian-li]

Hi Joe, thanks for getting back quickly.
I checked our configuration, we set check-thread-count to 16 and chunk-size is not specified.

I see there is issue with the limitRange which is set to WHERE ( ( true )AND ( true ) ) for the failed query I provided, which means that this query scans the entire table.

SELECT Count(*)                       AS CNT,
       Bit_xor(Cast(Conv(Substring(Md5(Concat_ws(',', `col1`, `col2`, `col3`,
               `col4`,
               Concat(
               Isnull(
               `col1`), Isnull(`col2`), Isnull(`col3`), Isnull(`col4`)))), 1, 16
               ), 16,
               10) AS
               UNSIGNED) ^ Cast(Conv(Substring(Md5(Concat_ws(',', `col1`, `col2`
                           ,
                           `col3`, `col4`, Concat(
                           Isnull(
                           `col1`), Isnull(`col2`), Isnull(`col3`), Isnull(
                           `col4`)))),
                           17, 16),
                           16, 10) AS
                           UNSIGNED)) AS CHECKSUM
FROM   `databasename`.`tablename`
WHERE  ( ( true )
         AND ( true ) ); 

Looking at where the limitRange is specified in the code, it should come from the chunk range info: https://github.com/pingcap/tidb-tools/blob/master/sync_diff_inspector/utils/utils.go#L815

You mentioned earlier that:

if the table doesn't has any index, we will treat the whole table as one chunk.

How does this work?

[mengxian-li]

In the PingCAP doc:

sync-diff-inspector divides data into chunks first according to TiDB statistics and you need to guarantee the accuracy of the statistics. You can manually run the analyze table {table_name} command when the TiDB server's workload is light.

Does it mean that it will split the data into chunks according to the analyzer db's statistics?
We did specify the task.analyzer-instance in our sync diff config. Do you have any suggestions of how to debug this?

[mengxian-li]

Here is my understanding of how the limitRange is derived:

1. InitChunks initiate chunk.Where clause with table range which is "TRUE" initially
2. The chunk.Where clause is only updated during binary search
3. However, this df.compareChecksumAndGetCount(ctx1, rangeInfo) happens before the binary search, which means that the chunk.Where may not be set correctly here so that the limitRange of the query in question becomes TRUE right?

Can you correct me if I'm wrong?

[joechenrh]

if the table doesn't has any index, we will treat the whole table as one chunk.

How does this work?

If you don't specify chunk-size and the table doesn't contain any index, sync-diff-inspector won't split the table and use whole table scan. Otherwise, you can find some logs like get split values by random under debug log level.

[joechenrh]

Chunks are generated from BucketIterator or RandomIterator using InitChunks, each of them represents a certian query range. During binary search, the range will continue to shrink(via Update) to locate different data

[mengxian-li]

Chunks are generated from BucketIterator or RandomIterator using InitChunks, each of them represents a certian query range. During binary search, the range will continue to shrink(via Update) to locate different data

Right, but I don't see where the chunk.where string clause is set to the chunk query range during InitChunks().

# func InitChunks(chunks []*Range, t ChunkType, firstBucketID, lastBucketID int, index int, collation, **limits string**, chunkCnt int) 
chunk.Where = fmt.Sprintf("((%s) AND (%s))", conditions, **limits**)

# in BucketIterator - splitChunkForBucket()
chunk.InitChunks(chunks, chunk.Bucket, firstBucketID, lastBucketID, beginIndex, s.table.Collation, **s.table.Range**, bucketChunkCnt)

# in RandomIterator - NewRandomIteratorWithCheckpoint()
chunk.InitChunks(chunks, chunk.Random, 0, 0, beginIndex, table.Collation, **table.Range**, bucketChunkCnt)

IIUC, the table.Range is the range specified per table in the table config, correct? So it seems to me that initChunks does not set the chunk.Where to the chunk range.
Can you double check?

[mengxian-li]

If you don't specify chunk-size and the table doesn't contain any index, sync-diff-inspector won't split the table and use whole table scan.

In our case, we do have index for the table to be compared.

[joechenrh]

Here I use a relatively small table which only contains 4 rows to do the check, and manually set the chunk size to 1. Then I can find the query is like:

[2025/04/11 09:21:56.214 +08:00] [DEBUG] [utils.go:811] ["count and checksum"] [sql="SELECT  COUNT(*) as CNT, BIT_XOR(CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))), 1, 16), 16, 10) AS UNSIGNED) ^ CAST(CONV(SUBSTRING(MD5(CONCAT_WS(',', `a`, `b`, CONCAT(ISNULL(`a`), ISNULL(`b`)))), 17, 16), 16, 10) AS UNSIGNED)) as CHECKSUM FROM `collation_test`.`t` WHERE ((((`a` COLLATE 'utf8mb4_general_ci' > ?) OR (`a` COLLATE 'utf8mb4_general_ci' = ? AND `b` > ?)) AND ((`a` COLLATE 'utf8mb4_general_ci' < ?) OR (`a` COLLATE 'utf8mb4_general_ci' = ? AND `b` <= ?))) AND (TRUE));"] [args="[\"1\",\"1\",\"2\",\"3\",\"3\",\"4\"]"]

In our case, we do have index for the table to be compared.

If possible, could you run with the same config again, but with debug log level, like sync-diff-inspector --config=config.toml -L debug and provide the log?

[joechenrh]

Right, but I don't see where the chunk.where string clause is set to the chunk query range during InitChunks().

The range is specified by Bounds stored in each chunk, which is used in chunk.ToString:

conditions, args := chunk.ToString(collation)
chunk.Where = fmt.Sprintf("((%s) AND (%s))", conditions, limits)

[mengxian-li]

The range is specified by Bounds stored in each chunk, which is used in chunk.ToString

I do see chunk bounds in the logs

[2025/04/06 05:23:46.731 +00:00] [INFO] [diff.go:288] ["global consume chunk info"] ["chunk index"="{\"table-index\":197,\"bucket-index-left\":0,\"bucket-index-right\":0,\"chunk-index\":1129,\"chunk-count\":24118}"] ["chunk bound"="[{\"column\":\"object_id\",\"lower\":\"28588896\",\"upper\":\"28628600\",\"has-lower\":true,\"has-upper\":true}]"]

But in the failed query, WHERE ( ( true ) AND ( true ) ) indicates that the conditions is not populated correctly.
I will enable debug log and post the logs here later.