Repository: pingcap/dumpling
Pull request #329 (2021-08-03 14:34:50)

export: initial work to support MemSQL/SingleStore (#311)
This is an automated cherry-pick of #311

### What problem does this PR solve? <!--add issue link with summary if exists-->

Issue: https://github.com/pingcap/dumpling/issues/309

- `WITH CONSISTENT SNAPSHOT` is not supported by MemSQL (same for Vitess).
- `SHOW CREATE DATABASE` is not supported by MemSQL.

### What is changed and how it works?


### Check List <!--REMOVE the items that are not applicable-->

Tests <!-- At least one of them must be included. -->


 - Manual test (add detailed scripts or steps below)


 
### Release note


- Support for backing up MySQL compatible databases that don't support `START TRANSACTION  ... WITH CONSISTENT SNAPSHOT`
- Support for backing up MySQL compatible databases that don't support `SHOW CREATE TABLE`


ti-chi-bot: @ti-chi-bot: This cherry pick PR is for a release branch and has not yet been approved by release team.
Adding the `do-not-merge/cherry-pick-not-approved` label.

To merge this cherry pick, it must first be approved by the collaborators.

**AFTER** it has been approved by collaborators, please ping the release team in a comment to request a cherry pick review.


<details>

Instructions for interacting with me using PR comments are available [here](https://git.k8s.io/community/contributors/guide/pull-requests.md).  If you have questions or suggestions related to my behavior, please file an issue against the [kubernetes/test-infra](https://github.com/kubernetes/test-infra/issues/new?title=Prow%20issue:) repository.
</details>

Patch:
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/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/dump.go b/v4/export/dump.go
index e72e4c3..9fff75d 100755
--- a/v4/export/dump.go
+++ b/v4/export/dump.go
@@ -852,7 +852,7 @@ func extractTiDBRowIDFromDecodedKey(indexField, key string) (string, error) {
 }
 
 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
 	}
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..b09a659 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"
@@ -49,6 +51,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)
 	}
@@ -642,7 +651,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/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/prepare_test.go b/v4/export/prepare_test.go
index acf0691..6644eeb 100644
--- a/v4/export/prepare_test.go
+++ b/v4/export/prepare_test.go
@@ -20,7 +20,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 +32,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 +41,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 +56,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)
 }