[Spacewalk-list] Error during schema upgrade
Frey, Evan
evan.frey at us.mizuho-sc.com
Tue Feb 16 14:03:36 UTC 2010
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/004-2-rhnChecksumType-data.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/004-2-RHNCHECKSUMTYPE-DATA.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/004-2-rhnChecksumType-data.sql
SQL> update rhnChecksumType
2 set label = translate(label, ' -', ' ');
5 rows updated.
SQL>
SQL> update rhnChecksumType
2 set description = upper(label) || 'sum';
5 rows updated.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/005-rhnChecksum-table.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/005-RHNCHECKSUM-TABLE.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/005-rhnChecksum-table.sql
SQL> --
SQL> -- Copyright (c) 2009 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software, express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> -- The checksum associated with a package/file/etc...
SQL>
SQL> create table
2 rhnChecksum
3 (
4 id number not null
5 constraint rhnChecksum_pk primary key,
6 checksum_type_id number not null
7 constraint rhnChecksum_typeid_fk
8 references rhnChecksumType(id),
9 checksum varchar2(128) not null
10 )
11 enable row movement
12 ;
Table created.
SQL>
SQL> CREATE INDEX rhnChecksum_chsum_idx on rhnChecksum(checksum);
Index created.
SQL>
SQL> CREATE SEQUENCE rhnChecksum_seq;
Sequence created.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/007-rhnChecksumView.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/007-RHNCHECKSUMVIEW.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/007-rhnChecksumView.sql
SQL> --
SQL> -- Copyright (c) 2009 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software, express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL>
SQL> create or replace view rhnChecksumView
2 as
3 select c.id,
4 ct.label checksum_type,
5 c.checksum
6 from rhnChecksum c,
7 rhnChecksumType ct
8 where c.checksum_type_id = ct.id;
View created.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/008-lookup_checksum.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/008-LOOKUP_CHECKSUM.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/008-lookup_checksum.sql
SQL> --
SQL> -- Copyright (c) 2009 Red Hat, Inc.
SQL> --
SQL> -- This software is licensed to you under the GNU General Public
License,
SQL> -- version 2 (GPLv2). There is NO WARRANTY for this software, express or
SQL> -- implied, including the implied warranties of MERCHANTABILITY or
FITNESS
SQL> -- FOR A PARTICULAR PURPOSE. You should have received a copy of GPLv2
SQL> -- along with this software; if not, see
SQL> -- http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt.
SQL> --
SQL> -- Red Hat trademarks are not licensed under GPLv2. No permission is
SQL> -- granted to use or replicate Red Hat trademarks that are incorporated
SQL> -- in this software or its documentation.
SQL> --
SQL> --
SQL> --
SQL> --
SQL>
SQL> CREATE OR REPLACE FUNCTION
2 LOOKUP_CHECKSUM(checksum_type_in IN VARCHAR2, checksum_in IN VARCHAR2)
3 RETURN NUMBER
4 IS
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 checksum_id NUMBER;
7 BEGIN
8 if checksum_in is null then
9 return null;
10 end if;
11
12 SELECT c.id
13 INTO checksum_id
14 FROM rhnChecksumView c
15 WHERE c.checksum = checksum_in
16 AND c.checksum_type = checksum_type_in;
17
18 RETURN checksum_id;
19 EXCEPTION
20 WHEN NO_DATA_FOUND THEN
21 INSERT INTO rhnChecksum (id, checksum_type_id, checksum)
22 VALUES (rhnChecksum_seq.nextval,
23 (select id from rhnChecksumType where label =
checksum_type_in),
24 checksum_in)
25 RETURNING id INTO checksum_id;
26 COMMIT;
27 RETURN checksum_id;
28 END;
29 /
Function created.
SQL> SHOW ERRORS
No errors.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/009-rhnChannel-checksum_type.sq
l' from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/009-RHNCHANNEL-CHECKSUM_TYPE.SQ
L'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/009-rhnChannel-checksum_type.sql
SQL> update rhnChannel
2 set checksum_type_id = (select id from rhnChecksumType where label =
'sha1')
3 where checksum_type_id is null
4 and (label like 'rhel-%-server-5'
5 or label like 'rhel-%-client-5'
6 or parent_channel in (select id from rhnChannel
7 where label like 'rhel-%-server-5'
8 or label like
'rhel-%-client-5'));
0 rows updated.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-1-rhnFile-checksum.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/010-1-RHNFILE-CHECKSUM.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-1-rhnFile-checksum.sql
SQL> alter table rhnFile add checksum_id NUMBER
2 CONSTRAINT rhn_file_chsum_fk
3 REFERENCES rhnChecksum (id);
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-2-rhnFile-data.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/010-2-RHNFILE-DATA.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-2-rhnFile-data.sql
SQL> declare
2 md5_id number;
3 begin
4 select id
5 into md5_id
6 from rhnChecksumType
7 where label = 'md5';
8
9 insert into rhnChecksum (id, checksum_type_id, checksum)
10 (select rhnChecksum_seq.nextval, md5_id, csum
11 from (select distinct md5sum as csum
12 from rhnFile
13 minus
14 select checksum as csum
15 from rhnChecksum
16 where checksum_type_id = md5_id));
17 commit;
18 update rhnFile p
19 set checksum_id = (select id
20 from rhnChecksum c
21 where checksum_type_id = md5_id
22 and p.md5sum = c.checksum);
23 commit;
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-3-rhnFile-md5.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/010-3-RHNFILE-MD5.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-3-rhnFile-md5.sql
SQL> alter table rhnFile drop column md5sum;
Table altered.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-4-rhnFile-checksum.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/010-4-RHNFILE-CHECKSUM.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/010-4-rhnFile-checksum.sql
SQL> alter table rhnFile modify checksum_id NUMBER NOT NULL;
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-1-rhnPackage-checksum.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/011-1-RHNPACKAGE-CHECKSUM.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-1-rhnPackage-checksum.sql
SQL> alter table rhnPackage add checksum_id NUMBER
2 CONSTRAINT rhn_package_chsum_fk
3 REFERENCES rhnChecksum (id);
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-2-rhnPackage-data.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/011-2-RHNPACKAGE-DATA.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-2-rhnPackage-data.sql
SQL> declare
2 md5_id number;
3 begin
4 select id
5 into md5_id
6 from rhnChecksumType
7 where label = 'md5';
8
9 insert into rhnChecksum (id, checksum_type_id, checksum)
10 (select rhnChecksum_seq.nextval, md5_id, csum
11 from (select distinct md5sum as csum
12 from rhnPackage
13 minus
14 select checksum as csum
15 from rhnChecksum
16 where checksum_type_id = md5_id));
17 commit;
18 update rhnPackage p
19 set checksum_id = (select id
20 from rhnChecksum c
21 where checksum_type_id = md5_id
22 and p.md5sum = c.checksum);
23 commit;
24 end;
25 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-3-rhnPackage-md5.sql' from
dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/011-3-RHNPACKAGE-MD5.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-3-rhnPackage-md5.sql
SQL> alter table rhnPackage drop column md5sum;
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-4-rhnPackage-checksum.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/011-4-RHNPACKAGE-CHECKSUM.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/011-4-rhnPackage-checksum.sql
SQL> alter table rhnPackage modify checksum_id NUMBER NOT NULL;
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/012-1-rhnPackageFile-checksum.s
ql' from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/012-1-RHNPACKAGEFILE-CHECKSUM.S
QL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/012-1-rhnPackageFile-checksum.sq
l
SQL> alter table rhnPackageFile add checksum_id NUMBER
2 CONSTRAINT rhn_package_file_chsum_fk
3 REFERENCES rhnChecksum (id);
Table altered.
SQL> commit;
Commit complete.
SQL> set echo on
SQL> spool
/var/log/spacewalk/schema-upgrade/20100216-081401-to-spacewalk-schema-0.8.log
append
SQL> whenever sqlerror exit sql.sqlcode
SQL> select
'spacewalk-schema-0.7-to-spacewalk-schema-0.8/012-2-rhnPackageFile-data.sql'
from dual;
'SPACEWALK-SCHEMA-0.7-TO-SPACEWALK-SCHEMA-0.8/012-2-RHNPACKAGEFILE-DATA.SQL'
-----------------------------------------------------------------------------
---
spacewalk-schema-0.7-to-spacewalk-schema-0.8/012-2-rhnPackageFile-data.sql
SQL> declare
2 md5_id number;
3 min_pid number;
4 max_pid number;
5 lmin number;
6 lmax number;
7 begin
8 select id
9 into md5_id
10 from rhnChecksumType
11 where label = 'md5';
12
13 select min(package_id), max(package_id)
14 into min_pid, max_pid
15 from rhnPackageFile;
16
17 lmin := min_pid;
18 lmax := lmin + 99999;
19 while lmin < max_pid loop
20 insert into rhnChecksum (id, checksum_type_id, checksum)
21 (select rhnChecksum_seq.nextval, md5_id, csum
22 from (select distinct md5 as csum
23 from rhnPackageFile
24 where package_id between lmin and lmax
25 and md5 is not null
26 minus
27 select checksum as csum
28 from rhnChecksum
29 where checksum_type_id = md5_id));
30 commit;
31 update rhnPackageFile p
32 set checksum_id = (select id
33 from rhnChecksum c
34 where checksum_type_id = md5_id
35 and p.md5 = c.checksum)
36 where package_id between lmin and lmax
37 and md5 is not null;
38 commit;
39 lmin := lmax + 1;
40 lmax := lmin + 99999;
41 end loop;
42 end;
43 /
declare
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
ORA-06512: at line 31
-----Original Message-----
From: spacewalk-list-bounces at redhat.com
[mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Milan Zazrivec
Sent: Tuesday, February 16, 2010 8:47 AM
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Error during schema upgrade
On Tuesday 16 February 2010 14:32:51 Frey, Evan wrote:
> I am trying to upgrade from .7 to .8 and during the schema upgrade I
> get the following:
>
> ERROR at line 1:
>
> ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
>
> ORA-06512: at line 31
Could you please attach the content of
/var/log/spacewalk/schema-upgrade/*-to-spacewalk-schema-0.8.log
from your Spacewalk installation?
-MZ
_______________________________________________
Spacewalk-list mailing list
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list
CONFIDENTIAL: This e-mail, including its contents and attachments,
if any, are confidential. It is neither an offer to buy or sell,
nor a solicitation of an offer to buy or sell, any securities or
any related financial instruments mentioned in it. If you are not
the named recipient please notify the sender and immediately delete
it. You may not disseminate, distribute, or forward this e-mail
message or disclose its contents to anybody else. Unless otherwise
indicated, copyright and any other intellectual property rights in
its contents are the sole property of Mizuho Securities USA Inc.
E-mail transmission cannot be guaranteed to be secure or
error-free. The sender therefore does not accept liability for any
errors or omissions in the contents of this message which arise as
a result of e-mail transmission. If verification is required
please request a hard-copy version.
Although we routinely screen for viruses, addressees should
check this e-mail and any attachments for viruses. We make no
representation or warranty as to the absence of viruses in this
e-mail or any attachments. Please note that to ensure regulatory
compliance and for the protection of our customers and business, we
may monitor and read e-mails sent to and from our server(s).
#####################################################################################
More information about the Spacewalk-list
mailing list