tidb-lightning 配置导入csv文件提示数据源目录为 is a directory

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
按照官网的 TiDB 数据库快速上手指南 部署的单节点Linux集群做测试

【概述】 场景 + 问题概述

【备份和数据迁移策略逻辑】
csv文件是从google cloud 的bigquery导出来的,格式如下:

[root@iZwz9bg08mzvex3rn5k06wZ ~]# more csv/test.tokens.csv
address,symbol,name,decimals,total_supply,block_timestamp,block_number,block_hash
xxxxxxxxxxxxxxxx,LOKI,Loki,18,100000000000000000000000000,2021-07-13 02:40:18 UTC,12816224,xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxx,"","",9,0,2021-07-13 03:12:25 UTC,12816355,xxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxx,MET.KEY,MET.KEY,18,1000000000000000000000000000,2021-07-13 07:50:51 UTC,12817550,xxxxxxxxxxxxxxxxxxxx

【背景】 做过哪些操作
有试过用tidb-lightning.toml配置文件的方式,还是提示一样的错误
配置如下:

[lightning]

# 日志

level = "info"

file = "tidb-lightning.log"

[tikv-importer]

# 选择使用的 local 后端

backend = "local"

# 设置排序的键值对的临时存放地址,目标路径需要是一个空目录

sorted-kv-dir = "/tidb-data/sorted-kv-dir/"

[mydumper]

no-schema = true

data-source-dir = /nfs_storage/tmp/csv/

[mydumper.csv]

# 字段分隔符,必须为 ASCII 字符。

separator = ','

# 引用定界符,可以为 ASCII 字符或空字符。

delimiter = '"'

# CSV 文件是否包含表头。

# 如果为 true,首行将会被跳过。

header = true

# CSV 是否包含 NULL。

# 如果为 true,CSV 文件的任何列都不能解析为 NULL。

not-null = false

# 如果 `not-null` 为 false(即 CSV 可以包含 NULL),

# 为以下值的字段将会被解析为 NULL。

null = '\N'

# 是否解析字段内的反斜线转义符。

backslash-escape = true

# 是否移除以分隔符结束的行。

trim-last-separator = false

[tidb]

# 目标集群的信息

host = "192.168.11.105"

port = 4000

user = "root"

password = ""

# 表架构信息在从 TiDB 的“状态端口”获取。

status-port = 10080

# 集群 pd 的地址

pd-addr = "192.168.11.105:2379"

【现象】 业务和数据库现象
暂无

【问题】 当前遇到的问题

  1. 使用tidb-lightning命令行执行:
tidb-lightning -d /root/csv/ --backend local --pd-urls "192.168.11.105:2379" --tidb-host "192.168.11.105" --tidb-port 4000 --tidb-status 10080 --tidb-user root --no-schema --sorted-kv-dir "/tidb-data/sorted-kv-dir/" 
Verbose debug logs will be written to /tmp/lightning.log.2021-07-15T12.14.46+0800

Error: read /root/csv: is a directory
tidb lightning encountered error:  read /root/csv: is a directory

[root@iZwz9bg08mzvex3rn5k06wZ tmp]# more /tmp/lightning.log.2021-07-15T14.43.34+0800
[2021/07/15 14:43:34.138 +08:00] [INFO] [info.go:40] ["Welcome to TiDB-Lightning"] [release-version=v5.1.0] [git-hash=f2fcbfa5a9783e33ff44b696920d8bbe6a19eaa6] [git-branch=heads/refs/tags/v5.1.0] [go-version=go1.16.4] [utc-build-time="2021-06-24 07:09:26"] [race-en
abled=false]
[2021/07/15 14:43:34.139 +08:00] [INFO] [lightning.go:219] [cfg] [cfg="{\"id\":1626331414138919458,\"lightning\":{\"table-concurrency\":6,\"index-concurrency\":2,\"region-concurrency\":4,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"lightn
ing_metadata\"},\"tidb\":{\"host\":\"192.168.11.105\",\"port\":4000,\"user\":\"root\",\"status-port\":10080,\"pd-addr\":\"192.168.11.105: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},\"checkpoint\":{\"schema\":\"tidb_lightning_checkpo
int\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":false},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":107374182400,\"batch-import-ratio\":0,\"data-source-dir\":\"file:///root/csv\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"d
elimiter\":\"\\\"\",\"null\":\"\\\\N\",\"header\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRI
CS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":true,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"local\",\"on-duplicate\":\"replace\",
\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"region-split-size\":100663296,\"sorted-kv-dir\":\"/tidb-data/sorted-kv-dir/\",\"disk-quota\":74968948736,\"range-concurrency\":16,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728},\"post-r
estore\":{\"checksum\":\"required\",\"analyze\":\"optional\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false},\"cron\":{\"switch-mode\":\"5m0s\",\"log-progress\":\"5m0s\",\"check-disk-quota\":\"1m0s\"},\"routes\":null,\"security\":{\"ca-pa
th\":\"\",\"cert-path\":\"\",\"key-path\":\"\",\"redact-info-log\":false},\"black-white-list\":{\"do-tables\":null,\"do-dbs\":null,\"ignore-tables\":null,\"ignore-dbs\":null}}"]
[2021/07/15 14:43:34.141 +08:00] [INFO] [lightning.go:282] ["load data source start"]
[2021/07/15 14:43:34.141 +08:00] [INFO] [lightning.go:285] ["load data source completed"] [takeTime=190.771µs] []
[2021/07/15 14:43:34.141 +08:00] [INFO] [checkpoints.go:979] ["open checkpoint file failed, going to create a new one"] [path=/tmp/tidb_lightning_checkpoint.pb] [error="open /tmp/tidb_lightning_checkpoint.pb: no such file or directory"]
[2021/07/15 14:43:34.145 +08:00] [INFO] [local.go:949] ["multi ingest support"]
[2021/07/15 14:43:34.146 +08:00] [INFO] [restore.go:369] ["the whole procedure start"]
[2021/07/15 14:43:34.149 +08:00] [INFO] [restore.go:660] ["restore all schema start"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [loader.go:59] ["failed to extract table schema"] [Path=] [error="read /root/csv: is a directory"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [restore.go:679] ["restore all schema failed"] [takeTime=826.725µs] [error="read /root/csv: is a directory"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [restore.go:388] ["run failed"] [step=2] [error="read /root/csv: is a directory"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [restore.go:399] ["the whole procedure failed"] [takeTime=3.688991ms] [error="read /root/csv: is a directory"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [main.go:90] ["tidb lightning encountered error stack info"] [error="read /root/csv: is a directory"] [errorVerbose="read /root/csv: is a directory\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0.2
0201126102027-b0a155152ca3/errors.go:174\ngithub.com/pingcap/errors.Trace\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/juju_adaptor.go:15\ngithub.com/pingcap/br/pkg/lightning/mydump.ExportStatement\n\tgithub.com/pingcap/br/pkg/lightning/mydump
/reader.go:88\ngithub.com/pingcap/br/pkg/lightning/mydump.(*MDTableMeta).GetSchema\n\tgithub.com/pingcap/br/pkg/lightning/mydump/loader.go:57\ngithub.com/pingcap/br/pkg/lightning/restore.(*restoreSchemaWorker).makeJobs\n\tgithub.com/pingcap/br/pkg/lightning/restore
/restore.go:488\ngithub.com/pingcap/br/pkg/lightning/restore.(*Controller).restoreSchema\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:678\ngithub.com/pingcap/br/pkg/lightning/restore.(*Controller).Run\n\tgithub.com/pingcap/br/pkg/lightning/restore/rest
ore.go:375\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).run\n\tgithub.com/pingcap/br/pkg/lightning/lightning.go:312\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).RunOnce\n\tgithub.com/pingcap/br/pkg/lightning/lightning.go:192\nmain.main.func2\n\tcommand-li
ne-arguments/main.go:86\nmain.main\n\tcommand-line-arguments/main.go:87\nruntime.main\n\truntime/proc.go:225\nruntime.goexit\n\truntime/asm_amd64.s:1371"]
[2021/07/15 14:43:34.150 +08:00] [ERROR] [main.go:91] ["tidb lightning encountered error"] [error="read /root/csv: is a directory"]
  1. 使用配置文件的方式执行:
[root@iZwz9bg08mzvex3rn5k06wZ ~]# tidb-lightning -config tidb-lightning.toml
Verbose debug logs will be written to tidb-lightning.log

Error: read /nfs_storage/tmp/csv: is a directory
tidb lightning encountered error:  read /nfs_storage/tmp/csv: is a directory

报的错跟用命令行一样。

csv文件路径

[root@iZwz9bg08mzvex3rn5k06wZ ~]# tree /root/csv/
/root/csv/
└── test.tokens.csv

【业务影响】
暂无
【TiDB 版本】
v5.1.0
【附件】

  • 相关日志、配置文件、Grafana 监控(https://metricstool.pingcap.com/)
  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息
  • TiDB-Overview 监控
  • 对应模块的 Grafana 监控(如有 BR、TiDB-binlog、TiCDC 等)
  • 对应模块日志(包含问题前后 1 小时日志)

若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

2赞

tidb-lightning -d ‘/root/csv/’ --backend local --pd-urls “192.168.11.105:2379” --tidb-host “192.168.11.105” --tidb-port 4000 --tidb-status 10080 --tidb-user root --no-schema --sorted-kv-dir “/tidb-data/sorted-kv-dir/”

加个引号试试…

我参考了这里…

1赞

感谢回复,我加上单引号试了也不行

[root@iZwz9bg08mzvex3rn5k06wZ ~]# tidb-lightning -d '/root/csv/' --backend local --pd-urls "192.168.11.105:2379" --tidb-host "192.168.11.105" --tidb-port 4000 --tidb-status 10080 --tidb-user root --no-schema --sorted-kv-dir "/tidb-data/sorted-kv-dir/"
Verbose debug logs will be written to /tmp/lightning.log.2021-07-15T14.23.35+0800

Error: read /root/csv: is a directory
tidb lightning encountered error:  read /root/csv: is a directory
1赞
  1. 参数都放到配置文件中试试,已经写了mydumper 下 data-source-dir 为什么又要加 -d 参数?
    image
1赞

:joy:我一开始就是用配置文件的方式去跑的,跑不通才用用命令行的。

  1. 麻烦用参数配置文件试试,另外确认下存放csv 文件的目录是否权限正确。 比如是否有x权限等,属主是否正确。
    ls -ld xx 查看每层目录权限
    ls -l 查看文件权限
    导入后,报错,麻烦反馈每层目录权限,文件权限,导入命令和报错权限,多谢。

我现在用的是root用户,执行了chmod 777 -R /nfs_storage/tmp/csv/然后用配置文件的方式运行,还是报一样的错。
权限目录如下

[root@iZwz9bg08mzvex3rn5k06wZ ~]# ls -ld /*
lrwxrwxrwx.   1 root root     7 5月  21 11:52 /bin -> usr/bin
dr-xr-xr-x.   5 root root  4096 5月  21 12:08 /boot
drwxr-xr-x    7 root root  4096 7月  14 15:49 /data
drwxr-xr-x   18 root root  2920 7月  14 16:01 /dev
drwxr-xr-x.  81 root root  4096 7月  14 16:36 /etc
drwxr-xr-x.   3 root root  4096 7月  14 16:06 /home
lrwxrwxrwx.   1 root root     7 5月  21 11:52 /lib -> usr/lib
lrwxrwxrwx.   1 root root     9 5月  21 11:52 /lib64 -> usr/lib64
drwx------.   2 root root 16384 5月  21 11:52 /lost+found
drwxr-xr-x.   2 root root  4096 4月  11 2018 /media
drwxr-xr-x.   2 root root  4096 4月  11 2018 /mnt
drwxrwxrwt   31 root root  4096 7月   8 19:23 /nfs_storage
drwxr-xr-x.   2 root root  4096 4月  11 2018 /opt
dr-xr-xr-x  141 root root     0 7月   8 18:49 /proc
dr-xr-x---.  11 root root  4096 7月  15 15:54 /root
drwxr-xr-x   27 root root   880 7月  15 12:29 /run
lrwxrwxrwx.   1 root root     8 5月  21 11:52 /sbin -> usr/sbin
drwxr-xr-x.   2 root root  4096 4月  11 2018 /srv
dr-xr-xr-x   13 root root     0 7月   9 02:49 /sys
drwxr-xr-x   10 root root  4096 7月  15 15:55 /tidb-data
drwxr-xr-x   11 tidb tidb  4096 7月  14 16:06 /tidb-deploy
drwxrwxrwt.  62 root root  4096 7月  15 14:43 /tmp
drwxr-xr-x.  13 root root  4096 5月  21 11:52 /usr
drwxr-xr-x.  19 root root  4096 5月  21 03:56 /var

[root@iZwz9bg08mzvex3rn5k06wZ ~]# ls -ld /nfs_storage/*
drwxr-xr-x  7 root       root           4096 7月  15 10:49 /nfs_storage/tmp
[root@iZwz9bg08mzvex3rn5k06wZ ~]# ls -ld /nfs_storage/tmp/*
drwxrwxrwx 2 root root 4096 7月  15 11:38 /nfs_storage/tmp/csv
[root@iZwz9bg08mzvex3rn5k06wZ ~]# ls -ld /nfs_storage/tmp/csv/*
-rwxrwxrwx 1 root root 36882170 7月  14 16:17 /nfs_storage/tmp/csv/test.tokens.csv

配置文件执行

[root@iZwz9bg08mzvex3rn5k06wZ ~]# tidb-lightning -config tidb-lightning.toml
Verbose debug logs will be written to tidb-lightning.log

Error: read /nfs_storage/tmp/csv: is a directory
tidb lightning encountered error:  read /nfs_storage/tmp/csv: is a directory

[root@iZwz9bg08mzvex3rn5k06wZ ~]# tail -f tidb-lightning.log
[2021/07/15 16:00:49.766 +08:00] [WARN] [config.go:518] ["currently only per-task configuration can be applied, global configuration changes can only be made on startup"] ["global config changes"="[lightning.level,lightning.file]"]
[2021/07/15 16:00:49.766 +08:00] [INFO] [info.go:40] ["Welcome to TiDB-Lightning"] [release-version=v5.1.0] [git-hash=f2fcbfa5a9783e33ff44b696920d8bbe6a19eaa6] [git-branch=heads/refs/tags/v5.1.0] [go-version=go1.16.4] [utc-build-time="2021-06-24 07:09:26"] [race-enabled=false]
[2021/07/15 16:00:49.766 +08:00] [INFO] [lightning.go:219] [cfg] [cfg="{\"id\":1626336049766664936,\"lightning\":{\"table-concurrency\":6,\"index-concurrency\":2,\"region-concurrency\":4,\"io-concurrency\":5,\"check-requirements\":true,\"meta-schema-name\":\"lightning_metadata\"},\"tidb\":{\"host\":\"192.168.11.105\",\"port\":4000,\"user\":\"root\",\"status-port\":10080,\"pd-addr\":\"192.168.11.105: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},\"checkpoint\":{\"schema\":\"tidb_lightning_checkpoint\",\"driver\":\"file\",\"enable\":true,\"keep-after-success\":false},\"mydumper\":{\"read-block-size\":65536,\"batch-size\":107374182400,\"batch-import-ratio\":0,\"data-source-dir\":\"file:///nfs_storage/tmp/csv\",\"character-set\":\"auto\",\"csv\":{\"separator\":\",\",\"delimiter\":\"\\\"\",\"null\":\"\\\\N\",\"header\":true,\"trim-last-separator\":false,\"not-null\":false,\"backslash-escape\":true},\"max-region-size\":268435456,\"filter\":[\"*.*\",\"!mysql.*\",\"!sys.*\",\"!INFORMATION_SCHEMA.*\",\"!PERFORMANCE_SCHEMA.*\",\"!METRICS_SCHEMA.*\",\"!INSPECTION_SCHEMA.*\"],\"files\":null,\"no-schema\":true,\"case-sensitive\":false,\"strict-format\":false,\"default-file-rules\":true,\"ignore-data-columns\":null},\"tikv-importer\":{\"addr\":\"\",\"backend\":\"local\",\"on-duplicate\":\"replace\",\"max-kv-pairs\":4096,\"send-kv-pairs\":32768,\"region-split-size\":100663296,\"sorted-kv-dir\":\"/tidb-data/sorted-kv-dir/\",\"disk-quota\":75010379776,\"range-concurrency\":16,\"engine-mem-cache-size\":536870912,\"local-writer-mem-cache-size\":134217728},\"post-restore\":{\"checksum\":\"required\",\"analyze\":\"optional\",\"level-1-compact\":false,\"post-process-at-last\":true,\"compact\":false},\"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}}"]
[2021/07/15 16:00:49.768 +08:00] [INFO] [lightning.go:282] ["load data source start"]
[2021/07/15 16:00:49.770 +08:00] [INFO] [lightning.go:285] ["load data source completed"] [takeTime=1.833963ms] []
[2021/07/15 16:00:49.770 +08:00] [INFO] [checkpoints.go:979] ["open checkpoint file failed, going to create a new one"] [path=/tmp/tidb_lightning_checkpoint.pb] [error="open /tmp/tidb_lightning_checkpoint.pb: no such file or directory"]
[2021/07/15 16:00:49.774 +08:00] [INFO] [local.go:949] ["multi ingest support"]
[2021/07/15 16:00:49.876 +08:00] [INFO] [restore.go:369] ["the whole procedure start"]
[2021/07/15 16:00:49.883 +08:00] [INFO] [restore.go:660] ["restore all schema start"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [loader.go:59] ["failed to extract table schema"] [Path=] [error="read /nfs_storage/tmp/csv: is a directory"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [restore.go:679] ["restore all schema failed"] [takeTime=2.311772ms] [error="read /nfs_storage/tmp/csv: is a directory"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [restore.go:388] ["run failed"] [step=2] [error="read /nfs_storage/tmp/csv: is a directory"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [restore.go:399] ["the whole procedure failed"] [takeTime=8.833285ms] [error="read /nfs_storage/tmp/csv: is a directory"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [main.go:90] ["tidb lightning encountered error stack info"] [error="read /nfs_storage/tmp/csv: is a directory"] [errorVerbose="read /nfs_storage/tmp/csv: is a directory\ngithub.com/pingcap/errors.AddStack\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ngithub.com/pingcap/errors.Trace\n\tgithub.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/juju_adaptor.go:15\ngithub.com/pingcap/br/pkg/lightning/mydump.ExportStatement\n\tgithub.com/pingcap/br/pkg/lightning/mydump/reader.go:88\ngithub.com/pingcap/br/pkg/lightning/mydump.(*MDTableMeta).GetSchema\n\tgithub.com/pingcap/br/pkg/lightning/mydump/loader.go:57\ngithub.com/pingcap/br/pkg/lightning/restore.(*restoreSchemaWorker).makeJobs\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:488\ngithub.com/pingcap/br/pkg/lightning/restore.(*Controller).restoreSchema\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:678\ngithub.com/pingcap/br/pkg/lightning/restore.(*Controller).Run\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:375\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).run\n\tgithub.com/pingcap/br/pkg/lightning/lightning.go:312\ngithub.com/pingcap/br/pkg/lightning.(*Lightning).RunOnce\n\tgithub.com/pingcap/br/pkg/lightning/lightning.go:192\nmain.main.func2\n\tcommand-line-arguments/main.go:86\nmain.main\n\tcommand-line-arguments/main.go:87\nruntime.main\n\truntime/proc.go:225\nruntime.goexit\n\truntime/asm_amd64.s:1371"]
[2021/07/15 16:00:49.885 +08:00] [ERROR] [main.go:91] ["tidb lightning encountered error"] [error="read /nfs_storage/tmp/csv: is a directory"]

  1. 请问您导入的集群版本也是 v5.1.0 吗?
  2. 之前是否有尝试过使用其他 csv 文件导入,不是从 oogle cloud 的bigquery导出,而是从 mysql 之类的导出。是否导入成功过?
  3. 这个 csv 文件是否可以上传,我们测试复现问题。您也可以使用一个最小的csv上传,比如只有几行数据,能复现问题即可,多谢。

用的版本是v5.1.0的,现在看是csv文件的问题吗?我在mongodb上用mongoimport导入是可以的。
csv附件已传test.tokens.csv (1.1 KB)

  1. 请问 ”csv文件是从google cloud 的bigquery导出来的“ 是使用什么方式导出的?
  2. 能否再发一下 tokens 的表结构,多谢。
  3. 我试了下自己在 tidb 环境创建 tokens 表,使用 dumpling 导出,lightining 导入是可以的。
    比较导出的文件,您这边缺少了 000000 这样的编号。

是用bigquery的命令行先用gz压缩导出google cloud storage然后再从google cloud storage 上下载下来解压缩。

导出命令: bq --location=US extract --destination_format CSV --compression GZIP --field_delimiter , --print_header=false 项目名:数据集名.tokens gs://temp-download/数据集名.tokens.csv.gz

google cloud storage 下载命令:gsutil -m cp gs://temp-download/数据集名.tokens.csv.gz /nfs_storage/tmp/csv/

在bigquery上的表结构如图

另外,我刚才有看到您上条回复,我也尝试把文件名从tset.tokens.csv改成bluegogo.csv(也试过把文件名改成bluegogo.bluegogo.csv导入还是报错),再用tidb-lightning配置文件方式导入,是没有报错了,登录db里只能看到一个叫bluegogo的db名,没有表。


image

以上,谢谢您。

多谢,我们在分析一下,上面修改文件名的方式不正确,这个可以忽略,多谢。

和您确认下,test 中是否已经创建了 tokens 表? 列还是使用的 string 属性吗?
因为配置文件中使用的 no-schema true,麻烦给一下 tidb 中当前的表结构,多谢。

没有创建tokens表是想用no-schema让程序自动创建。

程序没法自动创建,需要有相应的 schema 文件才能够创建。请先创建对应的表结构,在尝试导入。

我创建schema之后可以成功导进去数据 ,但是最后校验报错,我看了下文件行数是跟db表里的行数对不上,差13条。没看明白是哪里校验错误,想请帮忙看看,非常感谢。

  1. 创建schema语句:
CREATE TABLE tokens
(
    `address` varchar(255) PRIMARY KEY,
    `symbol` varchar(255),
     `name` varchar(255),
    `decimals` varchar(255),
    `total_supply` float,
    `block_timestamp` timestamp,
    `block_number` float,
    `block_hash` varchar(255)
) charset=utf8;
  1. tidb-lightning.toml配置
[lightning]
# 日志
level = "info"
file = "tidb-lightning.log"

[tikv-importer]
# 选择使用的 local 后端
backend = "local"
# 设置排序的键值对的临时存放地址,目标路径需要是一个空目录
sorted-kv-dir = "/tidb-data/sorted-kv-dir/"

[mydumper]
#no-schema = true
data-source-dir = "/nfs_storage/tmp/csv/"


[mydumper.csv]
# 字段分隔符,必须为 ASCII 字符。
separator = ','
# 引用定界符,可以为 ASCII 字符或空字符。
#delimiter = '"'
# CSV 文件是否包含表头。
# 如果为 true,首行将会被跳过。
header = true
# CSV 是否包含 NULL。
# 如果为 true,CSV 文件的任何列都不能解析为 NULL。
not-null = false
# 如果 `not-null` 为 false(即 CSV 可以包含 NULL),
# 为以下值的字段将会被解析为 NULL。
#null = '\N'
# 是否解析字段内的反斜线转义符。
backslash-escape = true
# 是否移除以分隔符结束的行。
trim-last-separator = false

[tidb]
# 目标集群的信息
host = "192.168.11.105"
port = 4000
user = "root"
password = ""
# 表架构信息在从 TiDB 的“状态端口”获取。
status-port = 10080
# 集群 pd 的地址
pd-addr = "192.168.11.105:2379"
  1. 运行tidb-lightning最后校验错误
[root@iZwz9bg08mzvex3rn5k06wZ ~]# tidb-lightning -config ./tidb-lightning.toml
Verbose debug logs will be written to tidb-lightning.log

+---+-------------------------------------------------------------------------------------+-------------+--------+
| # | CHECK ITEM                                                                          | TYPE        | PASSED |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 1 | Cluster is available                                                                | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 2 | Cluster has no other loads                                                          | performance | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 3 | Lightning has the correct storage permission                                        | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 4 | Cluster resources are rich for this import task                                     | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 5 | local disk resources are rich, source dir has 35.17MiB, local available is 72.58GiB | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 6 | Source csv files size is proper                                                     | performance | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 7 | checkpoints are valid                                                               | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 8 | table schemas are valid                                                             | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+

Error: checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)
tidb lightning encountered error:  checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)

4.tidb-lightning.log输出

[2021/07/19 15:59:11.743 +08:00] [INFO] [restore.go:3104] ["restore file completed"] [table=`bluegogo`.`tokens`] [engineNumber=0] [fileIndex=0] [path=bluegogo.tokens.csv:0] [readDur=212.373729ms] [encodeDur=3.099334399s] [deliverDur=180.798964ms] [checksum="{cksum=12041477351278468142,size=57265732,kvs=410932}"] [takeTime=3.397364459s] []
[2021/07/19 15:59:11.775 +08:00] [INFO] [local.go:651] ["write data to local DB"] [size=40417520] [kvs=205466] [files=1] [sstFileSize=30070396] [file=/tidb-data/sorted-kv-dir/bba16298-9cd2-5290-b70f-452a0f321bfb.sst/b2d37fe3-2270-4efb-9622-9b171f89ddd4.sst] [firstKey=7480000000000000B35F728000000000000001] [lastKey=7480000000000000B35F72800000000003229A]
[2021/07/19 15:59:11.953 +08:00] [INFO] [restore.go:1907] ["encode kv data and write completed"] [table=`bluegogo`.`tokens`] [engineNumber=0] [read=0] [written=57265732] [takeTime=3.61621029s] []
[2021/07/19 15:59:11.953 +08:00] [INFO] [backend.go:410] ["engine close start"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb]
[2021/07/19 15:59:11.953 +08:00] [INFO] [local.go:651] ["write data to local DB"] [size=16848212] [kvs=205466] [files=1] [sstFileSize=10116973] [file=/tidb-data/sorted-kv-dir/96fbb145-05bf-5297-ba74-11929107f22a.sst/7977c0cd-bc3b-476b-a173-b67b88c0c996.sst] [firstKey=7480000000000000B35F698000000000000001013078303030303030FF3030303030666538FF3530336462373363FF3638663161313837FF3465623964383638FF3833000000000000F9] [lastKey=7480000000000000B35F698000000000000001013078666666663864FF3137636230313965FF3038323563343738FF6336363662323531FF6137303939646633FF6664000000000000F9]
[2021/07/19 15:59:11.955 +08:00] [INFO] [backend.go:412] ["engine close completed"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb] [takeTime=1.949873ms] []
[2021/07/19 15:59:11.957 +08:00] [INFO] [restore.go:1672] ["restore engine completed"] [table=`bluegogo`.`tokens`] [engineNumber=0] [takeTime=3.61946368s] []
[2021/07/19 15:59:11.957 +08:00] [INFO] [restore.go:2706] ["import and cleanup engine start"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb]
[2021/07/19 15:59:11.957 +08:00] [INFO] [backend.go:424] ["import start"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb] [retryCnt=0]
[2021/07/19 15:59:11.957 +08:00] [INFO] [local.go:1945] ["start import engine"] [uuid=bba16298-9cd2-5290-b70f-452a0f321bfb] [ranges=1]
[2021/07/19 15:59:11.957 +08:00] [INFO] [local.go:1952] ["import engine unfinished ranges"] [count=1]
[2021/07/19 15:59:11.957 +08:00] [INFO] [localhelper.go:76] ["split and scatter region"] [minKey=7480000000000000FFB35F728000000000FF0000010000000000FA] [maxKey=7480000000000000FFB35F728000000000FF03229B0000000000FA] [retry=0]
[2021/07/19 15:59:11.957 +08:00] [INFO] [localhelper.go:333] ["paginate scan regions"] [count=1] [start=7480000000000000FFB35F728000000000FF0000010000000000FA] [end=7480000000000000FFB35F728000000000FF03229B0000000000FA]
[2021/07/19 15:59:11.957 +08:00] [INFO] [localhelper.go:107] ["paginate scan region finished"] [minKey=7480000000000000FFB35F728000000000FF0000010000000000FA] [maxKey=7480000000000000FFB35F728000000000FF03229B0000000000FA] [regions=1]
[2021/07/19 15:59:11.957 +08:00] [INFO] [localhelper.go:291] ["waiting for scattering regions done"] [regions=1] [take=189.405µs]
[2021/07/19 15:59:11.958 +08:00] [INFO] [localhelper.go:333] ["paginate scan regions"] [count=1] [start=7480000000000000FFB35F728000000000FF0000010000000000FA] [end=7480000000000000FFB35F728000000000FF03229B0000000000FA]
[2021/07/19 15:59:12.477 +08:00] [INFO] [local.go:1980] ["import engine success"] [uuid=bba16298-9cd2-5290-b70f-452a0f321bfb] [size=40417520] [kvs=205466] [importedSize=40417520] [importedCount=205466]
[2021/07/19 15:59:12.477 +08:00] [INFO] [backend.go:427] ["import completed"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb] [retryCnt=0] [takeTime=520.589993ms] []
[2021/07/19 15:59:12.477 +08:00] [INFO] [backend.go:439] ["cleanup start"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb]
[2021/07/19 15:59:12.483 +08:00] [INFO] [backend.go:441] ["cleanup completed"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb] [takeTime=5.697886ms] []
[2021/07/19 15:59:12.483 +08:00] [INFO] [restore.go:2714] ["import and cleanup engine completed"] [engineTag=`bluegogo`.`tokens`:0] [engineUUID=bba16298-9cd2-5290-b70f-452a0f321bfb] [takeTime=526.350966ms] []
[2021/07/19 15:59:12.483 +08:00] [INFO] [restore.go:1691] ["import whole table completed"] [table=`bluegogo`.`tokens`] [takeTime=4.145927311s] []
[2021/07/19 15:59:12.483 +08:00] [INFO] [backend.go:410] ["engine close start"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a]
[2021/07/19 15:59:12.485 +08:00] [INFO] [backend.go:412] ["engine close completed"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a] [takeTime=1.452282ms] []
[2021/07/19 15:59:12.485 +08:00] [INFO] [restore.go:2706] ["import and cleanup engine start"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a]
[2021/07/19 15:59:12.485 +08:00] [INFO] [backend.go:424] ["import start"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a] [retryCnt=0]
[2021/07/19 15:59:12.485 +08:00] [INFO] [local.go:1945] ["start import engine"] [uuid=96fbb145-05bf-5297-ba74-11929107f22a] [ranges=1]
[2021/07/19 15:59:12.485 +08:00] [INFO] [local.go:1952] ["import engine unfinished ranges"] [count=1]
[2021/07/19 15:59:12.485 +08:00] [INFO] [localhelper.go:76] ["split and scatter region"] [minKey=7480000000000000FFB35F698000000000FF0000010130783030FF30303030FF303030FF3030666538FF3530FF336462373363FF36FF38663161313837FFFF3465623964383638FFFF38330000000000FF00F9000000000000F9] [maxKey=7480000000000000FFB35F698000000000FF0000010130786666FF66663864FF313763FF6230313965FF3038FF323563343738FF63FF36363662323531FFFF6137303939646633FFFF66640000000000FF00F9000000000000FA] [retry=0]
[2021/07/19 15:59:12.485 +08:00] [INFO] [localhelper.go:333] ["paginate scan regions"] [count=1] [start=7480000000000000FFB35F698000000000FF0000010130783030FF30303030FF303030FF3030666538FF3530FF336462373363FF36FF38663161313837FFFF3465623964383638FFFF38330000000000FF00F9000000000000F9] [end=7480000000000000FFB35F698000000000FF0000010130786666FF66663864FF313763FF6230313965FF3038FF323563343738FF63FF36363662323531FFFF6137303939646633FFFF66640000000000FF00F9000000000000FA]
[2021/07/19 15:59:12.485 +08:00] [INFO] [localhelper.go:107] ["paginate scan region finished"] [minKey=7480000000000000FFB35F698000000000FF0000010130783030FF30303030FF303030FF3030666538FF3530FF336462373363FF36FF38663161313837FFFF3465623964383638FFFF38330000000000FF00F9000000000000F9] [maxKey=7480000000000000FFB35F698000000000FF0000010130786666FF66663864FF313763FF6230313965FF3038FF323563343738FF63FF36363662323531FFFF6137303939646633FFFF66640000000000FF00F9000000000000FA] [regions=1]
[2021/07/19 15:59:12.485 +08:00] [INFO] [localhelper.go:291] ["waiting for scattering regions done"] [regions=1] [take=191.891µs]
[2021/07/19 15:59:12.486 +08:00] [INFO] [localhelper.go:333] ["paginate scan regions"] [count=1] [start=7480000000000000FFB35F698000000000FF0000010130783030FF30303030FF303030FF3030666538FF3530FF336462373363FF36FF38663161313837FFFF3465623964383638FFFF38330000000000FF00F9000000000000F9] [end=7480000000000000FFB35F698000000000FF0000010130786666FF66663864FF313763FF6230313965FF3038FF323563343738FF63FF36363662323531FFFF6137303939646633FFFF66640000000000FF00F9000000000000FA]
[2021/07/19 15:59:12.906 +08:00] [INFO] [local.go:1980] ["import engine success"] [uuid=96fbb145-05bf-5297-ba74-11929107f22a] [size=16848212] [kvs=205466] [importedSize=16847720] [importedCount=205460]
[2021/07/19 15:59:12.907 +08:00] [INFO] [backend.go:427] ["import completed"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a] [retryCnt=0] [takeTime=421.958251ms] []
[2021/07/19 15:59:12.907 +08:00] [INFO] [backend.go:439] ["cleanup start"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a]
[2021/07/19 15:59:12.910 +08:00] [INFO] [backend.go:441] ["cleanup completed"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a] [takeTime=3.26934ms] []
[2021/07/19 15:59:12.910 +08:00] [INFO] [restore.go:2714] ["import and cleanup engine completed"] [engineTag=`bluegogo`.`tokens`:-1] [engineUUID=96fbb145-05bf-5297-ba74-11929107f22a] [takeTime=425.313825ms] []
[2021/07/19 15:59:12.913 +08:00] [INFO] [tidb.go:366] ["alter table auto_increment start"] [table=`bluegogo`.`tokens`] [auto_increment=205467]
[2021/07/19 15:59:12.981 +08:00] [INFO] [tidb.go:368] ["alter table auto_increment completed"] [table=`bluegogo`.`tokens`] [auto_increment=205467] [takeTime=67.775013ms] []
[2021/07/19 15:59:12.981 +08:00] [INFO] [restore.go:1256] ["restore table completed"] [table=`bluegogo`.`tokens`] [takeTime=4.667289753s] []
[2021/07/19 15:59:12.981 +08:00] [INFO] [restore.go:2060] ["local checksum"] [table=`bluegogo`.`tokens`] [checksum="{cksum=12041477351278468142,size=57265732,kvs=410932}"]
[2021/07/19 15:59:12.987 +08:00] [INFO] [meta_manager.go:448] ["check table checksum"] [table=`bluegogo`.`tokens`] [checksum=true] [new_status=checksuming]
[2021/07/19 15:59:12.987 +08:00] [INFO] [checksum.go:159] ["remote checksum start"] [table=tokens]
[2021/07/19 15:59:13.187 +08:00] [INFO] [checksum.go:162] ["remote checksum completed"] [table=tokens] [takeTime=200.278826ms] []
[2021/07/19 15:59:13.187 +08:00] [ERROR] [restore.go:1412] ["restore all tables data failed"] [takeTime=4.961552527s] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"]
[2021/07/19 15:59:13.187 +08:00] [INFO] [restore.go:1028] ["everything imported, stopping periodic actions"]
[2021/07/19 15:59:13.191 +08:00] [INFO] [pd.go:423] ["resume scheduler"] [schedulers="[balance-hot-region-scheduler,balance-region-scheduler,balance-leader-scheduler]"]
[2021/07/19 15:59:13.192 +08:00] [INFO] [pd.go:443] ["resume scheduler successful"] [scheduler=balance-hot-region-scheduler]
[2021/07/19 15:59:13.192 +08:00] [INFO] [pd.go:443] ["resume scheduler successful"] [scheduler=balance-region-scheduler]
[2021/07/19 15:59:13.192 +08:00] [INFO] [pd.go:443] ["resume scheduler successful"] [scheduler=balance-leader-scheduler]
[2021/07/19 15:59:13.192 +08:00] [INFO] [pd.go:534] ["restoring config"] [config="{\"enable-location-replacement\":\"true\",\"leader-schedule-limit\":4,\"max-merge-region-keys\":200000,\"max-merge-region-size\":20,\"max-pending-peer-count\":16,\"max-snapshot-count\":3,\"region-schedule-limit\":2048}"]
[2021/07/19 15:59:13.373 +08:00] [INFO] [restore.go:1196] ["add back PD leader&region schedulers"]
[2021/07/19 15:59:13.373 +08:00] [INFO] [restore.go:1129] ["cancel periodic actions"] [do=true]
[2021/07/19 15:59:13.373 +08:00] [INFO] [restore.go:994] ["switch to normal mode"]
[2021/07/19 15:59:13.386 +08:00] [WARN] [meta_manager.go:738] ["there are unfinished table in table meta table, cleanup skipped."]
[2021/07/19 15:59:13.386 +08:00] [ERROR] [restore.go:388] ["run failed"] [step=3] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"]
[2021/07/19 15:59:13.387 +08:00] [ERROR] [restore.go:399] ["the whole procedure failed"] [takeTime=5.177604126s] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"]
[2021/07/19 15:59:13.387 +08:00] [ERROR] [restore.go:154] ["tables failed to be imported"] [count=1]
[2021/07/19 15:59:13.387 +08:00] [ERROR] [restore.go:156] [-] [table=`bluegogo`.`tokens`] [status=checksum] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"]
[2021/07/19 15:59:13.387 +08:00] [ERROR] [main.go:90] ["tidb lightning encountered error stack info"] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"] [errorVerbose="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)\ngithub.com/pingcap/br/pkg/lightning/restore.(*TableRestore).compareChecksum\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:2737\ngithub.com/pingcap/br/pkg/lightning/restore.(*TableRestore).postProcess\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:2075\ngithub.com/pingcap/br/pkg/lightning/restore.(*Controller).restoreTables.func7\n\tgithub.com/pingcap/br/pkg/lightning/restore/restore.go:1404\nruntime.goexit\n\truntime/asm_amd64.s:1371"]
[2021/07/19 15:59:13.387 +08:00] [ERROR] [main.go:91] ["tidb lightning encountered error"] [error="checksum mismatched remote vs local => (checksum: 4414109404540991009 vs 12041477351278468142) (total_kvs: 410926 vs 410932) (total_bytes:57265240 vs 57265732)"]
[2021/07/19 15:59:13.387 +08:00] [INFO] [checksum.go:456] ["service safe point keeper exited"]
  1. csv文件和db表行数比对
[root@iZwz9bg08mzvex3rn5k06wZ ~]# wc -l /nfs_storage/tmp/csv/bluegogo.tokens.csv
205474 /nfs_storage/tmp/csv/bluegogo.tokens.csv

MySQL [bluegogo]> select count(*) from tokens;
+----------+
| count(*) |
+----------+
|   205460 |
+----------+
1 row in set (0.00 sec)
  1. tokens csv数据文件
    因为文件15M超过论坛上传限制,找了个图床来存,文件名被改掉了,需要重命名为bluegogo.tokens.csv
  1. 麻烦检查下 lighting 日志,最初的报错是什么?
  2. 这是一次导入的报错吗? 还是之前有遇到过报错,执行过 clean 之类的命令?

最初的报错就是这样,我通过删除/tmp/tidb_lightning_checkpoint.pb来反复导入验证,导入之前我会先清表再导。

  1. 参考这个文档完整的清理 https://docs.pingcap.com/zh/tidb/v4.0/tidb-lightning-faq#如何清除所有与-tidb-lightning-相关的中间数据
  2. 上传下完整的 lighting 日志

您好,日志附件tidb-lightning.log (18.0 KB)