dbcpconnection pool drops connection.

[#1]

Our flow files ends with putsql's to oracle database. At random times, the flow stops with files in the queue before the putsql's. (5 minute activity shows 0). Stopping and starting nifi does not do anything.

However we found that disabling and enabling the dbcpconnection pool starts it going again.

We are on nifi.1.2.0

[#2 · Accepted solution]

@Jonathan Bell Can you add validation query in connection pool as this Validation query used to validate connections before returning them. When connection is invalid, it get's dropped and new valid connection will be returned. Note!! Using validation might have some performance penalty.

Query:-

select CURRENT_TIMESTAMP

Connection pool Configs:-

and this validation query will take care of invalid connections and drops invalid connections and re enables connections, it helps you to disable and re enable connection pools.

[#3]

thanks, I will give it a try.

[#4]

Unfortunately, the validation query doesn't appear to be working. We are still gettng the same issue, the putsql's lose the connection to oracle(looking in oracle it doesn't show any connections), but the controller in nifi, is showing connected, so the putsql's just stop.

[#5]

@Jonathan Bell

We also faced these dropping connection issues but once we added validation query to the connection pool the issue got resolved but we haven't tried with oracle, Open a jira about lose of connection to oracle.

As a workaround to fix this issue you need to use Rest Api commands to disabling the controller service and to stop refererring put sql processor after that process got completed then again Enable DBCP Connection pool and start referring PutSQL processor will help.

I tried with the below example

I am having a DBCPConnection pool which is referring to put sql processor, so when you are trying to push records to oracle

Schedule another script which is going to

1.Stop the PutSQL processor

Rest Api Command to Stop the PutSQL processor as Follows

curl -i -X PUT -H 'Content-Type:application/json' -d
'{"component": {"state":
"STOPPED","id":
"61fe0748-0160-1000-3bda-82285b30a012"},"revision":
{"version": 1,"clientId":"6082f82f-0160-1000-c7de-88e9e0df0382"}}'
http://localhost:8080/nifi-api/processors/61fe0748-0160-1000-3bda-82285b30a012

Explanation:-

We need to use PUT http method in curl and my PutSQL Processor id is 61fe0748-0160-1000-3bda-82285b30a012 and i need to stop the processor so i used STOPPED as the state, if you want to start the processor then you need to use RUNNING as state.

To find out client id and version number use Developer tools (chrome, firefox etc) and perform any action (start, stop ...etc) in the NiFi UI and look at the calls made for the processor id.

for reference take a look in the below screenshot

1.Click on Network

2.In filter keep your processor id

3.Click on Response then you can find clientid,version

Once you get all the values prepare your curl command to stop PutSQL processor.

2.Start PutSQL processor

Just change the state as RUNNING then processor will change from Stopped state to running state.

curl -i -X PUT -H 'Content-Type:application/json' -d
'{"component": {"state":
"RUNNING","id":
"61fe0748-0160-1000-3bda-82285b30a012"},"revision":
{"version": 1,"clientId":"6082f82f-0160-1000-c7de-88e9e0df0382"}}'
http://localhost:8080/nifi-api/processors/61fe0748-0160-1000-3bda-82285b30a012

3.Stop DBCPConnection Pool Controller service

To Disable connection pool we need to change the state element value to DISABLED

curl -i -X PUT -H 'Content-Type:application/json' -d
'{"revision":{"clientId":"6082f82f-0160-1000-c7de-88e9e0df0382","version":1},"component":{"id":"61fc97d3-0160-1000-49e3-46201fe71092","state":"DISABLED"}}'
http://localhost:8080/nifi-api/controller-services/61fc97d3-0160-1000-49e3-46201fe71092

Explanation:-

DBCPConnectionPool Controller service id is 61fc97d3-0160-1000-49e3-46201fe71092

we need to use state as DISABLED i.e we are disabling the service.

4.Start DBCPConnection Pool Controller service

Just change the state to ENABLED then the controller service will be enabled.

curl -i -X PUT -H 'Content-Type:application/json' -d
'{"revision":{"clientId":"6082f82f-0160-1000-c7de-88e9e0df0382","version":1},"component":{"id":"61fc97d3-0160-1000-49e3-46201fe71092","state":"ENABLED"}}'
http://localhost:8080/nifi-api/controller-services/61fc97d3-0160-1000-49e3-46201fe71092

To find the version number just use the same method using developer tools as i mentioned above.

In addition if you want to stop and start Process Groups use the below curl commands

To Stop the Process Group:-

My Process Group id is 5d325978-0160-1000-f734-cbbf324b3ec3

To stop process group state needs to be STOPPED

curl -i -X PUT -H 'Content-Type: application/json' -d '{"id": "5d325978-0160-1000-f734-cbbf324b3ec3","state": "STOPPED"}' http://localhost:8080/nifi-api/flow/process-groups/5d325978-0160-1000-f734-cbbf324b3ec3

To Start Process Group:-

Change the state to RUNNING

curl -i -X PUT -H 'Content-Type: application/json' -d '{"id": "5d325978-0160-1000-f734-cbbf324b3ec3","state": "RUNNING"}' http://localhost:8080/nifi-api/flow/process-groups/5d325978-0160-1000-f734-cbbf324b3ec3

By using Rest Api commands prepare a script that will trigger before PutSQL processor.

Script:-

1.Stop the PutSQL Processor

2.Disable DBCP Connection Pool

3.Enable DBCP Connection Pool

4.Start PutSQL Processor

restapi-cmnds.txt

[#6]

@Shu can you please guide, how to trigger the rest api command scripts in NiFi which you have suggested in this post