Tidb-lightning Import Does Not Execute and Does Not Report Errors

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Tidb-lightning导入不执行也不报错

| username: TiDBer_小阿飞

【TiDB Usage Environment】Test
【TiDB Version】v6.5.2
【Reproduction Path】
Import Tool: tidb-lightning
source data: /data/TEST_TIDB.sql

The .toml configuration file is as follows:

[lightning]
level = "info"
file = "tidb-lightning.log"
server-mode = true
status-addr = ':8290'
check-requirements = true

[mydumper]
data-source-dir = "/data"

[tikv-importer]
backend = "local"
duplicate-resolution = 'remove'
sorted-kv-dir = "./some-dir"

[tidb]
host = "192.168.80.201"
port = 4000
user = "root"
status-port = 10080
pd-addr = "192.168.80.201:2379"
log-level = "error"

The execution statement is as follows:

[root@localhost tidb-community-toolkit-v6.5.3-linux-amd64]# tiup tidb-lightning -config lightning.toml --server-mode --status-addr :8290
tiup is checking updates for component tidb-lightning ...
A new version of tidb-lightning is available:
   The latest version:         v7.3.0
   Local installed version:    v7.2.0
   Update current component:   tiup update tidb-lightning
   Update all components:      tiup update --all

Starting component `tidb-lightning`: /root/.tiup/components/tidb-lightning/v7.2.0/tidb-lightning -config lightning.toml --server-mode --status-addr :8290
Verbose debug logs will be written to tidb-lightning.log

+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  # | CHECK ITEM                                                                                                | TYPE        | PASSED |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  1 | Source csv files size is proper                                                                           | performance | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  2 | the checkpoints are valid                                                                                 | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  3 | table schemas are valid                                                                                   | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  4 | all importing tables on the target are empty                                                              | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  5 | Cluster version check passed                                                                              | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  6 | Lightning has the correct storage permission                                                              | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  7 | local source dir and temp-kv dir are in different disks                                                   | performance | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  8 | local disk resources are rich, estimate sorted data size 0B, local available is 7.458GiB                  | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
|  9 | The storage space is rich, which TiKV/Tiflash is 22.37GiB/7.458GiB. The estimated storage space is 0B/0B. | performance | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
| 10 | Cluster doesn't have too many empty regions                                                               | performance | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
| 11 | Cluster region distribution is balanced                                                                   | performance | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+
| 12 | no CDC or PiTR task found                                                                                 | critical    | true   |
+----+-----------------------------------------------------------------------------------------------------------+-------------+--------+

【Encountered Problem: Problem Phenomenon and Impact】

[root@localhost tidb-community-toolkit-v6.5.3-linux-amd64]# cat tidb-lightning.log 
[2023/09/06 18:01:18.213 -07:00] [INFO] [lightning.go:266] ["starting HTTP server"] [address="[::]:8290"]
[2023/09/06 18:01:18.214 -07:00] [INFO] [lightning.go:283] ["Lightning server is running, post to /tasks to start an import task"] [address="[::]:8290"]
[2023/09/06 18:01:25.424 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/tasks]
[2023/09/06 18:01:25.424 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/progress/task]
[2023/09/06 18:01:25.425 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/tasks] [status=200] [body="{\"current\":null,\"queue\":[]}\n"] [takeTime=848.721µs] []
[2023/09/06 18:01:25.426 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/pause]
[2023/09/06 18:01:25.426 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/pause] [status=200] [body="{\"paused\":false}"] [takeTime=13.841µs] []
[2023/09/06 18:01:25.427 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/progress/task] [status=200] [] [takeTime=3.260614ms] []
[2023/09/06 18:02:53.542 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=POST] [url=/tasks]
[2023/09/06 18:02:53.542 -07:00] [INFO] [lightning.go:757] ["received task config"] [content="[lightning]\nlevel = \"info\"\nfile = \"tidb-lightning.log\"\nserver-mode = true\nstatus-addr = ':8290'\ncheck-requirements = true\n\n[mydumper]\ndata-source-dir = \"/data\"\n\n[tikv-importer]\nbackend = \"local\"\nduplicate-resolution = 'remove'\nsorted-kv-dir = \"./some-dir\"\n\n[tidb]\nhost = \"192.168.80.201\"\nport = 4000\nuser = \"root\"\nstatus-port = 10080\npd-addr = \"192.168.80.201:2379\"\nlog-level = \"error\"\n"]
[2023/09/06 18:02:53.545 -07:00] [WARN] [config.go:1070] ["currently only per-task configuration can be applied, global configuration changes can only be made on startup"] ["global config changes"="[lightning.level,lightning.file,lightning.max-size,lightning.max-days,lightning.max-backups,lightning.server-mode,lightning.status-addr,tidb.log-level]"]
[2023/09/06 18:02:53.547 -07:00] [WARN] [config.go:1070] ["currently only per-task configuration can be applied, global configuration changes can only be made on startup"] ["global config changes"="[lightning.level,lightning.file,lightning.server-mode,lightning.status-addr,tidb.log-level]"]
[2023/09/06 18:02:53.548 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=POST] [url=/tasks] [status=200] [body="{\"id\":1694048573548503198}\n"] [takeTime=6.331341ms] []
[2023/09/06 18:02:53.548 -07:00] [INFO] [info.go:49] ["Welcome to TiDB-Lightning"] [release-version=v7.2.0] [git-hash=9fd5f4a8e4f273a60fbe7d3848f85a1be8f0600b] [git-branch=heads/refs/tags/v7.2.0] [go-version=go1.20.5] [utc-build-time="2023-06-27 14:58:41"] [race-enabled=false]
[2023/09/06 18:02:53.548 -07:00] [INFO] [lightning.go:426] [cfg] [cfg="{\"id\":1694048573548503198,\"lightning\":{\"table-concurrency\":6,\"index-concurrency\":2,\"region-concurrency\":8,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"lightning_metadata\",\"max-error\":{\"type\":0,\"conflict\":9223372036854775807},\"max-error-records\":100,\"task-info-schema-name\":\"lightning_task_info\"},\"tidb\":{\"host\":\"192.168.80.201\",\"port\":4000,\"user\":\"root\",\"status-port\":10080,\"pd-addr\":\"192.168.80.201:2379\",\"sql-mode\":\"ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER\",\"tls\":\"false\",\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"max-allowed-packet\":67108864,\"distsql-scan-concurrency\":15,\"build-stats-concurrency\":20,\"index-serial-scan-concurrency\":20,\"checksum-table-concurrency\":2,\"vars\":null},\"checkpoint\":{\"schema\":\"tidb_lightning_checkpoint\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":\"remove\"},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":0,\"batch-import-ratio\":0,\"source-id\":\"\",\"data-source-dir\":\"file:///data\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"delimiter\":\"\\\"\",\"terminator\":\"\",\"null\":[\"\\\\N\"],\"header\":true,\"header-schema-match\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true,\"escaped-by\":\"\\\\\"},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRICS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":false,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null,\"data-character-set\":\"binary\",\"data-invalid-char-replace\":\"�\"},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"local\",\"on-duplicate\":\"\",\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"send-kv-size\":16384,\"compress-kv-pairs\":\"\",\"region-split-size\":0,\"region-split-keys\":0,\"region-split-batch-size\":4096,\"region-split-concurrency\":8,\"region-check-backoff-limit\":1800,\"sorted-kv-dir\":\"./some-dir\",\"disk-quota\":9223372036854775807,\"range-concurrency\":16,\"duplicate-resolution\":\"remove\",\"incremental-import\":false,\"keyspace-name\":\"\",\"add-index-by-sql\":false,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728,\"store-write-bwlimit\":0,\"pause-pd-scheduler-scope\":\"table\"},\"post-restore\":{\"checksum\":\"required\",\"analyze\":\"optional\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false,\"checksum-via-sql\":true},\"cron\":{\"switch-mode\":\"5m0s\",\"log-progress\":\"5m0s\",\"check-disk-quota\":\"1m0s\"},\"routes\":null,\"security\":{\"ca-path\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"black-white-list\":{\"do-tables\":null,\"do-dbs\":null,\"ignore-tables\":null,\"ignore-dbs\":null}}"]
[2023/09/06 18:02:53.552 -07:00] [INFO] [lightning.go:526] ["load data source start"]
[2023/09/06 18:02:53.555 -07:00] [INFO] [loader.go:459] ["[loader] file is filtered by file router"] [path=TEST_TIDB.sql]
[2023/09/06 18:02:53.555 -07:00] [INFO] [lightning.go:529] ["load data source completed"] [takeTime=2.678475ms] []
[2023/09/06 18:02:53.614 -07:00] [INFO] [lightning.go:567] ["acquired keyspace name"] [keyspaceName=]
[2023/09/06 18:02:53.615 -07:00] [INFO] [checkpoints.go:1115] ["open checkpoint file failed, going to create a new one"] [path=/tmp/tidb_lightning_checkpoint.pb] []
[2023/09/06 18:02:53.731 -07:00] [INFO] [local.go:680] ["multi ingest support"]
[2023/09/06 18:02:53.732 -07:00] [INFO] [import.go:483] ["the whole procedure start"]
[2023/09/06 18:02:53.739 -07:00] [INFO] [import.go:1945] [new_collation_enabled] [enabled=true]
[2023/09/06 18:02:53.739 -07:00] [INFO] [import.go:789] ["restore all schema start"]
[2023/09/06 18:02:53.740 -07:00] [INFO] [import.go:808] ["restore all schema completed"] [takeTime=302.833µs] []
[2023/09/06 18:02:53.793 -07:00] [INFO] [version.go:429] ["detect server version"] [type=TiDB] [version=6.5.3]
[2023/09/06 18:02:53.798 -07:00] [INFO] [version.go:429] ["detect server version"] [type=TiDB] [version=6.5.3]
[2023/09/06 18:02:53.878 -07:00] [INFO] [import.go:1466] ["restore all tables data start"]
[2023/09/06 18:02:53.914 -07:00] [INFO] [import.go:1351] ["cancel periodic actions"] [do=true]
[2023/09/06 18:02:53.914 -07:00] [INFO] [tikv_mode.go:59] ["switch tikv mode"] [mode=Normal]
[2023/09/06 18:02:53.914 -07:00] [INFO] [tikv_mode.go:59] ["switch tikv mode"] [mode=Import]
[2023/09/06 18:02:53.936 -07:00] [INFO] [import.go:1580] ["restore all tables data completed"] [takeTime=57.734533ms] []
[2023/09/06 18:02:53.936 -07:00] [INFO] [import.go:1583] ["cleanup task metas"]
[2023/09/06 18:02:53.948 -07:00] [INFO] [import.go:1817] ["skip full compaction"]
[2023/09/06 18:02:53.949 -07:00] [INFO] [import.go:1968] ["clean checkpoints start"] [keepAfterSuccess=remove] [taskID=1694048573548503198]
[2023/09/06 18:02:53.949 -07:00] [INFO] [import.go:1976] ["clean checkpoints completed"] [keepAfterSuccess=remove] [taskID=1694048573548503198] [takeTime=207.142µs] []
[2023/09/06 18:02:53.949 -07:00] [INFO] [import.go:511] ["the whole procedure completed"] [takeTime=217.248168ms] []
[2023/09/06 18:02:53.954 -07:00] [WARN] [import.go:1203] ["stopping periodic actions"] [error="context canceled"]
[2023/09/06 18:02:54.056 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/tasks]
[2023/09/06 18:02:54.056 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/tasks] [status=200] [body="{\"current\":null,\"queue\":[]}\n"] [takeTime=21.544µs] []
[2023/09/06 18:02:54.056 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/pause]
[2023/09/06 18:02:54.056 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/pause] [status=200] [body="{\"paused\":false}"] [takeTime=6.917µs] []
[2023/09/06 18:02:54.057 -07:00] [INFO] [lightning.go:200] ["process http request start"] [method=GET] [url=/progress/task]
[2023/09/06 18:02:54.057 -07:00] [INFO] [lightning.go:209] ["process http request completed"] [method=GET] [url=/progress/task] [status=200] [] [takeTime=656.462µs] []
[root@localhost tidb-community-toolkit-v6.5.3-linux-amd64]#

The page screenshot is as follows

| username: Fly-bird | Original post link

Can it connect upstream and downstream?

| username: tidb菜鸟一只 | Original post link

sorted-kv-dir = “./some-dir” – Write the absolute path here

| username: 胡杨树旁 | Original post link

It looks like it succeeded here. Have you checked the database?

| username: TiDBer_小阿飞 | Original post link

The target database has nothing, no database created, no tables created.

| username: TiDBer_小阿飞 | Original post link

This has been changed. It has been modified to a newly created directory, and the absolute path has also been written.

| username: TiDBer_小阿飞 | Original post link

I have some .sql statements exported from Navicat, but it seems there are some issues with the statements. I have modified the table creation statements.

| username: 裤衩儿飞上天 | Original post link

Use dumpling to export

| username: TiDBer_小阿飞 | Original post link

Well, dumpling can export, and it can be imported back in. But what if it’s exported from a single-instance MYSQL? There’s no dumpling tool for that, right?

| username: 裤衩儿飞上天 | Original post link

You can use Dumpling to export data from MySQL.

| username: TiDBer_小阿飞 | Original post link

Only the SQL format exported by Dumpling can be used, other formats are not acceptable. For other formats, you need to add the [[mydumper.files]] parameter and configure the regular expression. Unfortunately, regular expressions are very troublesome, this is too ridiculous!
Something like this:

[[mydumper.files]]
# Expression required to parse AWS Aurora parquet files
pattern = '(?i)^(?:[^/]*/)*([a-z0-9_]+)\.([a-z0-9_]+)/(?:[^/]*/)*(?:[a-z0-9\-_.]+\.(parquet))$'
schema = '$1'
table = '$2'
type = '$3'
| username: 胡杨树旁 | Original post link

Is the SQL file you exported from Navicat a combination of create table statements and insert statements? If so, this type cannot be used with Lightning, you can only use Source.

| username: TiDBer_小阿飞 | Original post link

Yes! Later, I created the table myself and tried leaving only the INSERT statements, but it still exited directly without executing. The difference between the statements exported by dumpling is insert into () and insert into values (). Using source @.sql is too slow and serial input; with my test data of 2 million rows, it would take forever…

| username: 胡杨树旁 | Original post link

Try using mydumper to export the file.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.