Moving this bug to POST for triage into Satellite since the upstream issue https://projects.theforeman.org/issues/34414 has been resolved.

This bugfix is NOT sufficient!

Copying and explaining observation from https://bugzilla.redhat.com/show_bug.cgi?id=2044314#c44 :

In Sat6.11.1 with the fix applied, syncing 7Server RHSCL repo ended up in mail notification:

Email:
SYNC SUMMARY
The synchronization of "Red Hat Software Collections RPMs for Red Hat Enterprise Linux 7 Server x86_64 7Server" has completed. Below is a summary of new errata. A large number of errata were synced for this repository, so only the first 100 are shown. 

New Errata
44 SECURITY	
28 BUGFIX	
74 ENHANCEMENT


What errata are in the mail? Generally those having katello_repository_errata.updated_at being *updated* during the sync. Which includes e.g. RHBA-2015:1068 or RHEA-2015:0883 or RHEA-2014:1749 .

Why such old errata are "updated"? Here is the reasoning:
- there is just one record for katello_errata.pulp_id = 'RHBA-2015:1068', *not* updated during the extra sync (that sent the errata in mail).
- BUT katello does not list errata per katello_errata - it lists them from katello_repository_errata.updated_at, which *was* updated during the last sync
- when this record is updated? Whenever pulp updates content of its repo, like:

rpm_updaterecord :
            content_ptr_id            |       id       |    updated_date     | ..
 2a6c640c-31d8-4914-a106-519cab99ab0a | RHBA-2015:1068 | 2015-06-04 00:00:00 | ..
 a57aad7d-d9d6-48ec-8601-6645bf9b7b5b | RHBA-2015:1068 | 2015-06-04 00:00:00 

The errata instances differ in digest only (see below). One "errata instance" was added to *previous* repo version (per core_repositoryversion), then removed and new errata instance added to the current repo version.

- why pulp replaced one errata instance by another? B'cos during the repo sync, it detected (the same) errata under a different digest. Errata digest is a hash from update record in repo metadata, per https://github.com/pulp/pulp_rpm/blob/main/pulp_rpm/app/advisory.py#L418-L431

- and indeed, some time ago, the RHSCL updateinfo had:

  <update from="release-engineering" status="final" type="bugfix" version="1">
    <id>RHBA-2015:1068</id>
    <title>nginx16 bug fix and enhancement update</title>
    <issued date="2015-06-04 00:00:00"/>
    <updated date="2015-06-04 00:00:00"/>
    <rights>Copyright 2015 Red Hat Inc</rights>
    <release></release>
    <pushcount>4</pushcount>
    <severity></severity>
    <summary>Updated nginx16 packages that ..

while now:

  <update status="final" from="release-engineering" version="1" type="bugfix">
    <id>RHBA-2015:1068</id>
    <issued date="2015-06-04 00:00:00" />
    <title>nginx16 bug fix and enhancement update</title>
    <rights>Copyright 2015 Red Hat Inc</rights>
    <solution>Before applying this update, ..

Content is the same, just reordered. So hash of it is different.


Therefore, whenever an updateinfo of an errata changes *ordering* of otherwise identical items, for one repo (e.g. on CDN), then katello marks the errata as "New Errata" in the notification mail.


Possible improvements:
- ensure updateinfo of same errata is consistent over time on CDN - this would be hardly achieved
- change digest calculation in pulp to ignore ordering of updateinfo items - possible, but redundantly complex (I think), also prone to some different subtle change that would end up in different digest of the same errata
- change katello email notification : do not filter errata's updated_at from katello_repository_errata, but from inner join of katello_errata:

currently:
foreman=# SELECT COUNT(kre.updated_at),kre.updated_at FROM katello_errata AS ke INNER JOIN katello_repository_errata AS kre on kre.erratum_id = ke.id WHERE kre.repository_id = 19 GROUP BY kre.updated_at ORDER BY kre.updated_at DESC;
 count |     updated_at      
-------+---------------------
   146 | 2022-07-30 11:34:24
   518 | 2022-07-30 11:18:48
(2 rows)

foreman=# 

(that is why 146 errata sent in the mail)

proposal:
foreman=# SELECT COUNT(ke.updated_at),ke.updated_at FROM katello_errata AS ke INNER JOIN katello_repository_errata AS kre on kre.erratum_id = ke.id WHERE kre.repository_id = 19 GROUP BY ke.updated_at ORDER BY ke.updated_at DESC;
 count |         updated_at         
-------+----------------------------
     1 | 2022-07-30 11:34:15.839358
     1 | 2022-07-30 11:18:48.426889
     1 | 2022-07-30 11:18:48.414097
     1 | 2022-07-30 11:18:48.400287
..

as really just *one* errata was created/updated by the sync ("Added Rpms: 22, Errata: 1").

I.e. in /usr/share/gems/gems/katello-4.3.0.42/app/lib/actions/katello/repository/errata_mail.rb :

          repo = ::Katello::Repository.find(input[:repo])
          last_updated = repo.repository_errata.order('updated_at ASC').last.try(:updated_at) || Time.now
          users = ::User.select { |user| user.receives?(:sync_errata) && user.organization_ids.include?(repo.organization.id) && user.can?(:view_products, repo.product) }.compact
          errata = ::Katello::Erratum.where(:id => repo.repository_errata.where('katello_repository_errata.updated_at > ?', last_updated).pluck(:erratum_id))

change that to:

          errata = ::Katello::Erratum.where(:id => repo.repository_errata).where('updated_at > ?', last_updated).pluck(:erratum_id))

(I havent checked the syntax)


Partha, as the Repositories team lead, does it sound reasonable? Or could this lead to some other issues (I dont see in my tunnelled view of current problem)?

Checked the syntax of my proposal and it should be:

          errata = ::Katello::Erratum.where(:id => repo.repository_errata.pluck(:erratum_id)).where('updated_at < ?', last_updated)

I raised https://bugzilla.redhat.com/show_bug.cgi?id=2116375 for the above bug (as I cant reopen the 6.11.1 closed clone).

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 (Important: Satellite 6.12 Release), 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/RHSA-2022:8506

The needinfo request[s] on this closed bug have been removed as they have been unresolved for 120 days