Data importer imports duplicates of deleted transactions when detection is set to external id

[kesot]

Support guidelines

• I've read the support guidelines

I've found a bug and checked that ...

• ... the documentation does not mention anything about my problem
• ... there are no open or closed issues that are related to my problem
• ... it's definitely a Firefly III issue, not me

Description

When importing transactions with duplicate detection set to external id the transactions that have been marked as deleted are imported again. The behavior is not consistent with 'content' duplicate detection where such transactions are skipped during the import.

Debug information

Expected behaviour

It should be consistent with content duplicate detection and should skip such transactions even if the duplicate is in 'soft deleted' state

Steps to reproduce

1. import a transaction with an external id
2. delete the transaction
3. import the same transaction again
   the transaction is created again but it should not be

Additional info

No response

[JC5]

I can't replicate this. I delete a transaction, it doesn't get re-imported, unless I purge my records first.

Could you share your data importer logs for the offending transaction?

[kesot]

2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Now submitting transaction 9/9  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Going to search for external_id:a5f331650a86c01e8569d5f132d2189d using query external_id_is:"a5f331650a86c01e8569d5f132d2189d"  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Looks like field "external_id" with value "a5f331650a86c01e8569d5f132d2189d" is unique, return false.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Transaction #9 is unique.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Going to map data for this line.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Submitting to Firefly III: {"apply_rules":true,"error_if_duplicate_hash":false,"transactions":[{"type":"transfer","date":"2024-02-02T00:00:00+01:00","amount":"1.000000000000","description":"Tuzemsk\u00e1 odchoz\u00ed \u00fahrada","payment_date":"2024-02-02","order":0,"currency_code":"CZK","tags":["booked"],"category_name":null,"category_id":null,"notes":"Tuzemsk\u00e1 odchoz\u00ed \u00fahrada","external_id":"a5f331650a86c01e8569d5f132d2189d","internal_reference":"","additional-information":"Tuzemsk\u00e1 odchoz\u00ed \u00fahrada","source_id":908,"destination_id":906,"destination_iban":"<REDACTED>","destination_name":"creditka","destination_number":""}]}  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.INFO: Add message to index #8: Created transfer <a target="_blank" href="http://localhost:2081/transactions/show/7867">#7867 "Tuzemská odchozí úhrada"</a> (CZK 1)  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Add message on index #8 (line no. 9): Created transfer <a target="_blank" href="http://localhost:2081/transactions/show/7867">#7867 "Tuzemská odchozí úhrada"</a> (CZK 1)  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Now in App\Services\Shared\Import\Status\SubmissionStatusManager::storeSubmissionStatus(submission-1ThyOGR3qBsD): submission_running  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Messages: 1, warnings: 0, errors: 8  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.INFO: Created transfer <a target="_blank" href="http://localhost:2081/transactions/show/7867">#7867 "Tuzemská odchozí úhrada"</a> (CZK 1)  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.ERROR: {"tag":["This name is already in use."]}  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Going to add import tag to transaction group #7867  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Added import tag to transaction group #7867  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.INFO: Done submitting 9 transactions to your Firefly III instance.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Routine manager: messages: 1, warnings: 0, errors: 8  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Now in setSubmissionStatus(submission_done)  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Found "submission-1ThyOGR3qBsD" in the session  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Now in startOrFindJob(submission-1ThyOGR3qBsD)  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Try to see if file exists for job submission-1ThyOGR3qBsD.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Status file exists for job submission-1ThyOGR3qBsD.  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Status: submission_running  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Messages: 1, warnings: 0, errors: 8  
2024-03-22 19:15:07 [2024-03-22 19:15:07] local.DEBUG: Now in App\Services\Shared\Import\Status\SubmissionStatusManager::storeSubmissionStatus(submission-1ThyOGR3qBsD): submission_done

fireflyiiidb/firefly/transactions/ http://localhost:2083/index.php?route=/sql&pos=0&db=firefly&table=transactions

Showing rows 0 - 7 (8 total, Query took 0.0005 seconds.) [deleted_at: 2024-03-22 19:14:00... - ...]

SELECT tj.date, tj.description, t.id, tj.id, tj.transaction_group_id, t.deleted_at, t.created_at, jm.name, jm.data FROM transactions t
INNER join transaction_journals tj on tj.id = t.transaction_journal_id
INNER join journal_meta jm on jm.transaction_journal_id = tj.id
WHERE jm.name = 'external_id'
and jm.data = '"a5f331650a86c01e8569d5f132d2189d"'
#and t.deleted_at > ("2024-03-21 22:19:41")
ORDER by t.deleted_at DESC;

[kesot]

the string used as external id is stored in quotes for some reason, maybe that's the issue?
also it's a transfer transaction not just an expense if that matters

[JC5]

"error_if_duplicate_hash":false

Could you share your (JSON) configuration please? I would expect this value to be true.

When this value is true, you would see this:

[2024-03-22 19:30:41] production.DEBUG: Now submitting transaction 20/20
[2024-03-22 19:30:41] production.DEBUG: Going to search for external_id:e1766b98bd1594c6b5b2028cac77fb2f using query external_id_is:"e1766b98bd1594c6b5b2028cac77fb2f"
[2024-03-22 19:30:41] production.DEBUG: No base url in getBaseUrl() session, will return config variable.
[2024-03-22 19:30:41] production.DEBUG: No access token in hasAccessToken() session, will return config variable.
[2024-03-22 19:30:41] production.DEBUG: Looks like field "external_id" with value "e1766b98bd1594c6b5b2028cac77fb2f" is unique, return false.
[2024-03-22 19:30:41] production.DEBUG: Transaction #20 is unique.
[2024-03-22 19:30:41] production.DEBUG: Going to map data for this line.
[2024-03-22 19:30:41] production.DEBUG: No base url in getBaseUrl() session, will return config variable.
[2024-03-22 19:30:41] production.DEBUG: No access token in hasAccessToken() session, will return config variable.
[2024-03-22 19:30:41] production.DEBUG: Submitting to Firefly III: {"apply_rules":true,"error_if_duplicate_hash":true,"transactions":[ ... ]}
[2024-03-22 19:30:41] production.ERROR: Submission error: 0 ["Duplicate of transaction #17536."]
[2024-03-22 19:30:41] production.DEBUG: This is a duplicate transaction error
[2024-03-22 19:30:41] production.ERROR: transactions.0.description: Duplicate of transaction #17536. (original value: "")

[kesot]

nordigen-erste-3.json

[JC5]

Set ignore_duplicate_transactions to true. I'll pick this up from my side!

[kesot]

It didn't really help, it's still imported

[kesot]

I tried to dig a bit into the code and it seems like the importer uses search api on external_id field and afaik search api doesn't return deleted transactions so it should be easily reproducible.

[JC5]

I know, but by setting the value to true, Firefly III should reject the transaction, despite it not being found.

[kesot]

seems UI is ignoring it. I tried to upload the file to fidi during the import setup with ignore_duplicate_transactions: true, but then it got imported again, checked the logs and it was false.
running it from cmd as a docker command picked up correct value. So I guess you will have to do a fix for UI as well.
thanks for help

[JC5]

Yes, that change is part of the nest release :)

[ksere]

Ended up here as I was trying to debug the same thing (deleted tx being re-imported).
Adding the ignore_duplicate_transactions: true to the json config file did work, but only if you run it with the auto-import.
Uploading it through the UI does not work.