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

[waryars]

Search before asking

• [X] 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

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