Hello Boris, NiFi Team, Thank you for the reply. We do have a validation query, and the issue persists. What we are seeing is that we will have a successful query, and then the connection will stay open to the database. When the next query goes through (beyond 5 minutes later), it will attempt to use that same stale connection, and the NiFi thread will hang for 30-40 minutes.
We have a workaround modifying TCP keepalive settings for now, however, we were wondering if there may be an additional setting in the DBCPConnetionPoolService that we can add to guarantee that the connection isn't kept open after the query completes. As always, any assistance you can offer is greatly appreciated. Regards, Paul On Fri, May 25, 2018 at 3:43 PM, Boris Tyukin <[email protected]> wrote: > 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 >> > >
