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

Camunda 8 Topics Discussion & Questions
,
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:

image (1)
image (1)778×596 15.4 KB

image (2)
image (2)772×794 24.9 KB

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:

image (3)
image (3)1916×907 39.7 KB

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.

2

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:

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

  1. 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.
  2. You can fork the Connectors and develop your own fix, or develop your own Connector.
  3. Use a service task with a job worker, and have the job worker execute the SQL needed.
3

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.

4

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.

5

@kamilleB - we do have a certification program for Connectors in the Marketplace! It’s intended to help avoid this exact kind of confusion. It is very new (as is the Marketplace), so developers/partners are still working on their certifications.

1 Like
6

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

image
image1877×732 31.2 KB

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:

image
image1920×919 134 KB

7

@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')

8

Hi @ev.bilske, nothing changed unfortunately:

image
image1875×895 32.2 KB

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)?

9

what version of camunda are you using?

10

Hi, I am currently using the version 8.5

11

@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.

12

@kamilleB Note that this connector (backend side) will be enabled starting from version 8.6.0-alpha2.

13

hi @jroques, what does that mean? Does it mean for now it’s not usable for 8.5 versions?

14

Hi @ev.bilske ,
I’ll try all that, and will make a feedback.

15

Exactly, if you’re using docker I think you can try the corresponding tag (8.6.0-alpha2). It’s available here.

16

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!

3 Likes
17

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

18

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

After updating connector image to 8.6.0-alpha2:

image
image1113×164 15.4 KB

I tried again and nothing has changed…

image
image1485×780 34 KB

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…

19

@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?

20

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:

image

My SQL Database is coming from a Windows server in a VM located in the same network.

Should I declare my SQL Database and or my VM somewhere in the docker compose?