Repository: pingcap/tidb-tools (go)

Issue:
some line breaks and backslashes are missing in fix.sql generated by sync-diff-inspector
## Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
If possible, provide a recipe for reproducing the error.
1、upstream database :  varchar field contains some newlines and backslashes (\\\)
2、downstream database : varchar field contains some newlines and backslashes (\)
3、then use sync-diff-inspector compare data,
4、Some line breaks and backslashes are missing in the _**fix.sql**_ 

2. What did you expect to see?
fix.sql have all line breaks and backslashes , just like upsteams data row.


3. What did you see instead?
Some line breaks and backslashes are missing in the _**fix.sql**_ 

4. What version of TiDB are you using (`tidb-server -V` or run `select tidb_version();` on TiDB)?
upstream : v3.0.8
downstream: v6.5.7
sync-diff-inspector : v6.5.3

5. which tool are you using?
sync-diff-inspector : v6.5.3

6. what versionof tool are you using (`pump -V` or `tidb-lightning -V` or `syncer -V`)?


Pull request:
sync_diff_inspector: process special character in generated fix SQL
<!--
Thank you for contributing to TiDB! Please read TiDB's [CONTRIBUTING](https://github.com/pingcap/tidb/blob/master/CONTRIBUTING.md) document **BEFORE** filing this PR.
-->

### What problem does this PR solve?
<!--
Please create an issue first to describe the problem.

There MUST be one line starting with "Issue Number:  " and 
linking the relevant issues via the "close" or "ref".

For more info, check https://pingcap.github.io/tidb-dev-guide/contribute-to-tidb/contribute-code.html#referring-to-an-issue.
-->

Issue Number: close #833

### What is changed and how it works?

Add missing process for characters like backslashes (\\) in fix SQL.

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test
 - Integration test

Related changes

 - Need to be included in the release note

```
process special characters like backslashes in generated fix SQL.
```


Patch:
diff --git a/sync_diff_inspector/utils/utils.go b/sync_diff_inspector/utils/utils.go
index 382bfe0..b7277db 100644
--- a/sync_diff_inspector/utils/utils.go
+++ b/sync_diff_inspector/utils/utils.go
@@ -172,6 +172,14 @@ func GetTableRowsQueryFormat(schema, table string, tableInfo *model.TableInfo, c
 	return query, orderKeyCols
 }
 
+// escapeString escapes special characters in the given string
+func escapeString(bs []byte) string {
+	s := string(bs)
+	s = strings.Replace(s, "\\", "\\\\", -1)
+	s = strings.Replace(s, "'", "\\'", -1)
+	return s
+}
+
 // GenerateReplaceDML returns the insert SQL for the specific row values.
 func GenerateReplaceDML(data map[string]*dbutil.ColumnData, table *model.TableInfo, schema string) string {
 	colNames := make([]string, 0, len(table.Columns))
@@ -191,7 +199,7 @@ func GenerateReplaceDML(data map[string]*dbutil.ColumnData, table *model.TableIn
 			if dbutil.IsBlobType(col.FieldType.GetType()) || IsBinaryColumn(col) {
 				values = append(values, fmt.Sprintf("x'%x'", data[col.Name.O].Data))
 			} else {
-				values = append(values, fmt.Sprintf("'%s'", strings.Replace(string(data[col.Name.O].Data), "'", "\\'", -1)))
+				values = append(values, fmt.Sprintf("'%s'", escapeString(data[col.Name.O].Data)))
 			}
 		} else {
 			values = append(values, string(data[col.Name.O].Data))
@@ -228,7 +236,7 @@ func GenerateReplaceDMLWithAnnotation(source, target map[string]*dbutil.ColumnDa
 				if dbutil.IsBlobType(col.FieldType.GetType()) || IsBinaryColumn(col) {
 					value1 = fmt.Sprintf("x'%x'", data1.Data)
 				} else {
-					value1 = fmt.Sprintf("'%s'", strings.Replace(string(data1.Data), "'", "\\'", -1))
+					value1 = fmt.Sprintf("'%s'", escapeString(data1.Data))
 				}
 			} else {
 				value1 = string(data1.Data)
@@ -253,7 +261,7 @@ func GenerateReplaceDMLWithAnnotation(source, target map[string]*dbutil.ColumnDa
 				if dbutil.IsBlobType(col.FieldType.GetType()) || IsBinaryColumn(col) {
 					values2 = append(values2, fmt.Sprintf("x'%x'", data1.Data))
 				} else {
-					values2 = append(values2, fmt.Sprintf("'%s'", strings.Replace(string(data2.Data), "'", "\\'", -1)))
+					values2 = append(values2, fmt.Sprintf("'%s'", escapeString(data2.Data)))
 				}
 			} else {
 				values2 = append(values2, string(data2.Data))
@@ -293,7 +301,7 @@ func GenerateDeleteDML(data map[string]*dbutil.ColumnData, table *model.TableInf
 			if dbutil.IsBlobType(col.FieldType.GetType()) || IsBinaryColumn(col) {
 				kvs = append(kvs, fmt.Sprintf("%s = x'%x'", dbutil.ColumnName(col.Name.O), data[col.Name.O].Data))
 			} else {
-				kvs = append(kvs, fmt.Sprintf("%s = '%s'", dbutil.ColumnName(col.Name.O), strings.Replace(string(data[col.Name.O].Data), "'", "\\'", -1)))
+				kvs = append(kvs, fmt.Sprintf("%s = '%s'", dbutil.ColumnName(col.Name.O), escapeString(data[col.Name.O].Data)))
 			}
 		} else {
 			kvs = append(kvs, fmt.Sprintf("%s = %s", dbutil.ColumnName(col.Name.O), string(data[col.Name.O].Data)))


Test patch:
diff --git a/sync_diff_inspector/utils/utils_test.go b/sync_diff_inspector/utils/utils_test.go
index 2131fa2..bb7c598 100644
--- a/sync_diff_inspector/utils/utils_test.go
+++ b/sync_diff_inspector/utils/utils_test.go
@@ -347,12 +347,12 @@ func TestGenerateSQLs(t *testing.T) {
 	require.Equal(t, replaceSQL, "REPLACE INTO `diff_test`.`atest`(`id`,`name`,`birthday`,`update_time`,`money`) VALUES (NULL,NULL,'2018-01-01 00:00:00','10:10:10',11.1111);")
 	require.Equal(t, deleteSQL, "DELETE FROM `diff_test`.`atest` WHERE `id` is NULL AND `name` is NULL AND `birthday` = '2018-01-01 00:00:00' AND `update_time` = '10:10:10' AND `money` = 11.1111 LIMIT 1;")
 
-	// test value with "'"
-	rowsData["name"] = &dbutil.ColumnData{Data: []byte("a'a"), IsNull: false}
+	// test value with special characters
+	rowsData["name"] = &dbutil.ColumnData{Data: []byte("\b\"\n\\1'`"), IsNull: false}
 	replaceSQL = GenerateReplaceDML(rowsData, tableInfo, "diff_test")
 	deleteSQL = GenerateDeleteDML(rowsData, tableInfo, "diff_test")
-	require.Equal(t, replaceSQL, "REPLACE INTO `diff_test`.`atest`(`id`,`name`,`birthday`,`update_time`,`money`) VALUES (NULL,'a\\'a','2018-01-01 00:00:00','10:10:10',11.1111);")
-	require.Equal(t, deleteSQL, "DELETE FROM `diff_test`.`atest` WHERE `id` is NULL AND `name` = 'a\\'a' AND `birthday` = '2018-01-01 00:00:00' AND `update_time` = '10:10:10' AND `money` = 11.1111 LIMIT 1;")
+	require.Equal(t, replaceSQL, "REPLACE INTO `diff_test`.`atest`(`id`,`name`,`birthday`,`update_time`,`money`) VALUES (NULL,'\b\"\n\\\\1\\'`','2018-01-01 00:00:00','10:10:10',11.1111);")
+	require.Equal(t, deleteSQL, "DELETE FROM `diff_test`.`atest` WHERE `id` is NULL AND `name` = '\b\"\n\\\\1\\'`' AND `birthday` = '2018-01-01 00:00:00' AND `update_time` = '10:10:10' AND `money` = 11.1111 LIMIT 1;")
 }
 
 func TestResetColumns(t *testing.T) {
diff --git a/tests/sync_diff_inspector/fix_sql/config.toml b/tests/sync_diff_inspector/fix_sql/config.toml
new file mode 100644
index 0000000..c21b0b7
--- /dev/null
+++ b/tests/sync_diff_inspector/fix_sql/config.toml
@@ -0,0 +1,43 @@
+# Diff Configuration.
+
+######################### Global config #########################
+
+# how many goroutines are created to check data
+check-thread-count = 4
+
+# set false if just want compare data by checksum, will skip select data when checksum is not equal.
+# set true if want compare all different rows, will slow down the total compare time.
+export-fix-sql = true
+
+# ignore check table's data
+check-struct-only = false
+
+######################### Databases config #########################
+[data-sources]
+[data-sources.tidb1]
+    host = "127.0.0.1"
+    port = 4001
+    user = "root"
+    password = ""
+
+[data-sources.tidb]
+    host = "127.0.0.1"
+    port = 4000
+    user = "root"
+    password = ""
+
+######################### Task config #########################
+[task]
+    # 1 fix sql: fix-on-tidb.sql
+    # 2 log: sync-diff.log
+    # 3 summary: summary.txt
+    # 4 checkpoint: a dir
+    output-dir = "/tmp/tidb_tools_test/sync_diff_inspector/output"
+
+    source-instances = ["tidb1"]
+
+    target-instance = "tidb"
+
+    # tables need to check.
+	target-check-tables = ["fix_sql_test.fix_table"]
+
diff --git a/tests/sync_diff_inspector/fix_sql/run.sh b/tests/sync_diff_inspector/fix_sql/run.sh
new file mode 100644
index 0000000..b8934fd
--- /dev/null
+++ b/tests/sync_diff_inspector/fix_sql/run.sh
@@ -0,0 +1,30 @@
+#!/bin/sh
+
+set -ex
+
+cd "$(dirname "$0")"
+OUT_DIR=/tmp/tidb_tools_test/sync_diff_inspector/output
+rm -rf $OUT_DIR
+mkdir -p $OUT_DIR
+
+for port in 4000 4001; do
+  mysql -uroot -h 127.0.0.1 -P $port -e "create database if not exists fix_sql_test;"
+  mysql -uroot -h 127.0.0.1 -P $port -e "create table fix_sql_test.fix_table(id int, col varchar(255));"
+done
+
+# Insert different values into two data sources
+mysql -uroot -h 127.0.0.1 -P 4000 -e "insert into fix_sql_test.fix_table values (1, '\b\n\\\\a\\'\"\`');"
+mysql -uroot -h 127.0.0.1 -P 4001 -e "insert into fix_sql_test.fix_table values (1, '\t\nb\\\\\\'');"
+
+echo "check result should be failed"
+sync_diff_inspector --config=./config.toml > $OUT_DIR/fix_sql_test.output || true
+check_contains "check failed!!!" $OUT_DIR/sync_diff.log
+
+echo "applying fix SQL"
+cat $OUT_DIR/fix-on-tidb/*.sql | mysql -uroot -h127.0.0.1 -P 4000
+rm -rf $OUT_DIR/*
+
+echo "check result should be pass after applying fix SQL"
+sync_diff_inspector --config=./config.toml > $OUT_DIR/fix_sql_test.output
+check_contains "check pass!!!" $OUT_DIR/sync_diff.log
+rm -rf $OUT_DIR/*