[Spacewalk-list] error on schema upgrade after upgrading to 1.8

[Jan Pazdziora]

On Wed, Dec 05, 2012 at 04:26:00AM -0500, Maria Iano wrote:
> 
> >>
> >>insert into rhnPackageProvider (id, name) values
> >>(sequence_nextval('rhn_package_provider_id_seq'), 'Novell Inc.' );
> >>
> >>Out of curiosity I tried restoring the database and running that
> >>query on its own and it ran with no error. I tried the upgrade after
> >>running that, and also after restoring again but it died both times
> >>with the same error.
> >
> >Did it really die on the exact same line?
> 
> Yes it died on the same line. It died with a different error the
> second time, sorry I missed that earlier.  The first time I ran it,
> it died with this error:
> psql:/var/log/spacewalk/schema-upgrade/20121204-153735-script.sql:
> 2356: ERROR:  duplicate key value violates unique constraint
> "rhn_pkg_provider_id_pk"

That would however mean that the sequence has given the same number
twice. This is a symptom of corrupted datastore.

> After that I restored the database from backup and manually ran:
> select * from rhn_package_provider_id_seq;
> select * from rhnPackageProvider;
> insert into rhnPackageProvider (id, name) values
> (sequence_nextval('rhn_package_provider_id_seq'), 'Novell Inc.' );
> select * from rhnPackageProvider;
> select * from rhn_package_provider_id_seq;
> 
> and I'll paste in the output below. It had the expected effect of
> adding " Novell Inc." to rhnPackageProvider. After that I ran
> spacewalk-schema-upgrade and the error message was:
> psql:/var/log/spacewalk/schema-upgrade/20121204-162859-script.sql:
> 2356: ERROR:  duplicate key value violates unique constraint
> "rhn_pkg_provider_name_uq"

In this situation it is expected -- the inserted a record that the
schema upgrade script expects not to be there.

> I thought there must be too, but I can't find it. The first
> occurrence in 20121204-162859-script.sql of 'Novell Inc' is on that
> line.
> 
> # grep -in 'Novell Inc' /var/log/spacewalk/schema-upgrade/20121204-162859-script.sql
> 2356:(sequence_nextval('rhn_package_provider_id_seq'), 'Novell Inc.' );

Can you add

	select * from rhn_package_provider_id_seq;
	select * from rhnPackageProvider;

to the start of that 143-novell-package-keys.sql schema upgrade
script, to see what's in the database just before the insert is
attempted? If you find that record already there, you might want to go
back to your backup, remove that insert, and just live with the value
inserted by miracle.

> Is there something that runs before that script?

Nothing we are aware of.

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