tidb-lightning导入数据出错

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

  • 【TiDB 版本】: Release Version: v4.0.0-beta.2-200-gb54ac5b2e Git Commit Hash: b54ac5b2ec3bd1f5de37eb813d17de43cc500bf3
  • 【问题描述】: 一、tidb兼容性问题: datetime/timestamp/text 类型的字段不允许设置默认值,否则会报错。以前(大约2年前)测试tidb2.X版本是可以直接导入不报错的。数据源版本信息 10.3.15-MariaDB

失败: CREATE TABLE activity ( id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘’, name varchar(64) DEFAULT ‘’ COMMENT ‘’, max_num smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, status tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, send_flag tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, expire_date timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘’, create_time timestamp NOT NULL DEFAULT current_timestamp() COMMENT ‘’, update_time timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT ‘’, PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=6005 DEFAULT CHARSET=utf8 COMMENT=’’;

成功: CREATE TABLE activity ( id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘’, name varchar(64) DEFAULT ‘’ COMMENT ‘’, max_num smallint(5) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, status tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, send_flag tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, expire_date timestamp NOT NULL COMMENT ‘’, create_time timestamp NOT NULL DEFAULT current_timestamp() COMMENT ‘’, update_time timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT ‘’, PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=6005 DEFAULT CHARSET=utf8 COMMENT=’’;

错误: CREATE TABLE sdk_dispatcher_strategy ( id int(10) unsigned NOT NULL AUTO_INCREMENT, biz_id bigint(20) unsigned NOT NULL COMMENT ‘’, sdk_id int(10) unsigned NOT NULL COMMENT ‘’, config text NOT NULL DEFAULT ‘’ COMMENT ‘’, rtc_sdk_type tinyint(2) unsigned NOT NULL COMMENT ‘’, state tinyint(4) unsigned NOT NULL DEFAULT 1 COMMENT ‘’, create_time datetime NOT NULL DEFAULT current_timestamp() COMMENT ‘’, update_time datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT ‘’, PRIMARY KEY (id), UNIQUE KEY bsd_id (biz_id,sdk_id) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT=’’;

错误: CREATE TABLE sale_performance_goal ( id int(11) unsigned NOT NULL AUTO_INCREMENT, month int(6) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, goal varchar(15) NOT NULL DEFAULT ‘0.00’ COMMENT ‘’, operator int(11) unsigned NOT NULL DEFAULT 0 COMMENT ‘’, create_time datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘’, update_time datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘’, PRIMARY KEY (id), UNIQUE KEY unique_month (month) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT=’’;

另外 lightning 导入数据出错之后 只知道是哪个字段出错,却不知道是哪张表出错,我们一个database有1000张表,查找起来特别麻烦

[2020/04/24 06:55:58.565 +00:00] [INFO] [lightning.go:205] [“load data source completed”] [takeTime=61.190512ms] [] [2020/04/24 06:55:58.566 +00:00] [INFO] [checkpoints.go:767] [“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”] [2020/04/24 06:55:58.574 +00:00] [INFO] [restore.go:262] [“the whole procedure start”] [2020/04/24 06:55:58.580 +00:00] [INFO] [restore.go:300] [“restore table schema start”] [db=englishbreak] [2020/04/24 06:55:58.648 +00:00] [INFO] [tidb.go:106] [“create tables start”] [db=englishbreak] [2020/04/24 06:56:05.734 +00:00] [ERROR] [tidb.go:124] [“create tables failed”] [db=englishbreak] [takeTime=7.085297803s] [error=“create table failed: Error 1067: Invalid default value for ‘update_time’”] [2020/04/24 06:56:05.734 +00:00] [ERROR] [restore.go:308] [“restore table schema failed”] [db=englishbreak] [takeTime=7.154160928s] [error=“create table failed: Error 1067: Invalid default value for ‘update_time’”] [2020/04/24 06:56:05.734 +00:00] [ERROR] [restore.go:277] [“run failed”] [step=1] [error=“restore table schema englishbreak failed: create table failed: Error 1067: Invalid default value for ‘update_time’”] [2020/04/24 06:56:05.734 +00:00] [ERROR] [restore.go:283] [“the whole procedure failed”] [takeTime=7.15980782s] [error=“restore table schema englishbreak failed: create table failed: Error 1067: Invalid default value for ‘update_time’”] [2020/04/24 06:56:05.734 +00:00] [ERROR] [main.go:75] [“tidb lightning encountered error”] [error=“restore table schema englishbreak failed: create table failed: Error 1067: Invalid default value for ‘update_time’”] [errorVerbose=“Error 1067: Invalid default value for ‘update_time’\ngithub.com/pingcap/errors.AddStack\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/errors.go:174\ github.com/pingcap/errors.Trace\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/juju_adaptor.go:15\ github.com/pingcap/tidb-lightning/lightning/common.SQLWithRetry.Exec.func1\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/common/util.go:172\ github.com/pingcap/tidb-lightning/lightning/common.SQLWithRetry.perform\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/common/util.go:110\ github.com/pingcap/tidb-lightning/lightning/common.SQLWithRetry.Exec\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/common/util.go:170\ github.com/pingcap/tidb-lightning/lightning/restore.(*TiDBManager).InitSchema\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/tidb.go:119\ngithub.com/pingcap/tidb-lightning/lightning/restore.(*RestoreController).restoreSchema\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:306\ngithub.com/pingcap/tidb-lightning/lightning/restore.(*RestoreController).Run\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/restore/restore.go:267\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).run\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:221\ngithub.com/pingcap/tidb-lightning/lightning.(*Lightning).RunOnce\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/lightning/lightning.go:146\ main.main\ \t/home/jenkins/agent/workspace/release_tidb_4.0/go/src/github.com/pingcap/tidb-lightning/cmd/tidb-lightning/main.go:72\ runtime.main\ \t/usr/local/go/src/runtime/proc.go:203\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357\ create table failed\ restore table schema englishbreak failed”]

问题已收到,正在分析,请稍等

你好,

目前4.0.0-beta 不建议测试 tidb-lightning 和 tiflash 可以将集群升级至4.0-rc 再去尝试,当前版本可能存在一些问题~

及时 tidb-ansible 部署也可以将配置导入 tiup,在进行升级哦~

https://pingcap.com/docs-cn/stable/how-to/upgrade/using-tiup/

如何升级到4.0-rc

这边启动是 tiup --nightly的方式

另外当前tidb的版本(最新版本)是否存在 “datetime/timestamp/text 类型的字段不允许设置默认值” 的问题?

你好,

升级方式看下上面的连接,有详细的升级流程。

tidb 没有这方便的限制,手动执行看是否创建成功

最新版本的tidib确实不支持设置默认值:

mysql> CREATE TABLE activity1 ( id int(11) unsigned NOT NULL AUTO_INCREMENT, -> name varchar(64) DEFAULT ‘’, -> max_num smallint(5) unsigned NOT NULL DEFAULT 0 , -> status tinyint(1) unsigned NOT NULL DEFAULT 0 , -> send_flag tinyint(1) unsigned NOT NULL DEFAULT 0 , -> expire_date timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ , -> create_time timestamp NOT NULL DEFAULT current_timestamp() , -> update_time timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() , -> PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=6005 DEFAULT CHARSET=utf8 ; ERROR 1067 (42000): Invalid default value for ‘expire_date’

Release Version: v4.0.0-rc Git Commit Hash: 79db9e30ab8f98ac07c8ae55c66dfecc24b43d56 Git Branch: heads/refs/tags/v4.0.0-rc UTC Build Time: 2020-04-08 07:32:25

你好,

timestamp 有效值范围 1970-01-01 00:00:01.0000002038-01-19 03:14:07.999999 如果需要特殊格式需要谨慎去掉 sql_mode 中 STRICT_TRANS_TABLES

https://pingcap.com/docs-cn/stable/reference/sql/sql-mode/#重要的-sql_mode-值