Re: Upgrade error

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

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