Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

Solved Go to solution
Highlighted

FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

New Contributor

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

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

3 REPLIES 3

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

New Contributor

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

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

Re: FAILED with DBCPConnectionPool 1.9.2 to SQL SERVER

New Contributor

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

Thanks a lot!

Don't have an account?
Coming from Hortonworks? Activate your account here