Hi Paul, have you tried to set validation query on the pool? something like SELECT 1 FROM dual
I had a bunch of issues with our databases but after we started using validation query, all issues with disconnected sessions were gone. Give it a try if you have not done this yet On Fri, May 25, 2018 at 2:54 PM, Paul Riddle <[email protected]> wrote: > NiFi Team, > > We are seeing issues in the DBCPConnectionPool service and ExecuteSQL > processor where threads are hanging from NiFi consistently. The first set > of queries goes through without issue when the processor is initially > started. After 5 minutes of inactivity, a second set of queries simply > hangs. > > Netstat on the server (netstat -nputwoc | grep 1521 ) shows that once we > restart the service, the connection pool is cleared and the processor > continues to work as normal as long as there is activity within 5min ( that > consumes the connection pool) . > > After 5 min of inactive period the next attempt to make JDBC connection > uses the connection from the same old pool . It then appears that that pool > is not good to consume, and if used, causes threads to hang in NiFi > (consistently reproducible). > > There are no errors in the logs during this occurance, or bulletins within > NiFi. The threads hang for over 30 minutes at that point. > > > > Other than default processor configs: > The max wait time on the processor is set to 30 seconds > > > Other than default controller service configs: > database driver- oracle.jdbc.driver.OracleDriver > max wait time- 500 ms > Max total connections- 8 > > Any assistance you can provide is appreciated. > > Regards, > Paul >
