Re: question about db structure re: fixing potential corruption

[brush <..hidden..>]

thanks so much, chris.  this is phenomenally timely and helpful!

further discussion inline.

On Sun, Jun 26, 2011 at 06:32:58AM -0700, Chris Travers wrote:
> On Sun, Jun 26, 2011 at 5:10 AM, brush <..hidden..> wrote:
> > hi folks,
> >
> > i've run into the first signs of potential DB corruption.  so far, it's showing
> > up as a discrepancy between what trial balance shows on the first page of
> > by-account totals when selected w/o any dates, vs. what it shows when you drill
> > down into the account.
> >
> > select sum(amount) from acc_trans where chart_id = X agrees with the former
> > total.
> >
> > my initial thought is that this is caused by entries in acc_trans that don't
> > have an entry in transactions table, and so perhaps aren't displayed in the
> > drilldown display.  but this seems not to be correct.
> >
> > can anyone with more experience suggest what the difference is between what
> > tables are being accessing in the two levels of trials balance that would
> > indicate what might be missing from what table?
> 
> A couple questions:
> 
> 1)  What version of LedgerSMB are you running?  Was it continuously
> upgraded to this version or was it a new installation in the current
> stable branch?

currently lsmb 1.2.22, upgraded over time since SL 2.4 or something back in
2005.

> 
> 2) The transactions table tracking is a litlte problematic in 1.2.  It
> accomplishes its primary task (of preventing id collisions) well
> enough, but is not useful for RI checking.
> 
> To check this what you really need to do is:
> 
> SELECT entry_id FROM acc_trans WHERE trans_id NOT IN (select id from
> ar union select id from ap union select id from gl);

this worked perfectly; i found the problematic entries and a couple others i
hadn't noticed.  two main questions:

1) what's the best workflow to fix the DB?  clearly, we need AP/AR/GL entries
to match the acc_trans ones.  it seems i could: 

  a) create new AR/AP/GL entries in LSMB, then delete the orphan acc_trans
entries in postgres 
  b) try to create new AR/AP/GL entries to match the acc_trans, in postgres.

as i write, seems like a) makes the most sense.  yes?

2) any ideas how this may have happened in the first place?  practices to
avoid?

> That's the first place to look.  However, if that doesn't work,. I'd
> check for ar/ap records against non-existant customers.

no, these are all fine.

thanks so much!
.b