Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

avatar
Rising Star

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

avatar
Master 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

avatar
Rising Star

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

avatar
Master 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).

avatar
Rising Star

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

Thanks a lot!