Skip to main content
Anderosn
Anderosn
Silver ⭐️⭐️⭐️
August 9, 2023
Solved

PutSQL - Not enough FlowFiles for transaction. Returning all FlowFiles to queue, No incoming FlowFiles.

  1. PutSql_penalizing_queue.PNGimage.pngimage.pngPutSQL is penalizing the queue and  no exceptions are found in logs

2023-08-09 08:24:07,045 DEBUG [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=8e5c441f-696c-3145-8ba0-67499ed29990] Not enough FlowFiles for transaction. Returning all FlowFiles to queue
2023-08-09 08:24:07,046 DEBUG [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=8e5c441f-696c-3145-8ba0-67499ed29990] No incoming FlowFiles.
2023-08-09 08:24:07,047 DEBUG [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=8e5c441f-696c-3145-8ba0-67499ed29990] Not enough FlowFiles for transaction. Returning all FlowFiles to queue
2023-08-09 08:24:07,047 DEBUG [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=8e5c441f-696c-3145-8ba0-67499ed29990] No incoming FlowFiles.
I know if there is SQLException, Flowfile will be forwarded via failure else the flowfile will be added back to the queue for any other exception 
In my case no exceptions are being listed in log file. 
flow is GenerateFile(Json array with 10 Entires) -> SplitJson(splits Json into 10 flow files) -> EvaluteJsonPath(Adds attributes which are used in putSQL to insert data into DB) -> PutSQL.

MattWho
MattWhoAnswer
Titanium ⭐️⭐️⭐️
August 10, 2023

@Anderosn 
In-between your SplitJson and PuSQL processors are you rebalancing the FlowFile across multiple nodes in a NiFi cluster? 
Are you routing any of the split Json messges down a different dataflow path that does not lead to this pusSQL processor?

The reason I ask is because the splitJson processor will write the following FlowFile attributes to each new FlowFile created (each split):

MattWho_0-1691674964147.png

The fragment.identifier value and fragment.count are used by the putSQL processor when "Support FragmentTransactions" is set to "true" (default).  This means that, if not all split jsons are present at this putSQL and located on the same node of the NiFi cluster, the FlowFiles part of the same fragment.identifier will not be processed and remain on the inbound connection to the PutSQL.

MattWho_1-1691675192003.png


I'd start my listing the connection and checking these attributes to verify the fragment.count is "10", the fragment.identifier has same value on all 10, and fragment.index value shows numbers 1 to 10 across those 10 FlowFiles.

If making sure all fragments are processed in same transaction is not a requirement for your dataflow, try changing "Support Fragmented Transactions" to false and see if these 10 FlowFiles get successfully executed by your putSQL processor.

 

If you found that the provided solution(s) assisted you with your query, please take a moment to login and click Accept as Solution below each response that helped.

Thank you,

Matt

 

View in thread

2 replies

cotopaul
cotopaul
Platinum ⭐️
August 10, 2023

@Anderosn, try reducing the Batch Size to 10 instead of 100. Even though the hint says it is the preferred number of Flow File to be put in the database, I do not know exactly if this is a hard limit or a soft limit.

Translate
MattWho
Titanium ⭐️⭐️⭐️
MattWhoAnswer
Titanium ⭐️⭐️⭐️
August 10, 2023

@Anderosn 
In-between your SplitJson and PuSQL processors are you rebalancing the FlowFile across multiple nodes in a NiFi cluster? 
Are you routing any of the split Json messges down a different dataflow path that does not lead to this pusSQL processor?

The reason I ask is because the splitJson processor will write the following FlowFile attributes to each new FlowFile created (each split):

MattWho_0-1691674964147.png

The fragment.identifier value and fragment.count are used by the putSQL processor when "Support FragmentTransactions" is set to "true" (default).  This means that, if not all split jsons are present at this putSQL and located on the same node of the NiFi cluster, the FlowFiles part of the same fragment.identifier will not be processed and remain on the inbound connection to the PutSQL.

MattWho_1-1691675192003.png


I'd start my listing the connection and checking these attributes to verify the fragment.count is "10", the fragment.identifier has same value on all 10, and fragment.index value shows numbers 1 to 10 across those 10 FlowFiles.

If making sure all fragments are processed in same transaction is not a requirement for your dataflow, try changing "Support Fragmented Transactions" to false and see if these 10 FlowFiles get successfully executed by your putSQL processor.

 

If you found that the provided solution(s) assisted you with your query, please take a moment to login and click Accept as Solution below each response that helped.

Thank you,

Matt

 

Translate
    Terms & ConditionsAccessibility statement