Lightning的sql_mode是否可以调整

使用tidb-lightning导入数据的时候由于linghtning的sql_mode设置非常严格(“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”,,)导致在导入一些使用不规范的数据表(not null的字段值为空)的时候各种报错, [tidb@b-tidb-core01 conf]$ /home/application/tidb-toolkit-v3.0.0-linux-amd64/bin/tidb-lightning -config tidb-lightning.toml [2019/08/27 14:33:21.178 +08:00] [ERROR] [datum.go:1248] [“convert to MySQL enum failed”] [error=“item is not in enum [yes no]”] [errorVerbose=“item is not in enum [yes no]ngithub.com/pingcap/tidb/types.ParseEnumName /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/enum.go:52 github.com/pingcap/tidb/types.(*Datum).convertToMysqlEnum /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:1238ngithub.com/pingcap/tidb/types.(*Datum).ConvertTo /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:709ngithub.com/pingcap/tidb/table.CastValue /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/table/column.go:167 github.com/pingcap/tidb-lightning/lightning/kv.(*TableKVEncoder).Encode /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/kv/sql2kv.go:142ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).encodeLoop /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1603ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).restore /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1659ngithub.com/pingcap/tidb-lightning/lightning/restore.(*TableRestore).restoreEngine.func1 /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:773 runtime.goexit /usr/local/go/src/runtime/asm_amd64.s:1337”] [stack=“github.com/pingcap/tidb/types.(*Datum).convertToMysqlEnum /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:1248ngithub.com/pingcap/tidb/types.(*Datum).ConvertTo /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:709ngithub.com/pingcap/tidb/table.CastValue /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/table/column.go:167 github.com/pingcap/tidb-lightning/lightning/kv.(*TableKVEncoder).Encode /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/kv/sql2kv.go:142ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).encodeLoop /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1603ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).restore /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1659ngithub.com/pingcap/tidb-lightning/lightning/restore.(*TableRestore).restoreEngine.func1 /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:773”] [2019/08/27 14:33:59.081 +08:00] [ERROR] [datum.go:1248] [“convert to MySQL enum failed”] [error=“item is not in enum [yes no other off interest]”] [errorVerbose=“item is not in enum [yes no other off interest]ngithub.com/pingcap/tidb/types.ParseEnumName /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/enum.go:52 github.com/pingcap/tidb/types.(*Datum).convertToMysqlEnum /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:1238ngithub.com/pingcap/tidb/types.(*Datum).ConvertTo /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:709ngithub.com/pingcap/tidb/table.CastValue /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/table/column.go:167 github.com/pingcap/tidb-lightning/lightning/kv.(*TableKVEncoder).Encode /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/kv/sql2kv.go:142ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).encodeLoop /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1603ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).restore /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1659ngithub.com/pingcap/tidb-lightning/lightning/restore.(*TableRestore).restoreEngine.func1 /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:773 runtime.goexit /usr/local/go/src/runtime/asm_amd64.s:1337”] [stack=“github.com/pingcap/tidb/types.(*Datum).convertToMysqlEnum /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:1248ngithub.com/pingcap/tidb/types.(*Datum).ConvertTo /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/types/datum.go:709ngithub.com/pingcap/tidb/table.CastValue /home/jenkins/workspace/release_tidb_3.0/go/pkg/mod/github.com/pingcap/tidb@v0.0.0-20190527075144-06f3f63d5a87/table/column.go:167 github.com/pingcap/tidb-lightning/lightning/kv.(*TableKVEncoder).Encode /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/kv/sql2kv.go:142ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).encodeLoop /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1603ngithub.com/pingcap/tidb-lightning/lightning/restore.(*chunkRestore).restore /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:1659ngithub.com/pingcap/tidb-lightning/lightning/restore.(*TableRestore).restoreEngine.func1 /home/jenkins/workspace/release_tidb_3.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:773”]

tidb-lightning的sql_mode: [2019/08/27 14:32:21.113 +08:00] [INFO] [lightning.go:73] [cfg] [cfg="{“lightning”:{“level”:“info”,“file”:"/home/tidb/ssd/tidb-lightning/logs/tidb-lightning.log",“max-size”:128,“max-days”:28,“max-backups”:14,“table-concurrency”:6,“index-concurrency”:2,“region-concurrency”:32,“io-concurrency”:5,“pprof-port”:8289,“check-requirements”:true},“tidb”:{“host”:“10.10.30.41”,“port”:4001,“user”:“root”,“status-port”:4002,“pd-addr”:“10.10.30.45:5001”,“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”,“log-level”:“error”,“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”:65536000,“batch-size”:107374182400,“batch-import-ratio”:0.75,“data-source-dir”:"/home/tidb/ssd/tidb-lightning/scripts/kkgoo",“no-schema”:false,“character-set”:“utf8mb4”,“csv”:{“separator”:",",“delimiter”:""",“header”:true,“trim-last-separator”:false,“not-null”:false,“null”:"\N",“backslash-escape”:true},“case-sensitive”:false},“black-white-list”:null,“tikv-importer”:{“addr”:“127.0.0.1:8287”},“post-restore”:{“level-1-compact”:false,“compact”:false,“checksum”:true,“analyze”:true},“cron”:{“switch-mode”:“5m0s”,“log-progress”:“5m0s”},“routes”:null,“config-file”:“tidb-lightning.toml”}"]

可以调整 sql_mode。详细查看官方 FAQ:https://pingcap.com/docs-cn/v3.0/faq/tidb-lightning/