SQL Woes on 1.6 II: The Quest for Currency

[Linas Vepstas]

On Thu, Jul 26, 2001 at 12:22:43AM -0400, Michael T. Garrison Stuber was heard to remark:
> Ah the joys of beta testing . . .
> 
> My many thanks to Linas for updating the SQL backend.  I synced to head on 
> 1.6, rebuilt GNUCash, and installed.  (FWIW, I always do a fully clean 
> build if I'm going to do an install).  And then I fired it up.  Here is 
> where things got interesting . . .
> 
> I started by opening my XML file, and saving into the database I had been 
> using.  That worked reasonably okay, but I got an error saying that the DB 
> was in use by another user (it wasn't). 

You'll get this if some previous instance of gnucash crashed without logging
out. 
select session_mode,login_name,gecos,time_on,time_off from gncsession;

single-update mode requires all other sesssions to be logged off.

> So I figure it was time for the 
> real test.  I hit save, and it didn't do all the stuff it was doing before 
> (I'd be more precise but I'm tired) so Linas's fix is at least doing part 
> of what it should do.  Then I hit exit.  It takes it a bit, but it's an 
> improvement over how it had been running.  (Yeah) And then I try to reopen 
> GNUCash and things really get interesting . . .
> 
> I get this error saying:
> Warning: xaccTransSetCurrency: Transaction currency seems to be 
> inconsistent.
>         defacto common=(null), asked to set ISO4217::USD
> repeating again, and again, and again. (It seems to repeat for almost as 
> many accounts as I have).

I haven't seen this, but then I have ben doing all y work in the 1.7 tree,
and backporting to 1.6.  I guess I haven't tested in 1.6 sufficiently.

> And low and behold almost all of my balances are gone, except for the stock 
> accounts.

This is typical broken-ness associated with the multi-user mode.  I'm
not sure what made it break.  Usually editing a transaction causes the
balances to reappear. I've only seen this in 1.7, not 1.6.

> So I figure, "well maybe it's just a corrupt DB, and I'll start over again"
> 
> So I reload my XML file and save it to a new DB.  I get lots of wierd 
> errors.  It's at this point I realize that either GNUCash or Postgres gets 
> confused with mixed case database names.  (Did I miss this in the 
> documentation?  There should probably be some handling for this -- it's not 
> like a user won't try it.)  

I think its postgres. gnucash doesn't try anything funny with case.

> So I try all over again with yet another (and 
> this time all lowercase) DB name.  I save my data into the database. 
> Everything looks beautiful.  I exit and reload from the database, and poof! 
> (yes, that's a technical term) I'm getting the currency errors and my 
> balances are non-existent.
> 
> I recall (ever so dimly) from the list some discussion of a second currency 
> type being removed (added?) to the split.  Is it possible that this change 
> was inadvertently introduced/removed from 1.6?

The second currency type was removed from the account structure in 1.7.  In
anticipation of this change, the sql backend never stored it to begin with.
(we've been planning this since before 1.4  ...)

> Also, I am consistently getting the "Can't get lock DB is in use" error 
> from GNUCash, even though the DB isn't in use.  This is happening whenever 
> I run in single-update mode, regardless of the database.

when gnucash gets an sql error, (e.g. due to its own bug, like sending bad 
sql syntax), it assumes the worst, and closes the connection to the server
without logging out. Worse, it may due this silently (due to limitations in
the current error reporting infrastructure.).  

> Anyway, if somebody's got the bandwidth to fix this, I'll be happy to beat 
> on it some more.  Thanks to all.

I'll look into it.  I'm hoping its something easy.  After all, it used to work ...

--linas


-- 
I'm very PUBLIC-MINDED, I'm helping a NIGERIAN get his $25,000,000 back!