Normalize cu_changes table
Open, MediumPublic
Actions

Assigned To

None

Authored By

	Zabe
	Apr 12 2022, 12:10 PM

Description

In every row of the cu_changes table the user agent is saved in the cuc_agent column. User agents are usually quite common, thus this quite a data duplication with the same user agent being saved up to million of times in the db.

Perhaps it makes sence to normalize the user agent by creating a new table where we map ids to user agents and then only reference those ids in the cu_changes table.

We might also want to do something similar for cuc_actiontext.

Related Objects
Search...

Status	Assigned	Task
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	Dreamy_Jazz	T359560 Create the CheckUserDataPruner service
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
Open	None	T328289 update labtestwiki user and password
Resolved	Marostegui	T361673 Filter cu_useragent on sanitarium

Event Timeline

Zabe created this task.Apr 12 2022, 12:10 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 12 2022, 12:10 PM

For some of the values for cuc_actiontext there is also T41013

This is a good idea, and would likely make T147894 obsolete. It would also make T234980 much easier.

I wonder if it gets worked on though, given T295073: <Org-Wide Impact> Google Chrome User-Agent Deprecation Impact

Generally sounds good but:

Let's not do it while the actor/comment migration is happening on CU tables. One thing at a time.
Old UA strings should be removed

I agree with both comments by @Ladsgroup
For the second one, we should modify purgeOldData.php to also run a query that looks for any rows in the new cu_user_agents table where its identifier (cuua_id) doesn't occur in the cu_changes table in its now-normalized cu_agent_id column. Those rows should then be deleted. This further justifies why cu_changes.cu_agent_id should be indexed, which is desirable for T147894 anyway.