Page MenuHomePhabricator

Normalise MW Core database language fields length
Closed, ResolvedPublic

Description

Noted on https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/Babel/+/597284/ doing T226546

varbinary(35) page_lang,
varbinary(20) ll_lang,
varbinary(32) lc_lang,
varbinary(32) site_language

They all should realy be the same length. As @Krinkle also pointed out page_lang is the newest, and also the longest... Maybe we should move the rest to match

WHAT: https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/597664/

ALTER TABLE /*$wgDBprefix*/l10n_cache MODIFY `lc_lang` VARBINARY(35) NOT NULL;
ALTER TABLE /*$wgDBprefix*/langlinks MODIFY `ll_lang` VARBINARY(35) NOT NULL DEFAULT '';
ALTER TABLE /*$wgDBprefix*/sites MODIFY `site_language` VARBINARY(35) NOT NULL;

WHERE: All wikis
WHEN: At DBAs leisure
BACK COMPAT: Yes
TESTED: Live on beta

Schema change progress:

  • Labswiki
  • Labtestwiki

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Change 597664 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@master] Normalise length of language fields to varbinary(35)

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

Is there a task for doing this in Wikimedia production first?

Is there a task for doing this in Wikimedia production first?

Nope. But DBA's won't usually do scheme changes before the MW patches are merged...

Krinkle renamed this task from Normalise MW Core database language field length to Normalise MW Core database language fields length.May 25 2020, 1:31 AM
Krinkle added a project: DBA.
Krinkle added a subscriber: Marostegui.
Marostegui moved this task from Triage to Blocked external/Not db team on the DBA board.

I hace checked the gerrit patch - should be an easy schema change I think.
We receive quite a bunch of schema changes so please let's use the template at https://wikitech.wikimedia.org/wiki/Schema_changes#Workflow_of_a_schema_change once this is merged. It is a lot easier for use if we can just always follow the same template.
Moving this to our blocked column until the change is merged, once done, please edit it and use the above template.

Change 597664 merged by jenkins-bot:
[mediawiki/core@master] Normalise length of language fields to varbinary(35)

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

Change 598447 had a related patch set uploaded (by DannyS712; owner: DannyS712):
[mediawiki/core@master] Rename patch-l10ncache-lc_lang-35.sql to patch-l10n_cache-lc_lang-35.sql

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

Change 598447 merged by jenkins-bot:
[mediawiki/core@master] Rename patch-l10ncache-lc_lang-35.sql to patch-l10n_cache-lc_lang-35.sql

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

Change 598813 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/extensions/ContentTranslation@master] Normalise length of language fields

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

Mentioned in SAL (#wikimedia-operations) [2020-06-23T12:45:08Z] <marostegui> Deploy schema change on s6 codfw master (lag will appear on codfw) - T253276

MariaDB 10.1 doesn't allow the increase of varchar/varbinary with INPLACE alter tables, this partially starts on 10.2 and it is fully supported on 10.4 (we are migrating to 10.4 at the moment, but we've not migrated the masters yet).
This schema change will require master switchovers or upgrading them to MariaDB 10.4 (which also requires a switchover) otherwise we'd be locking the tables there.

s6 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1140
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085

Mentioned in SAL (#wikimedia-operations) [2020-06-29T07:13:29Z] <marostegui> Deploy schema change on db1085 with replication to labs T253276

Mentioned in SAL (#wikimedia-operations) [2020-06-29T08:24:13Z] <marostegui> Deploy schema change on s2 codfw (lag will show up) T253276

s2 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1146
  • db1129
  • db1125
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074

Mentioned in SAL (#wikimedia-operations) [2020-07-06T13:30:39Z] <marostegui> Deploy schema change on s5 codfw master T253276

s5 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1145
  • db1144
  • db1130
  • db1124
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082

Mentioned in SAL (#wikimedia-operations) [2020-07-07T12:01:18Z] <marostegui> Deploy schema change on labswiki (wikitech) master - T253276

s4 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1125
  • db1121
  • db1084
  • db1081

s8 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087

s7 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1136
  • db1127
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079

s1 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1140
  • db1139
  • db1135
  • db1134
  • db1124
  • db1119
  • db1118
  • db1107
  • db1106
  • db1105
  • db1099
  • db1091
  • db1089
  • db1083

Mentioned in SAL (#wikimedia-operations) [2020-07-13T05:34:43Z] <marostegui> Deploy schema change on s3 codfw master, lag will appear on codfw T253276

s3 eqiad progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
Marostegui changed the task status from Open to Stalled.Jul 14 2020, 12:58 PM
Marostegui updated the task description. (Show Details)

Only primary masters pending. Stalling till the DC switchover is done.

Marostegui changed the task status from Stalled to Open.Sep 8 2020, 6:29 AM
Marostegui moved this task from Blocked external/Not db team to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2020-09-08T06:31:28Z] <marostegui> Deploy schema change on s5 eqiad master - T253276

Mentioned in SAL (#wikimedia-operations) [2020-09-08T06:58:56Z] <marostegui> Deploy schema change on s2 eqiad master - T253276

Mentioned in SAL (#wikimedia-operations) [2020-09-08T08:55:43Z] <marostegui> Deploy schema change on s7 eqiad master - T253276

Mentioned in SAL (#wikimedia-operations) [2020-09-08T10:20:36Z] <marostegui> Deploy schema change on s4 eqiad master - T253276

Mentioned in SAL (#wikimedia-operations) [2020-09-08T10:53:40Z] <marostegui> Deploy schema change on s3 eqiad master - T253276

Marostegui updated the task description. (Show Details)

All done

It seems this is not done on muswiki (s5):

"l10n_cache lc_lang field-size-mismatch": {
    "s5": [
        "db1110:muswiki",
        "db1130:muswiki",
        "db1082:muswiki"
    ]
},
"langlinks ll_lang field-size-mismatch": {
    "s5": [
        "db1110:muswiki",
        "db1130:muswiki",
        "db1082:muswiki"
    ]
},
"sites site_language field-size-mismatch": {
    "s5": [
        "db1110:muswiki",
        "db1130:muswiki",
        "db1082:muswiki"
    ]
},

I assume we should check for rest of moved wikis from s3 to s5.

LSobanski removed Marostegui as the assignee of this task.
LSobanski moved this task from In progress to Refine on the DBA board.
LSobanski added a subscriber: LSobanski.

Reopening based on @Ladsgroup's comment.

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

Thanks @Ladsgroup - good catch. It was just 2 wikis, (2 of the ones that were moved).

mhwiktionary
  `lc_lang` varbinary(32) NOT NULL DEFAULT '',
  `ll_lang` varbinary(20) NOT NULL DEFAULT '',
  `site_language` varbinary(32) NOT NULL,
muswiki
  `lc_lang` varbinary(32) NOT NULL DEFAULT '',
  `ll_lang` varbinary(20) NOT NULL DEFAULT '',
  `site_language` varbinary(32) NOT NULL,

To make it more interesting, it only happens on some hosts

Hosts:

  • All codfw
  • dbstore1003:3315
  • clouddb1016.eqiad.wmnet:3315
  • clouddb1020.eqiad.wmnet:3315
  • db1082.eqiad.wmnet:3306
  • db1096.eqiad.wmnet:3315
  • db1110.eqiad.wmnet:3306
  • db1113.eqiad.wmnet:3315
  • db1124.eqiad.wmnet:3315
  • db1130.eqiad.wmnet:3306
  • db1144.eqiad.wmnet:3315
  • db1145.eqiad.wmnet:3315
  • db1150.eqiad.wmnet:3315

All fixed, thanks for reporting!