Forum OpenACS Development: Re: 5.0 upgrade process requires Tcl Calls

Collapse
28: Re: 5.0 upgrade process requires Tcl Calls (response to 27)
Posted by Jeff Davis on 12/16/03 02:19 AM
Bug 1294 looks like it might be pretty ugly to fix.

Basic summary is that you don't seem to be able to manipulate named constraints on a db migrated from 7.2 which means a number of the upgrade scripts will fail.

The right answer would seem to be to put properly named constraints in place but that might be pretty hard to do. Fortunately I am leaving on vacation and can't help :)

Collapse
29: Re: 5.0 upgrade process requires Tcl Calls (response to 28)
Posted by Joel Aufrecht on 12/31/03 02:19 AM
Progress report: I have upgraded a 4.6.0 database to 4.6.3 and from PG 7.2 to 7.3, apparently successfully.  I am now trying to upgrade to 5.0, following Caroline's path in this thread.
1) It's fairly unpleasant to have to run each of the ~20 upgrade files manually.  How will we know when this is stable enough to run automatically from the APM?

2) What is the correct order of upgrade scripts to run for 4.6.3 to 5.0?  Do we need any of the 4.6.4 through 4.7 files?

upgrade-4.2-4.5.sql
upgrade-4.5.1-4.5.2.sql
upgrade-4.5.1-4.6.sql
upgrade-4.5-4.5.1.sql
upgrade-4.6.1-4.6.2.sql
upgrade-4.6.1-4.7d.sql
upgrade-4.6.2-4.6.3.sql
upgrade-4.6.3-4.6.4.sql
upgrade-4.6.4-4.6.5.sql
upgrade-4.6-4.6.1.sql
upgrade-4.6.5-4.6.6.sql
upgrade-4.7.2d-5.0d.sql
upgrade-4.7d-4.7.2d.sql
upgrade-5.0.0a4-5.0.0a5.sql
upgrade-5.0.0b1-5.0.0b2.sql
upgrade-5.0.0b2-5.0.0b3.sql
upgrade-5.0.0b3-5.0.0b4.sql
upgrade-5.0d11-5.0d12.sql
upgrade-5.0d2-5.0d3.sql
upgrade-5.0d-5.0d2.sql
upgrade-5.0d6-5.0d7.sql
upgrade-5.0d7-5.0d9.sql

3) I ran

psql -f upgrade-4.6.3-4.6.4.sql
psql -f upgrade-4.6.4-4.6.5.sql
psql -f upgrade-4.6.5-4.6.6.sql

The first two worked fine but the third hung until I hit control-c:

psql -f upgrade-4.6.5-4.6.6.sql aufrecht-dev
UPDATE 1
psql:upgrade-4.6.5-4.6.6.sql:11: ERROR:  users_user_id_fk referential integrity violation - key referenced from users not found in persons

Cancel request sent
psql:upgrade-4.6.5-4.6.6.sql:12: WARNING:  Error occurred while executing PL/pgSQL function acs_objects_context_id_up_tr
psql:upgrade-4.6.5-4.6.6.sql:12: WARNING:  line 11 at SQL statement
psql:upgrade-4.6.5-4.6.6.sql:12: ERROR:  Query was cancelled.

Collapse
30: Re: 5.0 upgrade process requires Tcl Calls (response to 29)
Posted by Lars Pind on 01/02/04 02:44 PM
Re the 4.6.>4 / 4.7.* upgrade scripts:

These version numbers are not directly related to releases. For example, I'm not certain that the 4.6.3 release actually has an acs-kernel version 4.6.3, it might have been 4.6.4 or 4.6.5.

As for the 4.7 series, this was the original name HEAD, until we decided to rename it 5.0 instead, so yes, I'm pretty sure you need all of those as well.

Of course, thanks to branches, there's no obvious way to tell what the order should be for the 4.6.* and 4.7.* upgrade scripts, or whether they're all needed. We'll have to manually inspect each one of them. And cvs.openacs.org is currently down.

/Lars

Collapse
32: Re: 5.0 upgrade process requires Tcl Calls (response to 30)
Posted by Joel Aufrecht on 01/11/04 01:08 PM
People who have successfully upgraded: exactly which order of acs-kernel upgrade scripts did you use?
Collapse
33: Re: 5.0 upgrade process requires Tcl Calls (response to 32)
Posted by Vinod Kurup on 01/11/04 06:36 PM
These are the calls I ran (verbatim). 
cd
pg_dump kurup > mydb.dmp
dropdb dev-kurup
createdb dev-kurup
psql -f mydb.dmp dev-kurup
cd web/dev-kurup/packages/acs-kernel/sql/postgresql/upgrade/
psql dev-kurup < upgrade-4.6.4-4.6.5.sql 
psql dev-kurup < upgrade-4.6.5-4.6.6.sql 
psql dev-kurup < upgrade-4.7d-4.7.2d.sql 
psql dev-kurup < upgrade-4.7.2d-5.0d.sql 
psql dev-kurup < upgrade-5.0d-5.0d2.sql 
psql dev-kurup < upgrade-5.0d2-5.0d3.sql 
psql dev-kurup < upgrade-5.0d6-5.0d7.sql 
psql dev-kurup < upgrade-5.0d7-5.0d9.sql 
psql dev-kurup < upgrade-5.0d11-5.0d12.sql 
psql dev-kurup < upgrade-5.0.0a4-5.0.0a5.sql 
psql dev-kurup < upgrade-5.0.0b1-5.0.0b2.sql 
psql dev-kurup < upgrade-5.0.0b2-5.0.0b3.sql 
psql dev-kurup < upgrade-5.0.0b3-5.0.0b4.sql 
cd ../../../../acs-service-contract/sql/postgresql/upgrade/
psql dev-kurup < upgrade-4.7d2-4.7d3.sql 
cd ../../../../acs-authentication/sql/postgresql/
psql dev-kurup < acs-authentication-create.sql 
cd ../../../acs-lang/sql/postgresql/
psql dev-kurup < acs-lang-create.sql 
dev-kurup-start.sh
Collapse
34: Re: 5.0 upgrade process requires Tcl Calls (response to 33)
Posted by Joel Aufrecht on 01/11/04 10:27 PM
Did you run upgrade-4.6.3-4.6.4.sql?

My upgrade is currently failing on the second command in, upgrade-4.6.4-4.6.5.sql: 

insert into users (user_id) values (0);
ERROR:  users_user_id_fk referential integrity violation - key referenced from users not found in persons
aufrecht-dev=# select * from users where user_id=0;
 user_id | password | salt | screen_name | priv_name | priv_email | email_verified_p | email_bouncing_p | no_alerts_until | last_visit | second_to_last_visit | n_sessions | password_question | password_answer | password_changed_date 
---------+----------+------+-------------+-----------+------------+------------------+------------------+-----------------+------------+----------------------+------------+-------------------+-----------------+-----------------------
(0 rows)

aufrecht-dev=# select * from persons where person_id=0;
 person_id | first_names | last_name 
-----------+-------------+-----------
(0 rows)

aufrecht-dev=# insert into persons values (0,'Unregistered','Visitor');
ERROR:  persons_person_id_fk referential integrity violation - key referenced from persons not found in parties
aufrecht-dev=#
At this point it looks like some of the stuff in upgrade-4.6-4.6.1.sql didn't get run on my database, so I guess I'll start over at that point and watch more closely.
Collapse
35: Re: 5.0 upgrade process requires Tcl Calls (response to 34)
Posted by Vinod Kurup on 01/11/04 10:51 PM
My site was already around 4.6.4, so I didn't run the 4.6.3-4.6.4 upgrade script.

I just loaded up my before-upgrade DB and got this: 

testkurup=# select * from persons where person_id=0;
 person_id | first_names  | last_name
-----------+--------------+-----------
         0 | Unregistered | Visitor
(1 row)
 
testkurup=# select * from parties where party_id=0;
 party_id | email | url
----------+-------+-----
        0 |       |
(1 row)
So, I think you're right - you need to start around 4.6-4.6.1 where the party with party_id 0 gets created.
Collapse
37: Re: 5.0 upgrade process requires Tcl Calls (response to 35)
Posted by Vinod Kurup on 01/16/04 01:39 AM
Just found a problem with the upgrade. apm_package_version__delete and apm_package_version__copy were changed around 5.0d9, but the changes didn't get into an upgrade script. I've committed the changes to the upgrade script on the 5.0 branch, but if you've already upgraded, you need to 'create or replace' those 2 functions.
Collapse
38: Re: 5.0 upgrade process requires Tcl Calls (response to 37)
Posted by Vinod Kurup on 01/16/04 03:05 AM
Another problem: ref_timezones is now required by acs_lang, but didn't get installed on my system, so this bug shows up. acs_lang gets installed in the special zzz-postload.tcl script: 
if {![apm_package_installed_p acs-lang]} {

    apm_package_install -enable -mount_path acs-lang [acs_root_dir]/packages/acs-lang/acs-lang.info
 
    lang::catalog::import -locales [list "en_US"]

}
But apparently, apm_package_install doesn't automatically resolve dependencies, so ref-timezones (and acs-reference) never got installed. Should we add 2 more checks to zzz-postload.tcl, checking for and installing acs-reference and ref-timezones if needed? or is there a better way to automatically install required dependencies?
Collapse
39: Re: 5.0 upgrade process requires Tcl Calls (response to 38)
Posted by Vinod Kurup on 01/16/04 04:40 AM
Sorry to flood this thread, but found another one. Running the auth_driver_get_param_values test (in acs-authentication) fails with this error: 
ERROR:  duplicate key violates unique constraint "auth_driver_params_authority_id_key"
 
SQL:             
            insert into auth_driver_params (authority_id, impl_id, key, value)
            values ('12797', '12891', 'SnapshotURL', '0639DC91D')
My auth_driver_params table has a unique constraint on (authority_id, impl_id) whereas the version in 5.0 has a pk constraint on (authority_id, impl_id, key). The change is here. I only discovered this error by running the automated test - the system works fine otherwise.

I think this change happened between 5.0d4 and 5.0d5, but there's no upgrade script with that name. Here's the change that needs to be made: 

alter table auth_driver_params drop constraint auth_driver_params_authority_id_key;
alter table auth_driver_params add constraint auth_driver_params_pk primary key (authority_id,impl_id,key)
Is it OK for me to create an upgrade-5.0d4-5.0d5 script?