Bug 1410737

Summary:	constraint rhn_pe_v_r_e_uq in rhnpackageevr allows duplicated rows in postgres db
Product:	Red Hat Satellite 5	Reporter:	Ales Dujicek <adujicek>
Component:	Other	Assignee:	Tomáš Kašpárek <tkasparek>
Status:	CLOSED ERRATA	QA Contact:	Lukáš Hellebrandt <lhellebr>
Severity:	unspecified	Docs Contact:
Priority:	unspecified
Version:	580	CC:	lhellebr, mkorbel, tkasparek, tlestach
Target Milestone:	---
Target Release:	---
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	If docs needed, set a value
Doc Text:		Story Points:	---
Clone Of:		Environment:
Last Closed:	2018-02-05 13:57:18 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	1450111, 1450940

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