[BUG-2376] Database Connection Status Not Updating Automatically

I have a MS SQL Server database connection which is only queried on demand from a button. If I break the connection the gateway (by unplugging the ethernet cable or stopping the SQL server service), the status of the SQL server stays as “Valid” until I hit the button that queries that database. Then the status goes to reconnecting and eventually faulted. When I reconnect to the database, the status remains at faulted even if I re-query the database. I restore the database connection by opening the connection configuration and saving it again.

Is this the expected behavior? My understanding is that it would run the “Validation Query” every time the “Validation Timeout” elapses. I expected that the database connection would update automatically without me needing to run queries. My validation query is the default “select 1” and the timeout is 10000 milliseconds.

@benjamin.sperl, what version (and edition) of MSSQL are you running? Also, what version of Ignition? The validation query is indeed intended to verify connection health and should be updating the DB state automatically.

@kcollins1 I am running Ignition version 8.1.4. The SQL server version is 15.0.2000.5 and the edition is “Developer Edition (64-bit)”

I’ve been able to reproduce this locally with a test Docker Compose stack, e.g.:

---
services:
  gateway:
    image: inductiveautomation/ignition:8.1.4
    ports:
      - 8088:8088
    networks:
      - default
      - db
    command: |
      -a 127.0.0.1
      -h 8088
      -s 8043

  db:
    image: kcollins/mssql:2019
    ports:
      - 1433:1433
    networks:
      - db
    environment:
      # ACCEPT_EULA confirms your acceptance of the End-User Licensing Agreement.
      ACCEPT_EULA: Y
      # make sure to still use a strong password here, otherwise SQL Server will not start
      SA_PASSWORD: ch@nge_m3 
      MSSQL_DATABASE: ignition
      MSSQL_USER: ignition
      MSSQL_PASSWORD: ch@nge_m3
      MSSQL_PID: Developer  # Change to the edition you need, e.g. "Express", "Standard", etc.

networks:
  db:

From here I ended up testing the disconnect and reconnect with (the folder with my compose solution is named mssql-connection-test):

# Disconnect
docker network disconnect mssql-connection-test_db mssql-connection-test_db_1

# Reconnect w/ db alias
docker network connect --alias db mssql-connection-test_db mssql-connection-test_db_1

All the Docker stuff above is immaterial to the issue, but I wanted to share for those who might find it interesting. Ultimately, I was able to see the same “stuck” behavior with the DB connection, including the inability for it to go back to Valid on its own. It also seems to apply to other DB connection types as well… I’m opening a ticket to investigate further.

It seems that this may currently manifest from editing an existing DB connection, which seems to be causing the connection validation task to be unregistered. You can see the executions occurring (and the registration and unregistration of tasks) by enabling DEBUG against the Common.BasicExecutionEngine and gateway.Database.ConnectionManager.FaultedRetryDaemon loggers:

You should see the tests running every 10 seconds by default, like shown above.

The workaround at the moment is to delete and re-create the DB connection; alternatively, a gateway restart should also re-initialize the connection properly.

2 Likes

This has been fixed in the 8.1.5 release.

1 Like