I’m having weird issues with my Ignition connection to our Oracle database where a new session is being opened for each new query that is sent from any client or designer. Our administrator has limited the number of sessions, but once they fill up they never go away and have to be manually killed by the admin. Oracle simply lists their status as INACTIVE.
From my understanding, the JDBC in Ignition is supposed to preallocate connections in a pool to the server on creation, and queries are supposed to use those, not create their own.
It would also be helpful if anyone knows how to write it commands to the connection creation area “Extra Connection Properties” because as far as I know documentation doesn’t exist for it other than the Apache DBCP, but I don’t know how to transfer that to Ignition.
Extra Connection Properties are defined by the JDBC driver; they’re simply passed through directly to the JDBC driver, so they’ll differ depending on what database you’re connecting to.
There seems to be an exhaustive (but poorly documented) list of valid properties here: https://docs.oracle.com/database/121/JAJDB/constant-values.html
This doesn’t really help me transfer that information into Ignition. I’m finding the documentation for Ignition very weak in a lot of areas, but database connections specifically.
What do you mean by transfer? We’re literally not doing anything with these properties besides adding them to the connection string that’s used by JDBC. What they do is up to the JDBC driver and the target database.
I’m genuinely not trying to be snarky or anything - I’m just not sure what information you’re looking for; if you’ve got specific questions I can try to answer them and then get that information to training to add to the manual as well.
Does this mean I can simply type Java commands into the single text input space in the connection settings? As for specific questions, this JDBC stuff is just an attempt to fix the larger issue.
Why is Ignition creating a new session in the Oracle database for each query? I’ve used only the default settings and drivers to set up the connection.
Example: I change some dates in my program, and 3 separate queries are using those dates as properties. I query the Oracle sessions and 3 new ones have appeared, with none of the old ones disappearing. When I hit the maximum sessions per user as defined by the administrator, that error prevents any new queries from running. Even old sessions from days ago are still running and will continue to run until manually killed.
So, as described - this 100% sounds like a bug. If you're not already in contact with support, that should definitely be your next step. There are various short-term workarounds you can try (such as idle connection eviction) that may "fix" the problem, but understanding the core issue is what we're really after. Unfortunately, as previously mentioned, a lot of this JDBC stuff is not really "our" code, as much as just use using off-the-shelf components; specifically, Apache Commons DBCP(2, in 8.0+) for the connection pooling, and whatever JDBC driver for the actual connection.
I know for a fact that there's an internal ticket to investigate connections not being closed on certain databases - that's with our bug analysis team right now, awaiting replication. That may have the same root cause as what you're describing...or it may not.
More specifically,
No, you can't write arbitrary java there. What you do is set various properties and values - extra connection properties could be something like: zeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=truedisableMariaDbDriver
Except, obviously, Oracle's JDBC driver will support a different set of properties than, in this case, MariaDB. The link I posted earlier in the thread is Oracle's API reference on what properties are able to be parsed from a connection string - I have no idea what allowed, expected, or default values for those properties are - only that those are the properties I would expect to do something to the JDBC driver. There may be a more specific manual for Oracle's JDBC driver somewhere - but I couldn't find it.
1 Like
Thanks for your help. I’ve submitted a ticket about this issue and will continue troubleshooting it.
1 Like