Chris Travers wrote: > > > On Thu, Jan 7, 2010 at 4:35 PM, Alvin Starr <[email protected] > <mailto:[email protected]>> 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. I was hoping that the answer would be "just run this little obscure script that is completley undocumented" and life would be good. I got the feeling that I may be in for an ugly walk through the database and trying to figure out what I need to fix.
> > 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. > The database dump is 115K lines. There is about 6 years of data in the system. I would argue that all database driven applications should have a simple flat file export/import that dis-entangles the funny database issues on export and then tangles them back up on import and does an intregity check. It also means that you can make huge database structure changes and not have to restrict changes to the level of what can be fixed with a few sql statements. This means that dump/restore as part of an upgrade insures that you start with a clean database at the end of the upgrade. Upgrading a database in place is cute but just seems like an accident waiting to happen if you don't have some integrity checking tools. Depending on the cost I could possibly see my way clear to fund the development of an integrity checker or dump/restore tool. > 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 > [email protected] <mailto:[email protected]> || > > > > > ------------------------------------------------------------------------------ > 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 > [email protected] > <mailto:[email protected]> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------------ > 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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users > -- Alvin Starr || voice: (416)585-9971x690 Interlink Connectivity || fax: (416)585-9974 [email protected] || ------------------------------------------------------------------------------ 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 [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
