Bug 339103 - reports cannot be created using the DB backend
Summary: reports cannot be created using the DB backend
Status: RESOLVED FIXED
Alias: None
Product: kmymoney
Classification: Applications
Component: database (show other bugs)
Version: git (master)
Platform: Ubuntu Linux
: NOR normal
Target Milestone: ---
Assignee: KMyMoney Devel Mailing List
URL:
Keywords:
: 356166 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-09-15 19:02 UTC by ritzer
Modified: 2017-07-01 12:12 UTC (History)
5 users (show)

See Also:
Latest Commit:
Version Fixed In: 4.8.0


Attachments
unable-to-copy-report (2.15 MB, application/octet-stream)
2014-09-23 18:47 UTC, ritzer
Details
unable-to-add-reports.anon.xml (2.51 MB, application/xml)
2014-09-23 18:47 UTC, ritzer
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ritzer 2014-09-15 19:02:14 UTC
I cannot copy any of the existing reports. When I try to do this I always get the following error message: 
"Der Bericht kann nicht hinzugefügt werden. Grund: „Error in function void MyMoneyStorageSql::writeReport(const MyMoneyReport&, QSqlQuery&) : writing Reports
Driver = QSQLITE, Host = localhost, User = ritzeral, Database = /home/ritzeral/Dokumente/Haushalt/finanzen/20140221-finanzübersicht-db
Driver Error: 
Database Error No -1: 
Text:  
Error type 0
Executed: INSERT INTO kmmReportConfig (name, XML, id) VALUES (?, ?, ?);
Query error No 19: UNIQUE constraint failed: kmmReportConfig.id Zeile kann nicht abgeholt werden
Error type 1“
Comment 1 Cristian Oneț 2014-09-23 13:10:10 UTC
Thi seems like a duplicate of BUG 312676 but that should already be fixed in 4.6.4, are you sure that you use 4.6.4?
If you indeed use 4.6.4 could you provide an anonymous version of your file? 

http://docs.kde.org/stable/en/extragear-office/kmymoney/details.formats.anonymous.html
Comment 2 ritzer 2014-09-23 18:47:19 UTC
Created attachment 88819 [details]
unable-to-copy-report

Hello, 

I needed a few steps to be able to reproduce the error with the anonymous file: 

1) I exported the data as an anonymous file, closed the original file and loaded the anonymous file. I could not reproduce the error. 

2)I saved the anonymous file to a SQLite-database. I could not reproduce the error. 

3)I closed the SQLite-database and reopened it, now I could reproduce the error. 

I attached two files: 
* the anonymous file: unable-to-add-reports.anon.xml
* the database file: unable-to-copy-report



On Tue, 23 Sep 2014 13:10:10 +0000
Cristian Oneț <onet.cristian@gmail.com> wrote:

> https://bugs.kde.org/show_bug.cgi?id=339103
> 
> Cristian Oneț <onet.cristian@gmail.com> changed:
> 
>            What    |Removed                     |Added
> ----------------------------------------------------------------------------
>              Status|UNCONFIRMED                 |NEEDSINFO
>          Resolution|---                         |WAITINGFORINFO
>                  CC|                            |onet.cristian@gmail.com
> 
> --- Comment #1 from Cristian Oneț <onet.cristian@gmail.com> ---
> Thi seems like a duplicate of BUG 312676 but that should already be fixed in
> 4.6.4, are you sure that you use 4.6.4?
> If you indeed use 4.6.4 could you provide an anonymous version of your file? 
> 
> http://docs.kde.org/stable/en/extragear-office/kmymoney/details.formats.anonymous.html
> 
> -- 
> You are receiving this mail because:
> You reported the bug.
Comment 3 ritzer 2014-09-23 18:47:20 UTC
Created attachment 88820 [details]
unable-to-add-reports.anon.xml
Comment 4 Cristian Oneț 2014-09-24 06:00:01 UTC
I was able to reproduce the error using git master with the data you have provided, thanks.
Comment 5 Cristian Oneț 2014-09-24 06:25:23 UTC
This is caused by the fact that the kmmFileInfo.hiReportId has an inconsistent value considering the existing reports, Fernando, any ideas how this might happen?

As a workaround for the reporter.

Please run
select id from kmmreportconfig
To obtain the highest report id, then run
update kmmFileInfo set hiReportId = number_greater_than_highest_id

That's should fix your problem.
Comment 6 Cristian Oneț 2014-09-24 07:15:43 UTC
It just occurred to me that this was a new file that I saved as a database so that could be the explanation: when a new db is created from a file kmmFileInfo.hiReportId is not properly initialized causing the unique constraints failure when adding a new report.
Comment 7 ritzer 2014-09-24 18:11:03 UTC
The workaround works. Thanks a lot.

On Wed, 24 Sep 2014 06:25:23 +0000
Cristian Oneț <onet.cristian@gmail.com> wrote:

> https://bugs.kde.org/show_bug.cgi?id=339103
> 
> --- Comment #5 from Cristian Oneț <onet.cristian@gmail.com> ---
> This is caused by the fact that the kmmFileInfo.hiReportId has an inconsistent
> value considering the existing reports, Fernando, any ideas how this might
> happen?
> 
> As a workaround for the reporter.
> 
> Please run
> select id from kmmreportconfig
> To obtain the highest report id, then run
> update kmmFileInfo set hiReportId = number_greater_than_highest_id
> 
> That's should fix your problem.
> 
> -- 
> You are receiving this mail because:
> You reported the bug.
Comment 8 Fernando Vilas 2014-09-30 02:18:43 UTC
I am building a test case so we can duplicate this programatically and then fix it. Christian's idea of the highXid variables being improperly set seems like it is correct. The test case (and fix) will show it.

The other database work that is ongoing may be a better fix here anyway, but at least we will get a reasonable test case out of it.
Comment 9 Christian David 2014-10-04 18:13:31 UTC
During the upgrade of database version 6.4 to 7.x the hiTransactionId filed is set 0 as well. It seems to be an issue in the upgrade process...
Comment 10 Christian David 2015-01-04 15:55:48 UTC
I published a Google Code In task to start solving this issue. So please do not fix it within the next 15 days ;)

About Google Code In: http://www.google-melange.com/gci/homepage/google/gci2014
Comment 11 Christian David 2015-01-31 21:15:51 UTC
Git commit 35c53b901b155e3a917e37e7d3e6124ec4dead1b by Christian Dávid, on behalf of Daniel Ring.
Committed on 31/01/2015 at 13:21.
Pushed by christiand into branch 'master'.

Added function highestIdNumString() to extract new ids from SQL tables

This adds a function to retrieve the highest ID number from the
database. The IDs are prefixed by a short identifier string, and are
stored in the database as strings.
REVIEW: 122138

M  +13   -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp
M  +1    -0    kmymoney/mymoney/storage/mymoneydatabasemgrtest.h
M  +26   -0    kmymoney/mymoney/storage/mymoneydbdriver.cpp
M  +5    -0    kmymoney/mymoney/storage/mymoneydbdriver.h
M  +12   -0    kmymoney/mymoney/storage/mymoneystoragesql.cpp
M  +9    -0    kmymoney/mymoney/storage/mymoneystoragesql.h

http://commits.kde.org/kmymoney/35c53b901b155e3a917e37e7d3e6124ec4dead1b
Comment 12 Christian David 2015-01-31 21:15:52 UTC
Git commit b36a26f9a2c2e683537e3e678a54caadc61a7171 by Christian Dávid.
Committed on 31/01/2015 at 20:43.
Pushed by christiand into branch 'master'.

Corrected calculation of the next MyMoneyObject ids in SQL backend

The next MyMoneyObjects' ids were stored in the table kmmFileInfo. But
this data got corrupted in the past several times.

Now the highest id is calculated - so corruptions should not be possible
anymore.

The calculation process is lazy and is only executed when needed as it
is quite expensive (uses regex on some RDBMSs). Unfortunatly the SQL
backend triggers the calculation all the time at the moment - way more
often than needed. Some writeFileInfo() and readFileInfo() calls must
be removed to fix that (future work).

Any calls to writeFileInfo() and readFileInfo() are removed - these
methods cannot be called before the upgrade process is finished!

Also the higest id numbers are not set during the upgrade process.
FIXED-IN: 4.8

M  +20   -20   kmymoney/mymoney/storage/mymoneydatabasemgr.cpp
M  +108  -234  kmymoney/mymoney/storage/mymoneystoragesql.cpp
M  +10   -6    kmymoney/mymoney/storage/mymoneystoragesql.h

http://commits.kde.org/kmymoney/b36a26f9a2c2e683537e3e678a54caadc61a7171
Comment 13 Cristian Oneț 2015-02-01 13:57:49 UTC
Christian, can this be picked in 4.7?
Comment 14 Christian David 2015-02-01 15:02:41 UTC
The changes are more than just a bug fix. Back-porting them might be not so easy (definitely more than some cherry-picks). I think we should streamline our power to release 4.8 in near future instead.
Comment 15 Cristian Oneț 2015-02-01 19:14:48 UTC
I agree, I just asked about 4.7 because I feel that we'll release 4.7.2 before a 4.8.0 release.
Comment 16 Christian David 2015-12-02 12:11:44 UTC
*** Bug 356166 has been marked as a duplicate of this bug. ***