Repository: pingcap/dumpling
Pull request #37 (2020-03-04 20:28:46)

tests: some improvements to the integration tests
1. Place the password into a config file when executing `mysql`. This avoid the constant "Using a password on the command line interface can be insecure" warning (see https://stackoverflow.com/questions/20751352/suppress-warning-messages-using-mysql-from-within-terminal-but-password-written)
2. We now insert N records using 1 insert statement instead of N statements.
3. Always include `SET NAMES binary;` into the `*.sql` dump, so MySQL 5.7's CLI can accept the naughty strings 🤷‍. 

codecov-io: # [Codecov](https://codecov.io/gh/pingcap/dumpling/pull/37?src=pr&el=h1) Report
> Merging [#37](https://codecov.io/gh/pingcap/dumpling/pull/37?src=pr&el=desc) into [master](https://codecov.io/gh/pingcap/dumpling/commit/c61adf1fcc920e1c491e94f300f799883a7453c9?src=pr&el=desc) will **increase** coverage by `0.09%`.
> The diff coverage is `100%`.


```diff
@@            Coverage Diff             @@
##           master      #37      +/-   ##
==========================================
+ Coverage   74.84%   74.93%   +0.09%     
==========================================
  Files          13       13              
  Lines         787      790       +3     
==========================================
+ Hits          589      592       +3     
  Misses        148      148              
  Partials       50       50
```

GregoryIan: LGTM

Patch:
diff --git a/.travis.yml b/.travis.yml
index d6a3bd1..b900289 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -1,4 +1,5 @@
 language: go
+dist: bionic
 
 go:
   - 1.13.x
@@ -12,3 +13,5 @@ after_success:
 
 services:
   - mysql
+
+if: branch = master OR branch =~ /^release-/
\ No newline at end of file
diff --git a/go.sum b/go.sum
index 5a124a6..799969b 100644
--- a/go.sum
+++ b/go.sum
@@ -48,6 +48,7 @@ github.com/grpc-ecosystem/go-grpc-middleware v1.0.0/go.mod h1:FiyG127CGDf3tlThmg
 github.com/grpc-ecosystem/go-grpc-prometheus v1.2.0/go.mod h1:8NvIoxWQoOIhqOTXgfV/d3M/q6VIi02HzZEHgUlZvzk=
 github.com/grpc-ecosystem/grpc-gateway v1.9.0/go.mod h1:vNeuVxBJEsws4ogUvrchl83t/GYV9WGTSLVdBhOQFDY=
 github.com/hashicorp/hcl v1.0.0/go.mod h1:E5yfLk+7swimpb2L/Alb/PJmXilQ/rhwaUYs4T20WEQ=
+github.com/inconshreveable/mousetrap v1.0.0 h1:Z8tu5sraLXCXIcARxBp/8cbvlwVa7Z1NHg9XEKhtSvM=
 github.com/inconshreveable/mousetrap v1.0.0/go.mod h1:PxqpIevigyE2G7u3NXJIT2ANytuPF1OarO4DADm73n8=
 github.com/jonboulle/clockwork v0.1.0/go.mod h1:Ii8DK3G1RaLaWxj9trq07+26W01tbo22gdxWY5EU2bo=
 github.com/julienschmidt/httprouter v1.2.0/go.mod h1:SYymIcj16QtmaHHD7aYtjjsJG7VTCxuUUipMqKk8s4w=
diff --git a/v4/export/sql.go b/v4/export/sql.go
index e580850..dd83c4f 100644
--- a/v4/export/sql.go
+++ b/v4/export/sql.go
@@ -112,6 +112,9 @@ func SelectAllFromTable(conf *Config, db *sql.DB, database, table string) (Table
 		table:    table,
 		rows:     rows,
 		colTypes: colTypes,
+		specCmts: []string{
+			"/*!40101 SET NAMES binary*/;",
+		},
 	}, nil
 }
 


Test patch:
diff --git a/tests/_utils/run_sql b/tests/_utils/run_sql
index efbfd4a..59cc497 100755
--- a/tests/_utils/run_sql
+++ b/tests/_utils/run_sql
@@ -4,6 +4,5 @@ set -e
 
 echo "[$(date)] Executing SQL: $1"
 
-mysql -u "$DUMPLING_TEST_USER" -h "$DUMPLING_TEST_HOST" -P "$DUMPLING_TEST_PORT" \
-  --password="$DUMPLING_TEST_PASSWORD" --database="$DUMPLING_TEST_DATABASE" \
-  --default-character-set utf8mb4 -e "$1"
+mysql --defaults-extra-file="$DUMPLING_TEST_DIR/mysql.cnf" \
+  --database="$DUMPLING_TEST_DATABASE" -e "$1"
diff --git a/tests/_utils/run_sql_file b/tests/_utils/run_sql_file
index 56e74e4..bc73363 100755
--- a/tests/_utils/run_sql_file
+++ b/tests/_utils/run_sql_file
@@ -4,6 +4,5 @@ set -e
 
 echo "[$(date)] Executing SQL file: $1"
 
-mysql -u "$DUMPLING_TEST_USER" -h "$DUMPLING_TEST_HOST" -P "$DUMPLING_TEST_PORT" \
-  --password="$DUMPLING_TEST_PASSWORD" --database="$DUMPLING_TEST_DATABASE" \
-  --default-character-set utf8mb4 < "$1"
+mysql --defaults-extra-file="$DUMPLING_TEST_DIR/mysql.cnf" \
+  --database="$DUMPLING_TEST_DATABASE" < "$1"
diff --git a/tests/file_size/run.sh b/tests/file_size/run.sh
index 3d72725..ee52fd7 100644
--- a/tests/file_size/run.sh
+++ b/tests/file_size/run.sh
@@ -10,10 +10,7 @@ run_sql "create table t (a varchar(255))"
 chars_20="1111_0000_1111_0000_"
 
 # insert 100 records, each occupies 20 bytes
-i=0; while [ $i -lt 100 ]; do
-  run_sql "insert into t values (\"$chars_20\")"
-  i=$(( i + 1 ))
-done
+run_sql "insert into t values $(seq -s, 100 | sed "s/[0-9]\+/('$chars_20')/g")"
 
 # dumping with file size = 200 bytes
 run_dumpling -F 200
diff --git a/tests/naughty_strings/data/naughty_strings-schema-create.sql b/tests/naughty_strings/data/naughty_strings-schema-create.sql
index 3db9dbf..f04ac68 100644
--- a/tests/naughty_strings/data/naughty_strings-schema-create.sql
+++ b/tests/naughty_strings/data/naughty_strings-schema-create.sql
@@ -1,1 +1,1 @@
-CREATE DATABASE `naughty_strings` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
+CREATE DATABASE `naughty_strings` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;
diff --git a/tests/naughty_strings/data/naughty_strings.t-schema.sql b/tests/naughty_strings/data/naughty_strings.t-schema.sql
index c97bbab..f38d2eb 100644
--- a/tests/naughty_strings/data/naughty_strings.t-schema.sql
+++ b/tests/naughty_strings/data/naughty_strings.t-schema.sql
@@ -1,3 +1,3 @@
 CREATE TABLE `t` (
-  `a` text COLLATE utf8mb4_bin
+  `a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
diff --git a/tests/naughty_strings/data/naughty_strings.t.sql b/tests/naughty_strings/data/naughty_strings.t.sql
index 8e143e5..fa5d74f 100644
--- a/tests/naughty_strings/data/naughty_strings.t.sql
+++ b/tests/naughty_strings/data/naughty_strings.t.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 INSERT INTO `t` VALUES
 (''),
 ('undefined'),
diff --git a/tests/naughty_strings/run.sh b/tests/naughty_strings/run.sh
index 36a25e9..4a96c9c 100755
--- a/tests/naughty_strings/run.sh
+++ b/tests/naughty_strings/run.sh
@@ -8,4 +8,5 @@ export DUMPLING_TEST_DATABASE="naughty_strings"
 run_sql_file "$DUMPLING_BASE_NAME/data/naughty_strings.t-schema.sql"
 run_sql_file "$DUMPLING_BASE_NAME/data/naughty_strings.t.sql"
 run_dumpling
-diff "$DUMPLING_BASE_NAME/data" "$DUMPLING_OUTPUT_DIR"
+# FIXME should compare the schemas too, but they differ too much among MySQL versions.
+diff "$DUMPLING_BASE_NAME/data/naughty_strings.t.sql" "$DUMPLING_OUTPUT_DIR/naughty_strings.t.sql"
diff --git a/tests/primary_key/result/pk_case_0.sql b/tests/primary_key/result/pk_case_0.sql
index 5e29d2b..60fd653 100644
--- a/tests/primary_key/result/pk_case_0.sql
+++ b/tests/primary_key/result/pk_case_0.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 INSERT INTO `pk_case_0` VALUES
 (0, 10),
 (1, 9),
diff --git a/tests/primary_key/result/pk_case_1.sql b/tests/primary_key/result/pk_case_1.sql
index 4553e76..d940c19 100644
--- a/tests/primary_key/result/pk_case_1.sql
+++ b/tests/primary_key/result/pk_case_1.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 INSERT INTO `pk_case_1` VALUES
 (0, 10),
 (1, 9),
diff --git a/tests/primary_key/result/pk_case_2.sql b/tests/primary_key/result/pk_case_2.sql
index 1d4f1c4..2364bb3 100644
--- a/tests/primary_key/result/pk_case_2.sql
+++ b/tests/primary_key/result/pk_case_2.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 INSERT INTO `pk_case_2` VALUES
 (0, 10),
 (1, 9),
diff --git a/tests/primary_key/result/pk_case_3.sql b/tests/primary_key/result/pk_case_3.sql
index ff8ddc1..42c6cb6 100644
--- a/tests/primary_key/result/pk_case_3.sql
+++ b/tests/primary_key/result/pk_case_3.sql
@@ -1,3 +1,4 @@
+/*!40101 SET NAMES binary*/;
 INSERT INTO `pk_case_3` VALUES
 (6, 4),
 (4, 6),
diff --git a/tests/run.sh b/tests/run.sh
index db6b30a..ce921d0 100755
--- a/tests/run.sh
+++ b/tests/run.sh
@@ -21,22 +21,31 @@ set -eu
 mkdir -p "$DUMPLING_TEST_DIR"
 PATH="tests/_utils:$PATH"
 
+cat > "$DUMPLING_TEST_DIR/mysql.cnf" <<EOF
+[client]
+user = ${DUMPLING_TEST_USER}
+host = ${DUMPLING_TEST_HOST}
+port = ${DUMPLING_TEST_PORT}
+password = ${DUMPLING_TEST_PASSWORD}
+default-character-set = utf8mb4
+EOF
+
 test_connection() {
-  i=0
-  while ! run_sql 'select 0 limit 0' > /dev/null; do
-      i=$((i+1))
-      if [ "$i" -gt 10 ]; then
-          echo 'Failed to ping MySQL Server'
-          exit 1
-      fi
-      sleep 3
-  done
+    i=0
+    while ! run_sql 'select 0 limit 0' > /dev/null; do
+        i=$((i+1))
+        if [ "$i" -gt 5 ]; then
+            echo 'Failed to ping MySQL Server'
+            exit 1
+        fi
+        sleep 3
+    done
 }
 
 test_connection
 
 for script in tests/*/run.sh; do
-    echo "Running test $script..."
+    echo "****************** Running test $script..."
     DUMPLING_BASE_NAME="$(dirname "$script")"
     export DUMPLING_BASE_NAME
     TEST_NAME="$(basename "$(dirname "$script")")"
diff --git a/tests/views/data/views-schema-create.sql b/tests/views/data/views-schema-create.sql
index 8ae8de9..23ac4bc 100644
--- a/tests/views/data/views-schema-create.sql
+++ b/tests/views/data/views-schema-create.sql
@@ -1,1 +1,1 @@
-CREATE DATABASE `views` /*!40100 DEFAULT CHARACTER SET latin1 */;
+CREATE DATABASE `views` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;
diff --git a/tests/views/run.sh b/tests/views/run.sh
index 88172b5..9355901 100644
--- a/tests/views/run.sh
+++ b/tests/views/run.sh
@@ -3,20 +3,17 @@
 set -eu
 
 run_sql "drop database if exists views"
-run_sql "create database views"
+run_sql_file "$DUMPLING_BASE_NAME/data/views-schema-create.sql"
 export DUMPLING_TEST_DATABASE="views"
 
 run_sql "create table t (a bigint, b varchar(255))"
 run_sql "create definer = 'root'@'localhost' view v as select * from t;"
 # insert 20 records to `t`.
-i=0; while [ $i -lt 20 ]; do
-  run_sql "insert into t values ($i, \"$i\")"
-  i=$(( i + 1 ))
-done
+run_sql "insert into t values $(seq -s, 0 19 | sed 's/[0-9]\+/(\0,"\0")/g')"
 
 run_dumpling --no-views
 file_not_exist "$DUMPLING_OUTPUT_DIR/views.v-schema.sql"
 
 run_dumpling --no-views=false
-diff "$DUMPLING_BASE_NAME/data/views-schema-create.sql" "$DUMPLING_OUTPUT_DIR/views-schema-create.sql"
+#diff "$DUMPLING_BASE_NAME/data/views-schema-create.sql" "$DUMPLING_OUTPUT_DIR/views-schema-create.sql"
 diff "$DUMPLING_BASE_NAME/data/views.v-schema.sql" "$DUMPLING_OUTPUT_DIR/views.v-schema.sql"
diff --git a/v4/export/dump_test.go b/v4/export/dump_test.go
index b9e4739..213e675 100644
--- a/v4/export/dump_test.go
+++ b/v4/export/dump_test.go
@@ -94,7 +94,10 @@ func (s *testDumpSuite) TestDumpTable(c *C) {
 	c.Assert(tbDataRes.DatabaseName(), Equals, "test")
 	c.Assert(tbDataRes.TableName(), Equals, "t")
 	c.Assert(tbDataRes.ColumnCount(), Equals, uint(1))
-	c.Assert(tbDataRes.SpecialComments().HasNext(), Equals, false)
+	specCmts := tbDataRes.SpecialComments()
+	c.Assert(specCmts.HasNext(), IsTrue)
+	c.Assert(specCmts.Next(), Equals, "/*!40101 SET NAMES binary*/;")
+	c.Assert(specCmts.HasNext(), IsFalse)
 	c.Assert(tbDataRes.Rows().HasNext(), Equals, true)
 	receiver := newSimpleRowReceiver(1)
 	c.Assert(tbDataRes.Rows().Next(receiver), IsNil)