tidb-lightning 导入数据报表不存在,库里是有这个空表的Error: table info dbname.table not found

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。

  • 【TiDB 版本】:V3.0.12
  • 【问题描述】:在配置tidb-lightning 导入数据时,库里已经建了空表,打算以csv 的方式导入,报表不存在,很奇怪。 配置文件如下:

cat tidb-lightning.toml [lightning] status-addr = ‘:8289’ server-mode = false level = “info” file = “tidb-lightning.log” max-size = 128 # MB max-days = 28 max-backups = 14 index-concurrency = 2 table-concurrency = 6 io-concurrency = 5

[checkpoint] enable = true schema = “tidb_lightning_checkpoint” driver = “file”

[mydumper] read-block-size = 65536 batch-size = 107_374_182_400 batch-import-ratio = 0.75 data-source-dir = “/opt/data” no-schema = true character-set = “auto”

[mydumper.csv] separator = ‘|’ delimiter = ‘"’ not-null = false null = ‘\N’ backslash-escape = true trim-last-separator = false

[tidb] host = “tidb1” port = 4000 user = “root” password = “111111” status-port = 10080 pd-addr = “pd1:2379” log-level = “error”

build-stats-concurrency = 20 distsql-scan-concurrency = 100 index-serial-scan-concurrency = 20 checksum-table-concurrency = 16

max-allowed-packet = 67_108_864

[post-restore] checksum = true level-1-compact = false compact = false

[cron] switch-mode = “5m” log-progress = “5m”

cat tikv-importer.toml log-file = “tikv-importer.log” log-level = “info”

[server] addr = “ip1:8287” grpc-concurrency = 16

[metric] job = “tikv-importer” interval = “15s” address = “”

[rocksdb] max-background-jobs = 32

[rocksdb.defaultcf] write-buffer-size = “5GB” max-write-buffer-number = 8 compression-per-level = [“lz4”, “no”, “no”, “no”, “no”, “no”, “lz4”]

[rocksdb.writecf] compression-per-level = [“lz4”, “no”, “no”, “no”, “no”, “no”, “lz4”]

[import] import-dir = “/home/tidb/data.import/” num-threads = 16 num-import-jobs = 24 max-open-engines = 8 min-available-ratio = 0.05

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

  1. 请把tidb-lightning.log日志发一下
  2. 请把导入时的语句和报错信息发一下
  3. 看到您已经配置了no-schema = true,,请检查要导入的表和建立的表在同一个schema下吗?

---------tidb-lightning.log日志

[2020/03/30 16:39:17.739 +08:00] [WARN] [config.go:339] [“currently only per-task configuration can be applied, global configuration changes can only be made on startup”] [“global config changes”="[lightning.status-addr,lightning.server-mode,lightning.level,lightning.file,lightning.max-size,lightning.max-days,lightning.max-backups,tidb.log-level]"] [2020/03/30 16:39:17.740 +08:00] [INFO] [version.go:48] [“Welcome to lightning”] [“Release Version”=v3.0.12] [“Git Commit Hash”=093ce9fef81f6c2f77880efc8bfce37f3105fc27] [“Git Branch”=HEAD] [“UTC Build Time”=“2020-03-16 12:36:11”] [“Go Version”=“go version go1.13 linux/amd64”] [2020/03/30 16:39:17.740 +08:00] [INFO] [lightning.go:173] [cfg] [cfg="{“id”:1585557557740637264,“lightning”:{“table-concurrency”:6,“index-concurrency”:2,“region-concurrency”:56,“io-concurrency”:5,“check-requirements”:true},“tidb”:{“host”:“ip1”,“port”:4000,“user”:“root”,“status-port”:10080,“pd-addr”:“ip:2379”,“sql-mode”:“ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”,“tls”:“false”,“security”:{“ca-path”:"",“cert-path”:"",“key-path”:""},“max-allowed-packet”:67108864,“distsql-scan-concurrency”:100,“build-stats-concurrency”:20,“index-serial-scan-concurrency”:20,“checksum-table-concurrency”:16},“checkpoint”:{“enable”:true,“schema”:“tidb_lightning_checkpoint”,“driver”:“file”,“keep-after-success”:false},“mydumper”:{“read-block-size”:65536,“batch-size”:107374182400,“batch-import-ratio”:0.75,“data-source-dir”:"/opt/data",“no-schema”:true,“character-set”:“auto”,“csv”:{“separator”:"|",“delimiter”:"\"",“header”:true,“trim-last-separator”:false,“not-null”:false,“null”:"\\N",“backslash-escape”:true},“case-sensitive”:false},“black-white-list”:{“do-tables”:null,“do-dbs”:null,“ignore-tables”:null,“ignore-dbs”:[“mysql”,“information_schema”,“performance_schema”,“sys”]},“tikv-importer”:{“addr”:"",“backend”:“importer”,“on-duplicate”:“replace”},“post-restore”:{“level-1-compact”:false,“compact”:false,“checksum”:true,“analyze”:true},“cron”:{“switch-mode”:“5m0s”,“log-progress”:“5m0s”},“routes”:null,“security”:{“ca-path”:"",“cert-path”:"",“key-path”:""}}"] [2020/03/30 16:39:17.741 +08:00] [INFO] [lightning.go:202] [“load data source start”] [2020/03/30 16:39:17.741 +08:00] [INFO] [lightning.go:205] [“load data source completed”] [takeTime=208.699碌s] [] [2020/03/30 16:39:17.747 +08:00] [INFO] [restore.go:262] [“the whole procedure start”] [2020/03/30 16:39:17.788 +08:00] [INFO] [restore.go:564] [“restore all tables data start”] [2020/03/30 16:39:17.788 +08:00] [ERROR] [restore.go:277] [“run failed”] [step=2] [error=“table info run1_his.logasset not found”] [2020/03/30 16:39:17.788 +08:00] [ERROR] [restore.go:283] [“the whole procedure failed”] [takeTime=40.486459ms] [error=“table info run1_his.logasset not found”] [2020/03/30 16:39:17.788 +08:00] [ERROR] [main.go:59] [“tidb lightning encountered error”] [error=“table info run1_his.logasset not found”] [errorVerbose=“table info run1_his.logasset not found\ngithub.com/pingcap/tidb-lightning/lightning/restore.(*RestoreController).restoreTables\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:607\ngithub.com/pingcap/tidb-lightning/lightning/restore.(*RestoreController).Run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:267\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).run\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:221\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).RunOnce\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:146\nmain.main\n\t/home/jenkins/agent/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/cmd/tidb-lightning/main.go:56\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:203\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1357”]

--------导入的语句 ./tidb-lightning -config tidb-lightning.toml Error: table info run1_his.logasset not found

-----------是在一个schema ,都是run1_his

您好:

    从日志看,table info run1_his.logasset not found ,确实没有这个表的信息,可以麻烦show 一下这个表的信息吗? 我在确认下是否在这个schema下是这个表名,多谢.  你可以把run1_his.logasset 复制进去查看,多谢

其实你应该 show create table 看下,或者,select count(*) from tblname; 你在 schema 中看到他存在其实他不一定存在。

%E5%9B%BE%E7%89%87

肯定是有这个表的,我自己建的,并成查询都是有的

您好: 请尝试将下游的表按照导入要求,创建为大小写一致,尝试导入,多谢

这个问题解决了,不过觉得不太友好,虽然我csv 的文件是大写的表名,最后还是判断为小写;

导入数据的时候,发现会错位,我的分隔符是|,其中有为空的列,导入的csv 文件如下: ||| 好像会直接只解析一次,中间的值得不到,导致后面的列错位了,请问怎么避免这种问题?

  1. 好的,表名大小写的问题,我们反馈下,尝试优化
  2. 第二个问题我再确认下,是说csv 里 导出的数据使用的 | ,但是 可能有两个中间为空值, | 空 | 空 | 的这种情况,导致后面解析的时候 把这两列忽略了吗? 直接从后面有数据的开始填充?

是的,直接后移了2列,导致后面的数据错乱,导不进去

  1. 请问,可以上传一个导入有问题的csv文件,我们测试下吗?多谢
  2. 你使用的lightning是哪个版本?

使用的是V3.0.12那个问题已经解决了,谢谢了,因为前面字符里面含有分隔符导致的;

有一个问题,空值导入bigint 报错,列的定义是可以为空的,这要怎么避免呢? failed to cast `` as bigint(20) for column

您好: 可以参考文档,配置[mydumper.csv] null = ‘’