【详述】从2.3.16 升级到2.5.19，使用外部表的方式访问MySQL8.0.32，能正常创建外部表，但是访问数据报错
【背景】无
【业务影响】
【是否存算分离】否
【StarRocks版本】2.5.19
【集群规模】5fe（3 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，104C/256G/万兆
【表模型】外部表
【导入或者导出方式】JDBC引擎
【联系方式】社区群9群-微笑人生
【附件】
-
查询报错相应截图
SR外部表
CREATE EXTERNAL TABLE ads_ec.rt_o_ec_douyin_ec_retail_live_duty_plan(
idbigint(20) NULL COMMENT “主键ID”,
lrm_person_duty_idint(11) NULL COMMENT “直播间档案排班计划ID”,
flowint(11) NULL COMMENT “流量”,
consumeint(11) NULL COMMENT “消耗”,
order_amountint(11) NULL COMMENT “订单数量”,
saleint(11) NULL COMMENT “销售额”,
roidecimal(16, 2) NULL COMMENT “ROI”,
conversiondecimal(16, 2) NULL COMMENT “转化率”,
lucky_bagint(11) NULL COMMENT “福袋”,
red_packetint(11) NULL COMMENT “红包”,
seckillint(11) NULL COMMENT “秒杀”,
created_timedatetime DEFAULT CURRENT_TIMESTAMP COMMENT “创建时间”,
created_byvarchar(96) NULL COMMENT “创建人编码”,
created_namevarchar(60) NULL COMMENT “创建人姓名”,
changed_timedatetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT “修改时间”,
changed_byvarchar(96) NULL COMMENT “修改人编码”,
changed_namevarchar(60) NULL COMMENT “修改人姓名”,
del_flagsmallint(6) NULL DEFAULT “0” COMMENT “是否删除：0-未删除（默认值）1-删除”,
senkawa_price_mindecimal(16, 2) NULL COMMENT “千川出价最小值”,
lucky_bag_frequencyint(11) NULL COMMENT “福袋频次”,
lucky_bag_amountint(11) NULL COMMENT “福袋数量”,
lucky_bag_product_codeBIGINT NULL COMMENT “福袋品号”,
senkawa_price_maxdecimal(16, 2) NULL COMMENT “千川出价最大值”
) ENGINE=JDBC
PROPERTIES (
“resource” = “mysql_ec_douyin_234_89_3306”,
“table” = “ec_retail_live_duty_plan”
);
MySQL建表SQL
预格式化文本将缩进 4 格
CREATE TABLE ec_retail_live_duty_plan ( id int unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’, lrm_person_duty_id int DEFAULT NULL COMMENT ‘直播间档案排班计划ID’, flow int DEFAULT NULL COMMENT ‘流量’, consume int DEFAULT NULL COMMENT ‘消耗’, order_amount int DEFAULT NULL COMMENT ‘订单数量’, sale int DEFAULT NULL COMMENT ‘销售额’, roi decimal(16,2) DEFAULT NULL COMMENT ‘ROI’, conversion decimal(16,2) DEFAULT NULL COMMENT ‘转化率’, lucky_bag int DEFAULT NULL COMMENT ‘福袋’, red_packet int DEFAULT NULL COMMENT ‘红包’, seckill int DEFAULT NULL COMMENT ‘秒杀’, created_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’, created_by varchar(32) DEFAULT NULL COMMENT ‘创建人编码’, created_name varchar(20) DEFAULT NULL COMMENT ‘创建人姓名’, changed_time timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘修改时间’, changed_by varchar(32) DEFAULT NULL COMMENT ‘修改人编码’, changed_name varchar(20) DEFAULT NULL COMMENT ‘修改人姓名’, del_flag tinyint(1) NOT NULL DEFAULT ‘0’ COMMENT ‘是否删除：0-未删除（默认值）1-删除’, senkawa_price_min decimal(16,2) DEFAULT NULL COMMENT ‘千川出价最小值’, lucky_bag_frequency int DEFAULT NULL COMMENT ‘福袋频次’, lucky_bag_amount int DEFAULT NULL COMMENT ‘福袋数量’, lucky_bag_product_code bigint unsigned DEFAULT NULL COMMENT ‘福袋品号’, senkawa_price_max decimal(16,2) DEFAULT NULL COMMENT ‘千川出价最大值’, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1053 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=‘电商零售计划-排班时间段计划’
报错日志
预格式化文本将缩进 4 格org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1064] [42000]: JDBC result type of column[lucky_bag_product_code] is [java.math.BigInteger], StarRocks does not recognize it, please set the type of this column to varchar to avoid information loss.
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:135)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:505)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:436)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:423)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:804)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3008)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:120)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:168)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:118)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4425)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: JDBC result type of column[lucky_bag_product_code] is [java.math.BigInteger], StarRocks does not recognize it, please set the type of this column to varchar to avoid information loss.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Co
解决方法：
换成Mysql引擎就可以了，JDBC引擎不行，但是那个认证方式还是要改的
1、让DBA修改账号的认证方式
预格式化文本将缩进 4 格ALTER USER ‘your_username’@‘ip’ IDENTIFIED WITH mysql_native_password BY ‘your_passwd’;
2、 使用Mysql引擎
示例sql如下：
预格式化文本将缩进 4 格CREATE EXTERNAL TABLE ads_ec.rt_o_ec_douyin_ec_retail_live_duty_plan (
id bigint NULL COMMENT “主键ID”,
lrm_person_duty_id bigint NULL COMMENT “直播间档案排班计划ID”,
flow bigint NULL COMMENT “流量”,
consume bigint NULL COMMENT “消耗”,
order_amount bigint NULL COMMENT “订单数量”,
sale bigint NULL COMMENT “销售额”,
roi decimal(16, 2) NULL COMMENT “ROI”,
conversion decimal(16, 2) NULL COMMENT “转化率”,
lucky_bag bigint NULL COMMENT “福袋”,
red_packet bigint NULL COMMENT “红包”,
seckill bigint NULL COMMENT “秒杀”,
created_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT “创建时间”,
created_by varchar(300) NULL COMMENT “创建人编码”,
created_name varchar(300) NULL COMMENT “创建人姓名”,
changed_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT “修改时间”,
changed_by varchar(300) NULL COMMENT “修改人编码”,
changed_name varchar(300) NULL COMMENT “修改人姓名”,
del_flag smallint(6) NULL DEFAULT “0” COMMENT “是否删除：0-未删除（默认值）1-删除”,
senkawa_price_min decimal(16, 2) NULL COMMENT “千川出价最小值”,
lucky_bag_frequency bigint NULL COMMENT “福袋频次”,
lucky_bag_amount bigint NULL COMMENT “福袋数量”,
lucky_bag_product_code bigint NULL COMMENT “福袋品号”,
senkawa_price_max decimal(16, 2) NULL COMMENT “千川出价最大值”
) ENGINE=mysql
PROPERTIES (
“host” = “your_ip”,
“port” = “your_port”,
“user” = “etl_cdc”,
“password” = “xx”,
“database” = “ec_douyin”,
“table” = “ec_retail_live_duty_plan”
);