Error: restore table schema mdb failed: create table failed: Error 1067: Invalid default value for 'Created_Time'

ighack · 2020 年7 月 20 日 02:36

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

【TiDB 版本】：tidb 4.0-rc
【问题描述】：Invalid default value for ‘Created_Time’,我把mysql的dmp通过lightning恢复到tidb里面去。但我有很多时间字段如
mdb.fd_vehicle-schema.sql: Created_Time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘申请时间’,
在恢复的时候报Invalid default value for ‘Created_Time’

来了老弟 · 2020 年7 月 20 日 03:07

你好，

4.x 希望可以通过 tiup upgrade 升级到 v4.0.2，关于当前问题应该是 sql_mode 设置的原因，可以使用以下命令查看 sql_mode SHOW GLOBAL VARIABLES LIKE 'sql_mode';
并通过 set @@global.sql_mode =’’; 设置空 sql_mode 来解决此问题

ighack · 2020 年7 月 20 日 06:18

我已经set @@global.sql_mode =’’;

但还是提示同样的错误

来了老弟 · 2020 年7 月 20 日 06:19

可否返回下完整的 sql 和报错内容，

上传下报错时，tidb.log 看是否存在详细信息。

ighack · 2020 年7 月 20 日 06:21

./bin/tidb-lightning -config tidb-lightning.toml
Error: restore table schema mdb failed: create table failed: Error 1067: Invalid default value for ‘Created_Time’

[2020/07/20 14:17:38.532 +08:00] [ERROR] [tidb.go:124] ["create tables failed"] [db=mdb] [takeTime=22.262854822s] [error="create table failed: Error 1067: Invalid default value for 'Created_Time'"]
[2020/07/20 14:17:38.532 +08:00] [ERROR] [restore.go:308] ["restore table schema failed"] [db=mdb] [takeTime=22.541720155s] [error="create table failed: Error 1067: Invalid default value for 'Created_Time'"]
[2020/07/20 14:17:38.533 +08:00] [ERROR] [restore.go:277] ["run failed"] [step=1] [error="restore table schema mdb failed: create table failed: Error 1067: Invalid default value for 'Created_Time'"]
[2020/07/20 14:17:38.533 +08:00] [ERROR] [restore.go:283] ["the whole procedure failed"] [takeTime=22.552301634s] [error="restore table schema mdb failed: create table failed: Error 1067: Invalid default value for 'Created_Time'"]
[2020/07/20 14:17:38.533 +08:00] [ERROR] [main.go:75] ["tidb lightning encountered error"] [error="restore table schema mdb failed: create table failed: Error 1067: Invalid default value for 'Created_Time'"] [errorVerbose="Error 1067: Invalid default value for 'Created_Time'\
github.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\
github.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\
github.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\
github.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\
github.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 mdb failed"]

ighack · 2020 年7 月 20 日 06:25

好像还不是默认值的问题，我看有些表还是创建成功了

来了老弟 · 2020 年7 月 20 日 06:26

tidb 中执行下 SHOW GLOBAL VARIABLES LIKE ‘sql_mode’;

在看下 [2020/07/20 14:17:38.532 +08:00] 对应时间的 tidb.log 。

ighack · 2020 年7 月 20 日 06:29

2020/07/20 14:17:38.532 +08:00] [WARN] [session.go:1044] [“run statement failed”] [conn=485] [schemaVersion=653] [error=“[types:1067]Invalid default value for ‘Created_Time’”] [session=“{
"currDBName": "mdb",
"id": 485,
"status": 2,
"strictMode": true,
"user": {
"Username": "tidb",
"Hostname": "10.3.87.221",
"CurrentUser": false,
"AuthUsername": "tidb",
"AuthHostname": "%"
}
}”]
[2020/07/20 14:17:38.532 +08:00] [WARN] [conn.go:726] [“command dispatched failed”] [conn=485] [connInfo=“id:485, addr:10.3.87.221:33552 status:10, collation:utf8mb4_general_ci, user:tidb”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“CREATE TABLE IF NOT EXISTS fd_ssa_cons_temp (Ssa_Id CHAR(32) NOT NULL COMMENT ‘单位ID’,Con_Id CHAR(32) NOT NULL COMMENT ‘委托方ID’,Ssa_Id_Old VARCHAR(30) DEFAULT NULL COMMENT ‘旧单位ID’,Creator CHAR(32) NOT NULL COMMENT ‘创建人’,Created_Time TIMESTAMP NOT NULL DEFAULT ‘0000-00-00 00:00:00’ ON UPDATE CURRENT_TIMESTAMP(),Updated_Time DATETIME DEFAULT NULL,Status VARCHAR(20) NOT NULL COMMENT ‘状态（1、暂存2、未审核3、已审核4、已失效）’,Seq_No CHAR(32) NOT NULL COMMENT ‘配置ID’,Ssa_Code VARCHAR(30) DEFAULT NULL COMMENT ‘单位内码’,Ssa_No VARCHAR(20) DEFAULT NULL COMMENT ‘客户编号’,PRIMARY KEY(Seq_No),UNIQUE uk_mdb_fd_Ssa_Id_Old(Ssa_Id_Old, Con_Id),INDEX idx_mdb_fd_Ssa_Code(Ssa_Code),INDEX idx_mdb_fd_Ssa_Id_Old(Ssa_Id_Old),INDEX idx_mdb_fd_Status(Status),INDEX idx_mdb_fd_Ssa_Id(Ssa_Id) USING BTREE) ENGINE = InnoDB DEFAULT CHARACTER SET = UTF8MB4 COMMENT = ‘上下游单位业主配置（临时）’;”] [txn_mode=PESSIMISTIC] [err=“[types:1067]Invalid default value for ‘Created_Time’
github.com/pingcap/errors.AddStack
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20190809092503-95897b64e011/errors.go:174
github.com/pingcap/parser/terror.(*Error).GenWithStackByArgs
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20200326020624-68d423641be5/terror/terror.go:243
github.com/pingcap/tidb/ddl.checkColumnDefaultValue
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:475
github.com/pingcap/tidb/ddl.setDefaultValue
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2696
github.com/pingcap/tidb/ddl.columnDefToCol
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:581
github.com/pingcap/tidb/ddl.buildColumnAndConstraint
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:434
github.com/pingcap/tidb/ddl.buildColumnsAndConstraints
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:290
github.com/pingcap/tidb/ddl.buildTableInfoWithStmt
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:1415
github.com/pingcap/tidb/ddl.(*ddl).CreateTable
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:1502
github.com/pingcap/tidb/executor.(*DDLExec).executeCreateTable
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/ddl.go:194
github.com/pingcap/tidb/executor.(*DDLExec).Next
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/ddl.go:91
github.com/pingcap/tidb/executor.Next
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/executor.go:217
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/adapter.go:511
github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/adapter.go:395
github.com/pingcap/tidb/executor.(*ExecStmt).Exec
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/executor/adapter.go:363
github.com/pingcap/tidb/session.runStmt
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/session/tidb.go:281
github.com/pingcap/tidb/session.(*session).executeStatement
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/session/session.go:1041
github.com/pingcap/tidb/session.(*session).execute
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/session/session.go:1153
github.com/pingcap/tidb/session.(*session).Execute
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/session/session.go:1089
github.com/pingcap/tidb/server.(*TiDBContext).Execute
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/server/driver_tidb.go:248
github.com/pingcap/tidb/server.(*clientConn).handleQuery
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/server/conn.go:1256
github.com/pingcap/tidb/server.(*clientConn).dispatch
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/server/conn.go:897
github.com/pingcap/tidb/server.(*clientConn).Run
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/server/conn.go:711
github.com/pingcap/tidb/server.(*Server).onConn
\t/home/jenkins/agent/workspace/tidb_v4.0.0-rc/go/src/github.com/pingcap/tidb/server/server.go:415
runtime.goexit
\t/usr/local/go/src/runtime/asm_amd64.s:1357”]

ighack · 2020 年7 月 20 日 06:33

Created_Time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ ON UPDATE CURRENT_TIMESTAMP
看是不是这个东西的问题

ighack · 2020 年7 月 20 日 06:43

应该就是这个问题了

glorvs · 2020 年7 月 20 日 06:45

datetime 类型取值范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59 所以不能设置为 0000-00-00 这样

来了老弟 · 2020 年7 月 20 日 06:52

上图中sql_mode 如下

请在 mysql -uroot -p -P -h 执行下该语句看是否可以吧，
SHOW GLOBAL VARIABLES LIKE ‘sql_mode’; 此 sql 的结果还是需要返回一下的，很关键。