SUMMARY While preparing my tax return, I produced a report to list transactions for a specific category that occurred last year. Then I realized that one transaction that occurred on 2020-09-07 was not listed on the report. I opened the account's ledger (my chequing account) and indeed, the transaction is missing. It is however listed on my bank account statement for that period, and the weird thing, today's account balances reconciles between my bank and KMM. This doesnt make sense. I reconcile my accounts every week until they balance to the penny with my banks, so if I had forgot to enter that transaction in KMM, the ledger would have been off with my bank's online account ledger. I inspected the ledger in KMM one transaction at the time starting from today (since balances reconciles) until 2020-09-08 which is the last time the balance reconciles with my bank. Before that point, the balance never reconciles. As a matter of fact, most balances listed in the ledger are thousands off from the real balance. Note the missing transaction was performed on 2020-09-07 so 2 days before the last balance that reconciles. Unfortunately, I noticed there are many missing transactions in the ledger. After 10 or so, I stopped looking since its pointless. STEPS TO REPRODUCE 1. I dont know 2. 3. OBSERVED RESULT Ledger balance reconciles now, but has missing transactions EXPECTED RESULT SOFTWARE/OS VERSIONS Linux Mint 19.3 KDE Frameworks 5.44.0 Qt 5.9.5 (built against 5.9.5) ADDITIONAL INFORMATION I use MySQL database.
It is very strange that your balance does not match the bank statement prior to September, but it does match now. Did you add some adjusting transaction to make it reconcile? When you reconcile, do you check both the starting and ending balance for the period you are reconciling? When you reconcile, do you check all transactions, or just the final balance? In terms of your missing transactions, have you searched for them by amount or payee, just to see if they might be entered with the wrong date? When you day the account does not match the statements prior to 07 September, did it ever match? My approach would be to check the ledger balance at the end of every statement. If the most recent statement matches, then work your way backwards until the most recent that does not match. Then check every transaction in the following statement - because if the transactions in KMM match all the ones on the statement, then the balances at the beginning and end of that period should either both match or neither match. That's where I would start looking for extra or missing transactions. Let us know if this leads you to any discoveries.
Jack, When I reconcile, I simply open up my bank's online account and lok one transaction at the time and mark them as reconciled in KMM, so usually (read always) when I am done, the KMM ledger and my bank's are identical with the same number of transactions, amounts and balances. Copies basically. I did not enter adjusting transactions (I'm not even sure what that is) and I am not using the reconciliation wizard since I manually match each transaction with my financial institutions. To reconcile, I start at the last reconciled transaction, then manually reconcile each one, enter the missing ones, and at the end, the ledgers are identical. I did search for the missing transactions. For example the first missing one from Sept-07 2020 was in the amount of $237.81 which is not found using the "Find transaction" search tool. Looking at the payee's transactions (under payee, I see all transactions, except of course the missing one). This missing transaction is my bi-monthly utility bill, so its pretty easy to spot a missing transactions since this occurs 6x per year. Like I said, the balance always matched, even prior to 2020-09-07. I've been using KMM since 2008, never had issues of that sort. Its so weird I was starting to wonder if I was losing it ;) I will keep digign up and I will upload a small picture to help visualize what happens
When you mark a transaction reconciled, are you only checking that it matches the bank's record, or do you also confirm that the balance matches? (When this is resolved, I suggest reading the user manual on reconciliation, and actually using the reconciliation wizard. I can't be certain, but I think it would have let you discover this problem earler.) To confirm - are you saying that the current balance matches between the bank and the ledger, and that it balances all the way back to 08 Sept, but not before then? I don't understand how you could have gone from the balances not matching prior to that date to them matching after that date. Do I have something backwards?
I always confirm the balance matches and all transactions are identical. I basically have a clone of my bank's legder... This may not be related to this problem, but I just discovered that a while before the missing transaction occurred (lets say 1 month before), the balance is exactly lower by the same amount as the missing transaction. Example: KMM's numbers: --Balance on 2020-09-06: $3730.32 --Balance on 2020-06-08: $2800.95 --Expenses: -$929.37 My bank's ledger: --Balance on 2020-09-06: $3968.13 --Balance on 2020-06-08: $2800.95 --Expenses: -$1167.18 You'll notice the expenses are 237.81 difference, and the start balances are also exactly 237.81. This is why the balances after the 2020-09-08 are correct but doesnt explain where before the 2020-09-06 things went sideways... Is it possible a bug caused the start balance of the account to be reduced by the amount of the missing transaction hence causing the balances thereafter to be identical... I will keep searching.
Okay I found something interesting. I opened 2 backups I made, one on 2019-11-24 the other on 2021-01-12. Exact dates don't really matter. Then I opened the backups side by side with my actual datafile and started comparing the ledger balances at random dates. The balances between the two backup files are always identical, but the balance from the datafile is always different. I walked back in time until the opening of the account on 2015-04-08. The opening balance is way off. This is a different problem. This account was opened with a starting balance of 0, then an initial transfer of $250 occurred (first transaction ever). In the backups, the first non-zero balance is of course $250, but in the datafile, the balance is $1.681.60 ??????? What's going on here? Data corruption?
Another comment... I was manually marking transactions as "reconciled" then a bunch of them just disappeared from the ledger between 2021-01-11 & 2021-03-13 but the account's balance is still OK. Seems to be the same bug. I have two issues: starting balance is wrong (I cannot find where the money comes from), and transactions are disappearing.
Very strange indeed. My next suggestion is to confirm there are no filters applied to the ledger display. The only way to keep the same final balance if a transaction is deleted is either for another transaction to be added or the amount of some transaction is changed by the amount of the deleted transaction. Changing the opening balance transaction would make the ledger balance not match the bank until the date of the removed transaction, but I can't think of any way that would happen. Out of curiosity, can you look at the opening balances account to see if it looks OK for all other accounts?
Found the issue. I examined EVERY single transaction one by one since 2015-04-07 (I need new eyes), and there are a total of 6 missing transactions totalling $1581.60. Strangely, the opening balance of this account is $1581.60 while it should be ZERO. Thats why the balance before the last missing transaction is not correct but is after (to this day). I conclude with a high level of certainty that a bug somewhere in KMM has adjusted the opening balance of the account by the exact amount of each of these missing transactions instead of keeping them as transactions in the ledger. Now, how to correct the opening balance? In the account edit dialog, the amount of opening amount is zero (was actually empty). If I can reset the opening balance to zero, re-enter the 6 missing transactions, all will be finally perfect.
Forgot to answer you about the other accounts: yes, the chequing account is the only one (AFAIK) with a wrong opening balance.
I can think of absolutely no way that multiple transactions would deleted and the opening balance of the account adjusted by exactly those amounts, which just means something happened that we don't understand. Have you actually looked at the ledger for the opening balance account, to confirm that is the only strange entry there? In addition, have you grepped (zgrep if your data file is compressed) for the date of one of the transactions, just to see if it is still there but assigned to a different account? It's not likely, since you already searched on the amount, but it is easy enough to check.
I agree, this is by far the weirdest anomaly I've encountered with KMM since I started using it around 2008. Question: my datafile has more than 16,000 transactions and over 36,000 splits. Could I be reaching the maximum that can either KMM or the MySQL engine handle? I'm not sure what you mean by "Have you actually looked at the ledger for the opening balance account, to confirm that is the only strange entry there?" but the first transaction of the faulty account was a deposit of $100 and right after that deposit, the balance jumps to $1681.60 which is $1581.60 too high (should have been $100). Like I said, this amount is mysteriouslt the same as the total of all missing transactions... If you meant to look at the "Opening Balances" account, there are NO transactions of $1581.60, and no transactions associated to the chequing account... I am not sure exactly how to use zgrep, but I tried with zgrep TRANSACTION KMM.kmy | grep '2020-03-02' which is the date of one of the missing transactions, and nothing came up (im probably not using zgrep properly).
I have almost 21k transactions and almost 50k splits so I don't think it is a limit on the KMM xml file. I would be surprised if there was any limit and I think some people have a lot more transactions than I do. I have been following this thread with interest. I have run into balance issues in the 15 years I have used KMM, but they always turned out to be self inflicted. I have this strange feeling that KMM has asked me if I wanted to adjust the opening balance of an account when I was doing something. If it did, it hasn't happened in a long time and I have no idea what I was doing at the time. I searched the source code for "opening balance" but there are too many lines for me to review to find anything useful. I thought I would share this just in case it triggers an idea with someone else.
I have about 22K transactions and about 48k splits. The only real limit would be if your PC didn't have enough memory for KMM to store all the data in memory, but swap space would take care of that, perhaps with a performance hit. I also don't think mySql is adding any limitations. Yes - I meant the "Opening balances" account, but you've looked and not found anything of interest. However, are you saying that your checking account started with a 0 balance, then a transaction for $100 and the same balance, and then the balance changed on the next transaction? What was the transaction that increased the balance? Or, do you mean that the second transaction was for X and the balance increased by X plus that other large amount? Balances in the ledger are calculated based on the transactions displayed, so if a balance changed by an amount different from the shown transaction, there is a problem there. If you can identify that transaction in your file, look to see if there are any strange splits. You can also right click on the transaction in the ledger to see if it shows "Go to ..." for anything other than the displayed category and payee, or other account for a transfer. Your zgrep command looks OK. Just to confirm it is working right you can try "zgrep '<TRANSACTION ' KMM.kmy | wc" which should give something close to your number of transactions. (note the space after TRANSACTION, otherwise you also count the closing tags.) You could also just "zgrep '2020-03-02' KMM.kmy" to see if anything shows up on that date.
All right, I just found something interesting (another)... While using the MySQL data, account starting balance is $1581.60 (wrongly so). I SAVE AS to a local XML (.kmy) file, reopen the saved file and boom, the starting balance is back at 0 like it should be (of course the missing transactions are still missing but at least the balances are normal now). Here's what I did: Worked from the local XML file (.kmy), fixed the data (re-added the transactions), then saved and re-imported into MySQL. All is finally good now. There's definitely a bug or some corruption going on otherwise why simply saving the MySQL data to a local file would change the account balance??? Doesn't seem right to me. Good to know neither KMM or MySQL are limited (at least at my usage level)... @Brendan: Yes I was also thinking I had never entered these transactions but when I discovered today's balance were good, I started digging until I realized the opening balance was "artificially" increased... Doing a save-as fixed it... Definitely not self-borkage... I haven't encountered an occurence where KMM would ask me to adjust an opening balance, and since the missing transactions were pretty much all dated around last september (with some that were present during that period), I would perhaps remember... @Jack: What I have observed is something like this: "Corrupted dataset": ================================================================================== Date Details Payment Deposit Balance 2015-04-07-------Deposit from blablabla---------- ---- 100.00------1,681.60 "Good dataset": ================================================================================== Date Details Payment Deposit Balance 2015-04-07-------Deposit from blablabla---------- ---- 100.00------100.00 On the corrupted dataset, as you can see, the opening balance was 1,581.60 for the balance to be 1,681.60 AFTER the first transaction (deposit of 100). Obviously, this acccount was opened with a ZERO balance, as shown by the "Good dataset". What Brendan talks about (KMM asking to adjust the balance) could have happened and could explain what happened, but in these missing transactions, there was a mortgage payment, a utility bill and other transactions I religiously track.... Anyways, I'm not sure what to do now this is fixed, but obviously, I will make more frequent backups, and since I discovered these missing transactions several months after their posted dates, what worries me is to backup my dataset thinking it is GOOD but in fact its missing data again. IMO KMM needs auditing features, more than the Consistency Check which I'm not really sure what it does... Okay enough for today, I need fresh air ;)
If your first transaction in the account is for $100 deposit, but the balance shows 1681.60, then something is hidden. When there is an opening balance in an account, it does show as a transaction transfer from the "Opening balances" account. Somehow, KMM thinks you already have 1582.60 in the account before that 100 deposit. Do you have a starting date filter on the ledger? KMM obviously thinks that amount is already in the account - we have to figure out where it is coming from. Until we have a better idea of what happened, I'm not sure what type of auditing checks would have prevented this.
Jack, of course I checked for the date filter in KMM settings, it was set to 2012-01-01 well before the opening of this account. There were no other filters or settings that I can think of. Like I said, the Opening Balances did NOT have an amount associated to the faulty account. The balance of the "Opening Balance" has always been the same for many years now, and I checked it to make sure something was not hidden... Anyways, why would a simple SAVE AS from the MySQL data to a local file fix it? Why would a SAVE AS, doing nothing else, remove this phantom amount of 1581.60? I've screwed up many many times in the past 13 years of using KMM, but I always noticed right away because the balances were no longer matching. This was not the case with the issue discussed here. Sounds like a bug to me, or some sort of data corruption. My theory, and believe me, I'm not sure so take it with a grain of salt: Somehow, due to a bug, KMM destroyed the "missing" transactions and adjusted the opening balance of the account to maintain current balance (similarly to what Brendan was suggesting). Why was there no transaction in the "Opening balances" account? I dont know, Why would this happen? Again, I dont know. I've seen very weird bugs in other applications before.... When I saved the MySQL data to a local file, reopened the saved file and noticed the balance of the account reduced by 1581.60, and saw the opening balance reset to ZERO, without doing anything else, I knew this was a bug.
I agree something has gone wrong. My suggestion to check the date filter was in case a transaction was created with an EXTREMELY early date. (My filter is 1900-01-01.) However, you are right that any transaction with a date prior the the account opening date would be caught by the regular consistency check. If you save your "fixed" file back to the sql back end, does the error come back? Also, just out of curiosity, if you open the "bad" sql dataset, and save it to a new MySql database, does it still show the problem. While I still don't have any idea where the actual problem might be, answers to those questions might provide suggestions where to look.
I would try to create a dump of a working and a non-working database and compare it. Of course, this might not be easy and depends on the dump. Just an idea to figure out what the problem might be. An interesting tables is kmmAccounts which contains a balance column (which does not exist in the XML storage). Looking at the view kmmBalances may also be an idea. All this using an external db viewer.
The issue just happened again. This time different transactions disappeared from the ledger and the balance changed to account for those transactions. Thank GOD I had a backup from yesterday... This time I noticed something different, and maybe this is what originally happened with the previous occurence: I marked a bunch of transactions in the chequing account as "reconciled", then noticed one of them simply vanishing. I immediately got a popup saying "Unable to modify transaction" with nothing in it. Then the balance was changed. I will look at the database stuff in the meantime, and upgrade KMM to latest. Are the AppImage considered production ready for day-to-day use?
This sounds very strange. Would be cool to understand the problem. > Are the AppImage considered production ready for day-to-day use? Yes, the stable ones on https://binary-factory.kde.org/job/KMyMoney_Stable_Appimage_Build/ are. Not the ones from the Nightly pipeline.
Just tried the last AppImage (KMyMoney-5.1.1-5ea8730-x86_64.AppImage) on one of my MySQL dataset, when I hit save, the app crashes. I have opened a separate bug report here: https://bugs.kde.org/show_bug.cgi?id=434722
I am closing this ticket as I believe this issue was related to data corruption with the SQL backend. As of today (2022-02-20) data is still reliable and this issue has not resurfaced.