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

Re: upgrades and database integrity.

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
    > if the problem description doesn't provide for you enough
    > 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
    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

    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
    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

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