Re: Upgrading lsmb-1.2 database

[Kai Huuhko <..hidden..>]

2014-10-13 13:56 GMT+03:00 Chris Travers <..hidden..>:
>
>
> On Mon, Oct 13, 2014 at 3:45 AM, Kai Huuhko <..hidden..> wrote:
>>
>> 2014-10-13 12:01 GMT+03:00 Chris Travers <..hidden..>:
>> >
>> >
>> > On Mon, Oct 13, 2014 at 1:20 AM, Kai Huuhko <..hidden..>
>> > wrote:
>> >>
>> >> Hi
>> >>
>> >> I'm attempting to upgrade an old database backup that was created back
>> >> in 2011 using ledgersmb-1.2.
>> >>
>> >> The pg_restore process completed successfully with just one trivial
>> >> error: could not execute query: ERROR:  language "plpgsql" already
>> >> exists
>> >
>> >
>> > Yeah that error is harmless and has more to do with the PostgreSQL
>> > version
>> > than anything else.
>> >>
>> >>
>> >> Trying to run the upgrade process using lsmb-1.4.4 it errored out
>> >> saying I should check the logs in /etc/ledgersmb but I have no such
>> >> directory.
>> >>
>> >> With lsmb-1.3.44 the upgrade process completes but with 0 row counts
>> >> for all but users (1) and vendors (2). I get no actual data in the
>> >> company, even the chart of accounts is empty.
>> >
>> >
>> > Ok, there is a sql/upgrade/1.3-1.2.sql which will restore your data to
>> > the
>> > pre-upgrade state.  The 1.2 data is still in yoru database as 1.2 data,
>> > but
>> > has been moved out of the way. Our upgrades are guaranteed
>> > non-destructive,
>> > but we could do a little more to make recovery easy.  At any rate,
>> > before
>> > you proceed, you will want to run that script.
>> >
>> >
>> > This problem happens when the upgrade fails (and it does so
>> > transactionally).  Your old 1.2 data plus some mapping information is in
>> > the
>> > lsmb12 schema.  The 1.3-1.2.sql deletes the mapping information and
>> > moves
>> > the data back to where it is accessible to LedgerSMB 1.2 (or for the
>> > upgrade).
>> >>
>> >>
>> >> Attached is a syslog dump of entries logged by postgresql during the
>> >> upgrade process using lsmb-1.3.44.
>> >>
>> >> Can anyone help me get the database upgraded completely?
>> >
>> >
>> > Your issue is found in your logs here:
>> >
>> > loka 13 08:09:25 minilith.hima postgres[10339]: [3-1] ERROR:  insert or
>> > update on table "acc_trans" violates foreign key constraint
>> > "acc_trans_trans_id_fkey"
>> >
>> > loka 13 08:09:25 minilith.hima postgres[10339]: [3-2] DETAIL:  Key
>> > (trans_id)=(11188) is not present in table "transactions".
>> >
>> > There are a couple possible causes here.  This could be old data
>> > corruption,
>> > or it could be an issue with the transactions table being incomplete.
>> >
>> > Once you run the downgrade script, try this:
>> >
>> > SELECT count(*) FROM ar WHERE id = 11188;
>> > SELECT count(*) FROM ap WHERE id = 11188;
>> > SELECT count(*) FROM gl WHERE id = 11188;
>> >
>> > Based upon the results of those queries we can decide what to do.
>>
>> Each query returned zero count.
>
>
> Ok, this is telling me that you have dangling rows in acc_trans, which don;t
> correspond to any transactions.
>
> You may want to look through them first and see if you can tell what they
> are.  The best way to do that is to use a query similar to this one:
>
>
> select a.accno, a.description, ac.amount, ac.transdate, ac.source, ac.memo
>  from account a
> join acc_trans ac on a.id = ac.chart_id
> where ac.trans_id not in (
> select id from ar union select id from ap union select id from gl
> );
>
> Then you can archive them out of acc_trans, or deal with them, and then
> delete them from acc_trans.  I usually like to copy them somewhere else
> first so one can audit and deal with them later if necessary.

Found them, apparently a transaction had become duplicated without any
corresponding entries in ar, ap or gl. Data looks good now.

Thanks for the help!

>>
>>
>> >
>> >>
>> >>
>> >>
>> >> ------------------------------------------------------------------------------
>> >> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>> >> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS
>> >> Reports
>> >> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
>> >> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>> >> http://p.sf.net/sfu/Zoho
>> >> _______________________________________________
>> >> Ledger-smb-users mailing list
>> >> ..hidden..
>> >> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>> >>
>> >
>> >
>> >
>> > --
>> > Best Wishes,
>> > Chris Travers
>> >
>> > Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
>> > lock-in.
>> > http://www.efficito.com/learn_more
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>> > Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
>> > Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
>> > Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>> > http://p.sf.net/sfu/Zoho
>> > _______________________________________________
>> > Ledger-smb-users mailing list
>> > ..hidden..
>> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
>> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
>> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
>> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
>> http://p.sf.net/sfu/Zoho
>> _______________________________________________
>> Ledger-smb-users mailing list
>> ..hidden..
>> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>
>
>
>
> --
> Best Wishes,
> Chris Travers
>
> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
> lock-in.
> http://www.efficito.com/learn_more
>
> ------------------------------------------------------------------------------
> Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
> Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
> Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
> Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
> http://p.sf.net/sfu/Zoho
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>

------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://p.sf.net/sfu/Zoho
_______________________________________________
Ledger-smb-users mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users