DBCP connection pool problem - connection already closed

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
Reply | Threaded
Open this post in threaded view
|

Re: DBCP connection pool problem - connection already closed

Brett Ryan
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, 尹文才 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: DBCP connection pool problem - connection already closed

尹文才
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 <[hidden email]>:

> 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, 尹文才 <[hidden email]> 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
>