MS SQL and PostgreSQL connectors don't execute queries in Self Managed Camunda

相关文章推荐

风流倜傥的烈马 · 一文讲透超宽带（UWB）前世今生- ...· 4 月前 ·

刀枪不入的熊猫 · What's New in ...· 1 年前 ·

严肃的西红柿 · 苹果手机怎么投屏到电脑上_苹果手机怎么投屏到 ...· 1 年前 ·

空虚的钱包 · 美国侵犯难移民人权的事实真相· 1 年前 ·

温暖的海豚 · 动画电影扎堆上映《大护法》口碑未能引爆票房-新华网· 1 年前 ·

Hi everyone,

I am currently using Self-Managed Camunda on Docker on my laptop and I have a MS SQL database and a PostgreSQL database in a separated VM.
To access the both databases I am using the self-managed connectors like with this configuration:

I use the IP address of the VM as hostname. the VM and my laptop are in the same network.

And in the operate, no issue:

Problem, nothing happen in the database. If I put random hostname, random username, in the operate, the task will be executed without error.

Is there something I didn’t activated in Camunda self-Managed who prevent me to execute properly MS SQL or PostgreSQL queries?

(I used same configuration with another self managed tool and no issue with executing queries so not a network or port issues).

Thanks in advance.

Hi @kamilleB - the Postgres and MSSQL Connectors are contributed to the community by Infosys. From the Operate logs, it looks like the Connector is being invoked, which means the issue is likely with the Connectors themselves. Unfortunately, a quick look at the GitHub repository for those Connectors seems to confirm this:

github.com/Infosys/camunda-connectors mvn clean package … I changed the docker-comp …

So, what can you do, since it’s an issue with a third-party package rather than Camunda itself?

You can open another issue in the GitHub repository and/or comment on the issue linked above and wait for the maintainer to fix the issue.

You can fork the Connectors and develop your own fix, or develop your own Connector .

Use a service task with a job worker , and have the job worker execute the SQL needed.

Hi and thanks @nathan.loding ,

Well that’s unfortunate… I just wanted to create a quick demo of self-managed version of Camunda with a simple database and I finally may have to fix a connector provided by a partner in Camunda marketplace…

Is Camunda can check what’s inside marketplace and can remove connectors if they are not working? It would help others to not get struggled like me.

Hi @kamilleB - I’m sorry to hear you had issues, that sounds frustrating. Thank you for posting about it here.

For your initial issue: you could try the new Camunda-supported JDBC Connector as an alternative. We plan to formally release it as part of the 8.6.0-alpha2 release next month.

Thanks for your Marketplace feedback. I’ll bring it to the Connectors team to discuss further.

Hi @ev.bilske ,
unfortunately the JDBC connector is not working properly as it stuck at the task:

With the following metadata:
“flowNodeType”: “SERVICE_TASK”,
“flowNodeId”: “Activity_07x7b8x”,
“startDate”: “2024-05-30 14:17:18”,
“eventId”: “2251799814252992_2251799814252996”,
“jobType”: “io.camunda:connector-jdbc:1”,
“jobRetries”: 3,
“jobWorker”: “”,
“jobCustomHeaders”: {
“retryBackoff”: “PT0S”,
“resultVariable”: “dataresult”
“endDate”: null,
“jobDeadline”: null,
“incidentErrorType”: null,
“incidentErrorMessage”: null,
“flowNodeInstanceKey”: “2251799814252996”

So no error in the execution.

The documentation links doesn’t work so I cannot know if I fill the parameters in the right way.

Maybe some more guidance will help me to be able to make the connector works.

P.S.: On the modeler, when ticking the “Return results” checkbox:
I have this error message:

@kamilleB indeed, the documentation has not been published yet. That will happen closer to the formal release next month.

In the “data” variable (that contains the SQL query), try using this syntax:
INSERT INTO dbo.Customers (id, name) VALUES (1, 'value-1'),(2,'value-2')

Hi @ev.bilske , nothing changed unfortunately:

Here is the metadata:

“flowNodeType”: “SERVICE_TASK”,
“flowNodeId”: “Activity_07x7b8x”,
“startDate”: “2024-05-30 15:24:25”,
“eventId”: “2251799814262495_2251799814262499”,
“jobType”: “io.camunda:connector-jdbc:1”,
“jobRetries”: 3,
“jobWorker”: “”,
“jobCustomHeaders”: {
“retryBackoff”: “PT0S”,
“resultVariable”: “dataresult”
“endDate”: null,
“jobDeadline”: null,
“incidentErrorType”: null,
“incidentErrorMessage”: null,
“flowNodeInstanceKey”: “2251799814262499”

Is there a way to investigate where is the problem (Host, port or SQL query or even my config)?

kamilleB:

Is there a way to investigate where is the problem (Host, port or SQL query or even my config)?

what version of camunda are you using?

@kamilleB you could try these investigation steps:

Double check that you have access to the host

Double check credentials

Use

dbo

in the database property and remove it from the query

Wait for the task to timeout in Operate. Then, an error/incident message should appear providing more information.

If any of those work (or you find another solution) we’d love to hear about it as a reply here.

Thanks @ev.bilske and @jroques !

@kamilleB (and others who stumble on this thread) - Just to add a bit more context to understand what’s happening, the Connectors require two pieces to work: the element template , and the Connector itself running with the Connector Runtime . If you are using the 8.5 Docker images, the Connectors Runtime image does not contain the JDBC Connector; you would either need to add it manually (by including the JAR in the classpath in the container, or by manually adding and configuring it within the container), or use the 8.6.0-alpha2 images. Hope that helps the situation make a little more sense!

Hey, @kamilleB , Did you run the connector yet? Example on Linux run the connector with this command.
java -cp 'spring-zeebe-connector-runtime-8.1.17-with-dependencies.jar:connector-mysql-0.1.0-SNAPSHOT-with-dependencies.jar' io.camunda.connector.runtime.ConnectorRuntimeApplication

Hi and thanks @nathan.loding and the others who help me,

After updating connector image to 8.6.0-alpha2:

I tried again and nothing has changed…

Here is the metadata:
“flowNodeType”: “SERVICE_TASK”,
“flowNodeId”: “Activity_07x7b8x”,
“startDate”: “2024-05-31 11:09:28”,
“eventId”: “2251799814281620_2251799814281624”,
“jobType”: “io.camunda:connector-jdbc:1”,
“jobRetries”: 3,
“jobWorker”: “”,
“jobCustomHeaders”: {
“retryBackoff”: “PT0S”,
“resultVariable”: “dataresult”
“endDate”: null,
“jobDeadline”: null,
“incidentErrorType”: null,
“incidentErrorMessage”: null,
“flowNodeInstanceKey”: “2251799814281624”

I tried with dbo, without dbo in the SQL query, I tried multiple ports, I changed the SQL queries, the process doesn’t care and wait for long time. I don’t understand why the task doesn’t raise at least one error message to help debug…

@kamilleB it looks like a connection issue to me, you should have a timeout at some point though.
But I think the host is incorrect, you’re running the Connectors runtime inside a Container so if your docker compose file looks like this (or similar):

 connectors: # https://docs.camunda.io/docs/components/integration-framework/connectors/out-of-the-box-connectors/available-connectors-overview/
   image: camunda/connectors-bundle:${CAMUNDA_CONNECTORS_VERSION}
   container_name: connectors
   ports:
     - "8085:8080"
   environment:
     - ZEEBE_CLIENT_BROKER_GATEWAY-ADDRESS=zeebe:26500
     - ZEEBE_CLIENT_SECURITY_PLAINTEXT=true
     - ZEEBE_CLIENT_ID=${ZEEBE_CLIENT_ID}
     - ZEEBE_CLIENT_SECRET=${ZEEBE_CLIENT_SECRET}
     - ZEEBE_CLIENT_CONFIG_PATH=/tmp/zeebe_auth_cache
     - ZEEBE_TOKEN_AUDIENCE=zeebe-api
     - ZEEBE_AUTHORIZATION_SERVER_URL=http://keycloak:8080/auth/realms/camunda-platform/protocol/openid-connect/token
     - CAMUNDA_OPERATE_CLIENT_URL=http://operate:8080
     - CAMUNDA_IDENTITY_ISSUER_BACKEND_URL=http://keycloak:8080/auth/realms/camunda-platform
     - CAMUNDA_IDENTITY_CLIENT_ID=connectors
     - CAMUNDA_IDENTITY_CLIENT_SECRET=XALaRPl5qwTEItdwCMiPS62nVpKs7dL7
     - CAMUNDA_IDENTITY_TYPE=KEYCLOAK
     - CAMUNDA_IDENTITY_AUDIENCE=operate-api
     - management.endpoints.web.exposure.include=health
     - management.endpoint.health.probes.enabled=true
   env_file: connector-secrets.txt
   healthcheck:
     test: [ "CMD-SHELL", "curl -f http://localhost:8080/actuator/health/readiness" ]
     interval: 30s
     timeout: 1s
     retries: 5
     start_period: 30s
   networks:
     - camunda-platform
   depends_on:
     - zeebe
     - operate
     - identity
and your database is created from a docker compose file as well, like this:
  mssqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    restart: always
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password123
      - MSSQL_PID=developer
    ports:
      - 1433:1433
    networks:
      - camunda-platform_camunda-platform
Then you shouldn’t use 10.x.x.x to access the MSSQL database, but the service name: mssqlserver in my case.
Could you try this and let us know?
              Hi @jroques,
Yes I have same config like you:
connectors: # https://docs.camunda.io/docs/components/integration-framework/connectors/out-of-the-box-connectors/available-connectors-overview/
    image: camunda/connectors-bundle:${CAMUNDA_CONNECTORS_VERSION}
    container_name: connectors
    ports:
      - "8085:8080"
    environment:
      - ZEEBE_CLIENT_BROKER_GATEWAY-ADDRESS=zeebe:26500
      - ZEEBE_CLIENT_SECURITY_PLAINTEXT=true
      - ZEEBE_CLIENT_ID=${ZEEBE_CLIENT_ID}
      - ZEEBE_CLIENT_SECRET=${ZEEBE_CLIENT_SECRET}
      - ZEEBE_CLIENT_CONFIG_PATH=/tmp/zeebe_auth_cache
      - ZEEBE_TOKEN_AUDIENCE=zeebe-api
      - ZEEBE_AUTHORIZATION_SERVER_URL=http://keycloak:8080/auth/realms/camunda-platform/protocol/openid-connect/token
      - CAMUNDA_OPERATE_CLIENT_URL=http://operate:8080
      - CAMUNDA_IDENTITY_ISSUER_BACKEND_URL=http://keycloak:8080/auth/realms/camunda-platform
      - CAMUNDA_IDENTITY_CLIENT_ID=connectors
      - CAMUNDA_IDENTITY_CLIENT_SECRET=XALaRPl5qwTEItdwCMiPS62nVpKs7dL7
      - CAMUNDA_IDENTITY_TYPE=KEYCLOAK
      - CAMUNDA_IDENTITY_AUDIENCE=operate-api
      - management.endpoints.web.exposure.include=health
      - management.endpoint.health.probes.enabled=true
    env_file: connector-secrets.txt
    healthcheck:
      test: [ "CMD-SHELL", "curl -f http://localhost:8080/actuator/health/readiness" ]
      interval: 30s
      timeout: 1s
      retries: 5
      start_period: 30s
    networks:
      - camunda-platform
    depends_on:
      - zeebe
      - operate
      - identity
But still it still stuck in the insert step:
My SQL Database is coming from a Windows server in a VM located in the same network.
 jroques:
and your database is created from a docker compose file as well, like this:
  mssqlserver:
    image: mcr.microsoft.com/mssql/server:2019-latest
    restart: always
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=password123
      - MSSQL_PID=developer
    ports:
      - 1433:1433
    networks:
      - camunda-platform_camunda-platform
Then you shouldn’t use 10.x.x.x to access the MSSQL database, but the service name: mssqlserver in my case.
Should I declare my SQL Database and or my VM somewhere in the docker compose?