SUMMARY Losing transactions after 8000+ STEPS TO REPRODUCE 1. Given KMY with 9000 transactions in Mysql OR sql lite 2. Save as KMY data file format OBSERVED RESULT - Transactions are lost after 8000+ EXPECTED RESULT - Transactions should not be lost SOFTWARE/OS VERSIONS Windows: Windows 10
Created attachment 122194 [details] KMymoney SQLite Database with 15169 transactions I could not reproduce this problem with the attached SQLite database containing 15169 transactions. After saving as *.kmy or as *.xml, all 15169 transactions were included. The problem must have a cause outside KMyMoney e.g. faulty network connection or corrupt SQLite database.
SUMMARY Hello, guys, I've run anonymizer over my sample data, could you test using it? Losing transactions after 8000+ STEPS TO REPRODUCE 1. Given KMY with 9000 transactions in Mysql OR sql lite 2. Save as KMY data file format OBSERVED RESULT - Transactions are lost after 8000+ EXPECTED RESULT - Transactions should not be lost SOFTWARE/OS VERSIONS Windows: Windows 10
Created attachment 122242 [details] Sql lite db sample
(In reply to robsonsilva from comment #2) > Hello, guys, I've run anonymizer over my sample data, could you test using > it? > Losing transactions after 8000+ The provided db sample contains 8491 transactions. sqlite3 ~/Downloads/test.anon.sqlite "select count(*) from kmmTransactions" 8491 After exporting to an xml file the xml file shows that 8486 transaction were exported. grep "<TRANSACTIONS" test.xml <TRANSACTIONS count="8486"> Are you pointing to this difference or to something else ?
For e.g On ledger screen: If you check last transaction date (sqlite file) and then last transaction date after save as (kmy file) you will see these differences: Account ID, before, after save as kmy: A000331, 16-aug-2019, 08-jul-2019 A000329, 10-aug-2019, 06-jul-2019 A000344, 09-jul-2019, 25-jun-2019
(In reply to Ralf Habacker from comment #4) > Are you pointing to this difference or to something else ? This difference is perfectly OK since the higher number also includes the scheduled transactions. select count(*) from kmmTransactions where txType = 'N' shows exactly 8486 transactions and select count(*) from kmmTransactions where txType = 'S' shows the remaining 5.
After the 'save As' if you run a select to find transactions that just exists on kmmTransactions and not in kmmSplits, you will see that some kmmTransactions don't have related kmmSplits anymore. select * from kmmTransactions where id not in (select distinct transactionId from kmmSplits) /*I wrote by mind sorry for mispelling*/ For e.g On ledger screen: If you check last transaction date (sqlite file) and then last transaction date after save as (kmy file) you will see these differences: Account ID, before, after save as kmy: A000331, 16-aug-2019, 08-jul-2019 A000329, 10-aug-2019, 06-jul-2019 A000344, 09-jul-2019, 25-jun-2019
Created attachment 122245 [details] Screenshot showing one of the accounts (In reply to robsonsilva from comment #7) > For e.g On ledger screen: > > If you check last transaction date (sqlite file) and then last transaction > date after save as (kmy file) you will see these differences: > > Account ID, before, after save as kmy: > A000331, 16-aug-2019, 08-jul-2019 > A000329, 10-aug-2019, 06-jul-2019 > A000344, 09-jul-2019, 25-jun-2019 I just did that and I cannot confirm the dates you report for the sqlite file. I do see exactly the ones that you report for the kmy file. A000331 attached as an example.
(In reply to Thomas Baumgart from comment #8) > Created attachment 122245 [details] > Screenshot showing one of the accounts > > (In reply to robsonsilva from comment #7) > > > For e.g On ledger screen: > > > > If you check last transaction date (sqlite file) and then last transaction > > date after save as (kmy file) you will see these differences: > > > > Account ID, before, after save as kmy: > > A000331, 16-aug-2019, 08-jul-2019 > > A000329, 10-aug-2019, 06-jul-2019 > > A000344, 09-jul-2019, 25-jun-2019 > > I just did that and I cannot confirm the dates you report for the sqlite > file. I do see exactly the ones that you report for the kmy file. A000331 > attached as an example. The problem is sqlfile is already at a weird state,I guess. I've attached the steps starting from anonymizer.xml to sqlite to kmy file now... I've attached xml file and screenshots of steps. 1 - User open anonymizer.xml file 2 - User check reports, ledger 3 - User save as sqlite, close KMY and open again (now sqlite file) 4 - Orphan splits are found in sqlite (dbBrowser) select * from kmmTransactions where id not in (select distinct transactionId from kmmSplits) 5 - User cannot see latest transaction on reports, but he can see on ledger yet.. it seems kmmslipts are about to be lost (probably in some cache table?) 6 - User save as kmy file and Close KMY 7 - User Open kmy file and go to ledger 8 - Transactions missing
Created attachment 122247 [details] Steps
about step 5(In reply to Thomas Baumgart from comment #8) > Created attachment 122245 [details] > Screenshot showing one of the accounts > > (In reply to robsonsilva from comment #7) > > > For e.g On ledger screen: > > > > If you check last transaction date (sqlite file) and then last transaction > > date after save as (kmy file) you will see these differences: > > > > Account ID, before, after save as kmy: > > A000331, 16-aug-2019, 08-jul-2019 > > A000329, 10-aug-2019, 06-jul-2019 > > A000344, 09-jul-2019, 25-jun-2019 > > I just did that and I cannot confirm the dates you report for the sqlite > file. I do see exactly the ones that you report for the kmy file. A000331 > attached as an example. The problem is sqlfile is already at a weird state,I guess. I've attached the steps starting from anonymizer.xml to sqlite to kmy file now... I've attached xml file and screenshots of steps. 1 - User open anonymizer.xml file 2 - User check reports, ledger 3 - User save as sqlite, close KMY and open again (now sqlite file) 4 - Orphan splits are found in sqlite (dbBrowser) select * from kmmTransactions where id not in (select distinct transactionId from kmmSplits) 5 - User cannot see latest transaction on reports, but NOTE: there is no but on step 3 I forget to close and reopen, so, database file wasn't fully reload) 6 - User save as kmy file and Close KMY 7 - User Open kmy file and go to ledger 8 - Transactions missing
So you are saying that your primary file is a kmy or xml file and after exporting to sqlite the sqlite database is broken ? I did the opposite and took the "sql lite db sample" sqlite database as primary source. I added some debug code to void MyMoneyStorageXML::writeTransaction(QDomElement& transaction, const MyMoneyTransaction& tx) { if (tx.memo() == "T000000000000008308") qDebug() << tx.memo(); tx.writeXML(*m_doc, transaction); } did set a breakpoint at the qDebug() output and exported the sqlite database to an xml file. I saw that transaction 'T000000000000008308' has 2 splits while the next account related transaction 'T000000000000008311' did not have any splits although the loaded sql database showed that it should have. I used the following sql command to get the splits: select kmmSplits.splitId, kmmSplits.accountId, kmmSplits.postDate, kmmTransactions.id from kmmSplits, kmmTransactions where kmmSplits.transactionId = kmmTransactions.id 0|A000331|2019-07-08|T000000000000008308 1|A000291|2019-07-08|T000000000000008308 .. 0|A000331|2019-07-10|T000000000000008311 1|A000284|2019-07-10|T000000000000008311 It looks to me that exporting to an kml or xml file from a sqlite database is also broken independently from an import into a sqlite database.
Yes, when I first had the problem I was just using database. In ledger, transactions were there, then in reports transactions/other types as well, I didn't see last transactions. Then I went to save as, and some transactions didn't go.
(In reply to Ralf Habacker from comment #12) > 0|A000331|2019-07-08|T000000000000008308 > 1|A000291|2019-07-08|T000000000000008308 > .. > 0|A000331|2019-07-10|T000000000000008311 > 1|A000284|2019-07-10|T000000000000008311 > > It looks to me that exporting to an kml or xml file from a sqlite database > is also broken independently from an import into a sqlite database. The export fails because it expects to have a linear split order without any missing entries, but the splits returned from the query on kmmSplits do not include splits for transaction T000000000000008309 as shown below: 0|A000331|2019-07-08|T000000000000008308 1|A000291|2019-07-08|T000000000000008308 0|A000344|2019-07-09|T000000000000008310 1|A000330|2019-07-09|T000000000000008310 0|A000331|2019-07-10|T000000000000008311 1|A000284|2019-07-10|T000000000000008311 Therefore the code iterates to the end of all splits (because it do not find the requested transaction number) and cannot process any further splits. While this problem is solvable (skip any transaction whose transaction number is lower than the transaction number of the currently processed split and then notify the user that these transactions could not be exported) this does not change the basic problem that the sqlite database is inconsistent and we need to see where it happens.
(In reply to Ralf Habacker from comment #14) I opened file anonymizer.anon.xml and I've found many nodes like that "<SPLITS/>" my guess is that some problem happened on transactions importer or saving transactions where SPLITS got null, and skipping is happening for kmyfile and xml file but not for db exporter. Maybe this could be part of database integrity check on KMY?
This seems like it is going around in circles. I think it is appropriate to go back to the real core problem. If you were using a sqlite database, and found you had transactions which were seen in the ledger, but not in reports, that is what needs to be diagnosed. Exporting to other formats may fail to produce a complete, consistent file, but that is because there is already a problem with the data, not because of a problem with the process of exporting to a file, other than that it fails to notice the bad data and notify of the problem. Using the sqlite database when you first noticed the problem, if you save again as a database, and reopen the newly saved sqlite file, does anything change, or does it show EXACTLY the same problem?
Git commit 804a08d78aa5d62815d9f3d68fa5c77dd5e16c4f by Ralf Habacker. Committed on 20/08/2019 at 16:15. Pushed by habacker into branch '4.8'. Add consistency check for number of splits M +4 -0 kmymoney/mymoney/mymoneyfile.cpp https://commits.kde.org/kmymoney/804a08d78aa5d62815d9f3d68fa5c77dd5e16c4f
Git commit 297a776c68c64665c4a8a1dc939002e2adf72694 by Ralf Habacker. Committed on 20/08/2019 at 17:28. Pushed by habacker into branch '4.8'. Reduce the number of invalid split counts to less than one Thomas Baumgart pointed out that there can be transactions with only one split. M +2 -2 kmymoney/mymoney/mymoneyfile.cpp https://commits.kde.org/kmymoney/297a776c68c64665c4a8a1dc939002e2adf72694
(In reply to Jack from comment #16) > This seems like it is going around in circles. I think it is appropriate to > go back to the real core problem. If you were using a sqlite database, and > found you had transactions which were seen in the ledger, but not in > reports, that is what needs to be diagnosed. Exporting to other formats may > fail to produce a complete, consistent file, but that is because there is > already a problem with the data, not because of a problem with the process > of exporting to a file, other than that it fails to notice the bad data and > notify of the problem. > > Using the sqlite database when you first noticed the problem, if you save > again as a database, and reopen the newly saved sqlite file, does anything > change, or does it show EXACTLY the same problem? Yes you are right, I got a backup with the first occurrence, there I found 2 transactions without splits. When I spoted the problem in KMY I was generating reports, so from there I generated KMY/XML data type files, these types can skip missing transactions/splits. As a consequence I could see ledger and reports working. However, when I tried to go back to sql the mess were there and became worse. I added 2 attachments: - UsingJustDB: Where I reproduced the problem described above. - GettingRidOfInvalidFirst: Where first I deleted transactions without splits and verify KMY working fine for same scenario. :) * consistency check for number of splits was a great idea ! * tks guys.
Created attachment 122266 [details] UsingJustDB
Created attachment 122267 [details] GettingRidOfInvalidFirst
Add consistency check for number of splits, will guarantee database integrity for this scenario.
Git commit fa7a2616c7903f6f7fddd0700e1d390945b1a521 by Ralf Habacker. Committed on 26/09/2019 at 18:54. Pushed by habacker into branch '4.8'. Fix sql statement case M +15 -15 kmymoney/mymoney/storage/mymoneystoragesql.cpp https://commits.kde.org/kmymoney/fa7a2616c7903f6f7fddd0700e1d390945b1a521
Git commit 781c18c3356d043da25039d50b41a658de95cac2 by Ralf Habacker. Committed on 26/09/2019 at 18:54. Pushed by habacker into branch '4.8'. Fix fetchTransactions for sql databases Base implementation by Thomas Baumgart, adapted to 4.8 branch by Ralf Habacker. Implementation verified with mymoneydatabasemgrtest. FIXED-IN:4.8.5 M +15 -0 kmymoney/mymoney/storage/mymoneydbdef.h M +101 -77 kmymoney/mymoney/storage/mymoneystoragesql.cpp https://commits.kde.org/kmymoney/781c18c3356d043da25039d50b41a658de95cac2
Git commit f77639cd24b0689959e88a0f3dc127f5f09aba9e by Ralf Habacker. Committed on 29/09/2019 at 12:39. Pushed by habacker into branch '5.0'. Fix fetchTransactions for sql databases Base implementation by Thomas Baumgart from loval feature branch, adapted to 5.x branch by Ralf Habacker. Implementation verified with mymoneydatabasemgrtest. FIXED-IN:5.0.7 M +15 -0 kmymoney/plugins/sql/mymoneydbdef.h M +101 -78 kmymoney/plugins/sql/mymoneystoragesql.cpp https://commits.kde.org/kmymoney/f77639cd24b0689959e88a0f3dc127f5f09aba9e
Git commit 1216c3c5866dd9bf639216f96726c90ff0931cb5 by Ralf Habacker. Committed on 29/09/2019 at 12:39. Pushed by habacker into branch '5.0'. Fix sql statement case Conflicts: kmymoney/mymoney/sql/mymoneystoragesql.cpp M +15 -15 kmymoney/plugins/sql/mymoneystoragesql.cpp https://commits.kde.org/kmymoney/1216c3c5866dd9bf639216f96726c90ff0931cb5
(In reply to Ralf Habacker from comment #25) > Git commit f77639cd24b0689959e88a0f3dc127f5f09aba9e by Ralf Habacker. > Committed on 29/09/2019 at 12:39. > Pushed by habacker into branch '5.0'. > > Fix fetchTransactions for sql databases > > Base implementation by Thomas Baumgart from loval feature branch, > adapted to 5.x branch by Ralf Habacker. > This patch was reversed due to compilation errors. Since Thomas is working on a complete replacement of the SQL implementation and this patch corresponds to an earlier version of a part of it, it will probably be fixed with version 5.1.0 at the earliest.
commit has been reverted from 5.0 branch