Support Questions
Find answers, ask questions, and share your expertise

FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Explorer

Hi all,

I have an issue to connect to the SQL Server with DBCPConnectionPool 1.9.2 but it is worked with DBCPConnectionPool 1.2

Do you know if there are some modifications between 1.2 and 1.9.2 concerning DBCPConnectionPool ?


Connection parameter :

  • jdbc:jtds:sqlserver://${host}:${port};databaseName=xxxxxx;user=xxxxxx;password=xxxxxx
  • net.sourceforge.jtds.jdbc.Driver
  • /var/dlk/squad/squad-jars/jtds-1.3.1.jar


Thanks for helps.


Here error on NIFI 1.9.2

2019-07-31 12:53:42,483 ERROR [Timer-Driven Process Thread-12] o.a.n.p.standard.ListDatabaseTables ListDatabaseTables[id=db1439dc-aa6f-385b-be8f-66ee7faf84f4] ListDatabaseTables[id=db1439dc-aa6f-385b-be8f-66ee7faf84f4] failed to process session due to java.lang.AbstractMethodError; Processor Administratively Yielded for 1 sec: java.lang.AbstractMethodError
java.lang.AbstractMethodError: null
        at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)
        at org.apache.commons.dbcp2.DelegatingConnection.isValid(DelegatingConnection.java:874)
        at org.apache.commons.dbcp2.PoolableConnection.validate(PoolableConnection.java:270)
        at org.apache.commons.dbcp2.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:389)
        at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2398)
        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 org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
        at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
        at sun.reflect.GeneratedMethodAccessor537.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
        at com.sun.proxy.$Proxy304.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.ListDatabaseTables.onTrigger(ListDatabaseTables.java:230)
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
        at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)
2019-07-31 12:53:42,483 WARN [Timer-Driven Process Thread-12] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ListDatabaseTables[id=db1439dc-aa6f-385b-be8f-66ee7faf84f4] due to uncaught Exception: java.lang.AbstractMethodError
java.lang.AbstractMethodError: null
        at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2833)
        at org.apache.commons.dbcp2.DelegatingConnection.isValid(DelegatingConnection.java:874)
        at org.apache.commons.dbcp2.PoolableConnection.validate(PoolableConnection.java:270)
        at org.apache.commons.dbcp2.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:389)
        at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2398)
        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 org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:470)
        at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
        at sun.reflect.GeneratedMethodAccessor537.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:87)
        at com.sun.proxy.$Proxy304.getConnection(Unknown Source)
        at org.apache.nifi.processors.standard.ListDatabaseTables.onTrigger(ListDatabaseTables.java:230)
        at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
        at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1162)
        at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:209)
        at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
        at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)


1 ACCEPTED SOLUTION

Accepted Solutions

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Super Guru

If it works in NiFi 1.5 and not in 1.9.2, then the DBCP libraries probably got updated to DBCP 2, where the latter will call isValid() on the connection if a validation query is not set (see https://sourceforge.net/p/jtds/discussion/104388/thread/bbfbcf24/). Have you set the Validation Query property on the DatabaseConnectionPool? If not, set it to "SELECT 1" and try again, hopefully that will work. If that doesn't, you can try adding a user-defined property called "validationQuery" and set it to "SELECT 1". That should add it as a property on the JDBC connection itself.


Another workaround would be to use the official SQL Server driver (although I assume you chose jTDS on purpose).

View solution in original post

3 REPLIES 3

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Explorer

Hi,

I have also tried with NIFI 1.5 and it works to.

110142-capture-sqlserver-config.png

110089-capture-sqlserver-success.png


capture-sqlserver-config.png

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Super Guru

If it works in NiFi 1.5 and not in 1.9.2, then the DBCP libraries probably got updated to DBCP 2, where the latter will call isValid() on the connection if a validation query is not set (see https://sourceforge.net/p/jtds/discussion/104388/thread/bbfbcf24/). Have you set the Validation Query property on the DatabaseConnectionPool? If not, set it to "SELECT 1" and try again, hopefully that will work. If that doesn't, you can try adding a user-defined property called "validationQuery" and set it to "SELECT 1". That should add it as a property on the JDBC connection itself.


Another workaround would be to use the official SQL Server driver (although I assume you chose jTDS on purpose).

View solution in original post

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Explorer

The error message was not very clear but it works for HDF 1.9.2.

Thanks a lot!