[Spacewalk-list] duplicate key value during Spacewalk 1.6 to 1.7, using postgresql

[Alan Pittman]

Thanks for the reply. As you requested, I renamed the 111-rhnPackageKey-data.sql and then created a new version of the file with the information you supplied below. When I rerun the /usr/bin/spacewalk-schema-upgrade, I get the following message: 

[root at taeps001 ~]# /usr/bin/spacewalk-schema-upgrade
Unknown schema name [(0 rows)] found.
[root at taeps001 ~]#

What am I doing wrong?

Here's the content of the new 111 file:

[root at taeps001 ~]# cd /etc/sysconfig/rhn/schema-upgrade/spacewalk-schema-1.6-to-spacewalk-schema-1.7
[root at taeps001 spacewalk-schema-1.6-to-spacewalk-schema-1.7]# ll 111*
-rw-r--r-- 1 root root 1080 Mar  8 07:49 111-rhnPackageKey-data.sql
-rw-r--r-- 1 root root 1256 Mar  8 07:49 111-rhnPackageKey-data.sql.x
[root at taeps001 spacewalk-schema-1.6-to-spacewalk-schema-1.7]# cat ./111-rhnPackageKey-data.sql
-- Fedora 16, 17, 18
update rhnPackageKey set provider_id = lookup_package_provider('Fedora')
where key_id in ('067f00b6a82ba4b7', '50e94c991aca3465', '0983129322b3b81a');

-- Fedora 16
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '067f00b6a82ba4b7', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '067f00b6a82ba4b7' );
-- Fedora 17
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '50e94c991aca3465', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '50e94c991aca3465' );
-- Fedora 18
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '0983129322b3b81a', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '0983129322b3b81a' );

[root at taeps001 spacewalk-schema-1.6-to-spacewalk-schema-1.7]#

Alan



-----Original Message-----
From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Jan Pazdziora
Sent: Thursday, March 08, 2012 5:41 AM
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] duplicate key value during Spacewalk 1.6 to 1.7, using postgresql

On Wed, Mar 07, 2012 at 08:54:24PM +0000, Alan Pittman wrote:
> Hi,
>   I'm upgrading Spacewalk w/postgresql on a RHEL6, x86_64 server. All went well until I ran the schema update. Got the following  error message:
> 
>                                      ?column?                                      
> ------------------------------------------------------------------------------------
>  spacewalk-schema-1.6-to-spacewalk-schema-1.7/110-create_pxt_session.sql.postgresql
> (1 row)
> 
> CREATE FUNCTION
> COMMIT
>                                 ?column?                                 
> -------------------------------------------------------------------------
>  spacewalk-schema-1.6-to-spacewalk-schema-1.7/111-rhnPackageKey-data.sql
> (1 row)
> 
> psql:/var/log/spacewalk/schema-upgrade/20120307-154732-script.sql:5090: ERROR:  duplicate key value violates unique constraint "rhn_pkey_keyid_uq"
> 
> This is just the bottom of the schema-upgrade log file. If you want to see the whole file, let me know and I'll send it. Just trying to conserve space here.
> 

Can you replace the 111-rhnPackageKey-data.sql file with

-- Fedora 16, 17, 18
update rhnPackageKey set provider_id = lookup_package_provider('Fedora')
where key_id in ('067f00b6a82ba4b7', '50e94c991aca3465', '0983129322b3b81a');

-- Fedora 16
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '067f00b6a82ba4b7', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '067f00b6a82ba4b7' );
-- Fedora 17
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '50e94c991aca3465', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '50e94c991aca3465' );
-- Fedora 18
insert into rhnPackageKey (id, key_id, key_type_id, provider_id)
select sequence_nextval('rhn_pkey_id_seq'), '0983129322b3b81a', lookup_package_key_type('gpg'), lookup_package_provider('Fedora')
from dual
where not exists ( select 1 from rhnPackageKey where key_id = '0983129322b3b81a' );

and start the schema upgrade anew?

-- 
Jan Pazdziora
Principal Software Engineer, Satellite Engineering, Red Hat

_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list