You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@nifi.apache.org by 尹文才 <ba...@gmail.com> on 2018/01/11 07:56:51 UTC

DBCP connection pool problem - connection already closed

Hi guys, I'm using the PutDatabaseRecord processor to write some data into
sql server and the processor is using the DBCP controller service as its
connection pool.
Sometimes I could see the following exception inside my log file:
org.apache.nifi.processor.exception.ProcessException: Failed to commit
database connection due to com.microsoft.sqlserver.jdbc.SQLServerException:
The connection is closed

I know that DBCPConnectionPool is using the validation query property to
set test-on-borrow to true and also set the validation query accordingly,
so I set the validation query to "select 1",
but this doesn't seem to solve the problem. I tried to find the possible
reason and solution for this problem, the only thread I could find is that
someone talked about one possible reason on
StackOverFlow:

"In case of IOException, the sqlserver jdbc driver marks the connection as
closed, but this is not detected by the pool. So the connection is returned
in the pool, while unusable."

I'm not sure if the reason he mentioned above is the root cause of my
problem. Has anyone came across this kind of problem and how to work around
this issue? Thanks.

Regards,
Ben

Re: DBCP connection pool problem - connection already closed

Posted by 尹文才 <ba...@gmail.com>.

Thanks Brett, I will switch to use jTDS and see how it goes.

Regards,
Ben

2018-01-11 16:22 GMT+08:00 Brett Ryan <br...@gmail.com>:

> Hi Ben. It’s often recommended to use the jTDS driver [1] as the MS
> provided driver is considered buggy.
>
> I don’t make this claim, however; I’ve always used this driver for ms sql
> server and never encountered issues.
>
>   [1]: http://jtds.sourceforge.net/faq.html
>
> > On 11 Jan 2018, at 18:56, 尹文才 <ba...@gmail.com> wrote:
> >
> > Hi guys, I'm using the PutDatabaseRecord processor to write some data
> into
> > sql server and the processor is using the DBCP controller service as its
> > connection pool.
> > Sometimes I could see the following exception inside my log file:
> > org.apache.nifi.processor.exception.ProcessException: Failed to commit
> > database connection due to com.microsoft.sqlserver.jdbc.
> SQLServerException:
> > The connection is closed
> >
> > I know that DBCPConnectionPool is using the validation query property to
> > set test-on-borrow to true and also set the validation query accordingly,
> > so I set the validation query to "select 1",
> > but this doesn't seem to solve the problem. I tried to find the possible
> > reason and solution for this problem, the only thread I could find is
> that
> > someone talked about one possible reason on
> > StackOverFlow:
> >
> > "In case of IOException, the sqlserver jdbc driver marks the connection
> as
> > closed, but this is not detected by the pool. So the connection is
> returned
> > in the pool, while unusable."
> >
> > I'm not sure if the reason he mentioned above is the root cause of my
> > problem. Has anyone came across this kind of problem and how to work
> around
> > this issue? Thanks.
> >
> > Regards,
> > Ben
>

Re: DBCP connection pool problem - connection already closed

Posted by Brett Ryan <br...@gmail.com>.

Hi Ben. It’s often recommended to use the jTDS driver [1] as the MS provided driver is considered buggy.

I don’t make this claim, however; I’ve always used this driver for ms sql server and never encountered issues.

  [1]: http://jtds.sourceforge.net/faq.html

> On 11 Jan 2018, at 18:56, 尹文才 <ba...@gmail.com> wrote:
> 
> Hi guys, I'm using the PutDatabaseRecord processor to write some data into
> sql server and the processor is using the DBCP controller service as its
> connection pool.
> Sometimes I could see the following exception inside my log file:
> org.apache.nifi.processor.exception.ProcessException: Failed to commit
> database connection due to com.microsoft.sqlserver.jdbc.SQLServerException:
> The connection is closed
> 
> I know that DBCPConnectionPool is using the validation query property to
> set test-on-borrow to true and also set the validation query accordingly,
> so I set the validation query to "select 1",
> but this doesn't seem to solve the problem. I tried to find the possible
> reason and solution for this problem, the only thread I could find is that
> someone talked about one possible reason on
> StackOverFlow:
> 
> "In case of IOException, the sqlserver jdbc driver marks the connection as
> closed, but this is not detected by the pool. So the connection is returned
> in the pool, while unusable."
> 
> I'm not sure if the reason he mentioned above is the root cause of my
> problem. Has anyone came across this kind of problem and how to work around
> this issue? Thanks.
> 
> Regards,
> Ben