On Thu, Jan 7, 2010 at 4:35 PM, Alvin Starr
<..hidden..> wrote:
I started using sql-ledger about 5 years ago and my last upgrade was 2.6.19
This was partly due to the fact that I was going to move to ledger-smb
one of these days.
Well the day came and I upgraded through to ledgersmb 1.2.18.
Everything seemed ok till we started getting the following errors while
posting a payment to an invoice.
====================
*DBD::Pg::st execute failed: ERROR: duplicate key violates unique
constraint "transactions_pkey" at LedgerSMB/IS.pm line 862. *
Error!
*INSERT INTO ar (id, customer_id) VALUES (?, ?)
ERROR: duplicate key violates unique constraint "transactions_pkey"*
====================
This error seems to delete the invoice from the AR.
A little googling and I found some comments about this being a
referential integrity problem.
The question is how do I fix it?
Ok, let's start by explaining the problem. The short version is that if the problem description doesn't provide for you enough information for you to solve it you would probably be best off with professional tech support.
SQL-Ledger (and hence LedgerSMB through 1.3.x) uses some rather problematic join conditions regarding financial information because of the re-use of the id sequence as a sort of virtual primary key across tables.
acc_trans.trans_id joins (select id from ar union select id from ap union select id from gl)
Worse, shipto.trans_id joins (select id from ar union select id from ap union select id from customer union select id from vendor)
If two tables re-use the same id, then you any of have the following problems:
acc_trans records will join multiple times, throwing your financial reports off.
shipto addresses may become confused between invoices, customers, and vendors.
In 1.2 we try to prevent this problem by storing ID's in the transactions table, which then can be joined against by these other tables (though that part doesn't work yet). There is a bug with this check that is corrected in the 1.3 betas, and the fix works well enough to prevent nearly all the problems or at least flag small problems before they become big problems. There is an optional backport of the 1.3 fix available for 1.2 as well.
However fixing the problem basically requires going through the relevant tables and untangling the data. The tables involved are acc_trans, shipto, ar, ap, gl, customer, vendor, dpt_trans and determining which records go where, updating id's and so forth.
Hope this helps.
Chris Travers
Are there any tools to dump and restore the whole ledgersmb application
without dumping the raw database?
P.S.
I signed up to the help mail list and it seems to be broken.
--
Alvin Starr || voice: (416)585-9971x690
Interlink Connectivity || fax: (416)585-9974
..hidden.. ||
------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev
_______________________________________________
Ledger-smb-users mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users