Open kennytm opened 8 months ago
The collation
setting is only applied to the end of ORDER BY
, which only affects the last column with a composite PK (or no PK)
This patch should be applied (this is not the final correct solution because it will amplify #760):
diff --git a/pkg/diff/diff.go b/pkg/diff/diff.go
index 06242f9..b76021b 100644
--- a/pkg/diff/diff.go
+++ b/pkg/diff/diff.go
@@ -986,11 +986,11 @@ func getChunkRows(ctx context.Context, db *sql.DB, schema, table string, tableIn
}
for i, key := range orderKeys {
- orderKeys[i] = dbutil.ColumnName(key)
+ orderKeys[i] = dbutil.ColumnName(key) + collation
}
- query := fmt.Sprintf("SELECT /*!40001 SQL_NO_CACHE */ %s FROM %s WHERE %s ORDER BY %s%s",
- columns, dbutil.TableName(schema, table), where, strings.Join(orderKeys, ","), collation)
+ query := fmt.Sprintf("SELECT /*!40001 SQL_NO_CACHE */ %s FROM %s WHERE %s ORDER BY %s",
+ columns, dbutil.TableName(schema, table), where, strings.Join(orderKeys, ","))
log.Debug("select data", zap.String("sql", query), zap.Reflect("args", args))
rows, err := db.QueryContext(ctx, query, args...)
diff --git a/sync_diff_inspector/utils/utils.go b/sync_diff_inspector/utils/utils.go
index b8ca447..58c4748 100644
--- a/sync_diff_inspector/utils/utils.go
+++ b/sync_diff_inspector/utils/utils.go
@@ -163,11 +163,11 @@ func GetTableRowsQueryFormat(schema, table string, tableInfo *model.TableInfo, c
}
for i, key := range orderKeys {
- orderKeys[i] = dbutil.ColumnName(key)
+ orderKeys[i] = dbutil.ColumnName(key) + collation
}
- query := fmt.Sprintf("SELECT /*!40001 SQL_NO_CACHE */ %s FROM %s WHERE %%s ORDER BY %s%s",
- columns, dbutil.TableName(schema, table), strings.Join(orderKeys, ","), collation)
+ query := fmt.Sprintf("SELECT /*!40001 SQL_NO_CACHE */ %s FROM %s WHERE %%s ORDER BY %s",
+ columns, dbutil.TableName(schema, table), strings.Join(orderKeys, ","))
return query, orderKeyCols
}
Bug Report
Please answer these questions before submitting your issue. Thanks!
Only 1 row differs
5 row differs.
tidb-server -V
or runselect tidb_version();
on TiDB)?7.3.0
sync-diff-inspector 7.1.3 (master version 8811722d71222462578c24da878a59ca46c8baaf)
pump -V
ortidb-lightning -V
orsyncer -V
)?