Bug 873747

What Spacewalk version was this installation upgraded from?

(In reply to comment #1)
> What Spacewalk version was this installation upgraded from?

Always from the latest (http://spacewalk.redhat.com/yum/latest/RHEL/6/x86_64/)

Maybe there is a problem with the db schema. Is it possible to repair it, without losing data?

(In reply to comment #2)
> (In reply to comment #1)
> > What Spacewalk version was this installation upgraded from?
> 
> Always from the latest
> (http://spacewalk.redhat.com/yum/latest/RHEL/6/x86_64/)
> 
> Maybe there is a problem with the db schema. Is it possible to repair it,
> without losing data?

Restoring your 1.7 schema from backup and re-runing the schema upgrade would be the correct way.

But we would also like to know what caused this -- what is in your schema upgrade logs, etc. The missing rhnabrtinfo table sounds like something non-trivially wrong happened there.

Here the recent files in "/var/log/spacewalk/schema-upgrade/"

Do you need any more files?


--------------------------------------------------------

[root@myserver ~]# cat /var/log/spacewalk/schema-upgrade/20121106-161051-script.sql 
update rhnVersionInfo
       set label = 'schema-from-' || to_char(created, 'YYYYMMDD-HH24MISS'),
           modified = current_timestamp
       where label = 'schema';
commit;
-- oracle equivalent source sha1 cb07e6926fb5f79d7645be270e632f9168631964
--
-- Copyright (c) 2010--2012 Red Hat, Inc.
--
-- This software is licensed to you under the GNU General Public License,
-- version 2 (GPLv2). There is NO WARRANTY for this software, express or
-- implied, including the implied warranties of MERCHANTABILITY or FITNESS
-- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
-- along with this software; if not, see
-- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
--
-- Red Hat trademarks are not licensed under GPLv2. No permission is
-- granted to use or replicate Red Hat trademarks that are incorporated
-- in this software or its documentation.
--

--
-- empty varchars are not allowed for the oracle-postgres compatibility
-- create constraints on all varchar columns (all tables for the current user)
-- and returns number of errors during processing (if not 0 then
-- check the pgsql log -- usually /var/lib/pgsql/data/pg_log -- for errors)
--
create or replace function create_varnull_constriants() returns integer as $$
declare
    tabs record;
    total integer default 0;
begin

    for tabs in select 
        c.relname as "tab",
        a.attname as "col"
    from
        pg_catalog.pg_attribute a
        left outer join pg_catalog.pg_class c on a.attrelid = c.oid 
    where
        -- skip system columns
        a.attnum > 0
        -- skip dropped columns
        and not a.attisdropped
        -- filter only varchars
        and a.atttypid = 1043
        -- skip cols that already has this constraint
        and not exists (
            select 1 from pg_catalog.pg_constraint 
            where conname = 'vn_' || c.relname || '_' || a.attname
        )
        -- filter only tables owned by current user
        and a.attrelid in (
            select c.oid from pg_catalog.pg_class c
            where relkind = 'r' and pg_catalog.pg_table_is_visible(c.oid) and relowner = (
                select oid from pg_catalog.pg_authid where rolname = current_user
            )
        ) loop
        begin
            -- create constraint
            execute 'alter table ' || tabs.tab || ' add constraint vn_' ||
                tabs.tab || '_' || tabs.col || ' check (' || tabs.col || ' <> '''')';
            -- count them
        exception when others then
            total = total + 1;
            raise warning '% unable to create constraint for %.%', now(), tabs.tab, tabs.col;
        end;
    end loop;

    return total;
end;
$$ language plpgsql;

select create_varnull_constriants();


insert into rhnVersionInfo
       ( label, name_id, evr_id, created, modified )
       values ('schema', lookup_package_name('spacewalk-schema'),
               lookup_evr(null, '1.8.88' , '1.el6' ),
               current_timestamp, current_timestamp );
commit;


--------------------------------------------------------


[root@myserver ~]# cat /var/log/spacewalk/schema-upgrade/20121106-161051-to-spacewalk-schema-1.8.log 
UPDATE 1
COMMIT
CREATE FUNCTION
 create_varnull_constriants 
----------------------------
                          0
(1 row)

INSERT 0 1
COMMIT

(In reply to comment #4)
> Here the recent files in "/var/log/spacewalk/schema-upgrade/"
> 
> Do you need any more files?
> 
> 
> --------------------------------------------------------
> 
> [root@myserver ~]# cat
> /var/log/spacewalk/schema-upgrade/20121106-161051-script.sql 

Yes, the previous ones. This ones are from the run when the schema version was already 1.8.88-1.el6. Which is however strange.

Did you by any chance migrate your Spacewalk server installation from (say) EL5 to EL6?

Could you please paste output of

select label, created, ( select evr from rhnpackageevr where evr_id = id ) as evr from rhnVersionInfo order by created ;

when run in your psql?

Also, could you show a list of databases in your PostgreSQL?

Command

\l

in psql.

Meanwhile I set up a new 1.8 installation, because we need a working spacewalk server.

Here are the informations from the database, I backed up before. 



spaceschema=# \l
                                   List of databases
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 spaceschema | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
(4 rows)


spaceschema=# select label, created, ( select evr from rhnpackageevr where evr_id = id ) as evr from rhnVersionInfo order by created ;
            label            |            created            |       evr       
-----------------------------+-------------------------------+-----------------
 schema-from-20120524-175710 | 2012-05-24 17:57:10.916031+02 | (,1.8.39,1.el6)
 schema-from-20120525-095231 | 2012-05-25 09:52:31.403195+02 | (,1.8.40,1.el6)
 schema                      | 2012-11-06 16:11:18.546448+01 | (,1.8.88,1.el6)
(3 rows)

(In reply to comment #8)
> Meanwhile I set up a new 1.8 installation, because we need a working
> spacewalk server.

And I assume it works fine.

> spaceschema=# select label, created, ( select evr from rhnpackageevr where
> evr_id = id ) as evr from rhnVersionInfo order by created ;
>             label            |            created            |       evr    
> 
> -----------------------------+-------------------------------+---------------
> --
>  schema-from-20120524-175710 | 2012-05-24 17:57:10.916031+02 |
> (,1.8.39,1.el6)
>  schema-from-20120525-095231 | 2012-05-25 09:52:31.403195+02 |
> (,1.8.40,1.el6)
>  schema                      | 2012-11-06 16:11:18.546448+01 |
> (,1.8.88,1.el6)
> (3 rows)

This means the original installation was a Spacewalk nightly.

We don't support upgrades from Spacewalk nightly to next released release.

This BZ closed some time during 2.5, 2.6 or 2.7.  Adding to 2.7 tracking bug.