Page MenuHomePhabricator

job_cmd is varbinary(255) in production while being varbinary(60) in code since 2007
Closed, ResolvedPublic

Description

Drifts report gives this for old wikis:

"job job_cmd field-size-mismatch": {
    "s1": [
        "db1083:enwiki",
        "db1084:enwiki",
        "db1169:enwiki",
        "db1118:enwiki",
        "db1163:enwiki",
        "db1106:enwiki",
        "db1164:enwiki",
        "db1119: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",
        "db1136:cawiki",
        "db1079:cawiki"
    ]
},

In production it's varbinary(255):

wikiadmin@10.64.16.186(enwiki)> desc job;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| job_cmd             | varbinary(255)   | NO   | MUL |         |                |

While in the code it's varbinary(60). It looks like in rMWf38344aba8 (2007) @tstarling has changed the size of the field from 255 to 60 "to prevent key length overflow" (I don't understand the details and couldn't figure it out from the commit message).

Given that the table is empty, we can just fix it to 60 I assume.

Progress

  • s1
    • eqiad
    • codfw
  • s2
    • eqiad
    • codfw
  • s4
    • eqiad
    • codfw
  • s5
    • eqiad
    • codfw
  • s6
    • eqiad
    • codfw
  • s7
    • eqiad
    • codfw
  • s8
    • eqiad
    • codfw
  • labswiki
  • labtestwiki

Event Timeline

The change was made to allow MediaWiki to be installable with the UTF-8 character set selected. The maximum key length in MyISAM is 1000 bytes: https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html . With the index (job_cmd, job_namespace, job_title), job_namespace is 4 bytes, job_title is 255*3=765 bytes, leaving 231 bytes for job_cmd. Before the referenced patch, job_cmd would require 765 bytes, causing an error on install, so I reduced that to 60 bytes, believing that that would be long enough for such an identifier.

Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui updated the task description. (Show Details)
Marostegui moved this task from Ready to In progress on the DBA board.

s3 codfw

  • db2149
  • db2139
  • db2127
  • db2109
  • db2105
  • db2094
  • db2074
Marostegui updated the task description. (Show Details)

All done