Timestamps in Gnucash Mysql
I just found out that gnucash stores DatePosted and DateEntered as Strings instead of the proper DateTime or Timestamp sql data-types. Why is that? In XML it already uses a proper date-format. This makes is very hard to sort by date in SQL-queries and to select a range as you need a complex fulltext-index instead of a trivial integer index. (There are supposed to be MANY transactions and splits in there.) Marcus ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
unable to find a common currency in txn X, and that is strange
I'm attempting to diagnose a corruption I have in my GC data file (XML). I started GC 2.3.3 with --debug and am looking through the trace file. I seem to have lots of entries of the form: * 19:59:40 WARN [xaccTransFindOldCommonCurrency()] unable to find a common currency in txn 13db240e2d97ba42480b3d64eb2528fa, and that is strange. * 19:59:40 WARN [xaccTransScrubCurrency()] no common transaction currency found for trans="Westpac" (13db240e2d97ba42480b3d64eb2528fa) * 19:59:40 WARN [xaccTransScrubCurrency()] split="" account="Bank fee (MC)" commodity="AUD" * 19:59:40 WARN [xaccTransScrubCurrency()] split="" account="Cheque (MC)" commodity="AUD" There are 5800 of them which corresponds roughly to the number of transactions I have in the whole file. Can anybody please tell me whether this indicates a problem. From the message, it looks like it is a problem, but I can't see how every transaction would be bad. Thanks for any help, Graham ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Timestamps in Gnucash Mysql
On August 12, 2009 03:06:40 am marcus.wolsc...@googlemail.com wrote: > I just found out that gnucash stores > DatePosted and DateEntered as Strings > instead of the proper DateTime or Timestamp > sql data-types. > > Why is that? In XML it already uses a proper > date-format. > > This makes is very hard to sort by date in > SQL-queries and to select a range as you need > a complex fulltext-index instead of a trivial > integer index. (There are supposed to be MANY > transactions and splits in there.) That goes back to the time when the sql backend couldn't support different SQL/DDL code for the different db engines. There is no SQL standard for a date/time that all 3 db's (sqlite3/mysql/pgsql) share. Now that the sql backend can support different column types for the 3 dbs, it does make sense to use the proper types. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: GnuCash Crash when viewing an account with posted transaction
Hi, Mark Sluser writes: > I am using GnuCash 2.3.3 and postgresql backend. > I had to manually import some invoices/entries and > transactions/splits/ slots and now when I view an account with > invoices posted, GnuCash crashes. What do you mean by "manually import some invoices/entries"? How did you "manually import them"? > Here is my gnucash.trace file: > > * WARN [guid_init()] only got 1626 bytes. > The identifiers might not be very random. > * WARN Refusing to add non-unique action > CustomerReportAction' to action group 'MenuAdditions' > * WARN Could not spawn perl: Failed to execute child > process (No such file or directory) I see nothing in here that's important. > > Any idea what I could do to find the problem? Get a backtrace? http://wiki.gnucash.org/wiki/Stack_Trace > -Mark > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Reg Run Balance for Subaccount view
Hi Tim, Tim M writes: > Hi guys, > > I wrote a (beta) patch to display the running register balance in "View > Subaccounts" register view, which I submitted to the mailing list on July > 10th. The original thread is here: > > https://lists.gnucash.org/pipermail/gnucash-devel/2009-July/025807.html > > (I don't have the original post in my email anymore so I can't do a direct > reply) > > Has anyone taken a look at this and/or can anyone give me advice on how to > improve it and/or address the issues I was unable to resolve? It's nearly > finished except for the couple of minor issues mentioned in my original > post. Any help that can be offered is appreciated. I'm sorry nobody has had the time to get to it yet. I'm sure someone will eventually. I know that I personally have been insanely busy recently. Hang in there... > Thanks, > -Tim > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Patch for Cash Flow Report
David, David Eisner writes: > Hi. I've been looking into the Cash Flow report (cash-flow.scm) to > isolate some potential bugs. This entailed learning Scheme (I > programmed a little Common Lisp in a previous life). So far I've > filed one bug report and submitted a patch (against 2.2.9): > > http://bugzilla.gnome.org/show_bug.cgi?id=591117 > > What steps can I take to increase the probability of getting the bug > triaged? Should I also submit a patch against the SVN trunk? I think > it would be essentially the same patch, but I can do that if > necessary. I suspect people are just busy and haven't had a chance to look at it. I know that I haven't had time. :( Thank you for the reminder. I'm sure someone will get to it. > Thanks. > > -David -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Timestamps in Gnucash Mysql
On August 12, 2009 08:25:31 am Phil Longstaff wrote: > On August 12, 2009 03:06:40 am marcus.wolsc...@googlemail.com wrote: > > I just found out that gnucash stores > > DatePosted and DateEntered as Strings > > instead of the proper DateTime or Timestamp > > sql data-types. > > > > Why is that? In XML it already uses a proper > > date-format. > > > > This makes is very hard to sort by date in > > SQL-queries and to select a range as you need > > a complex fulltext-index instead of a trivial > > integer index. (There are supposed to be MANY > > transactions and splits in there.) > > That goes back to the time when the sql backend couldn't support different > SQL/DDL code for the different db engines. There is no SQL standard for a > date/time that all 3 db's (sqlite3/mysql/pgsql) share. > > Now that the sql backend can support different column types for the 3 dbs, > it does make sense to use the proper types. Also, sqlite does not have a datetime or timestamp but needs to use strings, and I've been coding mainly for sqlite, with mysql and pgsql taking 2nd place. Gnucash has both dates, and date-times. For dates, I can use CHAR(8) for sqlite, DATE for mysql and pgsql. For date-times, I can use CHAR(14) for sqlite, TIMESTAMP for mysql and TIMESTAMP for pgsql. Question: should the pgsql be "TIMESTAMP WITH TIME ZONE" or "TIMESTAMP WITHOUT TIME ZONE"? Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: unable to find a common currency in txn X, and that is strange
Can you try going to backup .xac files and seeing when it might have changed? -derek Graham Menhennitt writes: > I'm attempting to diagnose a corruption I have in my GC data file (XML). > I started GC 2.3.3 with --debug and am looking through the trace file. I > seem to have lots of entries of the form: > > * 19:59:40 WARN [xaccTransFindOldCommonCurrency()] > unable to find a common currency in txn > 13db240e2d97ba42480b3d64eb2528fa, and that is strange. > * 19:59:40 WARN [xaccTransScrubCurrency()] no common > transaction currency found for trans="Westpac" > (13db240e2d97ba42480b3d64eb2528fa) > * 19:59:40 WARN [xaccTransScrubCurrency()] split="" > account="Bank fee (MC)" commodity="AUD" > * 19:59:40 WARN [xaccTransScrubCurrency()] split="" > account="Cheque (MC)" commodity="AUD" > > There are 5800 of them which corresponds roughly to the number of > transactions I have in the whole file. > > Can anybody please tell me whether this indicates a problem. From the > message, it looks like it is a problem, but I can't see how every > transaction would be bad. > > Thanks for any help, > Graham > ___ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > > -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Timestamps in Gnucash Mysql
Phil Longstaff writes: > Also, sqlite does not have a datetime or timestamp but needs to use strings, > and I've been coding mainly for sqlite, with mysql and pgsql taking 2nd place. > > Gnucash has both dates, and date-times. For dates, I can use CHAR(8) for > sqlite, DATE for mysql and pgsql. For date-times, I can use CHAR(14) for > sqlite, TIMESTAMP for mysql and TIMESTAMP for pgsql. Question: should the > pgsql be "TIMESTAMP WITH TIME ZONE" or "TIMESTAMP WITHOUT TIME ZONE"? This opens up the whole can of worms regarding timestamps... IMHO the db should store timeout a timezone, and it should store everything in UTC. Then there is the issue of the engine and ui interpretting UTC (or not) as appropriate. Like I said, can of worms... > Phil -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: unable to find a common currency in txn X, and that is strange
Thanks for replying Derek. Yeah, I'll be doing that. I just wondered whether those messages were expected, or something to worry about. Graham Derek Atkins wrote: > Can you try going to backup .xac files and seeing when it might have > changed? > > -derek > > Graham Menhennitt writes: > > >> I'm attempting to diagnose a corruption I have in my GC data file (XML). >> I started GC 2.3.3 with --debug and am looking through the trace file. I >> seem to have lots of entries of the form: >> >> * 19:59:40 WARN [xaccTransFindOldCommonCurrency()] >> unable to find a common currency in txn >> 13db240e2d97ba42480b3d64eb2528fa, and that is strange. >> * 19:59:40 WARN [xaccTransScrubCurrency()] no common >> transaction currency found for trans="Westpac" >> (13db240e2d97ba42480b3d64eb2528fa) >> * 19:59:40 WARN [xaccTransScrubCurrency()] split="" >> account="Bank fee (MC)" commodity="AUD" >> * 19:59:40 WARN [xaccTransScrubCurrency()] split="" >> account="Cheque (MC)" commodity="AUD" >> >> There are 5800 of them which corresponds roughly to the number of >> transactions I have in the whole file. >> >> Can anybody please tell me whether this indicates a problem. From the >> message, it looks like it is a problem, but I can't see how every >> transaction would be bad. >> >> Thanks for any help, >> Graham >> ___ >> gnucash-devel mailing list >> gnucash-devel@gnucash.org >> https://lists.gnucash.org/mailman/listinfo/gnucash-devel >> >> >> > > ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: Timestamps in Gnucash Mysql
Derek, yes, I was planning to store w/o time zone, in UTC, but just wanted other people's input, because I know this has been a can of worms. Phil From: Derek Atkins To: Phil Longstaff Cc: gnucash-devel@gnucash.org Sent: Wednesday, August 12, 2009 9:22:24 AM Subject: Re: Timestamps in Gnucash Mysql Phil Longstaff writes: > Also, sqlite does not have a datetime or timestamp but needs to use strings, > and I've been coding mainly for sqlite, with mysql and pgsql taking 2nd place. > > Gnucash has both dates, and date-times. For dates, I can use CHAR(8) for > sqlite, DATE for mysql and pgsql. For date-times, I can use CHAR(14) for > sqlite, TIMESTAMP for mysql and TIMESTAMP for pgsql. Question: should the > pgsql be "TIMESTAMP WITH TIME ZONE" or "TIMESTAMP WITHOUT TIME ZONE"? This opens up the whole can of worms regarding timestamps... IMHO the db should store timeout a timezone, and it should store everything in UTC. Then there is the issue of the engine and ui interpretting UTC (or not) as appropriate. Like I said, can of worms... > Phil -derek -- Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory Member, MIT Student Information Processing Board (SIPB) URL: http://web.mit.edu/warlord/PP-ASEL-IA N1NWH warl...@mit.eduPGP key available ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
MySQL sync
Hello, I was testing latest development version with MySQL backend and in general I was satisfied with the results. Great piece of software! Now here the scenario I want to happen: I have PHP scripts which deal with GnuCash data in MySQL DB. It is not a problem to read and show account records from MySQL. But PHP scripts should be able to post invoices back to GnuCash DB. This side of business is working well too but if I have GnuCash open and connected to the same MySQL DB no update in GnuCash happens. I've being looking for something what may get GnuCash to re-read records but I cannot find anything like it. Does MySQL backend has something like this? Perhaps having "last modified" in DB record would be good and GnuCash should check it periodically. Or the best way is to not to cache anything in memory and use MySQL for each single operation. Or I just dreaming? :) Generally it comes to concurrent use of the same DB by two separate GnuCash processes and how they get in sync. Without syncing use of DB does not gain much in relation to XML file. Regards, Vladimir PS: I have read warnings about modification of the data by something but GnuCash. Still it appears to be the best way to resolve my problem. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: MySQL sync
There is currently no support for simultaneous access to the db by more than just gnucash. There are no plans to add it unless someone wants to step up and work on it. Phil From: Vladimir Bashkirtsev To: gnucash-devel@gnucash.org Sent: Wednesday, August 12, 2009 1:26:28 PM Subject: MySQL sync Hello, I was testing latest development version with MySQL backend and in general I was satisfied with the results. Great piece of software! Now here the scenario I want to happen: I have PHP scripts which deal with GnuCash data in MySQL DB. It is not a problem to read and show account records from MySQL. But PHP scripts should be able to post invoices back to GnuCash DB. This side of business is working well too but if I have GnuCash open and connected to the same MySQL DB no update in GnuCash happens. I've being looking for something what may get GnuCash to re-read records but I cannot find anything like it. Does MySQL backend has something like this? Perhaps having "last modified" in DB record would be good and GnuCash should check it periodically. Or the best way is to not to cache anything in memory and use MySQL for each single operation. Or I just dreaming? :) Generally it comes to concurrent use of the same DB by two separate GnuCash processes and how they get in sync. Without syncing use of DB does not gain much in relation to XML file. Regards, Vladimir PS: I have read warnings about modification of the data by something but GnuCash. Still it appears to be the best way to resolve my problem. ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
2.3.3 saving to postgresql causes zombie scheduled transactions
I found (one of) the corruptions that I mentioned in my email of yesterday! I have an existing GC XML file that contains a scheduled transaction that had "repeats until" selected with a date of 28/02/08 (I'm using dd/mm/yy format). After saving to postgresql and restarting GC, the same transaction now has "forever" selected. "Since last run" now contains all the occurances of this transaction from 28/2/08 until now. I was running with --debug and the trace file is full of: * 06:38:47 WARN [gnc_iso8601_to_timespec_gmt()] mktime failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0 tm_isdst=-1 for string=1970-01-01 00:00:00 * 06:38:47 CRIT [gnc_iso8601_to_timespec_gmt()] unable to recover from buggy mktime Note that I'm in Australia - a timezone that's ahead of GMT and has daylight saving over the new year transition. That might be relevant. So it looks like there is a time conversion problem when saving to postgres. It causes scheduled transactions to come back from the dead. Should I put it into bugzilla? Graham ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
Re: 2.3.3 saving to postgresql causes zombie scheduled transactions
On August 12, 2009 04:53:29 pm Graham Menhennitt wrote: > I found (one of) the corruptions that I mentioned in my email of yesterday! > > I have an existing GC XML file that contains a scheduled transaction > that had "repeats until" selected with a date of 28/02/08 (I'm using > dd/mm/yy format). After saving to postgresql and restarting GC, the same > transaction now has "forever" selected. "Since last run" now contains > all the occurances of this transaction from 28/2/08 until now. > > I was running with --debug and the trace file is full of: > > * 06:38:47 WARN [gnc_iso8601_to_timespec_gmt()] mktime > failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0 > tm_isdst=-1 for string=1970-01-01 00:00:00 > * 06:38:47 CRIT [gnc_iso8601_to_timespec_gmt()] unable to > recover from buggy mktime > > Note that I'm in Australia - a timezone that's ahead of GMT and has > daylight saving over the new year transition. That might be relevant. > > So it looks like there is a time conversion problem when saving to > postgres. It causes scheduled transactions to come back from the dead. > > Should I put it into bugzilla? Yes. Please log to bugzilla. Phil ___ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
