Connection not closing.

[Wet-Bandit]

Using a simple SQL destination that Inserts into a Postgresql database.
Not using javascript, using the Mirth UI.
When channel is done the database still shows the connection being used and mirth connect does not close it.

Mirth Connect Server 3.10.0
Built on November 5, 2020
Java version: 1.8.0_181

[tonygermano]

When using a Database Writer in query mode mirth will reuse the same connection on the next insert rather than opening and closing a connection for each message.

[Wet-Bandit]

I am talking about when all the messages are done and the destination does it's final INSERT.
The database connection stays open when it should close. (Also, this particular channel does a query on ORACLE in the source connector, but INSERTS into PostgreSQL database in the destination.)

[tonygermano]

When I said query mode, I meant not javascript mode. In SQL an insert is still technically a query.

You are wrong that the connection should close. The User Guide says in the Database Writer section:

The database connection will automatically be kept open across multiple dispatches.

[Wet-Bandit]

Dispatches means message from the source connector. When that poll is done though and it isn't going to run again for 24 hours, it shouldn't be holding onto a DB connection.

[tonygermano]

Not all source connectors poll, and many polling connectors run more frequently than every 24 hours. The default polling interval is 5 seconds.

[Wet-Bandit]

Any suggestions? Is there a way to get mirth connect to close the destination database connection? Put the Database Writer into INSERT mode instead of QUERY mode?

[tonygermano]

you can switch to javascript mode and manage the connection yourself

[Wet-Bandit]

Yes, I get really tired of that being the answer to everything. What's the point of the Mirth Connect UI, if it doesn't work correctly?
What is strange is that I have other channels that INSERT into ORACLE and we don't have this problem.

[tonygermano]

It is working correctly. It's behaving exactly the way it claims it should be, and the way that most people want it to behave.

If the way it handles connections doesn't work for you, you can open a feature request and explain why you need additional behavior beyond what is already provided. There is a Discussions category for Feature/Enhancement Ideas. In the mean time, you have the ability to handle the connections in any way you choose.

[Wet-Bandit]

I don't think properly closing a connection is "additional behavior".

[Wet-Bandit]

Some channels that INSERT into ORACLE don't hold onto the connection. The ones that INSERT into PostgreSQL do hold onto connection. Something is different with the driver or the closing of a connection. Permanently holding onto a destination (Database Writer) connection when there are no more messages does not make any sense and cannot be considered "working correctly". Most databases also have a timeout setting, which means that when the channel runs again (say 24 hours later) that connection is bad/stale anyway and won't work and Mirth connect would have to refresh/get a new connection). There is no way that this is correct.

[Wet-Bandit]

I have a fix for this but it is a really ridiculous to have to do this.

1. Added a final destination that runs on the last message and undeploys the channel it is running in. (closes db connection)
2. Added another Time based channel that when it runs, it deploys/starts the first channel in time to run on its time-based setting.

[pacmano1]

I'd think you would want the connection to stay open given the potential overhead of opening a connection. Of course there are exceptions, e.g. reading / writing that DB just once or very spread out during the day. If that's the use case, it's a fair request as an optional config thing, but I would want not want that as the default behavior. Having said that, I always use a cached db connection, and never close it - that is far easier to manage IMO. So effectively the connection to the database server is always open since so many channels and processes would be opening/closing it unnecessarily. That connection is persisted in $g.

The code template here: https://forums.mirthproject.io/forum/mirth-connect/support/16507-configuration-map?p=93365#post93365. I did not write it - I think it might have originated from the Mirth Advanced class.