Page MenuHomePhabricator

ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007
Closed, ResolvedPublic

Description

The drift report has:

"ipblocks ipb_timestamp field-type-mismatch": {
    "s1": [
        "db1083:enwiki",
        "db1084:enwiki",
        "db1169:enwiki",
        "db1118:enwiki",
        "db1163:enwiki",
        "db1106:enwiki",
        "db1164:enwiki",
        "db1119:enwiki",
        "db1134:enwiki",
        "db1135:enwiki"
    ],
    "s2": [
        "db1122:svwiki",
        "db1129:svwiki",
        "db1076:svwiki"
    ],
    "s3": [
        "db1123:aswikibooks",
        "db1166:aswikibooks",
        "db1157:aswikibooks",
        "db1175:aswikibooks",
        "db1112:aswikibooks"
    ],
    "s6": [
        "db1131:ruwiki",
        "db1168:ruwiki",
        "db1085:ruwiki",
        "db1173:ruwiki"
    ],
    "s7": [
        "db1086:cawiki",
        "db1174:cawiki",
        "db1127:cawiki",
        "db1079:cawiki"
    ]
},

It's varbinary(14) in production:

wikiadmin@10.64.16.186(enwiki)> desc ipblocks;
+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| ipb_timestamp        | varbinary(14)       | NO   | MUL | NULL    |                |
...

But in the code it's binary(14) and has been since 2007 (was changed from char(14) in rMWf38344a) and has been in char(14) since its introduction in r498, It moved around quite a bit though: r575 and r647). I don't know why it's set to varbinary in production.

According to T42626: Standardise type of timestamp database fields (MySQL), this should be binary(14) and given the fact that this has been like that in basically any wiki made in the past decade (including wikidata), I think it's fine to assume this change would be safe.

ALTER TABLE /*_*/ ipblocks
  MODIFY ipb_timestamp BINARY(14) NOT NULL;

Schema change progress:

  • s1
    • eqiad
    • codfw
  • s2
    • eqiad
    • codfw
  • s3
    • eqiad
    • codfw
  • s4
    • eqiad
    • codfw
  • s5
    • eqiad
    • codfw
  • s6
    • eqiad
    • codfw
  • s7
    • eqiad
    • codfw
  • s10
  • labtestwiki

Confirmed that s8 is not affected.

Event Timeline

Reedy renamed this task from ipb_timestamp is varbinary(14) in old wikis while being bianry(14) in the code since 2007 to ipb_timestamp is varbinary(14) in old wikis while being binary(14) in the code since 2007.Mar 27 2021, 5:15 PM
Marostegui triaged this task as Medium priority.Apr 6 2021, 6:35 AM
Marostegui moved this task from Triage to Ready on the DBA board.
Kormat added a subscriber: Kormat.

Deploying to s6 eqiad replica (db1098) to soak overnight.

Kormat moved this task from Ready to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2021-07-15T08:56:43Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 14 hosts with reason: Deploying schema change to s6 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T08:56:50Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 14 hosts with reason: Deploying schema change to s6 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T08:58:44Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 13 hosts with reason: Deploying schema change to s5 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T08:58:49Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 13 hosts with reason: Deploying schema change to s5 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:04:20Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 15 hosts with reason: Deploying schema change to s2 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:04:26Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 15 hosts with reason: Deploying schema change to s2 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:09:53Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 15 hosts with reason: Deploying schema change to s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:10:00Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 15 hosts with reason: Deploying schema change to s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:15:04Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 4:00:00 on 18 hosts with reason: Deploying schema change to s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:15:14Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 4:00:00 on 18 hosts with reason: Deploying schema change to s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:18:38Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 18 hosts with reason: Deploying schema change to s1 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:18:45Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 18 hosts with reason: Deploying schema change to s1 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:25:07Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 2:00:00 on 12 hosts with reason: Deploying schema change to s3 T278619

Mentioned in SAL (#wikimedia-operations) [2021-07-15T09:25:14Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 2:00:00 on 12 hosts with reason: Deploying schema change to s3 T278619

Kormat changed the task status from Open to Stalled.Jul 15 2021, 11:56 AM
Kormat updated the task description. (Show Details)
Kormat moved this task from In progress to Blocked on the DBA board.

Stalling until we switch back to eqiad.

Kormat changed the task status from Stalled to In Progress.Oct 21 2021, 11:46 AM

Mentioned in SAL (#wikimedia-operations) [2021-10-21T11:47:11Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 9 hosts with reason: Schema change s6 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T11:47:18Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 9 hosts with reason: Schema change s6 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T11:54:57Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 9 hosts with reason: Schema change s5 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T11:55:04Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 9 hosts with reason: Schema change s5 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:34:16Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 11 hosts with reason: Schema change s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:34:24Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 11 hosts with reason: Schema change s7 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:43:17Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 9 hosts with reason: Schema change s2 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:43:25Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 9 hosts with reason: Schema change s2 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:48:33Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 2:00:00 on 13 hosts with reason: Schema change s4 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:48:43Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 2:00:00 on 13 hosts with reason: Schema change s4 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:52:10Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 2:00:00 on 14 hosts with reason: Schema change s1 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:52:21Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 2:00:00 on 14 hosts with reason: Schema change s1 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:56:41Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 2:00:00 on 7 hosts with reason: Schema change s3 T278619

Mentioned in SAL (#wikimedia-operations) [2021-10-21T12:56:47Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 2:00:00 on 7 hosts with reason: Schema change s3 T278619