Incorrect transactions sum after MySQL to PSQL migration

[barreeeiroo]

Hi,

I have recently migrated my Firefly III database from MyQL engine to PostgreSQL, and when I run the php artisan firefly-iii:report-integrity command I face the following output:

      Now executing firefly-iii:create-group-memberships
  [✓] Validated group memberships
      Now executing firefly-iii:report-empty-objects
      Now executing firefly-iii:report-sum
  [x]  Error: Transactions for user #1 (REDACTED) are off by 0.070000000000!
      Now executing firefly-iii:upgrade-group-information

In the old instance, running that same command does not report that error.

To perform the migration, I have partially followed the guidance here. I used pgloader to perform the migration, but had to change the configuration a bit as I wanted a clean DB migration. The command executed successfully (I don't have the output anymore), no errors reported and the only warnings reported were related to index names getting truncated. Before this "successful" execution, I did also try the original method reported there but faced a lot of issues when doing the data only import, so I had to change it to fully create all tables.
I did also try to run the php artisan firefly-iii:force-decimal-size command, but no luck: the same error persists even after running it.

When I access Firefly III Web UI, I see the balances are the same as in the old instance (the old database is connected to a different web server, so I can access both at the same time): "as of now" Net Worth matches in both instances and so do the account balances.
I suspect it may be related to multi-currency transactions that I store, but I don't know how I can debug it.

Any idea on what could have gone wrong? It is a bit confusing that the balances match in both instances, but only one of them reports this issue of being off by 7 cents.

---

• Firefly Version: 6.1.21 (both instances)
  • Environment: Self Managed (original), Docker (new)
• Database: MySQL 8.0.35 (original, AWS RDS), PostgreSQL 17.2 (new, Docker hosted)

[JC5]

Mmm, what happens when you run php artisan firefly-iii:correct-uneven-amounts?

If that command doesn't work just check the list, I must admit I don't know the name by heart.

[JC5]

Yes, it is correct that transfers are different from normal transactions, and this is what they are supposed to look like:

As you can see, they are flipped as well as mirrored. This makes sure that each account always has "amount" in its native currency. In this example, account 222 has currency 3.

[barreeeiroo]

That makes sense, in that case it seems like the original MySQL DB got corrupted somehow and the correct one was PSQL.

Just another quick question: what does the php artisan firefly-iii:transfer-currencies command do? Or what was it intended for?
I will just manually fix those transactions which got altered by it, but curious about its purpose...

[JC5]

That's an old upgrade command from 4.8.0. It should have no effect on newer transactions.

[barreeeiroo]

Just confirmed that upgrading to 6.1.25 fixed the issue. Running php artisan firefly-iii:report-integrity no longer reports the mismatching balance, as it now also takes into account foreign amounts.

Leaving here the root cause for anyone else who faces the same issue:

The problem was some strange Transfer between accounts in different currency. Prior to 6.1.23, Firefly III would only perform the balance integrity taking into account the standard currency amount, while on 6.1.23 onwards it will also take into account the foreign one.
Transfers between accounts in different currency store the amounts in the following way: outgoing transaction stores the amount in their currency and foreign amount in the incoming account currency, and incoming transaction stores the amount as well in their currency but foreign amount in the outgoing account currency. As such, if outgoing transaction has, for example, amount -1.00 EUR foreign -1.07 USD, then incoming transaction will have amount 1.07 USD 1.00 EUR.
This command originally only took into account that -1.00 (EUR) + 1.07 (USD) == 0, which is wrong for cross-currency account transfers. Now, it will add up both values, so -1.00 (EUR) + 1.07 (USD) == -1.07 (USD) + 1.00 (EUR).

The solution for me was to just upgrade the version.