[Kestra EE] SQL Triggers broken in 0.19

[johnkm516]

Describe the issue

None of the SQL Triggers like

triggers:
  - id: trigger
    type: io.kestra.plugin.jdbc.sqlserver.Trigger
    logLevel: WARN
    interval: 5.0
    url: "{{ secret('db.plm.jdbc_uri') }}"
    username: "{{ secret('db.plm.username') }}"
    password: "{{ secret('db.plm.password') }}"
    sql: |
      SELECT TOP(1) 1 FROM tablename
    fetch: true
    fetchSize: 100

are working properly since 0.19.
I have a default worker and a worker group with a worker group key deployed. The triggers are executed by the default worker (no worker group key specified in the flow YAML).
Looking at the server logs for the default worker, the triggers are not being executed at all. However tasks with no worker specified (such as an SQL query task with no worker key specified) seem to work fine. Only triggers are affected.

Environment

• Kestra Version: 0.19.2
• Operating System (OS/Docker/Kubernetes): Docker

[loicmathieu]

I tried it with 0.19.2 (and develop) and I cannot reproduce the error.
Which backend do you use, JDBC or Kafka?
Do you also use multi-tenancy?

[johnkm516]

Hi @loicmathieu ,

Yes, I use multi-tenancy, and I'm on JDBC architecture.
As I showed you in our previous meeting I have 100+ flows using SQL trigger. When I upgraded to 0.19.2, none of these would trigger, despite data being queried (when I copy and paste the query it would return rows).

I'll try again just in case there was some other error, I did have this error when I was first attempting to deploy, but the problem eventually resolved itself after failing to deploy the container a bunch of times.

Caused by: org.flywaydb.core.api.FlywayException: Number of retries exceeded while attempting to acquire PostgreSQL advisory lock. Configure the number of retries with the 'lockRetryCount' configuration option: https://rd.gt/3A57jfk

[johnkm516]

I just discovered why it wasn't working.

This new mandatory field was added to the SQL trigger. This is a huge breaking change that was not mentioned in the 0.19 breaking changes. I have to update 100+ flows because of this.

[Ben8t]

@johnkm516 this should be fixed in the upcoming bug release today. You should be able to use your original flows without any change.

[loicmathieu]

Fixed by kestra-io/plugin-jdbc#399

It will be included in today 0.19 release

[Ben8t]

Tested on my hand, seems ok 👍
@johnkm516 do not hesitate to share if everything's ok here