【已解决】Doris 1.25 Mysql Catalog 同步数据报错

Viewed 90
catalog 1.2

create catalog stmt

CREATE CATALOG `mdb_catalog` PROPERTIES (
"password" = "*XXX",
"driver_class" = "com.mysql.cj.jdbc.Driver",
"only_specified_database" = "false",
"checksum" = "95cde01c78e7b04e13305338d60e056a",
"driver_url" = "mysql-connector-java-8.0.28.jar",
"lower_case_table_names" = "false",
"type" = "jdbc",
"user" = "dts1",
"jdbc_url" = "jdbc:mysql://10.1.193.23:3306/weike?useSSL=false&yearIsDateType=false&tinyInt1isBit=true&transformedBitIsBoolean=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull",
"metadata_refresh_interval_sec" = "20"
);

执行语句

insert into dim_mdb_lectures_lecture(id, channel_id, liveroom_id, name, start_time, cover, status, update_time,
                                     account_id, can_discuss, can_speak, create_time, need_password, password, money,
                                     need_money, popular, priority, duration, show_reward_message, image_mode,
                                     parent_lecture_id, push_count, liveroom_priority, last_push_time, need_signup,
                                     signup_condition, reward_config, reward_percent, category_id,
                                     is_invite_custom_active, description_img, lecture_mode, can_public_relay,
                                     relay_fee, avatar, show_in_liveroom_list, is_show_qrcode, subtitle,
                                     custom_sharecard, need_pay_channel, lecture_type, n_status, remark)
 SELECT
          id, channel_id, liveroom_id, name, start_time, cover, status, update_time,
                                     account_id, can_discuss, can_speak, create_time, need_password, password, money,
                                     need_money, popular, priority, duration, show_reward_message, image_mode,
                                     parent_lecture_id, push_count, liveroom_priority, last_push_time, need_signup,
                                     signup_condition, reward_config, reward_percent, category_id,
                                     is_invite_custom_active, description_img, lecture_mode, can_public_relay,
                                     relay_fee, avatar, show_in_liveroom_list, is_show_qrcode, subtitle,
                                     custom_sharecard, need_pay_channel, lecture_type, n_status, remark
from mdb_catalog.weike.lectures_lecture
where id between 16 and 5000000

详细报错

[2024-03-15 11:02:12] [HY000][1105] errCode = 2, detailMessage = (10.2.0.7)[INTERNAL_ERROR]UdfRuntimeException: get next block failed:
[2024-03-15 11:02:12] CAUSED BY: SQLException: Error
[2024-03-15 11:02:12] CAUSED BY: DateTimeException: Invalid value for MonthOfYear (valid values 1 - 12): 0
____9
2 Answers

麻烦大佬提供一下mysql 源表的结构，然后doris端，show create table 外表。另外Doris 1.2.5官方并不建议使用该版本，可以考虑升级到对应的bugfix 1.2.8或者2.0以上。

vinlee303

Mysql 建表语句

CREATE TABLE `lectures_lecture` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` longtext,
  `start_time` datetime(6) NOT NULL,
  `cover` varchar(200) DEFAULT NULL,
  `status` varchar(10) NOT NULL,
  `update_time` datetime(6) NOT NULL,
  `account_id` int(11) NOT NULL,
  `can_discuss` tinyint(1) NOT NULL,
  `can_speak` tinyint(1) NOT NULL,
  `create_time` datetime(6) NOT NULL,
  `need_password` tinyint(1) NOT NULL,
  `password` varchar(32) NOT NULL,
  `money` int(10) unsigned NOT NULL,
  `need_money` tinyint(1) NOT NULL,
  `popular` int(10) unsigned NOT NULL,
  `priority` int(11) NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  `show_reward_message` tinyint(1) NOT NULL,
  `image_mode` varchar(10) NOT NULL,
  `parent_lecture_id` int(11) DEFAULT NULL,
  `push_count` int(11) NOT NULL,
  `liveroom_priority` int(11) NOT NULL,
  `last_push_time` datetime(6) DEFAULT NULL,
  `channel_id` int(11) DEFAULT NULL,
  `liveroom_id` int(11) DEFAULT NULL,
  `need_signup` tinyint(1) NOT NULL,
  `signup_condition` varchar(256) NOT NULL,
  `reward_config` varchar(128) NOT NULL,
  `reward_percent` int(11) NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `is_invite_custom_active` tinyint(1) NOT NULL,
  `description_img` varchar(2000) DEFAULT NULL,
  `lecture_mode` varchar(16) NOT NULL,
  `can_public_relay` tinyint(1) NOT NULL,
  `relay_fee` int(10) unsigned NOT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `show_in_liveroom_list` tinyint(1) NOT NULL,
  `is_show_qrcode` tinyint(1) NOT NULL,
  `subtitle` varchar(300) DEFAULT NULL,
  `custom_sharecard` varchar(256) DEFAULT NULL,
  `need_pay_channel` tinyint(1) NOT NULL,
  `lecture_type` varchar(30) NOT NULL,
  `n_status` varchar(20) NOT NULL,
  `remark` json DEFAULT NULL COMMENT '仅存储课程属性，不用于查询',
  `group_id` int(11) NOT NULL DEFAULT '0' COMMENT '分组ID',
  PRIMARY KEY (`id`),
  KEY `lecturer_lectureinfo_account_id_c46ec5ad_fk_account_account_id` (`account_id`),
  KEY `lectures_lecture_ce4c302f` (`parent_lecture_id`),
  KEY `lectures_lecture_72eb6c85` (`channel_id`),
  KEY `lectures_lecture_e78aab15` (`liveroom_id`),
  KEY `lectures_lecture_b583a629` (`category_id`),
  KEY `lectures_lecture_start_time_e2b53473_uniq` (`start_time`),
  KEY `lectures_lecture_priority_9ba0d24b_uniq` (`priority`),
  KEY `lectures_lecture_create_time` (`create_time`),
  KEY `idx-liveroom_priority` (`liveroom_priority`) USING BTREE COMMENT '直播间优先级索引'
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4

Doris catalog字段建表语句

CREATE TABLE `lectures_lecture`
(
    `id`                      int(11)       NOT NULL,
    `name`                    varchar(100)  NOT NULL,
    `description`             text          NULL,
    `start_time`              datetime      NULL,
    `cover`                   varchar(200)  NULL,
    `status`                  varchar(10)   NOT NULL,
    `update_time`             datetime      NULL,
    `account_id`              int(11)       NOT NULL,
    `can_discuss`             boolean       NOT NULL,
    `can_speak`               boolean       NOT NULL,
    `create_time`             datetime      NULL,
    `need_password`           boolean       NOT NULL,
    `password`                varchar(32)   NOT NULL,
    `money`                   bigint(20)    NOT NULL,
    `need_money`              boolean       NOT NULL,
    `popular`                 bigint(20)    NOT NULL,
    `priority`                int(11)       NOT NULL,
    `duration`                bigint(20)    NOT NULL,
    `show_reward_message`     boolean       NOT NULL,
    `image_mode`              varchar(10)   NOT NULL,
    `parent_lecture_id`       int(11)       NULL,
    `push_count`              int(11)       NOT NULL,
    `liveroom_priority`       int(11)       NOT NULL,
    `last_push_time`          datetime      NULL,
    `channel_id`              int(11)       NULL,
    `liveroom_id`             int(11)       NULL,
    `need_signup`             boolean       NOT NULL,
    `signup_condition`        varchar(256)  NOT NULL,
    `reward_config`           varchar(128)  NOT NULL,
    `reward_percent`          int(11)       NOT NULL,
    `category_id`             int(11)       NULL,
    `is_invite_custom_active` boolean       NOT NULL,
    `description_img`         varchar(2000) NULL,
    `lecture_mode`            varchar(16)   NOT NULL,
    `can_public_relay`        boolean       NOT NULL,
    `relay_fee`               bigint(20)    NOT NULL,
    `avatar`                  varchar(255)  NULL,
    `show_in_liveroom_list`   boolean       NOT NULL,
    `is_show_qrcode`          boolean       NOT NULL,
    `subtitle`                varchar(300)  NULL,
    `custom_sharecard`        varchar(256)  NULL,
    `need_pay_channel`        boolean       NOT NULL,
    `lecture_type`            varchar(30)   NOT NULL,
    `n_status`                varchar(20)   NOT NULL,
    `remark`                  text          NULL COMMENT '仅存储课程属性，不用于查询',
    `group_id`                int(11)       NOT NULL COMMENT '分组ID'
) ENGINE = JDBC_EXTERNAL_TABLE;
____9