Repository: pingcap/dumpling (go)

Issue:
dump pending on `SHOW TABLE STATUS FROM all_generate_column`
## Question

i use `make integration_test` to run test

but dumpling pending on `begin to run Dump` for a long time

after check the pprof, i found that current goroutine is working on `ListAllDatabasesTables`

there is the  `show processlist` result


```
+-----+-----------------+-----------------+---------------------+---------+------+-------------------------+----------------------------------------------+
| Id  | User            | Host            | db                  | Command | Time | State                   | Info                                         |
+-----+-----------------+-----------------+---------------------+---------+------+-------------------------+----------------------------------------------+
| 4   | event_scheduler | localhost       | <null>              | Daemon  | 2086 | Waiting on empty queue  | <null>                                       |
| 114 | root            | localhost:54709 | all_generate_column | Sleep   | 750  |                         | <null>                                       |
| 115 | root            | localhost:54727 | <null>              | Query   | 0    | starting                | show full processlist                        |
| 160 | root            | localhost:55436 | <null>              | Sleep   | 93   |                         | <null>                                       |
| 161 | root            | localhost:55437 | <null>              | Sleep   | 93   |                         | <null>                                       |
| 162 | root            | localhost:55438 | <null>              | Query   | 93   | Waiting for commit lock | SHOW TABLE STATUS FROM `all_generate_column` |
+-----+-----------------+-----------------+---------------------+---------+------+-------------------------+----------------------------------------------+
```

and i found that mysql is wait `Waiting for commit lock`


### dumpling log

```
****************** Running test tests/all_generate_column/run.sh...
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing SQL: drop database if exists all_generate_column;
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing SQL: drop database if exists all_generate_column;
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing SQL: create database all_generate_column;
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing SQL: create table all_generate_column.t(a int as (1), b int as (2)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing SQL: insert into all_generate_column.t values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
[2021年 8月 1日 星期日 14时37分00秒 CST] Executing bin/dumpling...
/tmp/dumpling_test_result/sql_res.all_generate_column
Release version: v5.1.0-5-gf9dd668-dev
Git commit hash: f9dd668ffe54950a96477ea9124853d07bf04f14
Git branch:      master
Build timestamp: 2021-08-01 06:36:54Z
Go version:      go version go1.16.5 darwin/arm64

[2021/08/01 14:37:01.082 +08:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v5.1.0-5-gf9dd668-dev] ["Git Commit Hash"=f9dd668ffe54950a96477ea9124853d07bf04f14] ["Git Branch"=master] ["Build timestamp"="2021-08-01 06:36:54"] ["Go Version"="go version go1.16.5 darwin/arm64"]
[2021/08/01 14:37:01.084 +08:00] [INFO] [config.go:608] ["detect server type"] [type=MySQL]
[2021/08/01 14:37:01.084 +08:00] [INFO] [config.go:627] ["detect server version"] [version=8.0.19]
[2021/08/01 14:37:01.084 +08:00] [INFO] [dump.go:93] ["begin to run Dump"] [conf="{\"s3\":{\"endpoint\":\"\",\"region\":\"\",\"storage-class\":\"\",\"sse\":\"\",\"sse-kms-key-id\":\"\",\"acl\":\"\",\"access-key\":\"\",\"secret-access-key\":\"\",\"provider\":\"\",\"force-path-style\":true,\"use-accelerate-endpoint\":false},\"gcs\":{\"endpoint\":\"\",\"storage-class\":\"\",\"predefined-acl\":\"\",\"credentials-file\":\"\"},\"AllowCleartextPasswords\":false,\"SortByPk\":true,\"NoViews\":true,\"NoHeader\":false,\"NoSchemas\":false,\"NoData\":false,\"CompleteInsert\":false,\"TransactionalConsistency\":true,\"EscapeBackslash\":true,\"DumpEmptyDatabase\":true,\"PosAfterConnect\":false,\"CompressType\":0,\"Host\":\"127.0.0.1\",\"Port\":3308,\"Threads\":4,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"/tmp/dumpling_test_result/sql_res.all_generate_column\",\"StatusAddr\":\":8281\",\"Snapshot\":\"\",\"Consistency\":\"flush\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"all_generate_column\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"HasTiKV\":false,\"ServerType\":1,\"ServerVersion\":\"8.0.19\"},\"Rows\":0,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{},\"Tables\":null}"]
```

### mysql8 log
```


2021-08-02T00:40:24.594124Z	   36 Connect	root@localhost on  using SSL/TLS
2021-08-02T00:40:24.594702Z	   36 Query	select @@version_comment limit 1
2021-08-02T00:40:24.595800Z	   36 Query	select 0 limit 0
2021-08-02T00:40:24.595914Z	   36 Quit
2021-08-02T00:40:28.243055Z	   37 Connect	root@localhost on  using SSL/TLS
2021-08-02T00:40:28.243424Z	   37 Query	select @@version_comment limit 1
2021-08-02T00:40:28.243688Z	   37 Query	drop database if exists all_generate_column
2021-08-02T00:40:28.251026Z	   37 Query	SELECT DATABASE()
2021-08-02T00:40:28.251288Z	   37 Quit
2021-08-02T00:40:28.302149Z	   38 Connect	root@localhost on  using SSL/TLS
2021-08-02T00:40:28.302354Z	   38 Query	select @@version_comment limit 1
2021-08-02T00:40:28.302572Z	   38 Query	create database all_generate_column
2021-08-02T00:40:28.304898Z	   38 Quit
2021-08-02T00:40:28.348801Z	   39 Connect	root@localhost on  using SSL/TLS
2021-08-02T00:40:28.349508Z	   39 Query	select @@version_comment limit 1
2021-08-02T00:40:28.350001Z	   39 Query	create table all_generate_column.t(a int as (1), b int as (2)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
2021-08-02T00:40:28.358319Z	   39 Quit
2021-08-02T00:40:28.413799Z	   40 Connect	root@localhost on  using SSL/TLS
2021-08-02T00:40:28.414130Z	   40 Query	select @@version_comment limit 1
2021-08-02T00:40:28.414462Z	   40 Query	insert into all_generate_column.t values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),()
2021-08-02T00:40:28.416392Z	   40 Quit
2021-08-02T00:40:28.471913Z	   41 Connect	root@localhost on  using TCP/IP
2021-08-02T00:40:28.472502Z	   41 Query	SELECT @@max_allowed_packet
2021-08-02T00:40:28.473161Z	   41 Query	SELECT version()
2021-08-02T00:40:28.478585Z	   42 Connect	root@localhost on  using TCP/IP
2021-08-02T00:40:28.478926Z	   42 Query	SELECT @@max_allowed_packet
2021-08-02T00:40:28.479273Z	   42 Query	FLUSH TABLES WITH READ LOCK
2021-08-02T00:40:28.484311Z	   43 Connect	root@localhost on  using TCP/IP
2021-08-02T00:40:28.484528Z	   43 Query	SELECT @@max_allowed_packet
2021-08-02T00:40:28.484670Z	   43 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-08-02T00:40:28.484778Z	   43 Query	START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */
2021-08-02T00:40:28.484916Z	   43 Query	SHOW MASTER STATUS
2021-08-02T00:40:28.485217Z	   43 Query	SHOW SLAVE STATUS
2021-08-02T00:40:28.485373Z	   43 Query	SHOW DATABASES
2021-08-02T00:40:28.486455Z	   43 Query	SHOW TABLE STATUS FROM `all_generate_column`
```

---
but if i change mysql version to 5.7.x
Tests can be run successfully

Pull request:
*: use `show full tables` in ListAllDatabasesTables
<!--
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-->

close #322 

### What is changed and how it works?

when meet a specific buggy version of mysql  [8.0.3  8.0.23),  use SQL `show full tables` to get all table names from upstream database.

As the average data size per row of data is not taken from the `Show full table` results, dumpling will set this value to `200000`.
This has the potential to cause the user to export too many or too few files, but I find this more acceptable than getting stuck in the dump stage.

### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->

 - Unit test
 - Integration test

Side effects

 - Possible performance regression
 - Increased code complexity

Related changes

 - Need to cherry-pick to the release branch
 - Need to update the documentation
 
### Release note

- fix pending on `show table status` in some mysql version






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/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..51a96fb 100755
--- a/v4/export/dump.go
+++ b/v4/export/dump.go
@@ -851,6 +851,19 @@ 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)
 	if err != nil {
@@ -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/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/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..04b806d 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"
 
@@ -89,7 +90,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 +109,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 +146,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 +170,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)