Forum OpenACS Q&A: Problem upgrading oacs-5.8 to oacs-5.9

After upgrading a 5.8 instance with several custom packages to 5.9 I found out that almost all my custom views joining to acs_objects had disappeared and I tracked down the problem to the kernel upgrade script upgrade-5.9.0d4-5.9.0d5.sql.

The script drops the tree_sortkey and max_child_sortkey columns from acs_objects and, given the use of 'cascade', also all the views making use of the two columns. I understand that this is necessary, but having some dozens of views to rebuild it's also quite annoying.

In my case I wrote a small before-upgrade script (luckily all my views share the same prefix) extracting the view definitions from pg_views, removing via regsub the two columns and saving them to a file that I then feeded to psql after the upgrade.

The trick worked except a single view calling the old tree_level function. I had to create a new orresponding function with a recursive query and I post the code here, in case it can be useful to anybody.

CREATE OR REPLACE FUNCTION tree_level(p_object_id integer)
  RETURNS integer AS $sql$                                                 
   result  integer := null;                                                       

     with recursive objects as ( 
        select i.object_id, i.context_id
        from acs_objects i
        where i.object_id = p_object_id
        select p.object_id, p.context_id
        from acs_objects p, objects s
        where p.object_id = s.context_id 
	select count(*) into result
	from objects;

    return result;

LANGUAGE 'plpgsql';
Posted by Gustaf Neumann on
yes, when custom views refer to dropped fields, custom upgrade scripts are needed to regenerate these views. This happens easily with wild-card views.

Many thanks for the constructive report!

Posted by Claudio Pasolini on
I'm having trouble upgrading another 5.8 instance to oacs-5-9-1.

I installed Naviserver, PostgreSQL etcetera using the latest script on a fresh Ubuntu 16.04.

First of all I upgraded the instance to the latest oacs-5-8-1, restarted the instance and verified that there were no errors in the error.log. Then I copied the 5.9.1 core packages over the 5.8.1 ones and tried to upgrade locally, but executing /acs-admin/apm/packages-install?operation=upgrade I got the following error:

no such key: word
    while executing
"nsv_get ad_page_contract_filters $filter"
    (procedure "ad_page_contract_filter_priority" line 2)
    invoked from within
"ad_page_contract_filter_priority $flag"
    (procedure "ad_page_contract" line 133)

It seems that ad_page_contract is broken. I then restarted the instance and retried but got the following from /acs-admin/install:

invalid command name "::xo::db::sql::site_node"
    while executing
"::xo::db::sql::site_node node_id -url /"
    invoked from within
"next "
    invoked from within
"ns_cache_eval xo_site_nodes id-$url { next }"
    (procedure ":get_node_id" line 2)
    invoked from within
":get_node_id -url $url"
    (procedure "get" line 10)
    invoked from within
"::xo::site_node get -url $url -node_id $node_id"
    (procedure "::nsf::procs::site_node::get" line 2)
    invoked from within
"site_node::get -url $url"
    (procedure "::nsf::procs::site_node::closest_ancestor_package" line 14)
    invoked from within
"site_node::closest_ancestor_package  -node_id [ad_conn node_id]  -package_key [subsite::package_keys]  -include_self  -element "node_id""
    (procedure "ad_conn" line 131)
    invoked from within
"ad_conn subsite_node_id"
    (procedure "ad_conn" line 135)
    invoked from within
"ad_conn subsite_id"
    (procedure "::nsf::procs::template::resource_path" line 8)
    invoked from within
"template::resource_path -type templates -style $style -relative"
    (procedure "template::themed_template" line 7)
    invoked from within
"template::themed_template [file rootname $relative_name]"
    (procedure "adp_parse_ad_conn_file" line 13)
    invoked from within
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
            } ad_script_abort val {
                # do nothing
    invoked from within
"rp_serve_concrete_file [ad_conn file]"
    (procedure "::nsf::procs::rp_serve_abstract_file" line 60)
    invoked from within

Something must be changed from the last time I upgraded.

Posted by Gustaf Neumann on

hmm, i tried the following:

1) Created a fresh db

sudo -u postgres $PGBIN/createdb -U nsadmin $DB

2) make a fresh tree based on openacs-5.8.1.tar.gz and a matching config file for NaviServer

3) installed into oacs-5-8-1t, restarted, got a running version of OpenACS 5.8.1

4) stopped oacs-5-8-1t instance, took openacs-5.9.1-core.tar.gz, expanded in in /tmp, copied /tmp/openacs-5.9.1/packages/* over 5.8.1t/packages

5) started server new, navigated to /acs-admin/install/ and then "upgrade from local filesystem", selected all suggested packages, etc..... and everything worked.

So, why do you got this error? The page contract filter "word" was already available in oacs-5-8, so something is probably broken during startup such that loading ran into a problem. Your are sure that you see no errors in the error.log in a step like (5) above?

Posted by Claudio Pasolini on
Hi Gustaf,

in my first attempt I copied the oacs-5-9-1 packages over a running oacs-5-8-1 instance and trying to upgrade I got the no such key: word error.

Then I restarted the instance, checked for errors in the error.log and found none of them and trying to upgrade I got the invalid command name "::xo::db::sql::site_node" error.

For some reason the ::xo procs were not available. Looking around I saw that the site-nodes-procs.tcl implements two versions of the procs based on the availability of the latest Xotcl.

I then set UseXotclSiteNodes to 0, restarted and all went well. The strange thing id that in the same environment the oacs-5-9-1 installed by script works perfectly well.

An old friend of mine used to say about programs: sometime they work, sometime not.

Posted by Gustaf Neumann on
When one is copying the 5-9 packages over a running pre-5.8.1 version (5.8.0), i would understand the problem with "no such key: word". The page contract filter "word " was introduced between 5.8.0 and 5.8.1, and the filters are loaded at start time. Are you sure, you restarted the server with 5.8.1 before copying the 5.9 packages?

I am a little confused by the sentence: "The strange thing id that in the same environment the oacs-5-9-1 installed by script works perfectly well.": are you saying, you have an upgraded and a fresh installed system on the same machine, and the fresh-installed works and the upgrqaded not? Can it be that the upgraded instance has still an old version of xotcl-core?

Posted by Claudio Pasolini on
Yes, the old instance had xotcl-core installed and when copying the 5.9.1 packages got by I didn't notice that xotcl-core was not there.

Making available the new xotcl-core solved the problem. Now I'm having some other problems, but I'll investigate before posting.

Many thanks for your help.