According to https://drift-tracker.toolforge.org/report/checkuser/, the cul_timestamp column in the cu_log table has multiple drifts indicating that the DBs have the wrong column type. I have verified that this drift exists for a DB on s1.
The correct column type for this is mwtimestamp which on MariaDB is BINARY(14) NOT NULL. From db2153 the incorrect type is varbinary(14) NOT NULL DEFAULT ''.
Progress:
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Open | None | T313251 Collect and fix schema drifts of wmf-deployed extension tables in production | |||
| Resolved | Ladsgroup | T321063 Fix CheckUser database schema drifts in production | |||
| Resolved | Ladsgroup | T356631 Column cu_log.cul_timestamp has an incorrect type and incorrectly has a default value set |
On db2152 for enwiki I ran show create table cu_log\G which shows that there is a schema draft as the column is varbinary(14) NOT NULL DEFAULT '' as shown below:
wikiadmin2023@10.192.0.4(enwiki)> show create table cu_log\G
*************************** 1. row ***************************
Table: cu_log
Create Table: CREATE TABLE `cu_log` (
`cul_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cul_timestamp` varbinary(14) NOT NULL DEFAULT '',
`cul_type` varbinary(30) NOT NULL DEFAULT '',
`cul_target_id` int(10) unsigned NOT NULL DEFAULT 0,
`cul_target_text` blob NOT NULL,
`cul_target_hex` varbinary(255) NOT NULL DEFAULT '',
`cul_range_start` varbinary(255) NOT NULL DEFAULT '',
`cul_range_end` varbinary(255) NOT NULL DEFAULT '',
`cul_actor` bigint(20) unsigned NOT NULL,
`cul_reason_id` bigint(20) unsigned NOT NULL,
`cul_reason_plaintext_id` bigint(20) unsigned NOT NULL,
`cul_result_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`cul_result_plaintext_id` bigint(20) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`cul_id`),
KEY `cul_timestamp` (`cul_timestamp`),
KEY `cul_type_target` (`cul_type`,`cul_target_id`,`cul_timestamp`),
KEY `cul_target_hex` (`cul_target_hex`,`cul_timestamp`),
KEY `cul_range_start` (`cul_range_start`,`cul_timestamp`),
KEY `cul_actor_time` (`cul_actor`,`cul_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=878564 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
1 row in set (0.000 sec)The column should not be a VARBINARY as the timestamp does not allow infinity. Furthermore, there should be no default on this column as the value must always be set when the row is inserted.
At the top of the report, there is the timestamp: Start time of building the report: 31 Jul 2023 10:52:34
It's six months old. I can re-run it and then deploy it again to see what's missing but generally speaking, it's not automatic and done on ad-hoc basis (it shouldn't but oh well)
I presume that the data from db2153 isn't out of date considering I checked production directly (therefore at least one DB is still not fixed).
We should do another report of drifts and go through them again. But I rather wait T355609: Make cuc_id a bigint to finish first
That makes sense and I'm happy to wait for that task.
I would also note that the mismatch in the default value for cul_type wasn't noticed by the tracker (filed that as T356634).
Was added as binary without default in 6cb7f881835019e5d5d2e3c16546e440e0ab61a0 and still in code like that.
ladsgroup opened https://gitlab.wikimedia.org/repos/sre/schema-changes/-/merge_requests/26
Add change_cul_timestamp_T356631.py
ladsgroup merged https://gitlab.wikimedia.org/repos/sre/schema-changes/-/merge_requests/26
Add change_cul_timestamp_T356631.py