数据库 EXECUTE 类型语句出错

[Jerry315]

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

• 【TiDB 版本】：Release Version: v4.0.0-alpha-1119-g41c7d7e94

• 【DM版本】：Release Version: v1.0.2

• 【问题描述】： load数据时报错，错误码10006。 ################报错代码####################

  “Type”: “UnknownError”, “msg”: “[code=10006:class=database:scope=downstream:level=high] run table schema failed - dbfile ./dumped_data.tidb/app_book.wn_leader_board_bak-schema.sql: execute statement failed: CREATE TABLE wn_leader_board_bak (id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘排行榜id’,nid int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘书籍id==fiction.id’,cat_id smallint(3) NOT NULL DEFAULT ‘0’ COMMENT ‘男频/女频: 1/2’,channel smallint(3) NOT NULL,editor_id int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘内容商id’,shelf_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘书籍上架时间，来自fiction表’,hot_num int(11) NOT NULL DEFAULT ‘100’ COMMENT ‘书籍人气’,favorite_num int(11) NOT NULL DEFAULT ‘10’ COMMENT ‘书籍收藏数’,update_status int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘是否完结:1是，0否’,evaluate float(4,1) NOT NULL DEFAULT ‘7.5’ COMMENT ‘书籍评分，默认7.5分’,reward_num int(11) DEFAULT ‘5’ COMMENT ‘赞赏次数’,create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘当前行创建时间’,PRIMARY KEY (id),KEY create_time_index (create_time),KEY nid_index (nid),KEY idx_nid (nid) USING BTREE,KEY idx_cat_id (cat_id…: Error 1067: Invalid default value for ‘shelf_time’ngithub.com/pingcap/dm/pkg/terror.(*Error).Delegate /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/terror/terror.go:267ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQLWithIgnoreError /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:159ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ExecuteSQL /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:174ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL.func2 /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:149ngithub.com/pingcap/dm/pkg/retry.(*FiniteRetryStrategy).Apply /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/retry/strategy.go:71ngithub.com/pingcap/dm/pkg/conn.(*BaseConn).ApplyRetryStrategy /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/pkg/conn/baseconn.go:180ngithub.com/pingcap/dm/loader.(*DBConn).executeSQL /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/db.go:144ngithub.com/pingcap/dm/loader.(*Loader).restoreStructure /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:964ngithub.com/pingcap/dm/loader.(*Loader).restoreTable /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:911ngithub.com/pingcap/dm/loader.(*Loader).restoreData /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:1064ngithub.com/pingcap/dm/loader.(*Loader).Restore /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:539ngithub.com/pingcap/dm/loader.(*Loader).Process /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:450ngithub.com/pingcap/dm/loader.(*Loader).Resume /home/jenkins/agent/workspace/build_dm_master/go/src/github.com/pingcap/dm/loader/loader.go:618 runtime.goexit /usr/local/go/src/runtime/asm_amd64.s:1337”

[小王同学]

检查下上下游的 sql_mode 是否一致。

[Jerry315]

@together-PingCAP

sql_mode

mysql>  select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

[小王同学]

1.检查下上下游的 sql_mode 是否一致，不一致建议改成一致的。

2.在下游 TiDB 集群手动创建一张表，并且手动插入数据，看下是否能成功插入。

[Jerry315]

我再上游设置了“set @@sql_mode=ANSI;”，然后重启worker，就过去了。

[小王同学]

[Jerry315]

使用sql语句创表失败

mysql> use test;
    Database changed
    mysql> CREATE TABLE `wn_leader_board` (
        ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '排行榜id',
        ->   `nid` int(11) NOT NULL DEFAULT '0' COMMENT '书籍id==fiction.id',
        ->   `cat_id` smallint(3) NOT NULL DEFAULT '0' COMMENT '男频/女频: 1/2',
        ->   `channel` smallint(3) NOT NULL,
        ->   `editor_id` int(11) NOT NULL DEFAULT '0' COMMENT '内容商id',
        ->   `shelf_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '书籍上架时间，来自fiction表',
        ->   `hot_num` int(11) NOT NULL DEFAULT '100' COMMENT '书籍人气',
        ->   `favorite_num` int(11) NOT NULL DEFAULT '10' COMMENT '书籍收藏数',
        ->   `update_status` int(11) NOT NULL DEFAULT '0' COMMENT '是否完结:1是，0否',
        ->   `evaluate` float(4,1) NOT NULL DEFAULT '7.5' COMMENT '书籍评分，默认7.5分',
        ->   `reward_num` int(11) DEFAULT '5' COMMENT '赞赏次数',
        ->   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '当前行创建时间',
        ->   PRIMARY KEY (`id`),
        ->   KEY `create_time_index` (`create_time`),
        ->   KEY `nid_index` (`nid`),
        ->   KEY `idx_nid` (`nid`) USING BTREE,
        ->   KEY `idx_cat_id` (`cat_id`) USING BTREE
        -> ) ENGINE=InnoDB AUTO_INCREMENT=1419262 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='书籍排行榜，由定时任务维护此表的数据';
    ERROR 1067 (42000): Invalid default value for 'shelf_time'

[Jerry315]

@Jerry315

我再上游设置了“set @@sql_mode=ANSI;”，然后重启worker，就过去了。

问题有出现了。

[小王同学]

测试了一下，这个建表语句在 mysql 上执行也是有问题的。

[Jerry315]

这个语句是没问题的，我可以执行的。

###############sql#############

CREATE TABLE `wn_leader_board` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '排行榜id',
  `nid` int(11) NOT NULL DEFAULT '0' COMMENT '书籍id==fiction.id',
  `cat_id` smallint(3) NOT NULL DEFAULT '0' COMMENT '男频/女频: 1/2',
  `channel` smallint(3) NOT NULL,
  `editor_id` int(11) NOT NULL DEFAULT '0' COMMENT '内容商id',
  `shelf_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '书籍上架时间，来自fiction表',
  `hot_num` int(11) NOT NULL DEFAULT '100' COMMENT '书籍人气',
  `favorite_num` int(11) NOT NULL DEFAULT '10' COMMENT '书籍收藏数',
  `update_status` int(11) NOT NULL DEFAULT '0' COMMENT '是否完结:1是，0否',
  `evaluate` float(4,1) NOT NULL DEFAULT '7.5' COMMENT '书籍评分，默认7.5分',
  `reward_num` int(11) DEFAULT '5' COMMENT '赞赏次数',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '当前行创建时间',
  PRIMARY KEY (`id`),
  KEY `create_time_index` (`create_time`),
  KEY `nid_index` (`nid`),
  KEY `idx_nid` (`nid`) USING BTREE,
  KEY `idx_cat_id` (`cat_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1419262 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='书籍排行榜，由定时任务维护此表的数据';

[小王同学]

@together-PingCAP

1.检查下上下游的 sql_mode 是否一致，不一致建议改成一致的。

刚试了下，上下游 sql_mode 都设置为 ANSI，建表都是成功的。就像上述回复的一样。要确保上下游 sql_mode 保持一致。

[Jerry315]

好的，感谢！

[不懂就问 (zhouyueyue)]