Suggested Investigations: Remove default from sic_updated_timestamp and drop indexes on sic_created_timestamp in cusi_case
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Dreamy_Jazz
	Dec 15 2025, 11:08 AM

Project Tags

Referenced Files

None

Subscribers

Aklapper

dom_walden

Dreamy_Jazz

Description

Summary

Per T411821, once T411788: Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager is completed we should drop the default on the sic_updated_timestamp column and remove the indexes on sic_created_timestamp

Proposed schema changes

After the column is appropriately populated (using the current values of sic_created_timestamp), then remove the default from sic_updated_timestamp:

> describe cusi_case;
+-----------------------+---------------------+------+-----+----------------+----------------+
| Field                 | Type                | Null | Key | Default        | Extra          |
+-----------------------+---------------------+------+-----+----------------+----------------+
| sic_id                | int(10) unsigned    | NO   | PRI | NULL           | auto_increment |
...
| sic_updated_timestamp | binary(14)          | NO   | MUL | NULL           |                |
+-----------------------+---------------------+------+-----+----------------+----------------+

Also drop any index with sic_created_timestamp in it from the cusi_case table

Acceptance criteria

The schema changes above have been merged into the master branch of the CheckUser
A Schema-change-in-production ticket is filed to make these changes on WMF wikis, only to be done once T411788: Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager is completed and stably deployed everywhere

Details

Related Changes in Gerrit:

	Subject	Author	Repo	Branch	Lines +/-
	Drop indexes on sic_created_timestamp in cusi_case table	Dreamy Jazz	mediawiki/extensions/CheckUser	master	+208 -31
	Remove default from sic_updated_timestamp in cusi_case	Dreamy Jazz	mediawiki/extensions/CheckUser	master	+278 -32

Customize query in gerrit

Related Objects
Search...

Status	Subtype	Assigned	Task
Resolved		Dreamy_Jazz	T412688 Suggested Investigations: Remove default from sic_updated_timestamp and drop indexes on sic_created_timestamp in cusi_case
Resolved		• hector.arroyo	T411788 Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager
Resolved		Dreamy_Jazz	T411821 Add sic_updated_timestamp column and associated indexes to the cusi_case table
Resolved		Dreamy_Jazz	T415055 Populate sic_updated_timestamp for existing cases
Resolved		Dreamy_Jazz	T415054 Set a value for sic_updated_timestamp when creating or updating cases
Resolved	BUG REPORT	Dreamy_Jazz	T415348 CheckUser update.php fails to run for SQLite and PostgreSQL

Event Timeline

Dreamy_Jazz created this task.Dec 15 2025, 11:08 AM

Restricted Application added projects: Product Safety and Integrity, WMF-NDA. · View Herald TranscriptDec 15 2025, 11:08 AM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Dreamy_Jazz added a subtask: T411788: Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager.Dec 15 2025, 11:08 AM

Dreamy_Jazz mentioned this in T411821: Add sic_updated_timestamp column and associated indexes to the cusi_case table.

Wargo removed a project: WMF-NDA.Dec 15 2025, 3:27 PM

OKryva-WMF moved this task from Backlog to Q1 Candidates on the CheckUser-SuggestedInvestigations board.Dec 19 2025, 10:46 AM

• hector.arroyo changed the status of subtask T411788: Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager from Open to In Progress.Feb 2 2026, 11:10 AM

We can make the patches to apply these changes now

Dreamy_Jazz moved this task from Backlog to Ready on the Product Safety and Integrity (Sprint Flower (Feb 9 - Feb 27)) board.Feb 10 2026, 12:26 PM

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

[mediawiki/extensions/CheckUser@master] Remove default from sic_updated_timestamp in cusi_case

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

gerritbot added a project: Patch-For-Review.Feb 11 2026, 8:20 PM

Dreamy_Jazz claimed this task.Feb 11 2026, 8:22 PM

Dreamy_Jazz moved this task from Ready to Needs review on the Product Safety and Integrity (Sprint Flower (Feb 9 - Feb 27)) board.

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

[mediawiki/extensions/CheckUser@master] Drop indexes on sic_created_timestamp in cusi_case table

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

Dreamy_Jazz added a project: Schema-change.Feb 12 2026, 2:23 PM

Dreamy_Jazz moved this task from Unsorted to Delete / Drop / Remove on the Schema-change board.

Dreamy_Jazz moved this task from Q1 Candidates to In Progress on the CheckUser-SuggestedInvestigations board.Feb 13 2026, 11:45 AM

Dreamy_Jazz closed subtask T411788: Suggested investigations: Replace 'Created' timestamp column with 'Updated' timestamp column in table pager as Resolved.Feb 13 2026, 3:14 PM

Change #1238817 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Remove default from sic_updated_timestamp in cusi_case

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

ReleaseTaggerBot added a project: MW-1.46-notes (1.46.0-wmf.18; 2026-03-03).Feb 26 2026, 11:00 AM

Change #1238820 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] Drop indexes on sic_created_timestamp in cusi_case table

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

Maintenance_bot removed a project: Patch-For-Review.Feb 26 2026, 12:30 PM

OKryva-WMF moved this task from Needs review to Needs QA on the Product Safety and Integrity (Sprint Flower (Feb 9 - Feb 27)) board.Feb 26 2026, 1:01 PM

The cusi_case table locally. sic_created_timestamp has no index associated with it. sic_updated_timestamp default is NULL:

DESCRIBE cusi_case;
MariaDB [enwiki]>
+-----------------------+---------------------+------+-----+---------+----------------+
| Field                 | Type                | Null | Key | Default | Extra          |
+-----------------------+---------------------+------+-----+---------+----------------+
| sic_id                | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| sic_status            | tinyint(3) unsigned | NO   | MUL | 0       |                |
| sic_status_reason     | varbinary(255)      | NO   |     |         |                |
| sic_created_timestamp | binary(14)          | NO   |     | NULL    |                |
| sic_url_identifier    | int(10) unsigned    | NO   | UNI | NULL    |                |
| sic_updated_timestamp | binary(14)          | NO   | MUL | NULL    |                |
+-----------------------+---------------------+------+-----+---------+----------------+

SHOW INDEXES FROM cusi_case;
MariaDB [enwiki]>
+-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                        | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cusi_case |          0 | PRIMARY                         |            1 | sic_id                | A         |       22254 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_url_identifier              |            1 | sic_url_identifier    | A         |       22254 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_status_updated_timestamp_id |            1 | sic_status            | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_status_updated_timestamp_id |            2 | sic_updated_timestamp | A         |         148 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_status_updated_timestamp_id |            3 | sic_id                | A         |       22254 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_updated_timestamp_id        |            1 | sic_updated_timestamp | A         |         124 |     NULL | NULL   |      | BTREE      |         |               |
| cusi_case |          0 | sic_updated_timestamp_id        |            2 | sic_id                | A         |       22254 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+---------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Thanks!

Dreamy_Jazz moved this task from In Progress to Done on the CheckUser-SuggestedInvestigations board.Mar 3 2026, 4:37 PM

Suggested Investigations: Remove default from sic_updated_timestamp and drop indexes on sic_created_timestamp in cusi_caseClosed, ResolvedPublicActions