[mongodb-x]-mongo维表连接失败

[waryars]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

Query failed with error code 13 and error message 'not authorized on cmdb to execute command { find: "ccObjInst_application", limit: 1000 }' on server 172.16.57.164:27017

What you expected to happen

维度连接成功使用

How to reproduce

一、环境准备
1、mysql表：order_common_bak0219
CREATE TABLE order_common_bak0219 (
id bigint(20) NOT NULL COMMENT 'ID',
worker_title varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '工单标题',
workflow_name varchar(50) DEFAULT NULL COMMENT '流程名称',
definition_id varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '流程引擎id',
version int(11) DEFAULT NULL COMMENT '乐观锁',
instance_id varchar(50) DEFAULT NULL COMMENT '流程实例id',
section_name varchar(50) DEFAULT NULL COMMENT '板块名称',
section varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '板块ID',
satisfaction smallint(2) DEFAULT NULL COMMENT '满意度',
create_by varchar(32) DEFAULT NULL,
update_by varchar(32) DEFAULT NULL,
update_date datetime DEFAULT NULL,
create_date datetime DEFAULT NULL,
status int(5) DEFAULT NULL COMMENT '工单状态: 0待提交,1进行中,2已取消,3已关闭,4已解决',
order_urgency varchar(20) DEFAULT NULL COMMENT '紧急程度',
handle_by varchar(512) DEFAULT NULL,
service_path varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '服务目录',
order_type varchar(50) DEFAULT NULL COMMENT '工单类型',
starter varchar(50) DEFAULT NULL COMMENT '提单人',
start_date datetime DEFAULT NULL COMMENT '提单时间',
del tinyint(2) DEFAULT '0' COMMENT '默认0,1删除,2作废',
recall tinyint(2) DEFAULT '0' COMMENT '是否支持撤回,0否,1是',
classify_id bigint(20) DEFAULT NULL COMMENT '服务目录id',
classify_section_id varchar(50) DEFAULT NULL COMMENT '服务目录所属板块ID',
classify_section_name varchar(20) DEFAULT NULL COMMENT '服务目录所属板块名称',
cmdb_app_id varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT 'cmdb应用的appid',
from_api tinyint(2) DEFAULT '0' COMMENT '默认0,1代表来源于api',
belong_to_section_id varchar(36) DEFAULT NULL COMMENT '工单归属板块ID',
belong_to_section_name varchar(60) DEFAULT NULL COMMENT '工单归属板块名称',
order_code varchar(30) DEFAULT NULL COMMENT '工单编码',
PRIMARY KEY (id),
UNIQUE KEY instance_id_indx (instance_id) USING BTREE,
KEY section_indx (section) USING BTREE,
KEY create_indx (create_date) USING BTREE,
KEY status_type_indx (status,order_urgency,order_type) USING BTREE,
KEY title_indx (worker_title) USING BTREE,
KEY classify_index (classify_id),
KEY cmdb_app_indx (cmdb_app_id),
KEY order_common_order_code_index (order_code),
KEY order_common_belong_to_section_id_index (belong_to_section_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='公共流程任务表'

2、mysql表：order_form_details_bak0219
CREATE TABLE order_form_details_bak0219 (
id bigint(20) NOT NULL COMMENT 'ID',
task_id varchar(50) DEFAULT NULL COMMENT '任务id',
instance_id varchar(50) DEFAULT NULL COMMENT '流程实例id',
form_details json DEFAULT NULL COMMENT '表单明细',
create_by varchar(32) DEFAULT NULL,
update_by varchar(32) DEFAULT NULL,
update_date datetime DEFAULT NULL,
create_date datetime DEFAULT NULL,
comm_id bigint(20) DEFAULT NULL COMMENT '关联order表主键',
form_key_id bigint(20) DEFAULT NULL,
simple_details json DEFAULT NULL COMMENT '表单明细(不包括fulltext)',
section varchar(50) DEFAULT NULL COMMENT '板块ID',
handler varchar(50) DEFAULT NULL COMMENT '处理人',
handle_date datetime DEFAULT NULL COMMENT '处理时间',
del tinyint(2) DEFAULT '0' COMMENT '默认0,1删除,2作废',
handle_result varchar(50) DEFAULT NULL COMMENT '记录节点处理结果',
node_name varchar(160) DEFAULT NULL COMMENT '节点名称',
task_definition_key varchar(60) DEFAULT NULL COMMENT '节点key',
application char(36) GENERATED ALWAYS AS (json_unquote(json_extract(form_details,_utf8mb4'$.application'))) VIRTUAL,
cmdbProductGroupId char(36) GENERATED ALWAYS AS (json_unquote(json_extract(form_details,_utf8mb4'$.cmdbProductGroupId'))) VIRTUAL,
actualManDay decimal(18,2) GENERATED ALWAYS AS (json_unquote(json_extract(form_details,_utf8mb4'$.actualManDay'))) VIRTUAL,
weekdetailTable varchar(1000) GENERATED ALWAYS AS (json_unquote(regexp_replace(json_extract(form_details,_utf8mb3'$.weekdetailTable'),_utf8mb4'\\\\n|\\\\r|\\\\t',_utf8mb3''))) VIRTUAL,
PRIMARY KEY (id),
KEY section_indx (section) USING BTREE,
KEY task_comm_index (comm_id,task_id,instance_id) USING BTREE,
KEY ofd_task_id_index (task_id),
KEY handle_date_index (handle_date),
KEY form_key_id_index (form_key_id),
KEY create_date_index (create_date),
KEY idx_task_definition_key (task_definition_key,comm_id,create_date),
KEY idx_application (application),
KEY idx_cmdbProductGroupId (cmdbProductGroupId),
KEY idx_task_definition_key_cid (task_definition_key,cmdbProductGroupId),
KEY idx_task_definition_key_cid_ (task_definition_key,del,cmdbProductGroupId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='流程运行表单明细(通过task_id跟order_common关联)'

3、mongo集合：ccObjInst_application
结构示例：
{
"_id" : ObjectId("6020d6c5d7e3f40001e225ca"),
"name" : "集团官方网站",
"app_id" : "JTWEB",
"maintainer" : "linshuying",
"version" : "",
"image" : "",
"developer" : "",
"tester" : "",
"release_date" : "",
"update_date" : "",
"run_mode" : "虚机",
"labels" : "[{"app":"huafagroup"}]",
"url" : "NA",
"source" : "NA",
"remark" : "",
"actualapp" : "是",
"id" : "1b4a9f1a4cbc4d81a3e6bf98ce7a5945",
"bkObjId" : "application",
"createTime" : ISODate("2021-02-08T06:14:29.921+0000"),
"updateTime" : ISODate("2021-02-08T06:14:29.921+0000"),
"cmdbCreateTime" : ISODate("2021-02-08T06:14:29.921+0000"),
"cmdbUpdateTime" : ISODate("2024-02-21T08:20:00.441+0000"),
"manager" : "linshuying",
"src" : "自研",
"pm" : "",
"productName" : "办公管理",
"productGroupName" : "办公协同",
"apptype" : "业务",
"level" : "B",
"segmentName" : "集团本部",
"productId" : "70b2772f00cf46daac5de973b9cfa597",
"productGroupId" : "659d8a73b65f4cff89fc5604f02a49e8",
"segmentId" : "3a57ef675fff4cea9f55102d12d983c7",
"sys_code" : "",
"vendor" : "",
"supplierTel" : "",
"supplierEmail" : "",
"supplierManager" : "",
"plan_stage" : "运维",
"appProductManager" : "linshuying",
"deptInformationizeLeader" : "",
"sort" : "8",
"alias" : ""
}

二、脚本配置
脚本：itsm_dwd_dim.sql
[root@t-hadoop01 binlog]# cat itsm_dwd_dim.sql
CREATE TABLE ods_s9132_order_common
(
id bigint,
worker_title varchar,
create_by varchar,
update_by varchar,
update_date timestamp,
create_date timestamp,
status int,
order_urgency varchar,
order_type varchar,
starter varchar,
start_date timestamp,
del int,
classify_id bigint,
order_code varchar
) WITH (
'connector' = 'binlog-x'
,'username' = 'itsm_repl'
,'password' = 'xxxxxxx'
,'cat' = 'insert,delete,update'
,'url' = 'jdbc:mysql://172.16.44.7:3306/itsm_db?useSSL=false'
,'host' = '172.16.44.7'
,'port' = '3306'
,'table' = 'itsm_db.order_common_bak0219'
,'timestamp-format.standard' = 'SQL'
);

CREATE TABLE ods_s9132_order_form_details
(
id bigint,
form_details varchar,
application varchar,
cmdbProductGroupId varchar,
create_by varchar,
update_by varchar,
update_date timestamp,
create_date timestamp,
comm_id bigint,
handler varchar,
handle_date timestamp,
del int,
task_definition_key varchar,
PROCTIME AS PROCTIME()
) WITH (
'connector' = 'binlog-x'
,'username' = 'itsm_repl'
,'password' = 'xxxxxx'
,'cat' = 'insert,delete,update'
,'url' = 'jdbc:mysql://172.16.44.7:3306/itsm_db?useSSL=false'
,'host' = '172.16.44.7'
,'port' = '3306'
,'table' = 'itsm_db.order_form_details_bak0219'
,'timestamp-format.standard' = 'SQL'
);

CREATE TABLE ODS_S9124_CCOBJINST_APPLICATION
(
id varchar,
segmentId varchar,
segmentName varchar,
productGroupId varchar,
productGroupName varchar,
app_id varchar,
name varchar,
sort varchar
) WITH (
'connector' = 'mongodb-x',
'uri' = 'mongodb://172.16.57.164:27017/cmdb?authSource=admin',
'database' = 'cmdb',
'username' = 'admin',
'password' = 'xxxxxx',
'collection' = 'ccObjInst_application',
'lookup.cache-type' = 'all'
);

CREATE TABLE dwd_itop_order_form_details
(
order_no varchar,
worker_title varchar,
order_kind varchar,
starter varchar,
start_date timestamp,
tmon varchar,
status int,
order_status varchar,
handler varchar,
handle_date timestamp,
form_details varchar,
task_definition_key varchar,
application varchar,
cmdbProductGroupId varchar
) WITH (
'connector' = 'kafka-x'
,'topic' = 'flinkcdc-mysql'
,'properties.bootstrap.servers' = '172.16.56.254:34715'
,'value.format' = 'debezium-json'
);

insert into dwd_itop_order_form_details
select a.order_code as order_no
,a.worker_title
,'运维工单' as order_kind
,a.starter
,a.start_date
,DATE_FORMAT(a.start_date, 'yyyy-MM') as tmon
,a.status
,case when a.status=4 then '已解决' else '进行中' end as order_status
,b.handler
,b.handle_date
,b.form_details
,b.task_definition_key
,b.application
,b.cmdbProductGroupId
from ods_s9132_order_common a
join (
select b.*
from ods_s9132_order_form_details b
where b.del=0
) b
on (a.id=b.comm_id)
left join ODS_S9124_CCOBJINST_APPLICATION for system_time as of b.PROCTIME as c
on (b.application=c.id)
where a.status<>0 -- 排除待提交
and a.del=0
;

三、执行命令
sh bin/chunjun-local.sh -job chunjun-examples/sql/binlog/itsm_dwd_dim.sql

四、执行命令后报错

Anything else

No response

Version

1.12_release

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct