FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Question

Hi all,I have an issue to connect to the SQL Server with DBCPConnectionPool 1.9.2 but it is worked with DBCPConnectionPool 1.2Do 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=xxxxxxnet.sourceforge.jtds.jdbc.Driver/var/dlk/squad/squad-jars/jtds-1.3.1.jarThanks for helps.Here error on NIFI 1.9.22019-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.AbstractMethodErrorjava.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.AbstractMethodErrorjava.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)

mburgess · Accepted Answer

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

maykiwogno · Answer

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

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.