Re: upgrades and database integrity.

[Alvin Starr <..hidden..>]

Chris Travers wrote:
> On Fri, Jan 8, 2010 at 12:52 PM, Alvin Starr <..hidden.. 
> <mailto:..hidden..>> wrote:
>
>
>     >
>     >
>     >
>     > 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.
>
>
> If you need more detailed information, feel free to ask.  I would 
> highly recommend backing everything up before starting.

Lets back up a bit.
I may be doing something stupid that worked in previous versions.
When we have a payment on an existing invoice we apply the payment and 
update as necessary and then post the transaction.
This triggers the "posting an existing transaction" warning.
I have never been quite comfortable with igoring warnings but this 
seemed to be the only way to get this to work.

Now clicking post on existing transactions causes  *LedgerSMB/IS.pm line 
862 to kick out an error.
this is in the post_invoice routine.
This routine appears to delete and then recreate the invoice.
The recreation appears to fail due to  the CREATE RULE ar_id_track_i AS 
ON INSERT TO ar DO INSERT INTO transactions (id, table_name) VALUES 
(new.id, 'ar'::text);

If I post an existing invoice and I get the error the old invoice will 
be deleted.
If I backup and repost the invoice as new the error will not appear and 
I will have a new invoice with the same values as the old.
If I then repost that invoice I will not get the error.
But If I repost that invoice again I will get the same error as before.

I have tried verious permutations and it seems like every second posting 
will fail.

I am thinking that this problem is not related to the customer/vendor 
list issues described before.


*
>     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.
>
>
> Well, if that was supported it wouldn't help in this case.  The issue 
> is that SQL-Ledger (and hence earlier versions of LedgerSMB) have 
> inadequate constraints and consequently you get really nasty issues 
> with data ambiguity.  At least when manually trying to fix it you can 
> identify the duplicate id's relatively quickly.  If trying to 
> automatically disentangle I would be concerned about automating fixes 
> that don't fix the problem but make it harder to track down.
A validity checker is a good thing in either case.
You are right once the database is buggered you can get into trouble if 
you just try an automatic fix but when you run fsck on a broken file 
system do you not usually just use "fsck -y" you could go in and patch 
the i-nodes but more often the answer is to track the errors and then go 
back and replace the files that are buggered.

I would argue that all non-trivial database applications should have an 
fsck equivalent utility and a tar/dump equivalent utility.
Both of those programs have saved my ass more times than I care to remember.

> Best Wishes,
> Chris Travers
>
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> 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
>   


--
Alvin Starr                   ||   voice: (416)585-9971x690
Interlink Connectivity        ||   fax:   (416)585-9974
..hidden..              ||