Add user agent ID column to each CheckUser result table
Closed, ResolvedPublic2 Estimated Story Points
Actions

Description

A column needs to be added to every CheckUser result table so that it can store the associated user agent string in the cu_useragent table.

The column will be named agent_id with the column prefix for the table appended to the name. For example, for the cu_changes table the column name would be cuc_agent_id. The column will be a bigint to be the same type as the cuua_id column in the cu_useragent table.

Acceptance criteria

Create the column in each of the cu_changes, cu_log_event, and cu_private_event tables.

Proposed schema

> describe cu_changes;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
....
| cuc_agent_id   | bigint(20) unsigned | NO   |     | 0       |                |
+----------------+---------------------+------+-----+---------+----------------+

> describe cu_log_event;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
....
| cule_agent_id  | bigint(20) unsigned | NO   |     | 0       |                |
+----------------+---------------------+------+-----+---------+----------------+

> describe cu_private_event;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
....
| cupe_agent_id  | bigint(20) unsigned | NO   |     | 0       |                |
+----------------+---------------------+------+-----+---------+----------------+

Details

Subject	Repo	Branch	Lines +/-
Add cule_agent_id to cu_log_event table	mediawiki/extensions/CheckUser	master	+207 -1
Add cupe_agent_id to cu_private_event	mediawiki/extensions/CheckUser	master	+287 -1
Add cuc_agent_id to cu_changes table	mediawiki/extensions/CheckUser	master	+314 -0

Customize query in gerrit

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
Resolved		Dreamy_Jazz	T359312 Create cu_useragent table
Resolved		Marostegui	T361631 Unable to create table cu_useragent on labtestwiki
Open		None	T328289 update labtestwiki user and password
Resolved		Marostegui	T361673 Filter cu_useragent on sanitarium

Event Timeline

Dreamy_Jazz created this task.Mar 27 2024, 6:09 PM

Dreamy_Jazz added a subtask: T359312: Create cu_useragent table.

Dreamy_Jazz added a project: Trust and Safety Product Sprint (Sprint Gangan (11th - 22nd March)).Mar 27 2024, 7:06 PM

Dreamy_Jazz set the point value for this task to 2.

Dreamy_Jazz moved this task from Priority Backlog to In Progress on the Trust and Safety Product Sprint (Sprint Gangan (11th - 22nd March)) board.

Change #1015147 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] Add cu_changes.cuc_agent_id

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

Change #1015148 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] Add cule_agent_id to cu_log_event table

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

Change #1015149 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/extensions/CheckUser@master] Add cupe_agent_id to cu_private_event

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

Dreamy_Jazz moved this task from In Progress to Needs review on the Trust and Safety Product Sprint (Sprint Gangan (11th - 22nd March)) board.Mar 27 2024, 11:24 PM

Dreamy_Jazz added a project: DBA.

Dreamy_Jazz renamed this task from Add user agent ID column to each CheckUser result table that references the cu_useragent table to Add user agent ID column to each CheckUser result table.Mar 27 2024, 11:27 PM

Dreamy_Jazz updated the task description. (Show Details)

Dreamy_Jazz added a project: Schema-change.Mar 27 2024, 11:37 PM

Dreamy_Jazz moved this task from Unsorted to Add / Create on the Schema-change board.

Dreamy_Jazz mentioned this in T147894: Create index for agent_id columns in the CheckUser result tables.Mar 28 2024, 9:30 AM

Dreamy_Jazz added a parent task: T361174: Write to agent_id columns in the CheckUserInsert service when migration configuration allows it.

Dreamy_Jazz removed a project: Epic.Mar 28 2024, 10:05 AM

Dreamy_Jazz added a parent task: T361173: Add schema migration config for cu_useragent table.

Dreamy_Jazz removed a parent task: T361174: Write to agent_id columns in the CheckUserInsert service when migration configuration allows it.Mar 28 2024, 10:07 AM

Dreamy_Jazz removed a parent task: T361139: Normalise the user agent column in CheckUser result tables.Mar 28 2024, 10:30 AM

Dreamy_Jazz edited projects, added Trust and Safety Product Sprint (Sprint Tabla (1st - 14th April)); removed Trust and Safety Product Sprint (Sprint Gangan (11th - 22nd March)).Apr 1 2024, 4:27 PM

Dreamy_Jazz moved this task from Priority Backlog to Needs Review on the Trust and Safety Product Sprint (Sprint Tabla (1st - 14th April)) board.

Change #1015147 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Add cuc_agent_id to cu_changes table

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

Change #1015148 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Add cule_agent_id to cu_log_event table

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

Change #1015149 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Add cupe_agent_id to cu_private_event

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

Dreamy_Jazz mentioned this in T361627: Create cuc_agent_id, cule_agent_id and cupe_agent_id columns in cu_changes, cu_log_event and cu_private_event tables respectively on WMF wikis.Apr 2 2024, 5:03 PM

Dreamy_Jazz moved this task from Needs Review to Needs QA on the Trust and Safety Product Sprint (Sprint Tabla (1st - 14th April)) board.Apr 2 2024, 5:05 PM

This comment was removed by Dreamy_Jazz.

I think we can bypass QA since these aren't being written to yet. (I did some basic regression testing just in case.)

Moving back since this was already moved to QA (I had an old tab open when I moved this to Done).

Suggested QA steps for a local wiki:

Run update.php
Open the DB for your wiki
If using MariaDB run describe table; or for SQLite use .schema table. Repeat this command three times, replacing table with cu_changes, then cu_log_event, finally cu_private_event
Inspect the output of the commands and verify that the cuc_agent_id, cule_agent_id, cupe_agent_id column exists in the table cu_changes, cu_log_event, and cu_private_event respectively (i.e. cuc_agent_id should be in cu_changes).