Logback cannot load SQLServerDriver

Ignition
1

I’m trying to add an appender to to logback.xml so we can write logs to a database, as well as to the wrapper.log. I have added the DBAppender with SQLServerDriver as the driverClass, but logback is unable to load the JDBC driver class.

Here’s what I have added to logback.xml, inside the existing configuration element:

<configuration debug="true">
  <appender name="PHX" class="ch.qos.logback.classic.db.DBAppender">
    <connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">
      <driverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</driverClass>
      <url>jdbc:sqlserver://serverName;databaseName=database</url>
      <user>LogTest</user>
      <password>logtestpassword</password>
    </connectionSource>
  </appender>
...
  <root level="INFO">
    <appender-ref ref="PHX"/>
    <appender-ref ref="SysoutAsync"/>
    <appender-ref ref="DBAsync"/>
  </root>
</configuration>

The driverClass is the same as the Microsoft SQLServer driver’s Classname, and the url is configured the same way we set our database connections. I have also tried setting the url to “serverName/database”, and to use a separate element for the databaseName. Neither one seems to make a difference, and it seems the error is occurring before that, when it is unable to find the SQLServerDriver class.

Here’s what’s in the wrapper.log:

INFO   | jvm 1    | 2019/10/01 14:41:02 | WrapperManager: Initializing...
INFO   | jvm 1    | 2019/10/01 14:41:03 | 14:41:03,072 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Found resource [data\\logback.xml] at [file:/C:/Program%20Files/Inductive%20Automation/Ignition/data/logback.xml]
INFO   | jvm 1    | 2019/10/01 14:41:03 | 14:41:03,148 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.classic.db.DBAppender]
INFO   | jvm 1    | 2019/10/01 14:41:03 | 14:41:03,154 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [PHX]
INFO   | jvm 1    | 2019/10/01 14:41:03 | 14:41:03,160 |-ERROR in ch.qos.logback.core.db.DriverManagerConnectionSource@4b5a4ad8 - Could not load JDBC driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.ClassLoader.loadClass(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.Class.forName0(Native Method)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.Class.forName(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.db.DriverManagerConnectionSource.start(DriverManagerConnectionSource.java:37)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.action.NestedComplexPropertyIA.end(NestedComplexPropertyIA.java:161)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.callEndAction(Interpreter.java:309)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:193)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:179)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.EventPlayer.play(EventPlayer.java:62)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:155)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:142)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:103)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:53)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.classic.util.ContextInitializer.configureByResource(ContextInitializer.java:75)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.classic.util.ContextInitializer.autoConfig(ContextInitializer.java:150)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.impl.StaticLoggerBinder.init(StaticLoggerBinder.java:84)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.impl.StaticLoggerBinder.<clinit>(StaticLoggerBinder.java:55)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.bind(LoggerFactory.java:150)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.performInitialization(LoggerFactory.java:124)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.getILoggerFactory(LoggerFactory.java:412)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:357)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.common.util.LoggerEx$Builder.build(LoggerEx.java:587)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.getLogger(IgnitionGateway.java:3139)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.<init>(IgnitionGateway.java:328)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.main(IgnitionGateway.java:266)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.catapult.Catapult.main(Catapult.java:8)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:349)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.Thread.run(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 14:41:03,163 |-ERROR in ch.qos.logback.core.joran.spi.Interpreter@18:14 - RuntimeException in Action for tag [appender] java.lang.IllegalStateException: DBAppender cannot function if the JDBC driver does not support getGeneratedKeys method *and* without a specific SQL dialect
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at java.lang.IllegalStateException: DBAppender cannot function if the JDBC driver does not support getGeneratedKeys method *and* without a specific SQL dialect
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.db.DBAppenderBase.start(DBAppenderBase.java:60)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.classic.db.DBAppender.start(DBAppender.java:92)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.action.AppenderAction.end(AppenderAction.java:90)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.callEndAction(Interpreter.java:309)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:193)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.Interpreter.endElement(Interpreter.java:179)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.spi.EventPlayer.play(EventPlayer.java:62)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:155)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:142)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:103)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.core.joran.GenericConfigurator.doConfigure(GenericConfigurator.java:53)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.classic.util.ContextInitializer.configureByResource(ContextInitializer.java:75)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at ch.qos.logback.classic.util.ContextInitializer.autoConfig(ContextInitializer.java:150)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.impl.StaticLoggerBinder.init(StaticLoggerBinder.java:84)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.impl.StaticLoggerBinder.<clinit>(StaticLoggerBinder.java:55)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.bind(LoggerFactory.java:150)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.performInitialization(LoggerFactory.java:124)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.getILoggerFactory(LoggerFactory.java:412)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:357)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.common.util.LoggerEx$Builder.build(LoggerEx.java:587)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.getLogger(IgnitionGateway.java:3139)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.<init>(IgnitionGateway.java:328)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.ignition.gateway.IgnitionGateway.main(IgnitionGateway.java:266)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at com.inductiveautomation.catapult.Catapult.main(Catapult.java:8)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:349)
INFO   | jvm 1    | 2019/10/01 14:41:03 | 	at 	at java.base/java.lang.Thread.run(Unknown Source)

If anyone has ever had any success with pointing Ignition’s logback to a database, I would appreciate any advice you could give.

2

Unfortunately the JDBC drivers are not in the right ClassLoader to make what you’re attempting possible.

3

When I spoke with @Carl.Gould last week, I thought he said that it would be possible to do so by adding an Appender to logback, but perhaps I misunderstood him. I had been thinking that I would have to write my own logging scripts, and was very happy to hear that wouldn’t be necessary.

Is there a different Appender that I should be using, or is the entire exercise impossible?

4

If you are willing to experiment with the SDK, you could probably create an appender shim in a jar that is specified as an export, then have the rest of the module connect that shim to a normal Ignition DB connection.

{ Totally speculative on my part… }

1 Like
5

You might get this appender to work if you put a copy of the JDBC driver jar into lib/core/gateway. Not sure if there would be adverse effects.

2 Likes
6

Thanks, I will try that. And if it doesn’t work, or if it causes any adverse effects I may try @pturmel’s approach.

7

I would expect it to break attempts to replace that driver via the gateway web interface. But likely otherwise work.

8

Looks like it works! And if we do ever need to update or replace that driver, we will deal with copying the new one over to lib/core/gateway as well.

Many thanks!

9

Another adverse effect: anything placed in lib/core/gateway will be removed by the installer if you upgrade Ignition, so you will need to account for that if you plan on upgrading.

2 Likes