[Spacewalk-list] Failed schema upgrade from 2.0 to 2.1, ERROR: duplicate key value violates unique constraint "rhn_pkey_keyid_uq"

[Eduardo Minguez]

----- Original Message -----
> From: "Jonathan Hoser" <jonathan.hoser at helmholtz-muenchen.de>
> To: spacewalk-list at redhat.com
> Sent: Wednesday, 5 March, 2014 2:07:32 PM
> Subject: Re: [Spacewalk-list] Failed schema upgrade from 2.0 to 2.1, ERROR: duplicate key value violates unique
> constraint "rhn_pkey_keyid_uq"
> 
> On 03/05/2014 10:20 AM, Eduardo Minguez wrote:
> >
> > ----- Original Message -----
> >> From: "Jonathan Hoser" <jonathan.hoser at helmholtz-muenchen.de>
> >> To: spacewalk-list at redhat.com
> >> Sent: Wednesday, 5 March, 2014 8:11:14 AM
> >> Subject: Re: [Spacewalk-list] Failed schema upgrade from 2.0 to
> >> 2.1, ERROR: duplicate key value violates unique
> >> constraint "rhn_pkey_keyid_uq"
> >>
> >> Hi,
> >>
> >> I had the same issue,
> >> (this is schema-update script #047)
> >> where I already had the to-be-inserted key in my table (no clue
> >> where
> >> I
> >> got it),
> >> and I manually updated it with the values the insert-statement
> >> wanted
> >> to
> >> place there.
> >>
> >> Afterwards the 2 (or 3?) remaining sqls I manually applied using
> >> the
> >> \i
> >> [filename] syntax,
> >> and everything was fine...
> >> (ooh... I just remembered, that a last transaction somewhere
> >> changes
> >> the
> >> database-version field... I have to go check on that one...)
> >>
> > Could you please add more detail to that procedure?
> >
> > I have no backup of the database :) (I have it installed for
> > testing purposes, so it's not important, but I don't want to
> > reinstall it :D)
> >
> > Thanks!
> Here you go.
> 
> pushd
> /etc/sysconfig/rhn/schema-upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/
> ls -al
> cat 047-rhnPackageKey-f20.sql.postgresql
> 
> 
> su - postgres
> psql spaceschema
> select * from rhnPackageKey where key_id='2eb161fa246110c1';
> #^^check if it is really there...
> #then update (in my case I was missing the provider_id entry)
> update rhnPackageKey set
> provider_id=lookup_package_provider('Fedora')
> where key_id='2eb161fa246110c1';
> 
> 
> #Run the remaining SQL scripts:
> \i
> /etc/sysconfig/rhn/schema-upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/048-rhn_channel.pkb.sql.postgresql
> \i
> /etc/sysconfig/rhn/schema-upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/050-rhnTimezone-data.sql.postgresql
> \i
> /etc/sysconfig/rhn/schema-upgrade/spacewalk-schema-2.0-to-spacewalk-schema-2.1/051-rhnServerOutdatedPackages.sql.postgresql
> 
> insert into rhnVersionInfo ( label, name_id, evr_id, created,
> modified )
> values ('schema', lookup_package_name('spacewalk-schema'),
> lookup_evr(null, '2.1.50' , '1.el6' ), current_timestamp,
> current_timestamp ); commit;
> 
> 

It worked great :) Then updated to the latest spacewalk-schema RPM and "spacewalk-schema-upgrade" and worked fine.

> 
> >
> >> Best
> >> -Jonathan
> >>
> >> On 03/04/2014 03:28 PM, Eduardo Minguez wrote:
> >>> Hi,
> >>>
> >>> CentOS 6.5 x86_64 yum updated to the latest version, and
> >>> following
> >>> the spacewalk upgrade guide in
> >>> https://fedorahosted.org/spacewalk/wiki/HowToUpgrade, it fails in
> >>> the schema-upgrade procedure like:
> >>>
> >>> (snip)
> >>> ...
> >>> ------------------------------------------------------------------------------------------------
> >>>  spacewalk-schema-2.0-to-spacewalk-schema-2.1/046-rhnUserExtGroupMapping-trigger.sql.postgresql
> >>> (1 row)
> >>>
> >>> CREATE FUNCTION
> >>> CREATE TRIGGER
> >>> COMMIT
> >>>                                      ?column?
> >>> -----------------------------------------------------------------------------------
> >>>  spacewalk-schema-2.0-to-spacewalk-schema-2.1/047-rhnPackageKey-f20.sql.postgresql
> >>> (1 row)
> >>>
> >>> psql:/var/log/spacewalk/schema-upgrade/20140304-125445-script.sql:4308:
> >>> ERROR:  duplicate key value violates unique constraint
> >>> "rhn_pkey_keyid_uq"
> >>>
> >>>
> >>> If I try to do it again it says:
> >>>
> >>> spacewalk-schema-upgrade
> >>> Unknown schema name [(0 rows)] found.
> >>>
> >>> Is there anything I can do to fix it?
> >>>
> >>> Thanks
> >>>
> >>> _______________________________________________
> >>> Spacewalk-list mailing list
> >>> Spacewalk-list at redhat.com
> >>> https://www.redhat.com/mailman/listinfo/spacewalk-list
> >>>
> >>
> 
> --
> Jonathan Hoser, M.Sc.
> Institute of Bioinformatics and System Biology
> 
> WWW: http://mips.helmholtz-muenchen.de
> 
> 
> Helmholtz Zentrum München
> Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH)
> Ingolstädter Landstr. 1
> 85764 Neuherberg
> www.helmholtz-muenchen.de
> Aufsichtsratsvorsitzende: MinDir´in Bärbel Brumme-Bothe
> Geschäftsführer: Prof. Dr. Günther Wess, Dr. Nikolaus Blum, Dr.
> Alfons Enhsen
> Registergericht: Amtsgericht München HRB 6466
> USt-IdNr: DE 129521671
> 
> _______________________________________________
> Spacewalk-list mailing list
> Spacewalk-list at redhat.com
> https://www.redhat.com/mailman/listinfo/spacewalk-list

-- 
Eduardo Mínguez Pérez
Infrastructure Consultant (RHCE, RHCSA)
Red Hat - Spain
Mobile: +34 629803049 (CET/CEST)
E-mail: eminguez at redhat.com