Bug 1410737 - constraint rhn_pe_v_r_e_uq in rhnpackageevr allows duplicated rows in postgres db
Summary: constraint rhn_pe_v_r_e_uq in rhnpackageevr allows duplicated rows in postgre...
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: Red Hat Satellite 5
Classification: Red Hat
Component: Other
Version: 580
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ---
Assignee: Tomáš Kašpárek
QA Contact: Lukáš Hellebrandt
URL:
Whiteboard:
Depends On:
Blocks: sat58-errata sat58-nth
TreeView+ depends on / blocked
 
Reported: 2017-01-06 09:53 UTC by Ales Dujicek
Modified: 2018-02-06 09:41 UTC (History)
4 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Environment:
Last Closed: 2018-02-05 13:57:18 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Product Errata RHBA-2018:0274 0 normal SHIPPED_LIVE Red Hat Satellite 5.8.0 bug fix update 2018-02-09 23:18:15 UTC

Description Ales Dujicek 2017-01-06 09:53:59 UTC
Description of problem:

similar issue to https://bugzilla.redhat.com/show_bug.cgi?id=1381857

Postgres allows duplicated rows when one of the fields is null:

rhnschema=# \d rhnpackageevr
         Table "public.rhnpackageevr"
...
Indexes:
    "rhn_pe_v_r_e_uq" UNIQUE, btree (version, release, epoch)
...

rhnschema=# select version,release,epoch, count(*) from rhnpackageevr group by version,release,epoch having count(*) > 1;
 version | release | epoch | count 
---------+---------+-------+-------
 1.10    | 65      |       |     2
 1.7     | 128     |       |     2
 1.8     | 88      |       |     2

Problem is that it cannot be migrated to oracle database

# spacewalk-dump-schema --to=oracle > migrate-to-oracle-4clP.sql
# spacewalk-sql -i < migrate-to-oracle-4clP.sql
...
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23  
Table created.

SQL>   2    3    4    5    6    7    8    9   10  INSERT /*+ APPEND */
*
ERROR at line 1:
ORA-00001: unique constraint (RHNSAT.RHN_PE_V_R_E_UQ) violated

Steps to Reproduce:
1. migrate satellite with embedded postgres to external oracle database


Version-Release number of selected component (if applicable):
spacewalk-schema-2.3.2-28.el6sat.noarch
satellite-schema-5.7.0.27-1.el6sat.noarch

Comment 1 Jan Dobes 2017-05-10 15:13:40 UTC
In table like rhnPackageEVR it's hard to fix it mainly because records from this table are referenced from many other tables which makes upgrade and removing duplicates very tricky.

$ git grep -i "references rhnpackageevr"
rhnActionPackage.sql:                             REFERENCES rhnPackageEvr (id),
rhnActionPackageRemovalFailure.sql:                           REFERENCES rhnPackageEVR (id),
rhnChannelNewestPackage.sql:                             REFERENCES rhnPackageEVR (id),
rhnPackage.sql:                             REFERENCES rhnPackageEvr (id),
rhnPackageNEVRA.sql:                             REFERENCES rhnPackageEVR (id),
rhnProxyInfo.sql:                          REFERENCES rhnPackageEVR (id)
rhnSatelliteInfo.sql:                               REFERENCES rhnPackageEVR (id),
rhnServerActionVerifyMissing.sql:                                   REFERENCES rhnPackageevr (id),
rhnServerActionVerifyResult.sql:                                   REFERENCES rhnPackageEVR (id),
rhnServerCrash.sql:                        references rhnPackageEVR(id),
rhnServerPackage.sql:                         REFERENCES rhnPackageEVR (id),
rhnServerProfilePackage.sql:                               REFERENCES rhnPackageEvr (id),
rhnTransactionPackage.sql:                             REFERENCES rhnPackageEVR (id),
rhnVersionInfo.sql:                      REFERENCES rhnPackageEVR (id),

Comment 3 Tomáš Kašpárek 2017-10-18 08:57:32 UTC
spacewalk.git(master): c9d271d5b7491fa5839248b21144a61542301778
spacewalk.git(master): 003da79976965c1325d9e10d4168ad10e164d72b

Comment 4 Tomáš Kašpárek 2017-10-19 11:20:30 UTC
spacewalk.git(master): 0aea96eae65883a04031bac77fd91a21836198ff

Comment 7 Lukáš Hellebrandt 2018-01-22 15:26:43 UTC
Verified with spacewalk-schema-2.5.1-56.

1) Have an EmbPostgres Sat, on it:
2) Manually duplicate one row in rhnpackageevr.
3) spacewalk-dump-schema --to=oracle > bup.sql
4) Have an ExtOracle Sat, on it:
5) scp root@<EmbPostgresSat>:bup.sql root@<EmbPostgresSat>:/tmp/dumped-tables . && cp -r dumped-tables /tmp
6) spacewalk-sql -i < migrate-to-oracle-4clP.sql
7) Get "ORA-00001: unique constraint (RHNSAT.RHN_PE_V_R_E_UQ) violated" -> You reproduced a bug
8) Now, apply the erratum to the ORIGINAL (EmbPostgres) Satellite and goto 1. This time, the export is successfull -> Verified.

Note: For this fix to be effective, you need to both apply the erratum and run the schema upgrade on the Satellite you are migrating FROM.

Comment 10 errata-xmlrpc 2018-02-05 13:57:18 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://access.redhat.com/errata/RHBA-2018:0274


Note You need to log in before you can comment on or make changes to this bug.