为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:
- 【系统版本 & kernel 版本】
NAME STATUS ROLES AGE VERSION INTERNAL-IP EXTERNAL-IP OS-IMAGE KERNEL-VERSION CONTAINER-RUNTIME
172.31.90.50 Ready <none> 142d v1.14.1 172.31.90.50 <none> Red Hat Enterprise Linux 8.0 (Ootpa) 4.18.0-80.el8.x86_64 docker://18.6.2
172.31.90.53 Ready <none> 120d v1.14.1 172.31.90.53 <none> Red Hat Enterprise Linux 8.0 (Ootpa) 4.18.0-80.el8.x86_64 docker://18.6.2
172.31.90.56 Ready <none> 120d v1.14.1 172.31.90.56 <none> Red Hat Enterprise Linux 8.0 (Ootpa) 4.18.0-80.el8.x86_64 docker://18.6.2
- 【TiDB 版本】 5.7.25-TiDB-v3.0.1 MySQL Community Server (Apache License 2.0)
- 【磁盘型号】 普通机械盘
- 【集群节点分布】
haodai-pd-0 1/1 Running 0 28h 10.11.15.68 172.31.90.50
haodai-pd-1 1/1 Running 0 28h 10.21.56.201 172.31.90.56
haodai-pd-2 1/1 Running 0 28h 10.11.10.53 172.31.90.53
haodai-tidb-0 2/2 Running 0 90m 10.11.15.75 172.31.90.50
haodai-tidb-1 2/2 Running 0 92m 10.21.56.207 172.31.90.56
haodai-tikv-0 1/1 Running 0 93m 10.11.15.74 172.31.90.50
haodai-tikv-1 1/1 Running 0 95m 10.21.56.206 172.31.90.56
haodai-tikv-2 1/1 Running 0 97m 10.11.10.57 172.31.90.53
- 【数据量 & region 数量 & 副本数】 100G 1850 3
- 【问题描述(我做了什么)】 TiDB集群是在k8s里用operator起,DM是在物理机上裸奔的。 使用DM从上游数据库导入数据到TiDB,在DM创建库表结构的时候报错,报错信息如下:
"errors": [
{
"Type": "UnknownError",
"msg": "[code=10006:class=database:scope=downstream:level=high] run table schema failed - dbfile ./dumped_data.database/database.table-schema.sql: execute statement failed: CREATE TABLE `table` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',`user_id` int(10) unsigned DEFAULT '0' COMMENT '用户id',`code` varchar(20) NOT NULL DEFAULT '' COMMENT '事件标识码',`code_name` varchar(255) DEFAULT '' COMMENT '事件描述',`click_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '点击时间',`ip` varchar(20) NOT NULL DEFAULT '' COMMENT 'ip地址',`package_id` varchar(30) NOT NULL DEFAULT '' COMMENT '包名',`imei` varchar(100) NOT NULL DEFAULT '' COMMENT '设备编号',`device` varchar(255) NOT NULL DEFAULT '' COMMENT '设备',`market` varchar(255) NOT NULL DEFAULT '' COMMENT '市场',`version` varchar(20) NOT NULL DEFAULT '' COMMENT '版本号',`system` tinyint(1) DEFAULT '1' COMMENT '系统1:iOS 2:安卓',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',PRIMARY KEY (`id`) USING BTREE) ENGINE...: Error 1067: Invalid default value for 'click_time'
github.com/pingcap/dm/pkg/terror.(*Error).Delegate
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267
github.com/pingcap/dm/pkg/baseconn.(*BaseConn).ExecuteSQLWithIgnoreError
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/baseconn/conn.go:179
github.com/pingcap/dm/pkg/baseconn.(*BaseConn).ExecuteSQL
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/baseconn/conn.go:194
github.com/pingcap/dm/loader.(*Conn).executeSQL.func2
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:120
github.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71
github.com/pingcap/dm/pkg/baseconn.(*BaseConn).ApplyRetryStrategy
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/baseconn/conn.go:200
github.com/pingcap/dm/loader.(*Conn).executeSQL
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:115
github.com/pingcap/dm/loader.(*Loader).restoreStructure
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:925
github.com/pingcap/dm/loader.(*Loader).restoreTable
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:872
github.com/pingcap/dm/loader.(*Loader).restoreData
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:1018
github.com/pingcap/dm/loader.(*Loader).Restore
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:533
github.com/pingcap/dm/loader.(*Loader).Process
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:444
github.com/pingcap/dm/loader.(*Loader).Resume
/home/jenkins/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:597
runtime.goexit
/usr/local/go/src/runtime/asm_amd64.s:1337"
分析后认为是TiDB不支持0日期0时间的原因导致,相关文档 与 MySQL 兼容性对比
现在手动的解决方法就是在DM导出数据完成后手动替换*-schema.sql文件里的默认值,例如
sed -i 's/0000-00-00 00:00:00/1988-04-17 03:00:00/g' *-schema.sql
sed -i 's/0000-00-00/1988-04-17/g' *-schema.sql
想知道能不能让DM自动替换掉
- 【关键词】 DM 0日期 0时间