Repository: pingcap/dumpling
Pull request #324 (2021-08-02 08:28:48)

prepare: filter databases before show table status
<!--
Thank you for contributing to Dumpling! Please read the [CONTRIBUTING](https://github.com/pingcap/dumpling/blob/master/CONTRIBUTING.md) document **BEFORE** filing this PR.

If you want to open the **Challenge Program** pull request, please use the following template:
https://raw.githubusercontent.com/pingcap/.github/master/.github/PULL_REQUEST_TEMPLATE/challenge-program.md

You can use it with query parameters: https://github.com/pingcap/dumpling/compare/master...${you branch}?template=challenge-program.md
-->

### What problem does this PR solve? <!--add issue link with summary if exists-->
In https://github.com/pingcap/dumpling/pull/305, we have switched the method to get table info to `SHOW TABLE STATUS`. To reduce the access to databases, we'd better filter databases before we executing `SHOW TABLE STATUS`.

### What is changed and how it works?
Filter databases before `SHOW TABLE STATUS` through `conf.Filter`.

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test
 - Integration test
 - Manual test (add detailed scripts or steps below)
Dump databases and check the log. The databases are filtered before `SHOW TABLE STATUS`.

Related changes

 - Need to cherry-pick to the release branch

### Release note

<!-- bugfixes or new feature need a release note, must in the form of a list, such as

- support -T/--tables-list argument

or if no need to be included in the release note, just add the following line

- No release note
-->
- No release note

ti-chi-bot: [REVIEW NOTIFICATION]

This pull request has not been approved.


To complete the [pull request process](https://book.prow.tidb.io/#/en/workflows/pr), please ask the reviewers in the [list](https://prow.tidb.io/tichi/repos/pingcap/dumpling/pulls/324/owners) to review by filling `/cc @reviewer` in the comment.
After your PR has acquired the required number of LGTMs, you can assign this pull request to the committer in the [list](https://prow.tidb.io/tichi/repos/pingcap/dumpling/pulls/324/owners) by filling  `/assign @committer` in the comment to help you merge this pull request.

The full list of commands accepted by this bot can be found [here](https://prow.tidb.io/command-help?repo=pingcap%2Fdumpling).

<details>

Reviewer can indicate their review by submitting an approval review.
Reviewer can cancel approval by submitting a request changes review.
</details>

<!--Review Notification Identifier-->

Patch:
diff --git a/.github/workflows/go.yml b/.github/workflows/go.yml
index c57b33b..eaf83d3 100644
--- a/.github/workflows/go.yml
+++ b/.github/workflows/go.yml
@@ -29,22 +29,113 @@ jobs:
         run: make test WITH_RACE=1
       - uses: codecov/codecov-action@v1
 
-  integration-test:
+  integration-test-mysql-5735:
     runs-on: ubuntu-latest
     timeout-minutes: 15
     strategy:
       fail-fast: true
+    services:
+      mysql:
+        image: mysql:5.7.35
+        env:
+          MYSQL_ALLOW_EMPTY_PASSWORD: yes
+        ports:
+          - 3306:3306
+        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
     steps:
       - uses: actions/checkout@v2
+      - name: Shutdown Ubuntu MySQL (SUDO)
+        run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it
       - name: Set up Go 1.16
         uses: actions/setup-go@v2
         with:
           go-version: 1.16
+      - uses: actions/cache@v2
+        with:
+          path: ~/go/pkg/mod
+          key: ${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}
+          restore-keys: |
+            ${{ runner.os }}-go-
+      - name: Get dependencies
+        run: go mod download
+      - name: Download dependencies
+        run: sh install.sh
+      - name: Integration test
+        run: make integration_test
+      - name: Set up tmate session
+        if: ${{ failure() }}
+        uses: mxschmitt/action-tmate@v3
+
+  integration-test-mysql-8026:
+    runs-on: ubuntu-latest
+    timeout-minutes: 15
+    strategy:
+      fail-fast: true
+    services:
+      mysql:
+        image: mysql:8.0.26
+        env:
+          MYSQL_ALLOW_EMPTY_PASSWORD: yes
+        ports:
+          - 3306:3306
+        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
+    steps:
+      - uses: actions/checkout@v2
+      - name: Shutdown Ubuntu MySQL (SUDO)
+        run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it
+      - name: Set up Go 1.16
+        uses: actions/setup-go@v2
+        with:
+          go-version: 1.16
+      - uses: actions/cache@v2
+        with:
+          path: ~/go/pkg/mod
+          key: ${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}
+          restore-keys: |
+            ${{ runner.os }}-go-
+      - name: Get dependencies
+        run: go mod download
+      - name: Download dependencies
+        run: sh install.sh
+      - name: Integration test
+        run: make integration_test
+      - name: Set up tmate session
+        if: ${{ failure() }}
+        uses: mxschmitt/action-tmate@v3
+
+  integration-test-mysql-8022:
+    runs-on: ubuntu-latest
+    timeout-minutes: 15
+    strategy:
+      fail-fast: true
+    services:
+      mysql:
+        image: mysql:8.0.22
+        env:
+          MYSQL_ALLOW_EMPTY_PASSWORD: yes
+        ports:
+          - 3306:3306
+        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
+    steps:
+      - uses: actions/checkout@v2
+      - name: Shutdown Ubuntu MySQL (SUDO)
+        run: sudo service mysql stop # Shutdown the Default MySQL, "sudo" is necessary, please not remove it
+      - name: Set up Go 1.16
+        uses: actions/setup-go@v2
+        with:
+          go-version: 1.16
+      - uses: actions/cache@v2
+        with:
+          path: ~/go/pkg/mod
+          key: ${{ runner.os }}-go-${{ hashFiles('**/go.sum') }}
+          restore-keys: |
+            ${{ runner.os }}-go-
+      - name: Get dependencies
+        run: go mod download
       - name: Download dependencies
         run: sh install.sh
-      - name: Start MySQL
-        run: |
-          sudo systemctl start mysql.service
-          mysqladmin -uroot -proot password ''
       - name: Integration test
         run: make integration_test
+      - name: Set up tmate session
+        if: ${{ failure() }}
+        uses: mxschmitt/action-tmate@v3
diff --git a/.gitignore b/.gitignore
index 04637f0..52a3813 100644
--- a/.gitignore
+++ b/.gitignore
@@ -4,3 +4,5 @@ coverage.txt
 .idea
 var
 fix.sql
+.vscode/
+export-20*/
diff --git a/Makefile b/Makefile
index 108c118..6067ae8 100644
--- a/Makefile
+++ b/Makefile
@@ -29,7 +29,7 @@ test: failpoint-enable
 
 integration_test: bins failpoint-enable bin/dumpling
 	@make failpoint-disable
-	./tests/run.sh
+	./tests/run.sh $(CASE)
 
 tools:
 	@echo "install tools..."
diff --git a/v4/export/block_allow_list.go b/v4/export/block_allow_list.go
index 381bea6..194be0e 100644
--- a/v4/export/block_allow_list.go
+++ b/v4/export/block_allow_list.go
@@ -8,6 +8,23 @@ import (
 	tcontext "github.com/pingcap/dumpling/v4/context"
 )
 
+func filterDatabases(tctx *tcontext.Context, conf *Config, databases []string) []string {
+	tctx.L().Debug("start to filter databases")
+	newDatabases := make([]string, 0, len(databases))
+	ignoreDatabases := make([]string, 0, len(databases))
+	for _, database := range databases {
+		if conf.TableFilter.MatchSchema(database) {
+			newDatabases = append(newDatabases, database)
+		} else {
+			ignoreDatabases = append(ignoreDatabases, database)
+		}
+	}
+	if len(ignoreDatabases) > 0 {
+		tctx.L().Debug("ignore database", zap.Strings("databases", ignoreDatabases))
+	}
+	return newDatabases
+}
+
 func filterTables(tctx *tcontext.Context, conf *Config) {
 	filterTablesFunc(tctx, conf, conf.TableFilter.MatchTable)
 }
diff --git a/v4/export/config.go b/v4/export/config.go
index 6efea6c..54933ba 100644
--- a/v4/export/config.go
+++ b/v4/export/config.go
@@ -733,3 +733,16 @@ func adjustFileFormat(conf *Config) error {
 	}
 	return nil
 }
+
+func matchMysqlBugversion(info ServerInfo) bool {
+	// if 8.0.3 <= mysql8 version < 8.0.23
+	// FLUSH TABLES WITH READ LOCK could block other sessions from executing SHOW TABLE STATUS.
+	// see more in https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-23.html
+	if info.ServerType != ServerTypeMySQL {
+		return false
+	}
+	currentVersion := info.ServerVersion
+	bugVersionStart := semver.New("8.0.2")
+	bugVersionEnd := semver.New("8.0.23")
+	return bugVersionStart.LessThan(*currentVersion) && currentVersion.LessThan(*bugVersionEnd)
+}
diff --git a/v4/export/dump.go b/v4/export/dump.go
index cd9a86b..f1191d6 100755
--- a/v4/export/dump.go
+++ b/v4/export/dump.go
@@ -851,8 +851,21 @@ func extractTiDBRowIDFromDecodedKey(indexField, key string) (string, error) {
 	return "", errors.Errorf("decoded key %s doesn't have %s field", key, indexField)
 }
 
+func getListTableTypeByConf(conf *Config) listTableType {
+	// use listTableByShowTableStatus by default because it has better performance
+	listType := listTableByShowTableStatus
+	if conf.Consistency == consistencyTypeLock {
+		// for consistency lock, we need to build the tables to dump as soon as possible
+		listType = listTableByInfoSchema
+	} else if conf.Consistency == consistencyTypeFlush && matchMysqlBugversion(conf.ServerInfo) {
+		// For some buggy versions of mysql, we need a workaround to get a list of table names.
+		listType = listTableByShowFullTables
+	}
+	return listType
+}
+
 func prepareTableListToDump(tctx *tcontext.Context, conf *Config, db *sql.Conn) error {
-	databases, err := prepareDumpingDatabases(conf, db)
+	databases, err := prepareDumpingDatabases(tctx, conf, db)
 	if err != nil {
 		return err
 	}
@@ -861,9 +874,7 @@ func prepareTableListToDump(tctx *tcontext.Context, conf *Config, db *sql.Conn)
 	if !conf.NoViews {
 		tableTypes = append(tableTypes, TableTypeView)
 	}
-	// for consistency lock, we need to build the tables to dump as soon as possible
-	asap := conf.Consistency == consistencyTypeLock
-	conf.Tables, err = ListAllDatabasesTables(tctx, db, databases, asap, tableTypes...)
+	conf.Tables, err = ListAllDatabasesTables(tctx, db, databases, getListTableTypeByConf(conf), tableTypes...)
 	if err != nil {
 		return err
 	}
@@ -1045,16 +1056,16 @@ func detectServerInfo(d *Dumper) error {
 // resolveAutoConsistency is an initialization step of Dumper.
 func resolveAutoConsistency(d *Dumper) error {
 	conf := d.conf
-	if conf.Consistency != "auto" {
+	if conf.Consistency != consistencyTypeAuto {
 		return nil
 	}
 	switch conf.ServerInfo.ServerType {
 	case ServerTypeTiDB:
-		conf.Consistency = "snapshot"
+		conf.Consistency = consistencyTypeSnapshot
 	case ServerTypeMySQL, ServerTypeMariaDB:
-		conf.Consistency = "flush"
+		conf.Consistency = consistencyTypeFlush
 	default:
-		conf.Consistency = "none"
+		conf.Consistency = consistencyTypeNone
 	}
 	return nil
 }
diff --git a/v4/export/prepare.go b/v4/export/prepare.go
index e995f8a..2a6d7a1 100644
--- a/v4/export/prepare.go
+++ b/v4/export/prepare.go
@@ -10,6 +10,8 @@ import (
 	"text/template"
 
 	"github.com/pingcap/errors"
+
+	tcontext "github.com/pingcap/dumpling/v4/context"
 )
 
 const (
@@ -75,10 +77,14 @@ func ParseOutputFileTemplate(text string) (*template.Template, error) {
 	return template.Must(DefaultOutputFileTemplate.Clone()).Parse(text)
 }
 
-func prepareDumpingDatabases(conf *Config, db *sql.Conn) ([]string, error) {
+func prepareDumpingDatabases(tctx *tcontext.Context, conf *Config, db *sql.Conn) ([]string, error) {
 	databases, err := ShowDatabases(db)
+	if err != nil {
+		return nil, err
+	}
+	databases = filterDatabases(tctx, conf, databases)
 	if len(conf.Databases) == 0 {
-		return databases, err
+		return databases, nil
 	}
 	dbMap := make(map[string]interface{}, len(databases))
 	for _, database := range databases {
diff --git a/v4/export/sql.go b/v4/export/sql.go
index 4d7a03a..76c8edb 100644
--- a/v4/export/sql.go
+++ b/v4/export/sql.go
@@ -12,6 +12,8 @@ import (
 	"strconv"
 	"strings"
 
+	"github.com/go-sql-driver/mysql"
+
 	tcontext "github.com/pingcap/dumpling/v4/context"
 
 	"github.com/pingcap/errors"
@@ -20,7 +22,17 @@ import (
 	"go.uber.org/zap"
 )
 
-const orderByTiDBRowID = "ORDER BY `_tidb_rowid`"
+const (
+	orderByTiDBRowID = "ORDER BY `_tidb_rowid`"
+)
+
+type listTableType int
+
+const (
+	listTableByInfoSchema listTableType = iota
+	listTableByShowFullTables
+	listTableByShowTableStatus
+)
 
 // ShowDatabases shows the databases of a database server.
 func ShowDatabases(db *sql.Conn) ([]string, error) {
@@ -49,6 +61,13 @@ func ShowCreateDatabase(db *sql.Conn, database string) (string, error) {
 	}
 	query := fmt.Sprintf("SHOW CREATE DATABASE `%s`", escapeString(database))
 	err := simpleQuery(db, query, handleOneRow)
+	if mysqlErr, ok := errors.Cause(err).(*mysql.MySQLError); ok {
+		// Falling back to simple create statement for MemSQL/SingleStore, because of this:
+		// ERROR 1706 (HY000): Feature 'SHOW CREATE DATABASE' is not supported by MemSQL.
+		if mysqlErr.Number == 1706 {
+			return fmt.Sprintf("CREATE DATABASE `%s`", escapeString(database)), nil
+		}
+	}
 	if err != nil {
 		return "", errors.Annotatef(err, "sql: %s", query)
 	}
@@ -143,9 +162,11 @@ func RestoreCharset(w io.StringWriter) {
 }
 
 // ListAllDatabasesTables lists all the databases and tables from the database
-// if asap is true, will use information_schema to get table info in one query
-// if asap is false, will use show table status for each database because it has better performance according to our tests
-func ListAllDatabasesTables(tctx *tcontext.Context, db *sql.Conn, databaseNames []string, asap bool, tableTypes ...TableType) (DatabaseTables, error) { // revive:disable-line:flag-parameter
+// listTableByInfoSchema list tables by table information_schema in MySQL
+// listTableByShowTableStatus has better performance than listTableByInfoSchema
+// listTableByShowFullTables is used in mysql8 version [8.0.3,8.0.23), more details can be found in the comments of func matchMysqlBugversion
+func ListAllDatabasesTables(tctx *tcontext.Context, db *sql.Conn, databaseNames []string,
+	listType listTableType, tableTypes ...TableType) (DatabaseTables, error) { // revive:disable-line:flag-parameter
 	dbTables := DatabaseTables{}
 	var (
 		schema, table, tableTypeStr string
@@ -153,11 +174,13 @@ func ListAllDatabasesTables(tctx *tcontext.Context, db *sql.Conn, databaseNames
 		avgRowLength                uint64
 		err                         error
 	)
-	if asap {
-		tableTypeConditions := make([]string, len(tableTypes))
-		for i, tableType := range tableTypes {
-			tableTypeConditions[i] = fmt.Sprintf("TABLE_TYPE='%s'", tableType)
-		}
+
+	tableTypeConditions := make([]string, len(tableTypes))
+	for i, tableType := range tableTypes {
+		tableTypeConditions[i] = fmt.Sprintf("TABLE_TYPE='%s'", tableType)
+	}
+	switch listType {
+	case listTableByInfoSchema:
 		query := fmt.Sprintf("SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE %s", strings.Join(tableTypeConditions, " OR "))
 		for _, schema := range databaseNames {
 			dbTables[schema] = make([]*TableInfo, 0)
@@ -188,7 +211,28 @@ func ListAllDatabasesTables(tctx *tcontext.Context, db *sql.Conn, databaseNames
 		}, query); err != nil {
 			return nil, errors.Annotatef(err, "sql: %s", query)
 		}
-	} else {
+	case listTableByShowFullTables:
+		for _, schema = range databaseNames {
+			dbTables[schema] = make([]*TableInfo, 0)
+			query := fmt.Sprintf("SHOW FULL TABLES FROM `%s` WHERE %s",
+				escapeString(schema), strings.Join(tableTypeConditions, " OR "))
+			if err = simpleQueryWithArgs(db, func(rows *sql.Rows) error {
+				var err2 error
+				if err2 = rows.Scan(&table, &tableTypeStr); err != nil {
+					return errors.Trace(err2)
+				}
+				tableType, err2 = ParseTableType(tableTypeStr)
+				if err2 != nil {
+					return errors.Trace(err2)
+				}
+				avgRowLength = 0 // can't get avgRowLength from the result of `show full tables` so hardcode to 0 here
+				dbTables[schema] = append(dbTables[schema], &TableInfo{table, avgRowLength, tableType})
+				return nil
+			}, query); err != nil {
+				return nil, errors.Annotatef(err, "sql: %s", query)
+			}
+		}
+	default:
 		queryTemplate := "SHOW TABLE STATUS FROM `%s`"
 		selectedTableType := make(map[TableType]struct{})
 		for _, tableType = range tableTypes {
@@ -642,7 +686,14 @@ func createConnWithConsistency(ctx context.Context, db *sql.DB) (*sql.Conn, erro
 	query = "START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */"
 	_, err = conn.ExecContext(ctx, query)
 	if err != nil {
-		return nil, errors.Annotatef(err, "sql: %s", query)
+		// Some MySQL Compatible databases like Vitess and MemSQL/SingleStore
+		// are newer than 4.1.8 (the version comment) but don't actually support
+		// `WITH CONSISTENT SNAPSHOT`. So retry without that if the statement fails.
+		query = "START TRANSACTION"
+		_, err = conn.ExecContext(ctx, query)
+		if err != nil {
+			return nil, errors.Annotatef(err, "sql: %s", query)
+		}
 	}
 	return conn, nil
 }


Test patch:
diff --git a/tests/quote/data/quote-database-schema-create-mysql57.sql b/tests/quote/data/quote-database-schema-create-mysql57.sql
new file mode 100755
index 0000000..a5df1c2
--- /dev/null
+++ b/tests/quote/data/quote-database-schema-create-mysql57.sql
@@ -0,0 +1,2 @@
+/*!40101 SET NAMES binary*/;
+CREATE DATABASE `quo``te/database` /*!40100 DEFAULT CHARACTER SET latin1 */;
diff --git a/tests/quote/data/quote-database.quote-table-schema-mysql57.sql b/tests/quote/data/quote-database.quote-table-schema-mysql57.sql
new file mode 100755
index 0000000..b3c55de
--- /dev/null
+++ b/tests/quote/data/quote-database.quote-table-schema-mysql57.sql
@@ -0,0 +1,7 @@
+/*!40101 SET NAMES binary*/;
+CREATE TABLE `quo``te/table` (
+  `quo``te/col` int(11) NOT NULL,
+  `a` int(11) DEFAULT NULL,
+  `gen``id` int(11) GENERATED ALWAYS AS (`quo``te/col`) VIRTUAL,
+  PRIMARY KEY (`quo``te/col`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
diff --git a/tests/quote/data/quote-database.quote-table.000000000-mysql57.sql b/tests/quote/data/quote-database.quote-table.000000000-mysql57.sql
new file mode 100755
index 0000000..5cee6b7
--- /dev/null
+++ b/tests/quote/data/quote-database.quote-table.000000000-mysql57.sql
@@ -0,0 +1,13 @@
+/*!40101 SET NAMES binary*/;
+INSERT INTO `quo``te/table` (`quo``te/col`,`a`) VALUES
+(0,10),
+(1,9),
+(2,8),
+(3,7),
+(4,6),
+(5,5),
+(6,4),
+(7,3),
+(8,2),
+(9,1),
+(10,0);
diff --git a/tests/quote/run.sh b/tests/quote/run.sh
index 232d153..eafe61e 100644
--- a/tests/quote/run.sh
+++ b/tests/quote/run.sh
@@ -1,13 +1,23 @@
-#!/bin/sh
+#!/bin/bash
 #
 # Copyright 2020 PingCAP, Inc. Licensed under Apache-2.0.
 
 set -eu
 
 mkdir -p "$DUMPLING_OUTPUT_DIR"/data
-cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table.000000000.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable.000000000.sql"
-cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table-schema.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable-schema.sql"
-cp "$DUMPLING_BASE_NAME/data/quote-database-schema-create.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase-schema-create.sql"
+
+mysql_version=$(echo "select version()" | mysql -uroot -h127.0.0.1 -P3306 | awk 'NR==2' | awk '{print $1}')
+echo "current user mysql version is $mysql_version"
+if [[ $mysql_version = 5* ]]; then
+  # there is a bug in mysql 5.x, see https://bugs.mysql.com/bug.php?id=96994, so we use different create db/table sql
+  cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table.000000000-mysql57.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable.000000000.sql"
+  cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table-schema-mysql57.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable-schema.sql"
+  cp "$DUMPLING_BASE_NAME/data/quote-database-schema-create-mysql57.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase-schema-create.sql"
+else
+  cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table.000000000.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable.000000000.sql"
+  cp "$DUMPLING_BASE_NAME/data/quote-database.quote-table-schema.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase.quo\`te%2Ftable-schema.sql"
+  cp "$DUMPLING_BASE_NAME/data/quote-database-schema-create.sql" "$DUMPLING_OUTPUT_DIR/data/quo\`te%2Fdatabase-schema-create.sql"
+fi
 
 db="quo\`te/database"
 run_sql "drop database if exists \`quo\`\`te/database\`"
diff --git a/tests/run.sh b/tests/run.sh
index 407a1af..bc535c2 100755
--- a/tests/run.sh
+++ b/tests/run.sh
@@ -1,4 +1,4 @@
-#!/bin/sh
+#!/bin/bash
 #
 # Copyright 2020 PingCAP, Inc. Licensed under Apache-2.0.
 
@@ -17,7 +17,6 @@ mkdir -p "$DUMPLING_TEST_DIR"
 PATH="tests/_utils:$PATH"
 . "tests/_utils/run_services"
 
-
 file_should_exist bin/tidb-server
 file_should_exist bin/tidb-lightning
 file_should_exist bin/dumpling
@@ -26,8 +25,9 @@ file_should_exist bin/sync_diff_inspector
 trap stop_services EXIT
 start_services
 
-for script in tests/*/run.sh; do
-    echo "****************** Running test $script..."
+run_case_by_fullpath() {
+    script="$1"
+    echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Running test $script..."
     DUMPLING_BASE_NAME="$(dirname "$script")"
     export DUMPLING_BASE_NAME
     TEST_NAME="$(basename "$(dirname "$script")")"
@@ -35,11 +35,24 @@ for script in tests/*/run.sh; do
     export DUMPLING_OUTPUT_DIR
 
     PATH="tests/_utils:$PATH" \
-    sh "$script"
-
-    echo "Cleaning up test output dir: $DUMPLING_OUTPUT_DIR"
+        bash "$script"
+    echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>TEST: $script Passed Cleaning up test output dir: $DUMPLING_OUTPUT_DIR"
     rm -rf "$DUMPLING_OUTPUT_DIR"
-
-done
-
-echo "Passed integration tests."
+}
+
+if [ "$#" -ge 1 ]; then
+    test_case="$@"
+else
+    test_case="*"
+fi
+
+if [ "$test_case" == "*" ]; then
+    for script in tests/*/run.sh; do
+        run_case_by_fullpath "$script"
+    done
+else
+    script="tests/$test_case/run.sh"
+    run_case_by_fullpath "$script"
+fi
+
+echo "Passed integration tests."
\ No newline at end of file
diff --git a/tests/views/run.sh b/tests/views/run.sh
index c9462e6..b975135 100644
--- a/tests/views/run.sh
+++ b/tests/views/run.sh
@@ -9,7 +9,8 @@ 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 "set session collation_connection='utf8mb4_general_ci'; create definer = 'root'@'localhost' view v as select * from t;"
+run_sql_file "$DUMPLING_BASE_NAME/data/views.v-schema-view.sql"
+
 # insert 20 records to `t`.
 run_sql "insert into t values $(seq -s, 20 | sed 's/,*$//g' | sed 's/[0-9]*/(\0,"\0")/g')"
 
diff --git a/v4/export/block_allow_list_test.go b/v4/export/block_allow_list_test.go
index fddc771..845bcfb 100644
--- a/v4/export/block_allow_list_test.go
+++ b/v4/export/block_allow_list_test.go
@@ -17,6 +17,7 @@ var _ = Suite(&testBWListSuite{})
 type testBWListSuite struct{}
 
 func (s *testBWListSuite) TestFilterTables(c *C) {
+	tctx := tcontext.Background().WithLogger(appLogger)
 	dbTables := DatabaseTables{}
 	expectedDBTables := DatabaseTables{}
 
@@ -36,7 +37,11 @@ func (s *testBWListSuite) TestFilterTables(c *C) {
 		TableFilter: tableFilter,
 	}
 
+	databases := []string{filter.InformationSchemaName, filter.PerformanceSchemaName, "xxx", "yyy"}
+	c.Assert(filterDatabases(tctx, conf, databases), DeepEquals, databases)
+
 	conf.TableFilter = tf.NewSchemasFilter("xxx")
+	c.Assert(filterDatabases(tctx, conf, databases), DeepEquals, []string{"xxx"})
 	filterTables(tcontext.Background(), conf)
 	c.Assert(conf.Tables, HasLen, 1)
 	c.Assert(conf.Tables, DeepEquals, expectedDBTables)
diff --git a/v4/export/config_test.go b/v4/export/config_test.go
index 466492d..d76245f 100644
--- a/v4/export/config_test.go
+++ b/v4/export/config_test.go
@@ -3,7 +3,7 @@
 package export
 
 import (
-	"context"
+	tcontext "github.com/pingcap/dumpling/v4/context"
 
 	. "github.com/pingcap/check"
 )
@@ -14,7 +14,24 @@ type testConfigSuite struct{}
 
 func (s *testConfigSuite) TestCreateExternalStorage(c *C) {
 	mockConfig := defaultConfigForTest(c)
-	loc, err := mockConfig.createExternalStorage(context.Background())
+	loc, err := mockConfig.createExternalStorage(tcontext.Background())
 	c.Assert(err, IsNil)
 	c.Assert(loc.URI(), Matches, "file:.*")
 }
+
+func (s *testConfigSuite) TestMatchMysqlBugversion(c *C) {
+	cases := []struct {
+		serverInfo ServerInfo
+		expected   bool
+	}{
+		{ParseServerInfo(tcontext.Background(), "5.7.25-TiDB-3.0.6"), false},
+		{ParseServerInfo(tcontext.Background(), "8.0.2"), false},
+		{ParseServerInfo(tcontext.Background(), "8.0.3"), true},
+		{ParseServerInfo(tcontext.Background(), "8.0.22"), true},
+		{ParseServerInfo(tcontext.Background(), "8.0.23"), false},
+	}
+	for _, x := range cases {
+		cmt := Commentf("server info %s", x.serverInfo)
+		c.Assert(x.expected, Equals, matchMysqlBugversion(x.serverInfo), cmt)
+	}
+}
diff --git a/v4/export/dump_test.go b/v4/export/dump_test.go
index 8b2fbc9..da55deb 100644
--- a/v4/export/dump_test.go
+++ b/v4/export/dump_test.go
@@ -88,3 +88,31 @@ func (s *testSQLSuite) TestDumpTableMeta(c *C) {
 		c.Assert(meta.HasImplicitRowID(), Equals, hasImplicitRowID)
 	}
 }
+
+func (s *testSQLSuite) TestGetListTableTypeByConf(c *C) {
+	conf := defaultConfigForTest(c)
+	tctx := tcontext.Background().WithLogger(appLogger)
+	cases := []struct {
+		serverInfo  ServerInfo
+		consistency string
+		expected    listTableType
+	}{
+		{ParseServerInfo(tctx, "5.7.25-TiDB-3.0.6"), consistencyTypeSnapshot, listTableByShowTableStatus},
+		// no bug version
+		{ParseServerInfo(tctx, "8.0.2"), consistencyTypeLock, listTableByInfoSchema},
+		{ParseServerInfo(tctx, "8.0.2"), consistencyTypeFlush, listTableByShowTableStatus},
+		{ParseServerInfo(tctx, "8.0.23"), consistencyTypeNone, listTableByShowTableStatus},
+
+		// bug version
+		{ParseServerInfo(tctx, "8.0.3"), consistencyTypeLock, listTableByInfoSchema},
+		{ParseServerInfo(tctx, "8.0.3"), consistencyTypeFlush, listTableByShowFullTables},
+		{ParseServerInfo(tctx, "8.0.3"), consistencyTypeNone, listTableByShowTableStatus},
+	}
+
+	for _, x := range cases {
+		conf.Consistency = x.consistency
+		conf.ServerInfo = x.serverInfo
+		cmt := Commentf("server info %s consistency %s", x.serverInfo, x.consistency)
+		c.Assert(getListTableTypeByConf(conf), Equals, x.expected, cmt)
+	}
+}
diff --git a/v4/export/prepare_test.go b/v4/export/prepare_test.go
index acf0691..aeb218b 100644
--- a/v4/export/prepare_test.go
+++ b/v4/export/prepare_test.go
@@ -5,6 +5,7 @@ package export
 import (
 	"context"
 	"fmt"
+	"strings"
 
 	tcontext "github.com/pingcap/dumpling/v4/context"
 
@@ -20,7 +21,8 @@ func (s *testPrepareSuite) TestPrepareDumpingDatabases(c *C) {
 	db, mock, err := sqlmock.New()
 	c.Assert(err, IsNil)
 	defer db.Close()
-	conn, err := db.Conn(context.Background())
+	tctx := tcontext.Background().WithLogger(appLogger)
+	conn, err := db.Conn(tctx)
 	c.Assert(err, IsNil)
 
 	rows := sqlmock.NewRows([]string{"Database"}).
@@ -31,7 +33,7 @@ func (s *testPrepareSuite) TestPrepareDumpingDatabases(c *C) {
 	mock.ExpectQuery("SHOW DATABASES").WillReturnRows(rows)
 	conf := defaultConfigForTest(c)
 	conf.Databases = []string{"db1", "db2", "db3"}
-	result, err := prepareDumpingDatabases(conf, conn)
+	result, err := prepareDumpingDatabases(tctx, conf, conn)
 	c.Assert(err, IsNil)
 	c.Assert(result, DeepEquals, []string{"db1", "db2", "db3"})
 
@@ -40,12 +42,12 @@ func (s *testPrepareSuite) TestPrepareDumpingDatabases(c *C) {
 		AddRow("db1").
 		AddRow("db2")
 	mock.ExpectQuery("SHOW DATABASES").WillReturnRows(rows)
-	result, err = prepareDumpingDatabases(conf, conn)
+	result, err = prepareDumpingDatabases(tctx, conf, conn)
 	c.Assert(err, IsNil)
 	c.Assert(result, DeepEquals, []string{"db1", "db2"})
 
 	mock.ExpectQuery("SHOW DATABASES").WillReturnError(fmt.Errorf("err"))
-	_, err = prepareDumpingDatabases(conf, conn)
+	_, err = prepareDumpingDatabases(tctx, conf, conn)
 	c.Assert(err, NotNil)
 
 	rows = sqlmock.NewRows([]string{"Database"}).
@@ -55,7 +57,7 @@ func (s *testPrepareSuite) TestPrepareDumpingDatabases(c *C) {
 		AddRow("db5")
 	mock.ExpectQuery("SHOW DATABASES").WillReturnRows(rows)
 	conf.Databases = []string{"db1", "db2", "db4", "db6"}
-	_, err = prepareDumpingDatabases(conf, conn)
+	_, err = prepareDumpingDatabases(tctx, conf, conn)
 	c.Assert(err, ErrorMatches, `Unknown databases \[db4,db6\]`)
 	c.Assert(mock.ExpectationsWereMet(), IsNil)
 }
@@ -89,7 +91,7 @@ func (s *testPrepareSuite) TestListAllTables(c *C) {
 	query := "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"
 	mock.ExpectQuery(query).WillReturnRows(rows)
 
-	tables, err := ListAllDatabasesTables(tctx, conn, dbNames, true, TableTypeBase)
+	tables, err := ListAllDatabasesTables(tctx, conn, dbNames, listTableByInfoSchema, TableTypeBase)
 	c.Assert(err, IsNil)
 
 	for d, t := range tables {
@@ -108,7 +110,7 @@ func (s *testPrepareSuite) TestListAllTables(c *C) {
 	query = "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,AVG_ROW_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'"
 	mock.ExpectQuery(query).WillReturnRows(sqlmock.NewRows([]string{"TABLE_SCHEMA", "TABLE_NAME", "TABLE_TYPE", "AVG_ROW_LENGTH"}).
 		AddRow("db", "t1", TableTypeBaseStr, 1).AddRow("db", "t2", TableTypeViewStr, nil))
-	tables, err = ListAllDatabasesTables(tctx, conn, []string{"db"}, true, TableTypeBase, TableTypeView)
+	tables, err = ListAllDatabasesTables(tctx, conn, []string{"db"}, listTableByInfoSchema, TableTypeBase, TableTypeView)
 	c.Assert(err, IsNil)
 	c.Assert(len(tables), Equals, 1)
 	c.Assert(len(tables["db"]), Equals, 2)
@@ -145,13 +147,13 @@ func (s *testPrepareSuite) TestListAllTablesByTableStatus(c *C) {
 			if tbInfo.Type == TableTypeBase {
 				rows.AddRow(tbInfo.Name, "InnoDB", 10, "Dynamic", 0, 0, 16384, 0, 0, 0, nil, "2021-07-08 03:04:07", nil, nil, "latin1_swedish_ci", nil, "", "")
 			} else {
-				rows.AddRow(tbInfo.Name, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, "VIEW")
+				rows.AddRow(tbInfo.Name, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, TableTypeView.String())
 			}
 		}
 		mock.ExpectQuery(fmt.Sprintf(query, dbName)).WillReturnRows(rows)
 	}
 
-	tables, err := ListAllDatabasesTables(tctx, conn, dbNames, false, TableTypeBase)
+	tables, err := ListAllDatabasesTables(tctx, conn, dbNames, listTableByShowTableStatus, TableTypeBase)
 	c.Assert(err, IsNil)
 
 	for d, t := range tables {
@@ -169,8 +171,79 @@ func (s *testPrepareSuite) TestListAllTablesByTableStatus(c *C) {
 		AppendViews("db", "t2")
 	mock.ExpectQuery(fmt.Sprintf(query, "db")).WillReturnRows(sqlmock.NewRows(showTableStatusColumnNames).
 		AddRow("t1", "InnoDB", 10, "Dynamic", 0, 1, 16384, 0, 0, 0, nil, "2021-07-08 03:04:07", nil, nil, "latin1_swedish_ci", nil, "", "").
-		AddRow("t2", nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, "VIEW"))
-	tables, err = ListAllDatabasesTables(tctx, conn, []string{"db"}, false, TableTypeBase, TableTypeView)
+		AddRow("t2", nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, TableTypeView.String()))
+	tables, err = ListAllDatabasesTables(tctx, conn, []string{"db"}, listTableByShowTableStatus, TableTypeBase, TableTypeView)
+	c.Assert(err, IsNil)
+	c.Assert(len(tables), Equals, 1)
+	c.Assert(len(tables["db"]), Equals, 2)
+	for i := 0; i < len(tables["db"]); i++ {
+		cmt := Commentf("%v mismatch: %v", tables["db"][i], data["db"][i])
+		c.Assert(tables["db"][i].Equals(data["db"][i]), IsTrue, cmt)
+	}
+
+	c.Assert(mock.ExpectationsWereMet(), IsNil)
+}
+
+func (s *testPrepareSuite) TestListAllTablesByShowFullTables(c *C) {
+	db, mock, err := sqlmock.New()
+	c.Assert(err, IsNil)
+	defer db.Close()
+	conn, err := db.Conn(context.Background())
+	c.Assert(err, IsNil)
+	tctx := tcontext.Background().WithLogger(appLogger)
+
+	// Test list all tables and skipping views.
+	data := NewDatabaseTables().
+		AppendTables("db1", []string{"t1", "t2"}, []uint64{1, 2}).
+		AppendTables("db2", []string{"t3", "t4", "t5"}, []uint64{3, 4, 5}).
+		AppendViews("db3", "t6", "t7", "t8")
+
+	query := "SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE='BASE TABLE'"
+	dbNames := make([]databaseName, 0, len(data))
+	for dbName, tableInfos := range data {
+		dbNames = append(dbNames, dbName)
+		columnNames := []string{strings.ToUpper(fmt.Sprintf("Tables_in_%s", dbName)), "TABLE_TYPE"}
+		rows := sqlmock.NewRows(columnNames)
+		for _, tbInfo := range tableInfos {
+			if tbInfo.Type == TableTypeBase {
+				rows.AddRow(tbInfo.Name, TableTypeBase.String())
+			} else {
+				rows.AddRow(tbInfo.Name, TableTypeView.String())
+			}
+		}
+		mock.ExpectQuery(fmt.Sprintf(query, dbName)).WillReturnRows(rows)
+	}
+
+	tables, err := ListAllDatabasesTables(tctx, conn, dbNames, listTableByShowFullTables, TableTypeBase)
+	c.Assert(err, IsNil)
+
+	for d, t := range tables {
+		expectedTbs, ok := data[d]
+		c.Assert(ok, IsTrue)
+		for i := 0; i < len(t); i++ {
+			cmt := Commentf("%v mismatch: %v", t[i], expectedTbs[i])
+			c.Assert(t[i].Equals(expectedTbs[i]), IsTrue, cmt)
+		}
+	}
+
+	// Test list all tables and not skipping views.
+	query = "SHOW FULL TABLES FROM `%s` WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'"
+	data = NewDatabaseTables().
+		AppendTables("db", []string{"t1"}, []uint64{1}).
+		AppendViews("db", "t2")
+	for dbName, tableInfos := range data {
+		columnNames := []string{strings.ToUpper(fmt.Sprintf("Tables_in_%s", dbName)), "TABLE_TYPE"}
+		rows := sqlmock.NewRows(columnNames)
+		for _, tbInfo := range tableInfos {
+			if tbInfo.Type == TableTypeBase {
+				rows.AddRow(tbInfo.Name, TableTypeBase.String())
+			} else {
+				rows.AddRow(tbInfo.Name, TableTypeView.String())
+			}
+		}
+		mock.ExpectQuery(fmt.Sprintf(query, dbName)).WillReturnRows(rows)
+	}
+	tables, err = ListAllDatabasesTables(tctx, conn, []string{"db"}, listTableByShowFullTables, TableTypeBase, TableTypeView)
 	c.Assert(err, IsNil)
 	c.Assert(len(tables), Equals, 1)
 	c.Assert(len(tables["db"]), Equals, 2)