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
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),
spacewalk.git(master): c9d271d5b7491fa5839248b21144a61542301778 spacewalk.git(master): 003da79976965c1325d9e10d4168ad10e164d72b
spacewalk.git(master): 0aea96eae65883a04031bac77fd91a21836198ff
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.
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