It looks like this bug has been there since we put the new tables in production, as older, backfilled data does not seem to be affected:

spark.sql("""
SELECT
  count(1) as count,
  page_redirect_target,
  DATE_TRUNC("month", revision_dt) as d
FROM wmf_content.mediawiki_content_current_v1
WHERE wiki_id = 'enwiki'
  AND page_namespace_id = 0
  AND (page_redirect_target IS NULL OR page_redirect_target = "")
  AND startswith(revision_content_slots['main'].content_body, '#REDIRECT')
GROUP BY d, page_redirect_target
ORDER BY d DESC
""").show(truncate=True)
[Stage 34:=====================================================>(517 + 1) / 518]
+------+--------------------+-------------------+
| count|page_redirect_target|                  d|
+------+--------------------+-------------------+
| 31235|                null|2025-10-01 00:00:00|
|153886|                null|2025-09-01 00:00:00|
| 88680|                null|2025-08-01 00:00:00|
| 70961|                null|2025-07-01 00:00:00|
| 67738|                null|2025-06-01 00:00:00|
| 68260|                null|2025-05-01 00:00:00|
| 79610|                null|2025-04-01 00:00:00|
| 71966|                null|2025-03-01 00:00:00|
| 61984|                null|2025-02-01 00:00:00|
| 57733|                null|2025-01-01 00:00:00|
| 69996|                null|2024-12-01 00:00:00|
| 60081|                null|2024-11-01 00:00:00|
| 73186|                null|2024-10-01 00:00:00|
| 60557|                null|2024-09-01 00:00:00|
| 62763|                null|2024-08-01 00:00:00|
| 49233|                null|2024-07-01 00:00:00|
|     1|                    |2021-02-01 00:00:00|
|     1|                    |2021-01-01 00:00:00|
|     1|                    |2008-05-01 00:00:00|
+------+--------------------+-------------------+

Change #1197648 had a related patch set uploaded (by Aqu; author: AQ):

[mediawiki/extensions/EventBus@master] Draft: Fix missing created_redirect_page

https://gerrit.wikimedia.org/r/1197648

Hm @Antoine_Quhen ! reading Draft: Fix missing created_redirect_page (1197648). IIUC, this means that we have never properly set the created_redirect_page.redirect_page_link field in events, yes?

So currently, on page moves we set:

# info about the page_id that was moved.
page 

#if the page that was moved itself is a redirect, info about the page it points to (which in most cases is probably not true on page moves, unless the page_id being moved is already a redirect)
page.redirect_page_link 

# info about the page that was created and left behind at the old page title as a redirect
created_redirect_page

But we do not properly set

# into about the page that the newly created redirect page points to (which should === page)
created_redirect_page.redirect_page_link

Correct?

If we want to add created_redirect_page.redirect_page_link, we'll need to add it to the schema. Here is how redirect_page_link is added to page object. We'd have to do the same for created_redirect_page here.

If we do want to add this, we may want to consider just adding redirect_page_link to the main page entity schema, so that all representations of a page have the ability to include and model the page they are redirecting to.

However...are we sure we want to do this? Where does the chain of pages inside of pages stop? page_link entity is a page model with an extra field. In the cases of redirect chains, Do we intend to also set e.g. created_redirect_page.redirect_page_link.redirect_page_link.redirect_page_link? I think not!

In the case of page moves, created_redirect_page.redirect_page_link.page_id should === page.page_id. I don't believe (?) that pages can be 'moved' to other wikis, so the interwiki_prefix field in page_link is not useful here. So the page object should have exactly the same info that you are trying to add in created_redirect_page.redirect_page_link.

Perhaps mw_content_history pipeline just needs to account for that fact and use page as the created_redirect_page redirect target?

Perhaps, what we need in EventBus (or maybe even upstream in the PageMoveEvent) is an assertion that $createdRedirectPageTarget == $event->getPageRecordAfter()?

I was looking for past discussion when we created the page_link schema. We did consider adding to entity page schema: T325315#8898609, but I don't see why we chose not to at the time. More discussion is also T331399: Create new mediawiki links change streams based on fragment/mediawiki/state/change/page.

I don't believe (?) that pages can be 'moved' to other wikis, so the interwiki_prefix field in page_link is not useful here

I am still not sure about page moves, but from reading those ^ tickets Daniel did say that it is possible for a redirect to link to another wiki's page. I'd guess that that would be a manually added redirect link though, not one that MW would automatically create for its created_redirect_page.

@Antoine_Quhen, @Ottomata and I met to discuss this ticket.

Although the issue discussed at T400632#11293892 could potentially affect future users of the page_change stream, it does not seem to be the culprit of this ticket.

Instead, we figured that the ingestion code at process_events.py#L206 is the issue. We misinterpreted the purpose of the page_change event created_redirect_page, and we should instead be ingesting page.redirect_page_link.page_title.

xcollazo opened https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/82

Fix page_redirect_target to ingest from the correct stream element.

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/82

Fix page_redirect_target to ingest from the correct stream element.

Change #1197648 abandoned by Aqu:

[mediawiki/extensions/EventBus@master] Draft: Fix missing created_redirect_page

Reason:

T400632

https://gerrit.wikimedia.org/r/1197648

To recap, this fix we merged should fix this issue from Oct 22 and on:

In T400632#11298740, @CodeReviewBot wrote:

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/82

Fix page_redirect_target to ingest from the correct stream element.

However, we still need to fix data from before Oct 22.

We can fix older data by either:

• Running a query like that in T400632#11273972 to identify all offending rows and manually add them to the inconsistencies table.
• Or, we could modify the reconcile algorithm to detect these cases.

In T400632#11383880, @xcollazo wrote:

To recap, this fix we merged should fix this issue from Oct 22 and on:

In T400632#11298740, @CodeReviewBot wrote:

xcollazo merged https://gitlab.wikimedia.org/repos/data-engineering/dumps/mediawiki-content-dump/-/merge_requests/82

Fix page_redirect_target to ingest from the correct stream element.

However, we still need to fix data from before Oct 22.

We can fix older data by either:

• Running a query like that in T400632#11273972 to identify all offending rows and manually add them to the inconsistencies table.
• Or, we could modify the reconcile algorithm to detect these cases.

On second though, as per the description of this issue, running a query to detect the offending rows would be brittle considering we would have to know all the possible REDIRECT translations.

Let's consider wether modifying the reconcile algorithm could work.

Additionally, if consistency is desired, we can UPDATE all old data to have NULL in page_redirect_target instead of the empty string.

Over at T410405 we did an UPDATE to all our content tables so that page_redirect_target is consistently NULL instead of a mix of NULLs and empty string when it is not defined.

Now we still need to do something to reconcile pages that are meant to have page_redirect_target set but do not.

One idea that @APizzata-WMF and I had discussed was to consider the page table from the MariaDB replicas when doing a reconcile. We though that, for daily reconcile, we could just pull the neccesary rows by leveraging the page_touched timestamp-like column. Unfortunately, what we missed in that conversation is that this column is not indexed, thus it would still incur a full table scan to be able to filter on it:

mysql:research@dbstore1008.eqiad.wmnet [enwiki]> SELECT index_name, column_name, seq_in_index FROM INFORM
ATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 'page';
+-----------------------------+------------------+--------------+
| index_name                  | column_name      | seq_in_index |
+-----------------------------+------------------+--------------+
| PRIMARY                     | page_id          |            1 |
| page_name_title             | page_namespace   |            1 |
| page_name_title             | page_title       |            2 |
| page_random                 | page_random      |            1 |
| page_len                    | page_len         |            1 |
| page_redirect_namespace_len | page_is_redirect |            1 |
| page_redirect_namespace_len | page_namespace   |            2 |
| page_redirect_namespace_len | page_len         |            3 |
+-----------------------------+------------------+--------------+
8 rows in set (0.001 sec)

Discussing with @JAllemandou, he suggests that perhaps we need to diverge the daily and monthly reconciles, and to consider heavier reconcile issues like this only on the monthly. On monthly run, we can also wait for the sqooped version of the page table, and leverage that.

Will discuss this further with @APizzata-WMF .

In T400632#11652060, @xcollazo wrote:

Will discuss this further with @APizzata-WMF .

After discussing with @xcollazo we are pushing out a fix that uses the redirect table of MariaDB, here is the MR.

xcollazo updated https://gitlab.wikimedia.org/repos/data-engineering/mediawiki-content-pipelines/-/merge_requests/104

add redirect checks

In T400632#11697165, @APizzata-WMF wrote:

In T400632#11652060, @xcollazo wrote:

Will discuss this further with @APizzata-WMF .

After discussing with @xcollazo we are pushing out a fix that uses the redirect table of MariaDB, here is the MR.

Do we need to do anything else here @APizzata-WMF? Or can we pass back to @MGerlach for verification?

Do we need to do anything else here @APizzata-WMF?

Not really, the mechanism for reconciliation is in place and has been running both daily and monthly. I'd say is validation time.
@MGerlach, please notify us if you find something weird in the data so that we can take action!

Just an FYI that @MGerlach will be out until May 6th -- let me know if you'd like faster validation and I can step in and handle.

In T400632#11856295, @Isaac wrote:

Just an FYI that @MGerlach will be out until May 6th -- let me know if you'd like faster validation and I can step in and handle.

No rush, we can wait.

In T400632#11855621, @xcollazo wrote:

In T400632#11697165, @APizzata-WMF wrote:

In T400632#11652060, @xcollazo wrote:

Will discuss this further with @APizzata-WMF .

After discussing with @xcollazo we are pushing out a fix that uses the redirect table of MariaDB, here is the MR.

Do we need to do anything else here @APizzata-WMF? Or can we pass back to @MGerlach for verification?

Thanks for working on this. I had a quick look at the table:

• the inconsistency in page_redirect_target is resolved
• However, I find 1,308,751 pages in English Wikipedia main namespace that are redirects (content_body contains #REDIRECT) but where page_redirect_target is not set (null). From what I understand from the latest MR, the MariaDB redirect table should add this information?

However, I find 1,308,751 pages in English Wikipedia main namespace that are redirects (content_body contains #REDIRECT) but where page_redirect_target is not set (null). From what I understand from the latest MR, the MariaDB redirect table should add this information?

Will check and come back at you

After some checks here are my results.

Total number of content_body contains #REDIRECT and page_redirect_target is null:

spark.sql("""
 select 
    distinct page_id
from wmf_content.mediawiki_content_current_v1 
where 
wiki_id = 'enwiki' and 
page_redirect_target is  null
and revision_content_slots['main'].content_body like '%#REDIRECT%'
""").count()

1924483

Let's call the results of this query source_p_r

Now let's see how many of these pages are actually redirected in MariaDB (redirect is the view for enwiki).

spark.sql("select distinct page_id from redirect where page_id in  (select distinct page_id from source_p_r)").count()

1908973

The difference between the two, 1924483-1908973=15510, shows that in mediawiki_content_current_v1 we have records with page_redirect_target is null and revision_content_slots['main'].content_body like '%#REDIRECT%' that actually don't have any redirects.

Comparing the numbers of reconciled records with the total redirects from MariaDB:

spark.sql("""
select distinct page_id 
from wmf_content.inconsistent_rows_of_mediawiki_content_history_v1 
where 
page_id in (select distinct page_id from redirect where page_id in  (select distinct page_id from source_p_r)) -- we select only the pages that we selected before
and wiki_id='enwiki'
and array_contains(reasons, 'mismatch_redirect') -- control only redirects
and computation_dt >= '2026-05-01 00:00:00' -- check the most recent reconciled records
""").count()

1908307

Which leaves 666 out that are yet to be reconciled (1908973-1908307).

Let's now check the value that will be used in the MERGE query to update the page_redirect_target column

spark.sql("""
select 
distinct page.redirect_page_link.page_title 
from event.mediawiki_content_history_reconcile_enriched_v1 
where year=2026 
and month=5  
and page.page_id in (select distinct page_id from redirect where page_id in  (select distinct page_id from source_p_r)) 
""").show(truncate=False)

+----------+
|page_title|
+----------+
|null      |
+----------+

Actually by double checking the whole event..mediawiki_content_history_reconcile_enriched_v1 table:

spark.sql("""
select 
distinct page.redirect_page_link.page_title 
from event.mediawiki_content_history_reconcile_enriched_v1 
""").show(truncate=False)

+----------+
|page_title|
+----------+
|null      |
+----------+

So we are detecting the redirects, but the output of the enrichment pipeline does not produce any page.redirect_page_link.page_title therefore we see the situation described.
cc @xcollazo @Ottomata

So we are detecting the redirects, but the output of the enrichment pipeline does not produce any page.redirect_page_link.page_title therefore we see the situation described.

Well that is not good! Now that you established that not even a single reconcile works for redirects, perusing the code points to the root cause:

In process_events.py we expect to see the redirect at:

...
    page.page_title                     AS s_page_title,
    page.redirect_page_link.page_title  AS s_page_redirect_target,                   <<<<<<<<
    revision.editor.user_id             AS s_user_id,
...

This agrees with the page_change schema:

  page:
    title: fragment/mediawiki/state/entity/page
    description: Fields for MediaWiki page entity.
    $id: /fragment/mediawiki/state/entity/page/2.0.0
    $schema: 'https://json-schema.org/draft-07/schema#'
    type: object
    additionalProperties: false
    required:
      - page_id
      - page_title
    properties:
...
      redirect_page_link:
        title: fragment/mediawiki/state/entity/page_link
        description: >
          If this page is currently a redirect, then this field contains
          information about the target page the redirect links to.
        $id: /fragment/mediawiki/state/entity/page_link/1.0.0
        $schema: 'https://json-schema.org/draft-07/schema#'
        type: object
        additionalProperties: false
        properties:
...
          page_id:
            description: The (database) page ID of the page.
            type: integer
            maximum: 9007199254740991
            minimum: 0
          page_title:                                                                <<<<<<<
            description: The normalized title of the page.
            type: string
            minLength: 1
...

However, when we emit the reconcile event, we do not add that at all. This is what we currently do for the page in emit_reconcile_events_to_kafka.py:

SELECT '/mediawiki/page/change/{wmf_event_stream_args['event_schema_version']}' AS `$schema`,
       s.changelog_kind AS changelog_kind,
       s.revision_comment AS comment,
       DATE_FORMAT(s.revision_dt, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") AS dt,
       NAMED_STRUCT(
           'stream', '{wmf_event_stream_args['event_stream_name']}',
           'domain', cd.domain_name
       ) AS meta,
       NAMED_STRUCT(
           'namespace_id', s.page_namespace_id,
           'page_id', s.page_id,
           'page_title', IF(COALESCE(ns.namespace_localized_name, '') = '',
                            s.page_title_no_ns,
                            CONCAT(REPLACE(ns.namespace_localized_name, ' ', '_'), ':', s.page_title_no_ns)
                         )
       ) AS page,
...

No definition of page.redirect_page_link at all. Whoops!

a-pizzata opened https://gitlab.wikimedia.org/repos/data-engineering/mediawiki-content-pipelines/-/merge_requests/112

fix for page_redirect_target

a-pizzata merged https://gitlab.wikimedia.org/repos/data-engineering/mediawiki-content-pipelines/-/merge_requests/112

fix for page_redirect_target

a-pizzata opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2239

new version for mw-content

a-pizzata merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2239

new version for mw-content

a-pizzata merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2239

after this merge we cam see reconciled records with the correct page.redirect_page_link.

Will wait the monthly reconcile to see how the situation described by @MGerlach has evolved.

spark.sql("""
 select 
    distinct page_id
from wmf_content.mediawiki_content_current_v1 
where 
wiki_id = 'enwiki' and 
page_redirect_target is  null
and revision_content_slots['main'].content_body like '%#REDIRECT%'
""").count()

15538

spark.sql("select distinct page_id from redirect where page_id in  (select distinct page_id from source_p_r)").count()

339

The difference between the two, 15538-339= 15199, shows that in mediawiki_content_current_v1 we have records with page_redirect_target is null and revision_content_slots['main'].content_body like '%#REDIRECT%' that actually don't have any redirects. ( the result is close to what we had before) .

spark.sql("""
select distinct page_id 
from wmf_content.inconsistent_rows_of_mediawiki_content_history_v1 
where 
page_id in (select distinct page_id from redirect where page_id in  (select distinct page_id from source_p_r)) -- we select only the pages that we selected before
and wiki_id='enwiki'
and array_contains(reasons, 'mismatch_redirect') -- control only redirects
and computation_dt >= '2026-06-01 00:00:00' -- check the most recent reconciled records
""").count()

334

Which leaves 5 out that are yet to be reconciled (339-334).

The numbers are way better now.

@MGerlach if you can confirm the improvement and have no further concerns, I'll go ahead and close the ticket.

In T400632#12022350, @APizzata-WMF wrote:

@MGerlach if you can confirm the improvement and have no further concerns, I'll go ahead and close the ticket.

Thanks for your continued work on this @APizzata-WMF .
I checked the mediawiki_content_current (for enwiki)

• The inconsistency is fixed (no more empty strings)
• I found 668 pages with page_redirect_target=null when the wikitext contained #REDIRECT. Comparing with the overall number of redirects where page_redirect_target is set correctly (>10M pages), I would consider this small number of cases acceptable as it might be difficult to catch some edge cases.
• Looking into some examples of the 668 cases, there might be a pattern how the #REDIRECT is missed. One example: https://en.wikipedia.org/wiki/Carolina_del_Norte There is a template before the ##REDIRECT command that adds a messagebox and links to the discussion about the redirect. Not sure if it this is the cause though and whether it is feasible/worth to try to fix this.

Thanks again.