Loss of formatDateFrom attribute during PutSQL failure retry in Apache NiFi

[#1]

Hi,

I'm having a problem with a NiFi flow. I use UpdateAttrubute processor when I create an attribute like this: 

formatDateFrom      ${dateFrom:format("yyyy-MM-dd")}

After this I have a PutSql processor that changes a status in DB. When everything is fine, it is ok. However, when something is wrong with the change, this status and my flow go to retry this attribute formatDateFrom disappears and is no longer present in the flow.

Why does this happen and how can I secure this attribute? Attributes created with EvalueateJsonPath didn't disappear, only those created with UpdateAttribute.

[#2]

@donaldo71 I am trying to follow your flow description here and am not very clear.  Can you share your flow definition and indicate where you are seeing the failure. 
What version of Apache NiFi are you using?

You have an UpdateAttribute processor that has its success relationship routed via a connection to the PutSQL processor.  How is putSQL configured?

When "something is wrong", what exception is being thrown in the nifi-app.log?

The more detail you can provide the better chance. community member may be able to provide guidance and suggestions.

Thank you,
Matt

[#3]

I mean that if the PutSql processor is unable to change the status, e.g. due to the database being unavailable, and the flow goes to the retry path to retry the process, then the attributes I created earlier in UpadetAttribute disappear from the process.This is my values in PutSql processor:

Support Fragmented Transactions	false
Database Session AutoCommit	false
Transaction Timeout	30 sec
Batch Size	50
Obtain Generated Keys	false
Rollback On Failure	false

[#4]

@donaldo71 

It would be helpful to know the full Apache NiFi version being used so I can see if there are any known issue that align here.

I assume that you have a prepared SQL statement configured in your PutSQL processor that uses NiFi expression language to insert values from the Source FlowFile's attributes?

Are you saying that on the very first failed execution of a "retry" routed FlowFile (the attempt that still had the "formatDateFrom" attribute set, there was no exception different from all subsequent retry requests made with FlowFile now missing that attribute)?

Thank you,
Matt

[#5]

This happens in version 1.23.2. In the PutSql processor, I have the following command: UPDATE tbl SET status = 'proceed', startDate = GETDATE() WHERE messageId = ${messageId} The first retry attempt no longer had this attribute. When I look at DataProvanance, I see the following next to this record in PutSql: SEND CLONE CLONE CLONE DONE - as if, each time I retry, PutSql cloned the flow file, which no longer has the formatDateTo and formatDataFrom attributes.

[#6]

@donaldo71 

I have not been able to identify a known issue that aligns with the description you have shared.

That is an interesting sequence of events on a single FlowFile (SEND followed by clones).  Can you share the "Relationships" configuration of your putSQL processor?

Make sure you have not checked the "retry" box on the "success" relationship.  

Something you might want to try to see if same issue persists is to check the box for "retry"  on the retry relationship.   This allows the original FlowFile to remain in the inbound connection up to the configured number of retry attempts (default 10) before being routed to retry relationship. 

I'd be curious of your observations post the above configuration change.

Would you be willing to download the your flow definition json for this dataflow and share it?

This is your full "SQL Statement" set in your ptSQL processor?

UPDATE tbl SET status = 'proceed', startDate = GETDATE() WHERE messageId = ${messageId}

Where are your utilizing those two attributes that go missing?

can you share your UpdateAttribute processor configuration?

Please help our community grow. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt