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

[Chris Travers <..hidden..>]

On Fri, Oct 7, 2011 at 5:47 AM,  <..hidden..> wrote:
>
>
> 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 ! :-)

I am intentionally jetlagged at the moment,. sleeping 4 hours in the
afternoon and 4 at night.  I will probably keep this up for a week,
before deciding to revert totally to local time (Jakarta, Indonesia).
>
> 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.

I think I am going to leave this last issue out of hte automatic error
detection scripts.  It's probably a good one for people to get some
help with (either paid support or a second set of eyes on the lists,
etc).  Thanks for telling me about the history of this db.  It's not
surprising we are finding some areas where there is orphaned data etc.
 Identifying all these issues though is very helpful to the project.

Best Wishes,
Chris travers

Best wishes,
Chris Travers