[Spacewalk-list] error on schema upgrade after upgrading to 1.8
Maria Iano
maria at purplecoffee.com
Wed Dec 5 17:00:36 UTC 2012
On Dec 5, 2012, at 5:02 AM, Jan Pazdziora wrote:
> 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.
Is there anything I can do that might help repair it?
>
>> 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.
I did that and ran the schema upgrade again. Here is the output:
?column?
--------------------------------------------------------------------------
spacewalk-schema-1.7-to-spacewalk-schema-1.8/143-novell-package-keys.sql
(1 row)
sequence_name | last_value | start_value | increment_by
| max_value | min_value | cache_value | log_cnt | is_cycled
| is_called
-----------------------------+------------+-------------+--------------
+---------------------+-----------+-------------+---------+-----------
+-----------
rhn_package_provider_id_seq | 107 | 100 | 1
| 9223372036854775807 | 1 | 1 | 26 | f
| t
(1 row)
id | name | created |
modified
-----+------------------+-------------------------------
+-------------------------------
100 | Red Hat Inc. | 2012-05-11 17:05:11.399089-04 | 2012-05-11
17:05:11.399089-04
101 | Fedora | 2012-05-11 17:05:11.401747-04 | 2012-05-11
17:05:11.401747-04
102 | CentOS | 2012-05-11 17:05:11.403533-04 | 2012-05-11
17:05:11.403533-04
103 | Scientific Linux | 2012-05-11 17:05:11.404611-04 | 2012-05-11
17:05:11.404611-04
105 | Oracle Inc. | 2012-05-11 17:05:11.407241-04 | 2012-05-11
17:05:11.407241-04
106 | Spacewalk | 2012-05-11 17:05:11.408151-04 | 2012-05-11
17:05:11.408151-04
107 | EPEL | 2012-05-11 17:05:11.409168-04 | 2012-05-11
17:05:11.409168-04
108 | VMware | 2012-05-23 13:45:15.527746-04 | 2012-05-23
13:45:15.527746-04
104 | SUSE | 2012-05-11 17:05:11.406173-04 | 2012-12-05
09:53:20.294829-05
(9 rows)
psql:/var/log/spacewalk/schema-upgrade/20121205-092140-script.sql:
2358: ERROR: duplicate key value violates unique constraint
"rhn_pkg_provider_id_pk"
After that, I restored the database again. I ran the insert command
myself, and this time I got the same error with the manual insert. I
ran a couple of delete commands which reportedly didn't do anything. I
ran the insert command again, and that time it worked:
spaceschema=# insert into rhnPackageProvider (id, name) values
(sequence_nextval('rhn_package_provider_id_seq'), 'Novell Inc.' );
ERROR: duplicate key value violates unique constraint
"rhn_pkg_provider_id_pk"
spaceschema=# select * from rhn_package_provider_id_seq;
sequence_name | last_value | start_value | increment_by
| max_value | min_value |
cache_value | log_cnt | is_cycled | is_called
-----------------------------+------------+-------------+--------------
+---------------------+-----------+-
------------+---------+-----------+-----------
rhn_package_provider_id_seq | 108 | 100 | 1
| 9223372036854775807 | 1 |
1 | 32 | f | t
(1 row)
spaceschema=# select * from rhnPackageProvider;
id | name | created |
modified
-----+------------------+-------------------------------
+-------------------------------
100 | Red Hat Inc. | 2012-05-11 17:05:11.399089-04 | 2012-05-11
17:05:11.399089-04
101 | Fedora | 2012-05-11 17:05:11.401747-04 | 2012-05-11
17:05:11.401747-04
102 | CentOS | 2012-05-11 17:05:11.403533-04 | 2012-05-11
17:05:11.403533-04
103 | Scientific Linux | 2012-05-11 17:05:11.404611-04 | 2012-05-11
17:05:11.404611-04
104 | Suse | 2012-05-11 17:05:11.406173-04 | 2012-05-11
17:05:11.406173-04
105 | Oracle Inc. | 2012-05-11 17:05:11.407241-04 | 2012-05-11
17:05:11.407241-04
106 | Spacewalk | 2012-05-11 17:05:11.408151-04 | 2012-05-11
17:05:11.408151-04
107 | EPEL | 2012-05-11 17:05:11.409168-04 | 2012-05-11
17:05:11.409168-04
108 | VMware | 2012-05-23 13:45:15.527746-04 | 2012-05-23
13:45:15.527746-04
(9 rows)
spaceschema=# delete from rhnPackageProvider where id=109;
DELETE 0
spaceschema=# delete from rhnPackageProvider where name='Novell Inc.';
DELETE 0
spaceschema=# insert into rhnPackageProvider (id, name) values
(sequence_nextval('rhn_package_provider_id_seq'), 'Novell Inc.' );
INSERT 0 1
spaceschema=# select * from
rhnPackageProvider
; id
| name | created | modified
-----+------------------+-------------------------------
+-------------------------------
100 | Red Hat Inc. | 2012-05-11 17:05:11.399089-04 | 2012-05-11
17:05:11.399089-04
101 | Fedora | 2012-05-11 17:05:11.401747-04 | 2012-05-11
17:05:11.401747-04
102 | CentOS | 2012-05-11 17:05:11.403533-04 | 2012-05-11
17:05:11.403533-04
103 | Scientific Linux | 2012-05-11 17:05:11.404611-04 | 2012-05-11
17:05:11.404611-04
104 | Suse | 2012-05-11 17:05:11.406173-04 | 2012-05-11
17:05:11.406173-04
105 | Oracle Inc. | 2012-05-11 17:05:11.407241-04 | 2012-05-11
17:05:11.407241-04
106 | Spacewalk | 2012-05-11 17:05:11.408151-04 | 2012-05-11
17:05:11.408151-04
107 | EPEL | 2012-05-11 17:05:11.409168-04 | 2012-05-11
17:05:11.409168-04
108 | VMware | 2012-05-23 13:45:15.527746-04 | 2012-05-23
13:45:15.527746-04
109 | Novell Inc. | 2012-12-05 10:10:27.147461-05 | 2012-12-05
10:10:27.147461-05
(10 rows)
Next I ran the schema upgrade but commented out the insert command. I
left everything else in. This time the schema upgrade completed
without an error message. The subsequent commands in the 143-novell-
package-keys.sql schema upgrade script didn't give an error. I did a
couple of spot checks and the commands seem to have worked correctly.
spaceschema=# select * from rhnPackageProvider;
id | name | created |
modified
-----+------------------+-------------------------------
+-------------------------------
100 | Red Hat Inc. | 2012-05-11 17:05:11.399089-04 | 2012-05-11
17:05:11.399089-04
101 | Fedora | 2012-05-11 17:05:11.401747-04 | 2012-05-11
17:05:11.401747-04
102 | CentOS | 2012-05-11 17:05:11.403533-04 | 2012-05-11
17:05:11.403533-04
103 | Scientific Linux | 2012-05-11 17:05:11.404611-04 | 2012-05-11
17:05:11.404611-04
105 | Oracle Inc. | 2012-05-11 17:05:11.407241-04 | 2012-05-11
17:05:11.407241-04
106 | Spacewalk | 2012-05-11 17:05:11.408151-04 | 2012-05-11
17:05:11.408151-04
107 | EPEL | 2012-05-11 17:05:11.409168-04 | 2012-05-11
17:05:11.409168-04
108 | VMware | 2012-05-23 13:45:15.527746-04 | 2012-05-23
13:45:15.527746-04
109 | Novell Inc. | 2012-12-05 10:10:27.147461-05 | 2012-12-05
10:10:27.147461-05
104 | SUSE | 2012-05-11 17:05:11.406173-04 | 2012-12-05
10:21:23.537836-05
(10 rows)
spaceschema=# select * from rhnPackageKey where key_id =
'2afe16421d061a62';
id | key_id | key_type_id | provider_id |
created | modified
-----+------------------+-------------+-------------
+-------------------------------+----------------------
---------
134 | 2afe16421d061a62 | 100 | 109 | 2012-12-05
10:21:23.587956-05 | 2012-12-05 10:21:23.5
87956-05
(1 row)
spaceschema=# select * from rhnPackageKey where key_id =
'14c28bc97e2e3b05';
id | key_id | key_type_id | provider_id |
created | modified
-----+------------------+-------------+-------------
+-------------------------------+----------------------
---------
135 | 14c28bc97e2e3b05 | 100 | 109 | 2012-12-05
10:21:23.589682-05 | 2012-12-05 10:21:23.5
89682-05
(1 row)
Spacewalk appears to be running with no problems. Should I be worrying
about database corruption?
Thank you!
Maria
More information about the Spacewalk-list
mailing list