It recently came up during an overhaul of one of our SQL databases, that Ignition is sending multiple “SELECT 1” queries. I went through the settings of that connection with our database manager and the only obvious source of that was the “Test on Borrow” Setting, which was on, by default. All other “Test on” settings were off, also by default.
We turned that setting off, and it remains off.
The next day, our DB manager got in touch with me that the spam was still continuing. About every 13ms or so:
The only code that we have written to touch that database is activated every 15-20 minutes or so during plant operations, and does not include any SELECT 1 Statements.
Unfortunately, nothing breaks. The problem arose because the constant touches of the database were blocking a change to the database our computer group was attempting. We disabled it long enough for them to update what they needed to update and turned it on again with no issues.
Yep, we have 10 second validation timeouts. Max wait is 5 seconds, Eviction time is 1,800 seconds. These are the only timing controls available on that page. They are all set by default.
Have confirmed with our Database manager that this is happening on all databases that Ignition has access to. Some read only, some read/write.
This is therefore definitely NOT a result of the code written to interface with the databases, as it is uniform despite the different types of codes and their different activation (timers vs. events). And, as it is happening across all of them seems to be due to a default setting in how Ignition makes connections, not related to read/write or whether a query is even desired.
The test-on-borrow setting, which defaults to true, causes every query elsewhere in Ignition to be preceded by a validation query. Which makes a bunch of validation queries on busy systems. It’s entirely possible there’s a bug on that setting. Perhaps it only takes effect after the connection is disabled then re-enabled. Or perhaps only when the entire gateway is restarted.
If you put the logger gateway.Database.ConnectionManager.FaultedRetryDaemon on DEBUG or higher, then reset your DB connection (just edit -> save with the same settings), you should see what we “expect” the validation query, rate, etc, settings to be.
22Nov2019 11:14:28 Completed test for datasource “######” in 1 ms, status is now: Valid
FaultedRetryDaemon 22Nov2019 11:14:28 Running test for datasource “######” (Current status = Valid)
FaultedRetryDaemon 22Nov2019 11:14:18 Completed test for datasource “######” in 2 ms, status is now: Valid
FaultedRetryDaemon 22Nov2019 11:14:18 Running test for datasource “######” (Current status = Valid)
FaultedRetryDaemon 22Nov2019 11:14:08 Completed test for datasource “######” in 2 ms, status is now: Valid
FaultedRetryDaemon 22Nov2019 11:14:08 Running test for datasource “######” (Current status = Valid)
You are correct. Thanks, and sorry. I copy pasted that, and only looked at the trailing digits (26.437 and 36.450, for instance). I’m more or less working as an intermediary for our computer group, and I didn’t inspect that as much as I should have.
Edit:
Thanks for your response. What it -seems like- is our computers group is upset the Ignition server is pinging the SQL databses to make sure they are still up for whenever it needs to write. The problem now seems to be getting an idea of what they consider to be reasonable and modifying the validation timeout. Apologies for the long thread, and my illiteracy (innumeracy?). Thanks for the injection of RTFMA commonsense, chi.
