Database, Tag History Error Issues

Hi everyone,

We upgraded from a 7.9 to an 8.0 project, and merged it with another 8.0 project that only contains a perspective app, and I ended up with some strange database/historian issues.

Firstly, four of my database connections are no longer valid because integrated security is not accepted anymore by the jdbc driver for SQL Server. Is there an alternative way around this, aside from disconnecting integrated security?

Secondly, I am running into this gateway error (it’s flooding my logger, preventing me from seeing other errors, making me have to filter or pause the logger to get them), telling me that the historical sink DB does not exist. The tags that were associated with it no longer exist, so I can’t figure out why this is spamming my logger and can’t identify where it is coming from. Any ideas?

java.lang.IllegalArgumentException: Specified historical sink 'DB' does not exist.

at com.inductiveautomation.ignition.gateway.history.HistoryManagerImpl.storeHistory(HistoryManagerImpl.java:166)

at com.inductiveautomation.gateway.tags.history.actor.HistoryActorFactory$OnChangeEvaluationContainer.queueForStorage(HistoryActorFactory.java:700)

at com.inductiveautomation.gateway.tags.history.actor.HistoryActorFactory$AtomicHistorizer.processValue(HistoryActorFactory.java:377)

at com.inductiveautomation.ignition.gateway.tags.runtime.nodes.ExecutableTag.valueChanged(ExecutableTag.java:256)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.AbstractValueGeneratingActor.fireValueChange(AbstractValueGeneratingActor.java:41)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.AbstractValueGeneratingActor.fireValueChange(AbstractValueGeneratingActor.java:34)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.reference.DerivedTagValueActorFactory$DerivedTagValueActor.processIncomingValue(DerivedTagValueActorFactory.java:107)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.reference.TagReferenceValueActorFactory$TagReferenceValueActor.tagChanged(TagReferenceValueActorFactory.java:153)

at com.inductiveautomation.ignition.gateway.tags.subscriptions.ProviderSubscriptionManagerImpl$SubscriptionNode$PropertySubscription.distribute(ProviderSubscriptionManagerImpl.java:539)

at com.inductiveautomation.ignition.gateway.tags.subscriptions.ProviderSubscriptionManagerImpl$SubscriptionNode$PropertySubscription.distribute(ProviderSubscriptionManagerImpl.java:525)

at com.inductiveautomation.ignition.gateway.tags.subscriptions.ProviderSubscriptionManagerImpl$SubscriptionNode$PropertySubscription.valueChanged(ProviderSubscriptionManagerImpl.java:603)

at com.inductiveautomation.ignition.gateway.tags.evaluation.subscriptions.AbstractNodePropertyManager.fireSubscriptionToTarget(AbstractNodePropertyManager.java:206)

at com.inductiveautomation.ignition.gateway.tags.evaluation.subscriptions.AbstractNodePropertyManager.fireSubscription(AbstractNodePropertyManager.java:199)

at com.inductiveautomation.ignition.gateway.tags.runtime.nodes.BasicTagDefinition$TagPropertySubscriptionManager.fireSubscription(BasicTagDefinition.java:949)

at com.inductiveautomation.ignition.gateway.tags.evaluation.subscriptions.AbstractNodePropertyManager.fireSubscription(AbstractNodePropertyManager.java:102)

at com.inductiveautomation.ignition.gateway.tags.runtime.nodes.BasicTagDefinition.dispatchPropSubscription(BasicTagDefinition.java:563)

at com.inductiveautomation.ignition.gateway.tags.runtime.nodes.ExecutableTag.valueChanged(ExecutableTag.java:264)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.AbstractValueGeneratingActor.fireValueChange(AbstractValueGeneratingActor.java:41)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.AbstractValueGeneratingActor.fireValueChange(AbstractValueGeneratingActor.java:34)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.opc.OpcActorFactory$OpcActor.setValue(OpcActorFactory.java:638)

at com.inductiveautomation.ignition.gateway.opcua.client.connection.OpcUaSubscriptionSynchronizer$DataValueConsumer.accept(OpcUaSubscriptionSynchronizer.kt:566)

at com.inductiveautomation.ignition.gateway.opcua.client.connection.OpcUaSubscriptionSynchronizer$DataValueConsumer.accept(OpcUaSubscriptionSynchronizer.kt:537)

at org.eclipse.milo.opcua.sdk.client.subscriptions.OpcUaMonitoredItem.lambda$setValueConsumer$0(OpcUaMonitoredItem.java:130)

at org.eclipse.milo.opcua.sdk.client.subscriptions.OpcUaMonitoredItem.onValueArrived(OpcUaMonitoredItem.java:188)

at org.eclipse.milo.opcua.sdk.client.subscriptions.OpcUaSubscriptionManager.lambda$null$39(OpcUaSubscriptionManager.java:698)

at org.eclipse.milo.opcua.stack.core.util.ExecutionQueue$PollAndExecute.run(ExecutionQueue.java:106)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Cheers,
Roger

You should still be able to use integrated security - have you copied the requisite sqljdbc_auth.dll file into the appropriate location, per the KBA?

Can you do a full tag export? Or upload a .gwbk? It's possible there's some new bug in the tag system preventing references from being fully deleted.

We're trying to use SQL Authentication, and this was working in 7.9, but on changing to 8.0, it stopped working. I did move that file into the correct location and restarted the service, but I imagine that might only be for Windows Authentication. (We may move to Windows Auth from SQL Auth, but I'm not sure yet)

Sure, i'll see if I can get that. However, I may have found some tags that have that provider name hidden somewhere, but I had to dig through a lot to find them. So it may not be a bug, but if it is possible to show the tag name that is causing the problem in the gateway log, that would be immensely helpful.

Better logging is definitely something we should be able to do - I opened an internal ticket to look into adding context information to those loggers.

This is interesting - 'integrated security' is the MSSQL JDBC driver's word for "native auth, via the DLL/system login (see: their documentation) but SQL auth should still work the same as it did in 7.9. You restored a 7.9 gwbk onto 8.0? Have you tried manually updating the JDBC driver used? Restoring a .gwbk carries the JDBC driver(s) you had on the old system with it, which means you're still using the (very) old JDBC driver from your 7.9 install, which might be the root of the problem.

Is there a specific error message being logged indicating why that DB connection is failing?

Yeah, I restored a 7.9 gwbk onto 8.0. Should I steal the drivers from another clean 8.0 installation and move them over? Or is there a place to download them for 8.0?

There doesn’t seem to be an error being logged right now, but it was complaining about integratedSecurity not being a thing for the driver sometime yesterday before I started getting flooded with the datasource DB problem, haha.

You can definitely copy them from another installation, or just download them from the vendor: JDBC Drivers and Translators - Ignition User Manual 8.0 - Ignition Documentation

Man, copying and downloading that sqljdbc driver doesn’t seem to fix the problem. But I did manage to grab the error now! Any ideas?


java.sql.SQLException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication. ClientConnectionId:2cfbfc3b-23e4-4153-9ddb-4bd5fc2b4e8e)

at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2385)

at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2110)

at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:257)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:159)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:250)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.db.DBTagActorFactory$DBTagActor.getConnection(DBTagActorFactory.java:275)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.db.DBTagActorFactory$DBTagActor.runInternal(DBTagActorFactory.java:223)

at com.inductiveautomation.ignition.gateway.tags.actors.factories.value.AbstractExpressionActor.run(AbstractExpressionActor.java:117)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:2cfbfc3b-23e4-4153-9ddb-4bd5fc2b4e8e

at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:2400)

at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:68)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3132)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)

at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)

at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:291)

at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2395)

at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2381)

... 14 common frames omitted

Caused by: java.lang.UnsatisfiedLinkError: Native Library C:\Program Files\Inductive Automation\Ignition\lib\sqljdbc_auth.dll already loaded in another classloader

at java.base/java.lang.ClassLoader$NativeLibrary.loadLibrary(Unknown Source)

at java.base/java.lang.ClassLoader.loadLibrary0(Unknown Source)

at java.base/java.lang.ClassLoader.loadLibrary(Unknown Source)

at java.base/java.lang.Runtime.loadLibrary0(Unknown Source)

at java.base/java.lang.System.loadLibrary(Unknown Source)

at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:41)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3132)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$100(SQLServerConnection.java:43)

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:3123)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1981)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1628)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1459)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:773)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)

at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:53)

at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:291)

at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2395)

at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2381)

at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2110)

at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:257)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:211)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1016)

at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)

... 4 common frames omitted

Two ideas - 1, remove the integratedSecurity=true from your DB connection(s), if you are just using sql authentication, or 2, restart the gateway, in case you've got two JDBC drivers installed (in memory, on the JVM).

This didn't seem to work.

This sort of took out the gateway permanently, it won't restart now.

I think i'll just re-install ignition and hope the new installation carries the correct settings back over.

Before you reinstall, could you copy the wrapper.log file and upload it here? Would be good to see why the gateway won't start back up - that's obviously something we want to avoid happening.

Sure, it is attached here.

Gateway seems to be stuck perpetually alternating between Starting and Unknown.

wrapper.zip (54.9 KB)

Also, re-installing Ignition did not fix this error, so I’m sort of stuck right now until I have a solution. I imagine some file is preventing this from restarting normally.

I managed to get it back up. Either deleting sqljdbc_auth.dll or starting/stopping/starting the service fixed it. Not sure, but i’ll post the thread dump here for you to check out!

wrapper.zip (22.9 KB)

Could you also upload one/all of the hs_err.pid log files? Path should be something like C:\Program Files\Inductive Automation\Ignition\hs_err_pid18868.log - there may be more than one/quite a few there.

Sure, here you go!

logs.zip (179.0 KB)

Databases are still down, unfortunately. But at least the gateway is up!

Well, the good news (for us) is that all of those crashes are with Microsoft’s JDBC driver, calling out to native code. Unfortunately, that’s not great news for you. If you wipe everything (or, perhaps, start from scratch on a new machine) and, without restoring a .gwbk, make a new connection to MSSQL with the seeded MSSQL JDBC driver, does that work without crashing? Something appears to be conflicting between your JDBC driver and your auth dll - did you use the auth DLL from the JDBC driver download, or just from the KBA I linked earlier?

That did not work, the database connections were still broken, and trying the same things out did not bring it back. The Oracle connection was fine, however, just the 4 SQL Server connections were not.

I did swap the auth DLL, but that didn't work either. I cleaned out the whole system so the original one from the KBA I placed in was definitely deleted.

I ended up reverting back to 7.9 to continue development until a replicated test environment is created, then i'll go back in and try again.

I saw this exact symptom when I had an incorrect .dll file present as well. I had to download an updated .dll from the Microsoft website, as per this thread.

That of course doesn't solve your other issues, but just for context :slight_smile:

1 Like

Yeah! I ended up solving the problem, the .dll file was part of it. I’ll post it below in reply to PGriffith.