2014-10-13 13:56 GMT+03:00 Chris Travers <chris.trav...@gmail.com>: > > > On Mon, Oct 13, 2014 at 3:45 AM, Kai Huuhko <kai.huu...@gmail.com> wrote: >> >> 2014-10-13 12:01 GMT+03:00 Chris Travers <chris.trav...@gmail.com>: >> > >> > >> > On Mon, Oct 13, 2014 at 1:20 AM, Kai Huuhko <kai.huu...@gmail.com> >> > 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 >> >> Ledger-smb-users@lists.sourceforge.net >> >> 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 >> > Ledger-smb-users@lists.sourceforge.net >> > 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 >> Ledger-smb-users@lists.sourceforge.net >> 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 > Ledger-smb-users@lists.sourceforge.net > 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 Ledger-smb-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
