Re: question about db structure re: fixing potential corruption

[Chris Travers <..hidden..>]

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?

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);

If that takes too long to run it can be rewritten as:

SELECT entry_id FROM acc_trans LEFT JOIN (select id from ar union
select id from ap union select id from gl) gl ON  gl.id =
acc_trans.trans_id WHERE gl.id is null;

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

Best Wishes,
Chris Travers