Page MenuHomePhabricator

iw_url in interwiki is varbinary(127) in production but blob in code
Closed, ResolvedPublic

Description

iw_url has been blob datatype since 2007 but in production it's varbinary(127):

wikiadmin@10.64.32.77(enwiki)> desc interwiki;
+-----------+----------------+------+-----+---------+-------+
| Field     | Type           | Null | Key | Default | Extra |
+-----------+----------------+------+-----+---------+-------+
| iw_prefix | varbinary(32)  | NO   | PRI | NULL    |       |
| iw_url    | varbinary(127) | NO   |     | NULL    |       |
| iw_local  | tinyint(1)     | NO   |     | 0       |       |
| iw_trans  | tinyint(1)     | NO   |     | 0       |       |
| iw_api    | blob           | NO   |     | NULL    |       |
| iw_wikiid | varbinary(64)  | NO   |     | NULL    |       |
+-----------+----------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

This will cause issues if the url for the interwiki is more than 127 characters.

ALTER TABLE /*_*/interwiki
  MODIFY iw_url BLOB NOT NULL;

Where: s1,s2,s3,s4,s5,s6,s7 (we still need to check which wikis (if not all))

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

Marostegui triaged this task as Medium priority.Mar 11 2021, 7:21 AM
Marostegui moved this task from Triage to Refine on the DBA board.

@Ladsgroup is this a schema change ticket or you need some help from us? Not sure what's expected from us on this task :)

Sorry, I should have been more clear. It's a drift report ticket. The field should become blob in production.

Thanks!
Does it happen everywhere?
Asking cause in some other tickets you report specific sections

Thanks!
Does it happen everywhere?
Asking cause in some other tickets you report specific sections

Good question. At first I thought it's everywhere but it's mostly in old wikis:

"interwiki iw_url field-size-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",
        "db1074: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",
        "db1136:cawiki"
    ]
},

For example, wikidata doesn't have this. Commons probably have it but s4 didn't show up because it was checking testcommonswiki for s4.

What is needed to move this out of Refine column?

Come up with the alter table statement and identify which hosts really need it in all the sections apart from s1 (as far as I remember your script only checked certain hosts but not all?)

The alter table would be:

ALTER TABLE /*_*/interwiki
  MODIFY iw_url BLOB NOT NULL;

the sections would be s1,s2,s3,s4,s6,s7

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

Deployed to an s6 eqiad replica (db1096:3316). Will leave to soak overnight.

Deployed to an s6 eqiad replica (db1096:3316). Will leave to soak overnight.

Replication still working fine, going for general deployment now.

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

Mentioned in SAL (#wikimedia-operations) [2021-07-14T07:47:41Z] <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 T277118

Mentioned in SAL (#wikimedia-operations) [2021-07-14T07:48:25Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 13 hosts with reason: Deploying schema change to s5 T277118

Mentioned in SAL (#wikimedia-operations) [2021-07-14T07:48:30Z] <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 T277118

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

Mentioned in SAL (#wikimedia-operations) [2021-07-14T07:49:45Z] <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 T277118

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

Mentioned in SAL (#wikimedia-operations) [2021-07-14T09:14: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 s7 T277118

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

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

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

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

Mentioned in SAL (#wikimedia-operations) [2021-07-14T13:09:25Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 4:00:00 on 18 hosts with reason: Deploying schema change to s1 T277118

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

Kormat changed the task status from Open to Stalled.Jul 14 2021, 1:10 PM
Kormat updated the task description. (Show Details)
Kormat moved this task from In progress to Blocked on the DBA board.

Stalling this until we switch back to eqiad.

Marostegui changed the task status from Stalled to Open.Oct 19 2021, 5:36 AM

This can now proceed

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

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

Mentioned in SAL (#wikimedia-operations) [2021-10-19T15:34:44Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 5:00:00 on 9 hosts with reason: Schema change s6 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T15:34:51Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 5:00:00 on 9 hosts with reason: Schema change s6 T277118

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

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

Mentioned in SAL (#wikimedia-operations) [2021-10-19T15:46:04Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 9 hosts with reason: Schema change s2 T277118

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

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

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

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:06:11Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 13 hosts with reason: Schema change s4 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:06:22Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 13 hosts with reason: Schema change s4 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:09:22Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 1:00:00 on 14 hosts with reason: Schema change s1 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:09:33Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 1:00:00 on 14 hosts with reason: Schema change s1 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:11:53Z] <kormat@cumin1001> START - Cookbook sre.hosts.downtime for 3:00:00 on 7 hosts with reason: Schema change s3 T277118

Mentioned in SAL (#wikimedia-operations) [2021-10-19T16:12:01Z] <kormat@cumin1001> END (PASS) - Cookbook sre.hosts.downtime (exit_code=0) for 3:00:00 on 7 hosts with reason: Schema change s3 T277118

Kormat updated the task description. (Show Details)

All done.