Re: Upgrade error

["G. Net" <..hidden..>]

Hi

hmm...
there isnt a Pg-upgrade-2.6.18-2.6.19.sql file in the tar file

latest is Pg-upgrade-2.6.17-2.6.18.sql

?

actually I made a mistake earlier - the tar is 1.1.1d not 1.1.1c


----- Original Message ----
From: Chris Travers <..hidden..>
To: ..hidden..
Sent: Sunday, 12 November, 2006 12:16:39 AM
Subject: Re: [Ledger-smb-users] Upgrade error

Here is what we can do to make this minimally operational:

go into the sql/Pg-upgrade-2.6.18-2.6.19.sql file and copy everything
below the rules which add the items into transactions (i.e. the
custom_table_catalog definition and the like).

Basically run every command other than those create rules and insert
commands.  Then make sure that the line in Form.pm whcih sets
{dbversion} i set to 2.6.19.

Best Wishes,
Chris Travers

On 11/11/06, G. Net <..hidden..> wrote:
> Hi Chris
>
> thanks for reply and advice.
>
> tried changing Form.pm dbversion to be 1.0, 1.1, and 1.1.1
> got complaints about 'dataset being newer than version'
>
> changed  version field in defaults table and get this:
>
> DBD::Pg::st execute failed: ERROR:  relation "custom_table_catalog" does not exist
> DBD::Pg::st fetchrow_hashref failed: no statement executing
> Set-Cookie: LedgerSMB=1:7feaef159562a47db52afa545da044c2; path=/;
>
> I guess I'll be going back to Sl then  :-(
>
> cheers
>
>
> ----- Original Message ----
> From: Chris Travers <..hidden..>
> To: ..hidden..
> Sent: Saturday, 11 November, 2006 11:29:22 PM
> Subject: Re: [Ledger-smb-users] Upgrade error
>
> Hi;
>
> Thank you for your interest.
>
> The duplicate key issue is a serious one.  It probably means that you
> have duplicate uses of id's in your database.  THis is a *bad* thing
> and leads to some really dangerous join projection/data ambiguity
> issues.  In general, it it is better to fix ambiguity before upgrading
> than after.  LedgerSMB 1.0 and 1.1 are insufficiently changed to
> prevent SQL-Ledger from running on the same db, so if you update the
> version field in defaults, you shoudl be able to get back to a working
> version in short order  (or you can modify the line where
> $self->{dbversion} is set in Form.pm to match, which is actually
> probably a better idea all things considered.).
>
> I would also suggest holding off until 1.2.  That is going to be a
> somewhat more difficult migration and so we might as well do it all at
> once.  THis also gives us a little time to sort out the data issue
> before transforming ambigous data into potentially even more ambiguous
> data :-)
>
> I am going to send you an SQL query to track down the the bad id's and
> which tables they are in tonight.
>
> Best Wishes,
> Chris Travers
> On 11/11/06, G. Net <..hidden..> wrote:
> > Hi
> >
> > Have been watching the fork unfold with interest and decided to take the plunge and upgrade to 1.1.1c from SL 2.6.16 before I get left too far behind
> >
> > The following error message shows up after logging in and after the upgrade scripts run and I'd be grateful for any suggestions as to how to sort it:
> >
> >
> > Upgrading to Version 1.1.1c ...
> > Error!
> >
> > CREATE TRIGGER parts_short AFTER UPDATE ON parts
> > FOR EACH ROW EXECUTE PROCEDURE trigger_parts_short();
> > create table transactions (
> > id int PRIMARY KEY,
> > table_name text
> > );
> > insert into transactions (id, table_name) SELECT id, 'ap' FROM ap;
> > CREATE RULE ap_id_track_i AS ON insert TO ap
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ap');
> > CREATE RULE ap_id_track_u AS ON update TO ap
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > insert into transactions (id, table_name) SELECT id, 'ar' FROM ar;
> > CREATE RULE ar_id_track_i AS ON insert TO ar
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'ar');
> > CREATE RULE ar_id_track_u AS ON update TO ar
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'business' FROM business;
> > CREATE RULE business_id_track_i AS ON insert TO business
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'business');
> > CREATE RULE business_id_track_u AS ON update TO business
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'chart' FROM chart;
> > CREATE RULE chart_id_track_i AS ON insert TO chart
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'chart');
> > CREATE RULE chart_id_track_u AS ON update TO chart
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'customer' FROM customer;
> > CREATE RULE customer_id_track_i AS ON insert TO customer
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'customer');
> > CREATE RULE customer_id_track_u AS ON update TO customer
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'department' FROM department;
> > CREATE RULE department_id_track_i AS ON insert TO department
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'department');
> > CREATE RULE department_id_track_u AS ON update TO department
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'employee' FROM employee;
> > CREATE RULE employee_id_track_i AS ON insert TO employee
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
> > CREATE RULE employee_id_track_u AS ON update TO employee
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'gl' FROM gl;
> > CREATE RULE gl_id_track_i AS ON insert TO gl
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'gl');
> > CREATE RULE gl_id_track_u AS ON update TO gl
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'oe' FROM oe;
> > CREATE RULE oe_id_track_i AS ON insert TO oe
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'oe');
> > CREATE RULE oe_id_track_u AS ON update TO oe
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'parts' FROM parts;
> > CREATE RULE parts_id_track_i AS ON insert TO parts
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'parts');
> > CREATE RULE parts_id_track_u AS ON update TO parts
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'partsgroup' FROM partsgroup;
> > CREATE RULE partsgroup_id_track_i AS ON insert TO partsgroup
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'partsgroup');
> > CREATE RULE partsgroup_id_track_u AS ON update TO partsgroup
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'pricegroup' FROM pricegroup;
> > CREATE RULE pricegroup_id_track_i AS ON insert TO pricegroup
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'pricegroup');
> > CREATE RULE pricegroup_id_track_u AS ON update TO pricegroup
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'project' FROM project;
> > CREATE RULE project_id_track_i AS ON insert TO project
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'project');
> > CREATE RULE project_id_track_u AS ON update TO project
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'vendor' FROM vendor;
> > CREATE RULE vendor_id_track_i AS ON insert TO vendor
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'vendor');
> > CREATE RULE employee_id_track_u AS ON update TO vendor
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > INSERT INTO transactions (id, table_name) SELECT id, 'warehouse' FROM warehouse;
> > CREATE RULE warehouse_id_track_i AS ON insert TO warehouse
> > DO ALSO INSERT INTO transactions (id, table_name) VALUES (new.id, 'employee');
> > CREATE RULE warehouse_id_track_u AS ON update TO warehouse
> > DO ALSO UPDATE transactions SET id = new.id WHERE id = old.id;
> > CREATE TABLE custom_table_catalog (
> > table_id SERIAL PRIMARY KEY,
> > extends TEXT,
> > table_name TEXT
> > );
> > CREATE TABLE custom_field_catalog (
> > field_id SERIAL PRIMARY KEY,
> > table_id INT REFERENCES custom_table_catalog,
> > field_name TEXT
> > );
> > CREATE OR REPLACE FUNCTION add_custom_field (VARCHAR, VARCHAR, VARCHAR)
> > RETURNS BOOL AS
> > '
> > DECLARE
> > table_name ALIAS FOR $1;
> > new_field_name ALIAS FOR $2;
> > field_datatype ALIAS FOR $3;
> > BEGIN
> > EXECUTE ''SELECT TABLE_ID FROM custom_table_catalog
> > WHERE extends = '''''' || table_name || '''''' '';
> > IF NOT FOUND THEN
> > BEGIN
> > INSERT INTO custom_table_catalog (extends) VALUES (table_name);
> > EXECUTE ''CREATE TABLE custom_''||table_name ||
> > '' (row_id INT)'';
> > EXCEPTION WHEN duplicate_table THEN
> > -- do nothing
> > END;
> > END IF;
> > EXECUTE ''INSERT INTO custom_field_catalog (field_name, table_id)
> > VALUES ( '''''' || new_field_name ||'''''', (SELECT table_id FROM custom_table_catalog
> > WHERE extends = ''''''|| table_name || ''''''))'';
> > EXECUTE ''ALTER TABLE custom_''||table_name || '' ADD COLUMN ''
> > || new_field_name || '' '' || field_datatype;
> > RETURN TRUE;
> > END;
> > ' LANGUAGE PLPGSQL;
> >
> > ERROR: duplicate key violates unique constraint "transactions_pkey"
> >
> >
> >
> >
> >
> > -------------------------------------------------------------------------
> > Using Tomcat but need to do more? Need to support web services, security?
> > Get stuff done quickly with pre-integrated technology to make your job easier
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> > _______________________________________________
> > Ledger-smb-users mailing list
> > ..hidden..
> > https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
> >
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>
>
>
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> Ledger-smb-users mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-users
>

-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Ledger-smb-users mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-users