export: initial work to support MemSQL/SingleStore

dveeden commented 3 years ago

What problem does this PR solve?

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

Tests

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 commented 3 years ago

[REVIEW NOTIFICATION]

This pull request has been approved by:

lance6716

To complete the pull request process, please ask the reviewers in the list 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 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.

Reviewer can indicate their review by submitting an approval review. Reviewer can cancel approval by submitting a request changes review.

dveeden commented 3 years ago

The type and version detection in Dumpling is based on SELECT VERSION() which doesn't work for MemSQL

[2021/07/14 10:42:47.079 +02:00] [INFO] [config.go:603] ["detect server type"] [type=MySQL]
[2021/07/14 10:42:47.079 +02:00] [INFO] [config.go:622] ["detect server version"] [version=5.5.58]

5.5.58 127.0.0.1:3306  test  SQL  SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.58    |
+-----------+
1 row in set (0.0582 sec)
5.5.58 127.0.0.1:3306  test  SQL  SHOW GLOBAL VARIABLES LIKE 'version%';
+-------------------------+------------------------------------------------------------------------------+
| Variable_name           | Value                                                                        |
+-------------------------+------------------------------------------------------------------------------+
| version                 | 5.5.58                                                                       |
| version_comment         | MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial) |
| version_compile_machine | x86_64                                                                       |
| version_compile_os      | Linux                                                                        |
+-------------------------+------------------------------------------------------------------------------+
4 rows in set (0.0007 sec)
5.5.58 127.0.0.1:3306  test  SQL  SHOW GLOBAL VARIABLES LIKE 'memsql\_version%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| memsql_version      | 7.3.13                                   |
| memsql_version_date | Sat Jul 10 22:28:48 2021 -0400           |
| memsql_version_hash | 761e3259b36e04796e1f07379772eed4141a939e |
+---------------------+------------------------------------------+
3 rows in set (0.0005 sec)

dveeden commented 3 years ago

I'm not familiar with MemSQL, could you briefly post your test result in this PR?

I used a cluster-in-a-box test setup which is basically MemSQL in a container.

[dvaneeden@dve-carbon dumpling]$ ./bin/dumpling -V
Release version: v5.1.0-6-g4c05065
Git commit hash: 4c05065b93b064421023a608aeafebf20986c266
Git branch:      memsql
Build timestamp: 2021-08-03 11:53:34Z
Go version:      go version go1.16.6 linux/amd64

[dvaneeden@dve-carbon dumpling]$ mysqlsh mysql://root@127.0.0.1/test --vertical -e 'select version(), @@version, @@version_comment, @@memsql_version'
*************************** 1. row ***************************
        version(): 5.5.58
        @@version: 5.5.58
@@version_comment: MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
 @@memsql_version: 7.3.13
[dvaneeden@dve-carbon dumpling]$ ./bin/dumpling -u root -p $pwd -h 127.0.0.1 -P 3306 --database test
Release version: v5.1.0-6-g4c05065
Git commit hash: 4c05065b93b064421023a608aeafebf20986c266
Git branch:      memsql
Build timestamp: 2021-08-03 11:53:34Z
Go version:      go version go1.16.6 linux/amd64

[2021/08/03 13:59:16.728 +02:00] [INFO] [versions.go:55] ["Welcome to dumpling"] ["Release Version"=v5.1.0-6-g4c05065] ["Git Commit Hash"=4c05065b93b064421023a608aeafebf20986c266] ["Git Branch"=memsql] ["Build timestamp"="2021-08-03 11:53:34"] ["Go Version"="go version go1.16.6 linux/amd64"]
[2021/08/03 13:59:16.729 +02:00] [INFO] [config.go:608] ["detect server type"] [type=MySQL]
[2021/08/03 13:59:16.729 +02:00] [INFO] [config.go:627] ["detect server version"] [version=5.5.58]
[2021/08/03 13:59:16.729 +02: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\":3306,\"Threads\":4,\"User\":\"root\",\"Security\":{\"CAPath\":\"\",\"CertPath\":\"\",\"KeyPath\":\"\"},\"LogLevel\":\"info\",\"LogFile\":\"\",\"LogFormat\":\"text\",\"OutputDirPath\":\"./export-2021-08-03T13:59:16+02:00\",\"StatusAddr\":\":8281\",\"Snapshot\":\"\",\"Consistency\":\"flush\",\"CsvNullValue\":\"\\\\N\",\"SQL\":\"\",\"CsvSeparator\":\",\",\"CsvDelimiter\":\"\\\"\",\"Databases\":[\"test\"],\"Where\":\"\",\"FileType\":\"sql\",\"ServerInfo\":{\"HasTiKV\":false,\"ServerType\":1,\"ServerVersion\":\"5.5.58\"},\"Rows\":0,\"ReadTimeout\":900000000000,\"TiDBMemQuotaQuery\":0,\"FileSize\":0,\"StatementSize\":1000000,\"SessionParams\":{},\"Tables\":null}"]
[2021/08/03 13:59:16.740 +02:00] [INFO] [dump.go:143] ["get global metadata failed"] [error="sql: SHOW MASTER STATUS: sql: SHOW MASTER STATUS, args: []: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER STATUS' at line 1"] [errorVerbose="Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MASTER STATUS' at line 1\nsql: SHOW MASTER STATUS, args: []\ngithub.com/pingcap/dumpling/v4/export.simpleQueryWithArgs\n\tgithub.com/pingcap/dumpling/v4/export/sql.go:889\ngithub.com/pingcap/dumpling/v4/export.simpleQuery\n\tgithub.com/pingcap/dumpling/v4/export/sql.go:883\ngithub.com/pingcap/dumpling/v4/export.ShowMasterStatus\n\tgithub.com/pingcap/dumpling/v4/export/sql.go:464\ngithub.com/pingcap/dumpling/v4/export.recordGlobalMetaData\n\tgithub.com/pingcap/dumpling/v4/export/metadata.go:98\ngithub.com/pingcap/dumpling/v4/export.(*globalMetadata).recordGlobalMetaData\n\tgithub.com/pingcap/dumpling/v4/export/metadata.go:65\ngithub.com/pingcap/dumpling/v4/export.(*Dumper).Dump\n\tgithub.com/pingcap/dumpling/v4/export/dump.go:141\nmain.main\n\tcommand-line-arguments/main.go:74\nruntime.main\n\truntime/proc.go:225\nruntime.goexit\n\truntime/asm_amd64.s:1371\nsql: SHOW MASTER STATUS"]
[2021/08/03 13:59:16.744 +02:00] [INFO] [dump.go:191] ["All the dumping transactions have started. Start to unlock tables"]
[2021/08/03 13:59:16.757 +02:00] [WARN] [sql.go:998] ["can't get estimate count from db"] [query="EXPLAIN SELECT `id` FROM `test`.`t1`"] []
[2021/08/03 13:59:16.758 +02:00] [INFO] [collector.go:232] ["backup success summary"] [total-ranges=3] [ranges-succeed=3] [ranges-failed=0] [total-take=2.990436ms] [total-kv-size=64B] [average-speed=21.58kB/s] [total-rows=1]
[2021/08/03 13:59:16.759 +02:00] [INFO] [main.go:81] ["dump data successfully, dumpling will exit now"]
[dvaneeden@dve-carbon dumpling]$ find export-2021-08-03T13\:59\:16+02\:00/
export-2021-08-03T13:59:16+02:00/
export-2021-08-03T13:59:16+02:00/test-schema-create.sql
export-2021-08-03T13:59:16+02:00/test.t1-schema.sql
export-2021-08-03T13:59:16+02:00/test.t1.000000000.sql
export-2021-08-03T13:59:16+02:00/metadata
[dvaneeden@dve-carbon dumpling]$ cat 'export-2021-08-03T13:59:16+02:00/test-schema-create.sql'
/*!40101 SET NAMES binary*/;
CREATE DATABASE `test`;

dveeden commented 3 years ago

What's the result of select version() for MemSQL? Can we use special SQL for different databases at first?

I already posted this:

Both version() and @@version report 5.5.8 which doesn't allow us to detect that this is MemSQL/SingleStore (Something like 5.5.8-memsql-7.3.13 would have been helpful). The way to detect that this is MemSQL and which version it is is @@memsql_version which reports 7.3.13.

lichunzhu commented 3 years ago

/merge

ti-chi-bot commented 3 years ago

This pull request has been accepted and is ready to merge.

Commit hash: 4c05065b93b064421023a608aeafebf20986c266

ti-chi-bot commented 3 years ago

In response to a cherrypick label: new pull request created: #327.

ti-chi-bot commented 3 years ago

In response to a cherrypick label: new pull request created: #328.

ti-chi-bot commented 3 years ago

In response to a cherrypick label: new pull request created: #329.

pingcap / dumpling