[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: First upgrade attempt from 1.2.21 -> 1.3.0RC4 == Fail





On Thu, 6 Oct 2011, Chris Travers wrote:

INSERT INTO invoice (id, trans_id, parts_id, description, qty, allocated,
            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
            deliverydate, serialnumber, notes)
    SELECT  id, trans_id, parts_id, description, qty, allocated,
            sellprice, fxsellprice, discount, assemblyitem, unit, project_id,
            deliverydate, serialnumber, notes
       FROM lsmb12.invoice;


psql:sql/upgrade/1.2-1.3-manual.sql:474: ERROR:  insert or update on table
"invoice" violates foreign key constraint "invoice_trans_id_fkey"
DETAIL:  Key (trans_id)=(16796) is not present in table "transactions".

Which I can't quite figure out since it seems transactions is empty.

Checking this out.  I think this is done in a transaction so it would
roll back.  But check to see if there is an RI issue here:

SELECT trans_id FROM invoice WHERE trans_id NOT IN (select id in ar
union select id from ap);

Best Wishes,
Chris Travers

Good God man do you ever sleep ! :-)

Changing the query slightly and running against my production 1.2 produces:

lsmbprod=# SELECT trans_id FROM invoice WHERE trans_id NOT IN (select id from ar union select id from ap);
 trans_id
----------
    16796
    16796
    16796
    16796
(4 rows)

There are invoice.id 16755, 16756, 16757, 16758 from an invoice from 2007-01-05 which looks like it was a duplicate that was cleaned up-- somehow.

ap.id 16798 looks like it is the invoice that 16796 *should* have been.

It looks like I could 'delete from invoice where trans_id = 16796;' if there would be no further ramifications.

Did I mention this was SQL-Ledger 1.6 which made it to SL 2.4.x before moving to LedgerSMB? This may be one of the oldest datasets trying to purely upgrade. It was in Quickbooks before that and the oldest ap invoice is from 1997.



Grateful for your help,

Louis