Re: question about db structure re: fixing potential corruption

[Chris Travers <..hidden..>]

On Sun, Jun 26, 2011 at 1:39 PM, brush <..hidden..> wrote:
> 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?

The first thing you need to do is figure out what these lines
represent.  you can then determine from there whether to delete the
lines or create ar/ap/gl entries.

If in doubt, I would create gl transactions in order to preserve the
info for future review.
>
> 2) any ideas how this may have happened in the first place?  practices to
> avoid?

I haven't seen this often enough to come up with a pattern that likely
matches what you are seeing.  I will say that if this occurs in future
versions it will be a lot clearer what happened than it is with
past/current versions.
>
>> 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.
>
Best Wishes,
Chris Travers