CentralAuth's localuser table contains many nulls and duplicate mappings
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	xcollazo
	Nov 26 2025, 6:27 PM

Project Tags

Referenced Files

None

Subscribers

View All 10 Subscribers

Description

While working on T410688 to integrate lu_global_id into a data engineering pipeline, we found out that data on CentralAuth's localuser table contains both NULLs as well as duplicate mappings.

Here are examples from a snapshot of the table taken on 2025-10 that we have available in the datalake. Note that you should ignore any references below to wiki_db and snapshot, as these columns are added when ingesting the data into the datalake and are not to be found on MariaDB.

In T410688#11409702, @xcollazo wrote:

In T410688#11408750, @JAllemandou wrote:

Funnily enough, now there are now 4.3M rows more on the target table than the source

I have investigated @xcollazo finding, and it's not great: the centralauth.local_user table contains rows with NULL values for local_user_id for many projects, and for other projects (ocwiki and outreachwiki for the least) it has multiple rows for the same local_user_id and global_user_id...
This explains the row duplication :(
I have been trying the MERGE approach on my test table removing corrupted data, but the job still fails. I'll continue my investigations in that direction.

Looked at this as well. Sharing issues with the table for completeness:

spark.sql("""
SELECT count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE snapshot='2025-10'
  AND wiki_db='centralauth'
  AND lu_local_id IS NULL
""").show(100, truncate=False)

+-----+
|count|
+-----+
|6365 |
+-----+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-10'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|221780|
+------+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-10'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|221647|
+------+

spark.sql("""
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-10'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
  ORDER BY count DESC
""").show(100, truncate=False)

+-----+-------------+-----------+------------+
|count|lu_wiki      |lu_local_id|lu_global_id|
+-----+-------------+-----------+------------+
|2573 |enwiki       |null       |null        |
|536  |eswiki       |null       |null        |
|164  |ptwiki       |null       |null        |
|118  |jawiki       |null       |null        |
|78   |frwiki       |null       |null        |
|68   |idwiki       |null       |null        |
|49   |ruwiki       |null       |null        |
|45   |metawiki     |null       |null        |
|44   |viwiki       |null       |null        |
|36   |dewiki       |null       |null        |
|35   |trwiki       |null       |null        |
|19   |mediawikiwiki|null       |null        |
|15   |itwiki       |null       |null        |
|15   |fawiki       |null       |null        |
|12   |enwiki       |null       |0           |
|9    |commonswiki  |null       |null        |
|8    |simplewiki   |null       |null        |
|7    |hewiki       |null       |null        |
|7    |svwiki       |null       |null        |
|6    |mswiki       |null       |null        |
|6    |nlwiki       |null       |null        |
|6    |zhwiki       |null       |null        |
|6    |plwiki       |null       |null        |
|5    |srwiki       |null       |null        |
|5    |azwiki       |null       |null        |
|5    |mkwiki       |null       |null        |
|4    |sqwiki       |null       |null        |
|4    |arzwiki      |null       |null        |
|4    |cawiki       |null       |null        |
|3    |cswiki       |null       |null        |
|3    |enwiktionary |null       |null        |
|3    |arwiki       |null       |null        |
|3    |fiwiki       |null       |null        |
|3    |metawiki     |null       |0           |
|2    |ocwiki       |30104      |43352052    |
|2    |ocwiki       |44967      |64797735    |
|2    |ocwiki       |33093      |1469800     |
|2    |ocwiki       |33359      |48463092    |
|2    |ocwiki       |15703      |573928      |
|2    |ocwiki       |55770      |62357577    |
|2    |ocwiki       |34908      |6532839     |
|2    |ocwiki       |1782       |417258      |
|2    |ocwiki       |25686      |13967321    |
|2    |ocwiki       |53445      |74506123    |
|2    |ocwiki       |31591      |44800838    |
|2    |ocwiki       |33969      |1038620     |
|2    |olowiki      |6501       |72655905    |
|2    |ocwiki       |23757      |16898251    |
|2    |ocwiki       |745        |5731        |
|2    |ocwiki       |27884      |6090122     |
|2    |ocwiki       |51960      |72164030    |
|2    |ocwiki       |57836      |62111448    |
|2    |ocwiki       |46918      |68928761    |
...

Seems that, other than nulls, the repeats on ocwiki and friends are duplicates of the same mapping, so perhaps all we need to do is filter the table for lu_local_id IS NULL before joining on it.

Related Objects

Mentioned In: T385310: Could not find local user data for {username}@{wikiId} (2025)
T411079: `central_auth.local_user` table contains corrupted records
Mentioned Here: T389666: NEW/CHANGE FEATURE REQUEST: make available the centralauth.globaluser table in Data Lake
T411152: Create global accounts for users where lu_global_id is null and only one row for a username
T303590: Fix localuser rows with lu_local_id or lu_global_id that aren't correctly set
T388983: localuser rows where lu_global_id IS NOT NULL AND lu_local_id IS NULL not fixed by populateLocalAndGlobalIds.php
T410688: Implement a new pipeline and table with reconciled historical revision data

Event Timeline

xcollazo created this task.Nov 26 2025, 6:27 PM

Restricted Application added a project: MediaWiki-Core-Platform-Team. · View Herald TranscriptNov 26 2025, 6:27 PM

Restricted Application added subscribers: hubaishan, Nemoralis, Huji and 2 others. · View Herald Transcript

xcollazo mentioned this in T411079: `central_auth.local_user` table contains corrupted records.Nov 26 2025, 6:38 PM

xcollazo merged a task: T411079: `central_auth.local_user` table contains corrupted records.

xcollazo added a subscriber: Ladsgroup.

Restricted Application added a subscriber: alaa. · View Herald TranscriptNov 26 2025, 6:38 PM

Nemoralis unsubscribed.Nov 26 2025, 8:00 PM

Huji unsubscribed.Nov 26 2025, 9:14 PM

So the cases where lu_global_id IS null (3913 accounts) would be rows where the user was never actually globalised.

Looking at migrateAccount.php:

		$this->addDescription( <<<'TEXT'
			Migrates the specified usernames to a global account if email matches
			and there are no conflicts. Assumes the localuser and globaluser tables
			are up to date (e.g. migratePass0 has been run).
			TEXT );

Which is reasonable... If we can't work out if a user is the same as on another wiki.. We can't globalise them.

However, where the account only exists on one wiki (ie it's unique), and for example has no email on either... Well, I think we can globalise those, which will set a lu_global_id and because it has a wiki account, that lu_local_id will not be null either.

This is attachbroken in migrateAccount.php:

			if ( $this->getOption( 'attachbroken', false ) ) {
				// This option is for T63876 / T41996 where the account has
				// an empty password and email set, and became unattached.
				// Since there is no way an account can have an empty password manually
				// it has to be due to a CentralAuth bug. So just attach it then.
				// But just to be on the safe side, check that it also has 0 edits.
				foreach ( $unattached as $wiki => $local ) {
					if ( $local['email'] === '' && $local['password'] === ''
						&& $local['editCount'] === '0'
					) {
						$this->output( "ATTACHING: $username@$wiki\n" );
						// Ironically, the attachment is made due to lack of a password.
						$central->attach(
							$wiki, 'password', /** $sendToRC = */ !$this->suppressRC
						);
					}
				}
			}

There are slightly less accounts with no lu_global_id (3913) than those with no lu_local_id (4042)...

wikiadmin2023@10.192.48.205(centralauth)> select lu_name, count(*) as cnt from localuser where lu_global_id is null group by lu_name having cnt > 1;
+------------------------------+-----+
| lu_name                      | cnt |
+------------------------------+-----+
| Besir.arifi                  |   2 |
| Fabriciotg                   |   2 |
| Mazzikamadura                |   2 |
| SELFEDITION                  |   2 |
| Trần Nguyễn Hạo Thiên        |   2 |
+------------------------------+-----+
5 rows in set (9 min 55.617 sec)

So basically we can globalise most of those accounts...

I don't know if my other script runs have tidied all of those things up... But I doubt it, as they don't delete rows... But it will have fixed some of the lu_local_id is null entries as per T303590#11411628

Are those ocwiki dupes legit? One picked at random...

wikiadmin2023@10.192.48.205(centralauth)> select * from localuser where lu_wiki='ocwiki' and (lu_global_id = 16898251 or lu_local_id = 23757 );
+---------+---------+-----------------------+--------------------+-------------+--------------+----------------------+
| lu_wiki | lu_name | lu_attached_timestamp | lu_attached_method | lu_local_id | lu_global_id | lu_attachment_method |
+---------+---------+-----------------------+--------------------+-------------+--------------+----------------------+
| ocwiki  | IIM 78  | 20150726154950        | login              |       23757 |     16898251 |                 NULL |
+---------+---------+-----------------------+--------------------+-------------+--------------+----------------------+
1 row in set (0.035 sec)

I'd expect to see 2 rows?

wikiadmin2023@10.192.48.205(centralauth)> show indexes from localuser;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| localuser |          0 | PRIMARY  |            1 | lu_wiki     | A         |      162658 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          0 | PRIMARY  |            2 | lu_name     | A         |   227233813 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          1 | lu_name  |            1 | lu_name     | A         |   227233813 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          1 | lu_name  |            2 | lu_wiki     | A         |   227233813 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.001 sec)

OWresch-WMF moved this task from Inbox, needs triage to Needs refinement on the MediaWiki-Core-Platform-Team board.Nov 27 2025, 3:33 PM

Tgr mentioned this in T385310: Could not find local user data for {username}@{wikiId} (2025).Nov 30 2025, 11:40 AM

In T411116#11411658, @Reedy wrote:

I don't know if my other script runs have tidied all of those things up... But I doubt it, as they don't delete rows... But it will have fixed some of the lu_local_id is null entries as per T303590#11411628

Are those ocwiki dupes legit? ...

Once the next snapshot of the localuser table is available in the datalake I will rerun the query, see if we still see the issues. It could very well be a datalake ingest issue for ocwiki and olowiki.

Restricted Application added a subscriber: Dragoniez. · View Herald TranscriptDec 3 2025, 7:02 PM

Don't know the state of things over there... But I've fixed up various amounts more erroneous rows now too...

Basically down to what's left in T411152: Create global accounts for users where lu_global_id is null and only one row for a username, but most progress being followed on T303590: Fix localuser rows with lu_local_id or lu_global_id that aren't correctly set.

Here is a rerun of the queries I had shared originally, but from the latest snapshot of the localuser table that we keep on the data lake:

spark.sql("""
SELECT count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE snapshot='2025-12'
  AND wiki_db='centralauth'
  AND lu_local_id IS NULL
""").show(100, truncate=False)

+-----+
|count|
+-----+
|500  |
+-----+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-12'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|224540|
+------+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-12'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|224523|
+------+

spark.sql("""
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2025-12'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
  ORDER BY count DESC
""").show(100, truncate=False)

+-----+------------+-----------+------------+
|count|lu_wiki     |lu_local_id|lu_global_id|
+-----+------------+-----------+------------+
|387  |enwiki      |null       |null        |
|12   |enwiki      |null       |0           |
|3    |metawiki    |null       |0           |
|2    |ocwiki      |25912      |44753940    |
|2    |ocwiki      |57704      |62182516    |
|2    |ocwiki      |37423      |33284779    |
|2    |ocwiki      |6535       |2377249     |
|2    |ocwiki      |33713      |48855318    |
|2    |ocwiki      |53423      |73074105    |
|2    |ocwiki      |9242       |8500410     |
|2    |ocwiki      |20749      |16901880    |
|2    |ocwiki      |29146      |49416428    |
|2    |ocwiki      |43697      |65596447    |
|2    |ocwiki      |24300      |42829695    |
|2    |ocwiki      |29868      |31779714    |
|2    |ocwiki      |44115      |30205466    |
|2    |ocwiki      |23191      |34997664    |
|2    |ocwiki      |8598       |7583641     |
|2    |ocwiktionary|1234       |11268188    |
...

So indeed we have less data with NULLs. However for the duplicates in, say, ocwiki, I can confirm that they indeed are not reproducible in production:

xcollazo@stat1011:~$ analytics-mysql centralauth

MariaDB [centralauth]> select * from localuser where lu_wiki = 'ocwiki' and lu_local_id = 25912;
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
| lu_wiki | lu_name    | lu_attached_timestamp | lu_attached_method | lu_local_id | lu_global_id | lu_attachment_method |
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
| ocwiki  | Alexey Rdz | 20160310020440        | login              |       25912 |     44753940 |                 NULL |
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
1 row in set (0.047 sec)

MariaDB [centralauth]> select * from localuser where lu_wiki = 'ocwiki' and lu_local_id = 57704;
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
| lu_wiki | lu_name    | lu_attached_timestamp | lu_attached_method | lu_local_id | lu_global_id | lu_attachment_method |
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
| ocwiki  | C. Scheler | 20250302011000        | login              |       57704 |     62182516 |                 NULL |
+---------+------------+-----------------------+--------------------+-------------+--------------+----------------------+
1 row in set (0.042 sec)

Thus I now suspect that there may be something wrong in our ingestion of this table into the datalake.

APizzata-WMF claimed this task.Mar 13 2026, 8:38 AM

Rerun of the queries that @xcollazo has done in the past:

spark.sql("""
SELECT count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE snapshot='2026-02'
  AND wiki_db='centralauth'
  AND lu_local_id IS NULL
""").show(100, truncate=False)

+-----+
|count|
+-----+
|0    |
+-----+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2026-02'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|227271|
+------+

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2026-02'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
)
""").show(100, truncate=False)

+------+
|count |
+------+
|227271|
+------+

spark.sql("""
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2026-02'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
  ORDER BY count DESC
""").show(100, truncate=False)

+-----+------------+-----------+------------+
|count|lu_wiki     |lu_local_id|lu_global_id|
+-----+------------+-----------+------------+
|2    |ocwiki      |8802       |8542373     |
|2    |ocwiki      |31241      |13362063    |
|2    |ocwiki      |10453      |10474181    |
|2    |ocwiki      |35717      |20867332    |
|2    |ocwiki      |6421       |5064862     |
|2    |ocwiki      |14970      |14015316    |
|2    |ocwiki      |28328      |34564594    |
|2    |ocwiktionary|2065       |31561985    |
|2    |ocwiktionary|5197       |6124343     |
|2    |omwiki      |856        |10487       |
|2    |omwiki      |12934      |79753389    |
|2    |orwiki      |11238      |46249589    |
|2    |orwiki      |40762      |27792359    |
|2    |orwiki      |18609      |55179228    |
|2    |orwiki      |37159      |63291883    |
|2    |orwiki      |1340       |1623        |
|2    |orwiki      |4246       |15197602    |
|2    |orwiki      |10235      |14446094    |
|2    |orwiki      |16601      |53291956    |
|2    |orwiki      |12285      |47947896    |
|2    |oswiki      |18889      |60190808    |
|2    |oswiki      |5796       |7279901     |
|2    |oswiki      |19075      |60970042    |

The are no more nulls, also for last month:

spark.sql("""
SELECT count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE snapshot='2026-01'
  AND wiki_db='centralauth'
  AND lu_local_id IS NULL
""").show(100, truncate=False)

+-----+
|count|
+-----+
|0    |
+-----+

Some curious findings came up during my analysis:

import wmfdata

spark = wmfdata.spark.create_custom_session(
    master="yarn",
    spark_config={
        "spark.shuffle.service.name": "spark_shuffle_3_3",
        "spark.shuffle.service.port": "7338",
        "spark.yarn.archive": "hdfs:///user/spark/share/lib/spark-3.3.2-assembly.zip",
        "spark.dynamicAllocation.maxExecutors": 24,
        "spark.executor.memory": "16g",
        "spark.driver.memory": "12g",
        "spark.driver.cores": "4",
        "spark.executor.cores": "2",
        "spark.sql.shuffle.partitions": 10240,
        "spark.driver.maxResultSize": "8G",
        "spark.rpc.askTimeout": "300s",
        "spark.jars.packages": (
            "org.apache.iceberg:iceberg-spark-runtime-3.3_2.12:1.2.1,"
            "com.mysql:mysql-connector-j:8.2.0,"
            "org.apache.spark:spark-avro_2.12:3.3.2"
        ),
        "spark.jars.ivySettings": "/etc/maven/ivysettings.xml",
        "spark.sql.sources.useV1SourceList": "avro",
    },
)

# spark session to load avro fiels


from pyspark.sql.functions import input_file_name, col,lit
sources = ['2025-10','2025-11','2025-12', '2026-01','2026-02']
df = None

for source in sources:
    if df is None:
        df = (
            spark.read
                 .format("avro").load(f"hdfs://analytics-hadoop/wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot={source}/wiki_db=centralauth/")
        )
        df =  df.withColumn("source_file", input_file_name())
        df =  df.withColumn("source", lit(source))
    else:
        df_to_union= (spark.read
                 .format("avro").load(f"hdfs://analytics-hadoop/wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot={source}/wiki_db=centralauth/")
                     )
        df_to_union = df_to_union.withColumn("source_file", input_file_name())
        df_to_union = df_to_union.withColumn("source",lit(source))
        df = df.union(df_to_union
        )

df.createOrReplaceTempView("joined_table")
spark.sql("cache table joined_table") 

spark.sql("""
with duplicates as (
select source, lu_wiki, lu_local_id
from joined_table
group by 1,2,3
having count(*) >1)
select count(1), t.source, split(t.source_file, 'wiki_db=centralauth/')[1]
from joined_table t
join duplicates using (source,lu_wiki, lu_local_id)
group by 2,3
order by 2,3
""").show(truncate=False)

+--------+-------+-----------------------------------------------+
|count(1)|source |split(source_file, wiki_db=centralauth/, -1)[1]|
+--------+-------+-----------------------------------------------+
|221608  |2025-10|part-m-00017.avro                              |
|221608  |2025-10|part-m-00019.avro                              |
|223126  |2025-11|part-m-00017.avro                              |
|223126  |2025-11|part-m-00019.avro                              |
|224520  |2025-12|part-m-00017.avro                              |
|224520  |2025-12|part-m-00019.avro                              |
|226036  |2026-01|part-m-00017.avro                              |
|226036  |2026-01|part-m-00019.avro                              |
|227271  |2026-02|part-m-00017.avro                              |
|227271  |2026-02|part-m-00019.avro                              |
+--------+-------+-----------------------------------------------+

# all the duplicates are across the files 17 and  19 

spark.sql("""
with duplicates as (
select source, lu_wiki, lu_local_id
from joined_table
group by 1,2,3
having count(*) >1)
select count(1), count(lu_wiki), t.source
from joined_table t
join duplicates using (source,lu_wiki, lu_local_id)
group by 3
order by 3
""").show(truncate=False)

+--------+-----------------------+-------+
|count(1)|count(DISTINCT lu_wiki)|source |
+--------+-----------------------+-------+
|443216  |11                     |2025-10|
|446252  |11                     |2025-11|
|449040  |11                     |2025-12|
|452072  |11                     |2026-01|
|454542  |11                     |2026-02|
+--------+-----------------------+-------+

# always the same number of wikipedias

spark.sql("""
with duplicates as (
select source, lu_wiki, lu_local_id
from joined_table
group by 1,2,3
having count(*) >1)
select count(1), t.source,substr(lu_wiki,1,1)
from joined_table t
join duplicates using (source,lu_wiki, lu_local_id)
group by 2,3
order by 2,3
""").show(truncate=False)

+--------+-------+---------------------+
|count(1)|source |substr(lu_wiki, 1, 1)|
+--------+-------+---------------------+
|443216  |2025-10|o                    |
|446252  |2025-11|o                    |
|449040  |2025-12|o                    |
|452072  |2026-01|o                    |
|454542  |2026-02|o                    |
+--------+-------+---------------------+

# all the wikis start with the same letter

spark.sql("""

select count(distinct lu_wiki), t.source
from joined_table t
where substr(lu_wiki,1,1) ='o'
group by 2
order by 2
""").show(truncate=False)

+-----------------------+-------+
|count(DISTINCT lu_wiki)|source |
+-----------------------+-------+
|11                     |2025-10|
|11                     |2025-11|
|11                     |2025-12|
|11                     |2026-01|
|11                     |2026-02|
+-----------------------+-------+

# and all the duplicates are actually in the wikis starting with o

I will try to run the sqoop script to my local folder to see if we still get duplicates and maybe check our config

APizzata-WMF added a project: Data-Engineering (Q3 FY25/26 January 1st - March 31th).Mar 16 2026, 4:07 PM

APizzata-WMF moved this task from Next Up to In progress on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.

possible cause: https://issues.apache.org/jira/browse/SQOOP-3262

Executed the following commands:

screen -S apizzata-sqoop-10-64

PYTHONPATH=/home/a-pizzata/sqoop-test/refinery/python
python3 /home/a-pizzata/sqoop-test/refinery/bin/sqoop-mediawiki-tables \
--job-name sqoop-centralauth-tables-a-pizzata1 \
--wiki-file "/home/a-pizzata/sqoop-test/centralauth.csv" \
--output-dir /user/a-pizzata/wmf/data/raw/mediawiki/tables \
--tables centralauth_globaluser,centralauth_localuser \
--user research \
--password-file /user/hive/warehouse/apizzata/key/mysql-analytics-research-client-pw.txt \
--partition-name snapshot \
--partition-value apizzata1 \
--mappers 64 \
--processors 10 \
--output-format avrodata \
--sample-wiki-for-jar centralauth \
--hdfs-tmp-path "/user/a-pizzata/wmf/data-tmp/" \
--log-file /home/a-pizzata/sqoop-test/sqoop-info-and-above.log \
--verbose

And

screen -S apizzata-sqoop-32-32

PYTHONPATH=/home/a-pizzata/sqoop-test/refinery/python
python3 /home/a-pizzata/sqoop-test/refinery/bin/sqoop-mediawiki-tables \
--job-name sqoop-centralauth-tables-a-pizzata1 \
--wiki-file "/home/a-pizzata/sqoop-test/centralauth.csv" \
--output-dir /user/a-pizzata/wmf/data_32/raw/mediawiki/tables \
--tables centralauth_globaluser,centralauth_localuser \
--user research \
--password-file /user/hive/warehouse/apizzata/key/mysql-analytics-research-client-pw.txt \
--partition-name snapshot \
--partition-value apizzata1 \
--mappers 32 \
--processors 32 \
--output-format avrodata \
--sample-wiki-for-jar centralauth \
--hdfs-tmp-path "/user/a-pizzata/wmf/data-tmp/" \
--log-file /home/a-pizzata/sqoop-test/sqoop-info-and-above.log \
--verbose

The two datasets should be the same but, unfortunately they are not!
The dataset created with 10 processors and 64 mappers contains duplicates (as the regular monthly run) while the 32 processors and 32 mappers does not.

df_32_32 =  (
    spark.read
        .format("avro").load(f"hdfs://analytics-hadoop/user/a-pizzata/wmf/data_32/raw/mediawiki/tables/centralauth_localuser/snapshot=apizzata1/wiki_db=centralauth/")
        )
       
df_32_32 = df_32_32.withColumn("source_file", input_file_name())
df_32_32.createOrReplaceTempView("table_32_32")
spark.sql("cache table table_32_32") 

spark.sql("""
select lu_wiki, count(*) from (
select  lu_wiki, lu_local_id
from table_32_32
group by 1,2
having count(*) >1)
group by 1""").show()

+-------+--------+
|lu_wiki|count(1)|
+-------+--------+
+-------+--------+

spark.sql("""
select  count(distinct source_file)
from table_32_32""").show()

+---------------------------+
|count(DISTINCT source_file)|
+---------------------------+
|                         17|
+---------------------------+

df_10_64 =  (
    spark.read
        .format("avro").load(f"hdfs://analytics-hadoop/user/a-pizzata/wmf/data/raw/mediawiki/tables/centralauth_localuser/snapshot=apizzata1/wiki_db=centralauth/")
        )
       
df_10_64 = df_10_64.withColumn("source_file", input_file_name())
df_10_64.createOrReplaceTempView("table_10_64")
spark.sql("cache table table_10_64") 

spark.sql("""
select lu_wiki, count(*) from (
select  lu_wiki, lu_local_id
from table_10_64
group by 1,2
having count(*) >1)
group by 1""").show()

+------------+--------+
|     lu_wiki|count(1)|
+------------+--------+
|      ocwiki|   62443|
| ocwikibooks|    2030|
|ocwiktionary|    5927|
|     olowiki|    8501|
|      omwiki|   13643|
|omwiktionary|    3466|
|      orwiki|   41028|
|orwikisource|    2981|
|orwiktionary|    3324|
|      oswiki|   27731|
|outreachwiki|   56895|
+------------+--------+

spark.sql("""
select  count(distinct source_file)
from table_10_64""").show()

+---------------------------+
|count(DISTINCT source_file)|
+---------------------------+
|                         27|
+---------------------------+


spark.sql("""
with duplicates as (
select lu_wiki, lu_local_id
from table_10_64
group by 1,2
having count(*) >1),
 duplicate_count as(
 select distinct duplicate_counts from (
select count(1) as duplicate_counts, split(t.source_file, 'wiki_db=centralauth/')[1] 
from table_10_64 t
join duplicates using (lu_wiki, lu_local_id)
group by 2)
)
select count(*),  duplicate_counts, split(t.source_file, 'wiki_db=centralauth/')[1] 
from table_10_64 t
join duplicate_count 
where source_file like '%17.avro%' or  source_file like '%19.avro%'
group by 2,3
order by 2,3
""").show(truncate=False)

+--------+----------------+-----------------------------------------------+
|count(1)|duplicate_counts|split(source_file, wiki_db=centralauth/, -1)[1]|
+--------+----------------+-----------------------------------------------+
|227969  |227969          |part-m-00017.avro                              |
|6151552 |227969          |part-m-00019.avro                              |
+--------+----------------+-----------------------------------------------+


spark.sql("""
with duplicates as (
select  lu_wiki, lu_local_id
from table_10_64
group by 1,2
having count(*) >1)
select count(1),substr(lu_wiki,1,1)
from table_10_64 t
join duplicates using (lu_wiki, lu_local_id)
group by 2
order by 2
""").show(truncate=False)

+--------+---------------------+
|count(1)|substr(lu_wiki, 1, 1)|
+--------+---------------------+
|455938  |o                    |
+--------+---------------------+

Therefore the duplication happens due to a combination of the bug posted here T411116#11714477 and of the number of processors/mappers.

The 32/32 is the configuration that @Ottomata used in his first run for the pipeline (T389666#11185908).

What should be next step? How do we configure these parameters? Is there a reference guide that can help me?

APizzata-WMF moved this task from In progress to In Review on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mar 18 2026, 3:15 PM

Let's ask @JAllemandou and @Snwachukwu , my sqoop fu is mostly limited to what you linked.

My way of dealing with that would be to change the puppet code to using 32 mappers.
This will involve creating anew variable and update the template, not great, but at least we'll have a solution.
And obviously, in addition to the code change, add a comment referencing your previous comment to explain why we do that.

I've read our code and the bug report again, there is something I don't understand: the bug is supposed to happen when splitting a table on String type field, but we split on a Long type field:
https://github.com/wikimedia/analytics-refinery/blob/master/python/refinery/sqoop.py#L1320
I'd really like for us to investigate more. let's sync @APizzata-WMF .
I should have read more carefully. the problem is with the local_user table, which splits by a string field. Please excuse me.

I can also see that that we are doing a split-by on a String column when sqooping the centralauth_localuser table so it does makes sense. What if we spilt by another column like lu_local_id?
https://github.com/wikimedia/analytics-refinery/blob/35e7f416fe4bc9e3aeb194474a4fa803d8983823/python/refinery/sqoop.py#L1355

In T411116#11723611, @JAllemandou wrote:

I've read our code and the bug report again, there is something I don't understand: the bug is supposed to happen when splitting a table on String type field, but we split on a Long type field:
https://github.com/wikimedia/analytics-refinery/blob/master/python/refinery/sqoop.py#L1320
I'd really like for us to investigate more. let's sync @APizzata-WMF .

The issue is on the localuser table. In that one we indeed split by a string lu_wiki.

In T411116#11723763, @Snwachukwu wrote:

I can also see that that we are doing a split-by on a String column when sqooping the centralauth_localuser table so it does makes sense. What if we spilt by another column like lu_local_id?
https://github.com/wikimedia/analytics-refinery/blob/35e7f416fe4bc9e3aeb194474a4fa803d8983823/python/refinery/sqoop.py#L1355

+1. Another candidate could be lu_global_id. Both will be heavily skewed though, but presumably not more than lu_wiki.

Now that I've made myself a fool by not being precise enough, let's get back to solutions :)
I can see tow ways:

the one I suggested above
reducing the mapper weight for that table (here). If we go from 0.5 to 0.25, the effect will be to reduce the number o mappers by two, exactly the same as changing it in puppet.

My way of dealing with that would be to change the puppet code to using 32 mappers.

But this would make correctness depend on the amount of mappers, yes? If splitting by strings is buggy, and Sqoop is EOLed, I suggest we abandon splitting by strings and move the splitting to use an integer column which we know work reliably? Note localuser seems to be the only table that we split by string.

What if we spilt by another column like lu_local_id?

I don't think that using lu_local_id is a good idea because ut's not a table index, making splitting the data a lot less efficient.

Yet another idea is to use Spark to get this data. But this is quite a change.

reducing the mapper weight for that table (here). If we go from 0.5 to 0.25, the effect will be to reduce the number o mappers by two, exactly the same as changing it in puppet.

I can test this easily and come back with the results

In T411116#11723883, @JAllemandou wrote:

What if we spilt by another column like lu_local_id?

I don't think that using lu_local_id is a good idea because ut's not a table index, making splitting the data a lot less efficient.

Then we are out of luck for mapper-independent correctness because all indexed columns are strings:

mysql:research@dbstore1008.eqiad.wmnet [centralauth]> desc localuser;
+-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+
| Field                 | Type                                                            | Null | Key | Default | Extra |
+-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+
| lu_wiki               | varbinary(255)                                                  | NO   | PRI |         |       |
| lu_name               | varbinary(255)                                                  | NO   | PRI |         |       |
| lu_attached_timestamp | binary(14)                                                      | YES  |     | NULL    |       |
| lu_attached_method    | enum('primary','empty','mail','password','admin','new','login') | YES  |     | NULL    |       |
| lu_local_id           | int(10) unsigned                                                | YES  |     | NULL    |       |
| lu_global_id          | int(10) unsigned                                                | YES  |     | NULL    |       |
| lu_attachment_method  | tinyint(3) unsigned                                             | YES  |     | NULL    |       |
+-----------------------+-----------------------------------------------------------------+------+-----+---------+-------+
7 rows in set (0.001 sec)

mysql:research@dbstore1008.eqiad.wmnet [centralauth]> SHOW INDEX FROM localuser;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| localuser |          0 | PRIMARY  |            1 | lu_wiki     | A         |      162607 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          0 | PRIMARY  |            2 | lu_name     | A         |   239358345 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          1 | lu_name  |            1 | lu_name     | A         |   239358345 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| localuser |          1 | lu_name  |            2 | lu_wiki     | A         |   239358345 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.001 sec)

I can test this easily and come back with the results

The results are as expected, we have no duplication:

df_10_64_025 =  (
    spark.read
        .format("avro").load(f"hdfs://analytics-hadoop/user/a-pizzata/wmf/data_025/raw/mediawiki/tables/centralauth_localuser/snapshot=apizzata1/wiki_db=centralauth/")
        )
       
df_10_64_025 = df_10_64_025.withColumn("source_file", input_file_name())
df_10_64_025.createOrReplaceTempView("table_10_64_025")

spark.sql("""
select lu_wiki, count(*) from (
select  lu_wiki, lu_local_id
from table_10_64_025
group by 1,2
having count(*) >1)
group by 1""").show()

+-------+--------+
|lu_wiki|count(1)|
+-------+--------+
+-------+--------+

spark.sql("""
select  count(distinct source_file)
from table_10_64_025""").show()

+---------------------------+
|count(DISTINCT source_file)|
+---------------------------+
|                         17|
+---------------------------+

I think it's easier to make it happen this way (reducing the mapper weight in sqoop script) than changing puppet. If ok for everyone, let's make it happen (with a comment in the code :) )

I think it's easier to make it happen this way

I agree with this!

Regarding the old data? Since we know the duplicates are always in the same file we could think of dropping these problematic files. What do you think?

Regarding the old data? Since we know the duplicates are always in the same file we could think of dropping these problematic files. What do you think?

Hm, this would mean partially incomplete data. I'd rather have duplicate in my data than incomplete one.
We should nonetheless communicate about this!
I reviewed mediawiki_history code, and my analysis says that we introduced some duplication :(
Given metrics didn't crash, it means the numbers are not huge, but it's not great anyhow.

Hm, this would mean partially incomplete data. I'd rather have duplicate in my data than incomplete one.

The file I am talking about is only made up of duplicates, therefore deleting it would just remove duplication

The file I am talking about is only made up of duplicates, therefore deleting it would just remove duplication

Ah! I had assumed duplicates were mixed with non-duplicates. In that case, yes please, remove those files with duplicates only!

In T411116#11726800, @JAllemandou wrote:

I think it's easier to make it happen this way (reducing the mapper weight in sqoop script) than changing puppet. If ok for everyone, let's make it happen (with a comment in the code :) )

+1. Make that comment BIG :D

APizzata-WMF moved this task from In Review to Ready to Deploy on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Mar 20 2026, 8:24 AM

The file I am talking about is only made up of duplicates, therefore deleting it would just remove duplication

To be extra sure I did some checks and here's some additional proofs:

-- 1. check that the counts of duplicates matched the counts of rows in the file
spark.sql("""
WITH duplicates AS (
        SELECT
            source,
            lu_wiki,
            lu_local_id
        FROM joined_table
        GROUP BY 1, 2, 3
        HAVING COUNT(*) > 1
    ),
    table_count AS (
        SELECT
            source,
            source_file,
            COUNT(*) AS table_count
        FROM joined_table
        GROUP BY 1, 2
    )
    SELECT
        COUNT(1),
        table_count,
        t.source,
        split(t.source_file, 'wiki_db=centralauth/')[1]
    FROM joined_table t
    JOIN duplicates
      USING (source, lu_wiki, lu_local_id)
    JOIN table_count
      USING (source, source_file)
    GROUP BY 2, 3, 4
    ORDER BY 3, 4
""").show(truncate=False)


+--------+-----------+-------+-----------------------------------------------+
|count(1)|table_count|source |split(source_file, wiki_db=centralauth/, -1)[1]|
+--------+-----------+-------+-----------------------------------------------+
|221608  |221608     |2025-10|part-m-00017.avro                              |
|221608  |5974975    |2025-10|part-m-00019.avro                              |
|223126  |223126     |2025-11|part-m-00017.avro                              |
|223126  |6015757    |2025-11|part-m-00019.avro                              |
|224520  |224520     |2025-12|part-m-00017.avro                              |
|224520  |6055371    |2025-12|part-m-00019.avro                              |
|226036  |226036     |2026-01|part-m-00017.avro                              |
|226036  |6095585    |2026-01|part-m-00019.avro                              |
|227271  |227271     |2026-02|part-m-00017.avro                              |
|227271  |6131087    |2026-02|part-m-00019.avro                              |
+--------+-----------+-------+-----------------------------------------------+

-- as we can see the file 17 has the same number of duplicates and total records.

-- 2. check the file 17 only contains wikis starting with o and that the count of records matches the previous result
spark.sql(
    """
    SELECT
        substr(lu_wiki, 1, 1),
        source,
        split(source_file, 'wiki_db=centralauth/')[1],
        count(*)
    FROM joined_table
    WHERE source_file LIKE '%017.%'
       OR source_file LIKE '%019.%'
    GROUP BY 1, 2, 3
    ORDER BY 2, 3, 1
    """
).show(70, truncate=False)

+---------------------+-------+-----------------------------------------------+--------+
|substr(lu_wiki, 1, 1)|source |split(source_file, wiki_db=centralauth/, -1)[1]|count(1)|
+---------------------+-------+-----------------------------------------------+--------+
|o                    |2025-10|part-m-00017.avro                              |221608  |
|o                    |2025-10|part-m-00019.avro                              |221608  |
|p                    |2025-10|part-m-00019.avro                              |5753367 |
|o                    |2025-11|part-m-00017.avro                              |223126  |
|o                    |2025-11|part-m-00019.avro                              |223126  |
|p                    |2025-11|part-m-00019.avro                              |5792631 |
|o                    |2025-12|part-m-00017.avro                              |224520  |
|o                    |2025-12|part-m-00019.avro                              |224520  |
|p                    |2025-12|part-m-00019.avro                              |5830851 |
|o                    |2026-01|part-m-00017.avro                              |226036  |
|o                    |2026-01|part-m-00019.avro                              |226036  |
|p                    |2026-01|part-m-00019.avro                              |5869549 |
|o                    |2026-02|part-m-00017.avro                              |227271  |
|o                    |2026-02|part-m-00019.avro                              |227271  |
|p                    |2026-02|part-m-00019.avro                              |5903816 |
+---------------------+-------+-----------------------------------------------+--------+

-- 17 has only o starting wikis and the numbers between the queries match

-- 3. testing if we have duplicates when we exclude file 17 

spark.sql(
    """
    WITH duplicates AS (
        SELECT
            source,
            lu_wiki,
            lu_local_id
        FROM joined_table
        WHERE source_file NOT LIKE '%17%'
        GROUP BY 1, 2, 3
        HAVING COUNT(*) > 1
    )
    SELECT
        COUNT(1),
        t.source,
        split(t.source_file, 'wiki_db=centralauth/')[1]
    FROM joined_table t
    LEFT JOIN duplicates
        USING (source, lu_wiki, lu_local_id)
    GROUP BY 2, 3
    ORDER BY 2, 3
    """
).show(truncate=False)

+--------+------+-----------------------------------------------+
|count(1)|source|split(source_file, wiki_db=centralauth/, -1)[1]|
+--------+------+-----------------------------------------------+
+--------+------+-----------------------------------------------+

-- no duplicates!

-- 4. checking the total number of rows for each snapshot without file17 and see if the number of distinct lu_wiki, lu_local_id + the number of records without file 17 = the count of the table with duplicates

spark.sql(
    """
    WITH duplicates AS (
        SELECT
            source,
            lu_wiki,
            lu_local_id
        FROM joined_table
        GROUP BY 1, 2, 3
        HAVING COUNT(*) > 1
    ),
    duplicate_counts AS (
        SELECT
            COUNT(DISTINCT lu_wiki, lu_local_id) AS duplicate_count,
            source
        FROM joined_table
        JOIN duplicates
            USING (source, lu_wiki, lu_local_id)
        GROUP BY 2
    ),
    counts_without_17 AS (
        SELECT
            COUNT(1) AS count_without_17,
            source
        FROM joined_table
        WHERE source_file NOT LIKE '%17%'
        GROUP BY 2
    ),
    regular_counts AS (
        SELECT
            COUNT(1) AS regular_count,
            source
        FROM joined_table
        GROUP BY 2
    )
    SELECT
        d.source,
        duplicate_count,
        count_without_17,
        regular_count,
        duplicate_count + count_without_17 = regular_count AS check
    FROM duplicate_counts d
    JOIN counts_without_17
        USING (source)
    JOIN regular_counts
        USING (source)
    GROUP BY 1, 2, 3, 4
    ORDER BY 1 ASC
    """
).show(truncate=False)

+-------+---------------+----------------+-------------+-----+
|source |duplicate_count|count_without_17|regular_count|check|
+-------+---------------+----------------+-------------+-----+
|2025-10|221608         |291890790       |292112398    |true |
|2025-11|223126         |294869829       |295092955    |true |
|2025-12|224520         |297741807       |297966327    |true |
|2026-01|226036         |300572347       |300798383    |true |
|2026-02|227271         |303153098       |303380369    |true |
+-------+---------------+----------------+-------------+-----+

Monday I will delete the file 17 from each snapshot and run msck repair table

Monday I will delete the file 17 from each snapshot and run msck repair table

While it will not hurt, you don't need to run the MSCK REPAIR. Removing the files will be enough as hive metastore remembers a folder per partition, not files.

Just ran the following with the watchful eye of @JAllemandou :

sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2025-10/wiki_db=centralauth/part-m-00017.avro
sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2025-11/wiki_db=centralauth/part-m-00017.avro
sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2025-12/wiki_db=centralauth/part-m-00017.avro
sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2026-01/wiki_db=centralauth/part-m-00017.avro
sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2026-02/wiki_db=centralauth/part-m-00017.avro

Will post asap the proof that we have no more duplicates, but only null in the old partitions

forgot to run the same command on snapshot= 2025-09:

sudo -u analytics kerberos-run-command analytics hdfs dfs -rm /wmf/data/raw/mediawiki_private/tables/centralauth_localuser/snapshot=2025-09/wiki_db=centralauth/part-m-00017.avro

Here is the query:

spark.sql(
    """
    WITH duplicates AS (
        SELECT
            snapshot,
            lu_wiki,
            lu_local_id
        FROM wmf_raw.centralauth_localuser
        WHERE wiki_db = 'centralauth'
        GROUP BY 1, 2, 3
        HAVING COUNT(*) > 1
    )
    SELECT
        COUNT(1),
        t.snapshot
    FROM wmf_raw.centralauth_localuser t
    JOIN duplicates
        USING (snapshot, lu_wiki, lu_local_id)
    WHERE t.wiki_db = 'centralauth'
    GROUP BY 2
    ORDER BY 2
    """
).show(truncate=False)

+--------+--------+
|count(1)|snapshot|
+--------+--------+
+--------+--------+

The table has been sqooped and I have ran all the queries to check the status:

spark.sql("""
SELECT count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE snapshot='2026-03'
  AND wiki_db='centralauth'
  AND lu_local_id IS NULL
""").show(100, truncate=False)

+-----+
|count|
+-----+
|0    |
+-----+

-- no nulls

spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2026-03'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id
  HAVING count > 1
)
""").show(100, truncate=False)

+-----+
|count|
+-----+
|0    |
+-----+

-- no lu_wiki, lu_local_id combination duplicates


spark.sql("""
SELECT count(1) as count FROM (
  SELECT count(1) as count,
         lu_wiki,
         lu_local_id,
         lu_global_id
  FROM wmf_raw.centralauth_localuser
  WHERE snapshot='2026-03'
    AND wiki_db='centralauth'
  GROUP BY lu_wiki, lu_local_id, lu_global_id
  HAVING count > 1
)
""").show(100, truncate=False)

+-----+
|count|
+-----+
|0    |
+-----+

-- no lu_wiki, lu_local_id, lu_global_id combination duplicates

spark.sql(
    """
    WITH duplicates AS (
        SELECT
            snapshot,
            lu_wiki,
            lu_local_id
        FROM wmf_raw.centralauth_localuser
        WHERE wiki_db = 'centralauth'
        GROUP BY 1, 2, 3
        HAVING COUNT(*) > 1
    )
    SELECT
        COUNT(1),
        t.snapshot
    FROM wmf_raw.centralauth_localuser t
    JOIN duplicates
        USING (snapshot, lu_wiki, lu_local_id)
    WHERE t.wiki_db = 'centralauth'
    GROUP BY 2
    ORDER BY 2
    """
).show(truncate=False)

+--------+--------+
|count(1)|snapshot|
+--------+--------+
+--------+--------+
-- no duplicates in te whole table

spark.sql("""
SELECT snapshot, count(1) as count
FROM wmf_raw.centralauth_localuser 
WHERE wiki_db='centralauth'
GROUP BY snapshot
ORDER BY snapshot desc
""").show(100, truncate=False)

+--------+---------+
|snapshot|count    |
+--------+---------+
|2026-03 |305949560|
|2026-02 |303153098|
|2026-01 |300572347|
|2025-12 |297741807|
|2025-11 |294869829|
|2025-10 |291890790|
|2025-09 |290182310|
+--------+---------+
-- and the table is populated

The issue is fixed

APizzata-WMF moved this task from Ready to Deploy to Done on the Data-Engineering (Q3 FY25/26 January 1st - March 31th) board.Apr 2 2026, 1:18 PM

Ahoelzl edited projects, added: Data-Engineering (Q4 FS25/26 April 1st - June 30st); removed: Data-Engineering (Q3 FY25/26 January 1st - March 31th).Apr 3 2026, 10:26 PM

Ahoelzl moved this task from Next Up to Done on the Data-Engineering (Q4 FS25/26 April 1st - June 30st) board.

Ahoelzl closed this task as Resolved.Apr 3 2026, 10:30 PM

Ahoelzl edited projects, added: Data-Engineering (Q3 FY25/26 January 1st - March 31th); removed: Data-Engineering (Q4 FS25/26 April 1st - June 30st).Apr 3 2026, 10:31 PM

Stang unsubscribed.Apr 7 2026, 8:33 PM

CentralAuth's localuser table contains many nulls and duplicate mappingsClosed, ResolvedPublicActions

Description

Related Objects

Event Timeline

CentralAuth's localuser table contains many nulls and duplicate mappings
Closed, ResolvedPublic
Actions