search

pingcap / dumpling

Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).
Apache License 2.0
280 stars 85 forks source link

dump pending on `SHOW TABLE STATUS FROM all_generate_column` #322

Closed Ehco1996 closed 3 years ago

Ehco1996 commented 3 years ago

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

Ehco1996 commented 3 years ago

mysql 8.0.23 fix a bug that:

FLUSH TABLES WITH READ LOCK could block other sessions from executing SHOW TABLE STATUS. (Bug #31894662) more info can be found in mysql8.0.23 release note