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
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, 尹文才 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
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 : > 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, 尹文才 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 >