Normalise the user agent column in CheckUser result tables
Open, Needs TriagePublic
Actions

Assigned To

None

Authored By

	Dreamy_Jazz
	Mar 27 2024, 6:04 PM

Description

The user agent column in the CheckUser result table presents a lot of duplication. This is because, as described in T305930 and T326379, there is a lot of duplication in these columns. This has become even more of a problem since T295073: <Org-Wide Impact> Google Chrome User-Agent Deprecation Impact. For example, on enwiki there are about on average 200 rows to each distinct user agent string value. Some rough calculations suggest that by de-duplicating the column the cu_changes table on enwiki would be several gigabytes smaller.

As such, having one table to store the user agent strings used by rows in the CheckUser result tables and then referencing these rows by an ID would be a step towards normalising the result tables and would save a non-insignificant amount of space in the database.

Related Objects
Search...

Status	Subtype	Assigned	Task
Open	Feature	None	T315926 Provide a way to search all of the XFF addresses listed in the XFF string
Open	Feature	None	T326379 Normalise the XFF and user agent columns
Resolved		• TBolliger	T120734 Epic ⚡️ Improve MediaWiki's blocking tools
Open		None	T146837 Add ability to search by user agent from CheckUser interface
Open		None	T147894 Create index for agent_id columns in the CheckUser result tables
Open		None	T305930 Normalize cu_changes table
Open		None	T361139 Normalise the user agent column in CheckUser result tables
Open		None	T361208 Remove agent columns from CheckUser result tables
Open		None	T361206 Stop writing old for user agent schema migration on WMF wikis
Open		None	T361205 Stop writing old for user agent schema migration
Open		None	T361199 Set user agent schema migration config to read new on WMF wikis
Open		None	T361201 Set user agent schema migration config to read new in extension.json
Open		None	T361192 Read user agent strings from the cu_useragent table in Special:CheckUser
Open		None	T361193 Read user agent strings from the cu_useragent table in the CheckUser API
Open		None	T361195 Read user agent strings from the cu_useragent table in Special:Investigate
Open		None	T361198 Create a maintenance script to populate the cu_useragent table and agent_id columns with values from the agent columns
Open		None	T361196 Write to the cu_useragent table and agent_id columns on WMF wikis
Open		None	T361197 Write to the cu_useragent table and agent_id columns on by default in extension.json
Open		None	T361174 Write to agent_id columns in the CheckUserInsert service when migration configuration allows it
Open		None	T361172 Purge rows from cu_useragent
Open		None	T361173 Add schema migration config for cu_useragent table
Resolved		Dreamy_Jazz	T361140 Add user agent ID column to each CheckUser result table
Open		None	T361210 FYI: Changes to the cuc_agent column in the cu_changes table
Resolved		Dreamy_Jazz	T361928 Update mediawiki.org pages for schema changes made by adding the cu_useragent table
Resolved		Dreamy_Jazz	T359312 Create cu_useragent table
Resolved		Marostegui	T361631 Unable to create table cu_useragent on labtestwiki
Declined		None	T328289 update labtestwiki user and password
Open		None	T369308 Decommission clouddb2002-dev.codfw.wmnet
Open		None	T378260 Retire labtestwiki
Resolved		Marostegui	T361673 Filter cu_useragent on sanitarium