Page MenuHomePhabricator

text table still has old_* fields and indexes on some hosts
Closed, ResolvedPublic

Description

I found these drifts:

"text old_comment field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_minor_edit field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_namespace field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_timestamp field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_timestamp index-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ]
},
"text old_title field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_title index-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ]
},
"text old_user field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},
"text old_user_text field-mismatch-prod-extra": {
    "s2": [
        "db1122.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1105.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1074.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1082.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1130.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1093.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1094.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ]
},

If I remember correctly, text table was split from revision table long time ago and that's why these extra fields still exist. (Double check please)

ALTER table required: alter table text drop column if exists old_namespace, drop column if exists old_title, drop column if exists old_comment, drop column if exists old_user, drop column if exists old_user_text, drop column if exists old_timestamp, drop column if exists old_minor_edit, drop column if exists inverse_timestamp, drop key if exists old_title;

Schema change progress:

  • labswiki (doesn't have the fields)
  • labtestwiki (doesn't have the fields)

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 13 2020, 11:20 AM

Can someone from Core Platform Team confirm Amir's guess?

Marostegui triaged this task as Medium priority.Apr 13 2020, 1:26 PM
Marostegui moved this task from Triage to Backlog on the DBA board.

Afaik Amir is correct. pinging @tstarling to confirm.

I'd be very surprised if anything still used these fields. But I have had such surprises before :P

@Ladsgroup did you get any response?

@Ladsgroup did you get any response?

I didn't get anything. Maybe pinging again?

daniel added a comment.May 7 2020, 3:35 PM

@Ladsgroup did you get any response?

I didn't get anything. Maybe pinging again?

I pinged Tim on Slack, I hope he'll take a look when he starts his day.

I don't know what those fields are doing there.

MariaDB [frwiki]> show create table text\G
*************************** 1. row ***************************
CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_namespace` varbinary(0) NOT NULL DEFAULT '',
  `old_title` varbinary(255) NOT NULL DEFAULT '',
  `old_text` mediumblob NOT NULL,
  `old_comment` varbinary(0) NOT NULL DEFAULT '',
  `old_user` varbinary(0) NOT NULL DEFAULT '',
  `old_user_text` varbinary(0) NOT NULL DEFAULT '',
  `old_timestamp` varbinary(0) NOT NULL DEFAULT '',
  `old_minor_edit` varbinary(0) NOT NULL DEFAULT '',
  `old_flags` varbinary(255) NOT NULL DEFAULT '',
  `inverse_timestamp` varbinary(0) NOT NULL DEFAULT '',
  PRIMARY KEY (`old_id`),
  KEY `old_title` (`old_title`)
) ENGINE=InnoDB AUTO_INCREMENT=171170754 DEFAULT CHARSET=binary PACK_KEYS=1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

You see that everything is varbinary(0), even fields which were originally integers. This is not just fields left over from MW 1.4, this table has either been altered to change the types, or the fields were dropped and then later re-added to fix something that uses them. Maybe searching the Server Admin Log will provide an explanation.

Nothing in MW core uses them. I don't know if a tool uses them. You could just drop them and see what happens.

Marostegui updated the task description. (Show Details)Thu, Jun 11, 11:31 AM

I have checked one of the newest wikis created (T250706) and this is how the table looks like there:

*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` tinyblob NOT NULL,
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10862 DEFAULT CHARSET=binary

I have compared that with the table on frwiki and the alter needed would look like:

alter table text drop column if exists old_namespace, drop column if exists old_title, drop column if exists old_comment, drop column if exists old_user, drop column if exists old_user_text, drop column if exists old_timestamp, drop column if exists old_minor_edit, drop column if exists inverse_timestamp, drop key if exists old_title;

I am going to alter an s6 host in codfw first, and leave it like that for a few days, after that, I will alter an eqiad one, leave it there and if nothing breaks, I will go ahead and alter it everywhere.

Marostegui updated the task description. (Show Details)Thu, Jun 11, 11:40 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-11T11:46:23Z] <marostegui> Deploy schema change on s6 codfw - T250066

Marostegui updated the task description. (Show Details)Thu, Jun 11, 11:48 AM
Marostegui moved this task from Backlog to In progress on the DBA board.

This is how db2087:3316 (s6) looks like now:

for i in frwiki jawiki ruwiki; do echo $i; mysql.py -hdb2087:3316 $i -e "show create table text\G" ; done
frwiki
*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` varbinary(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=172506449 DEFAULT CHARSET=binary PACK_KEYS=1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
jawiki
*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` varbinary(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=78308284 DEFAULT CHARSET=binary PACK_KEYS=1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
ruwiki
*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` varbinary(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=109096466 DEFAULT CHARSET=binary PACK_KEYS=1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Which now looks like enwiki slaves (apart from the data type difference on old_flags, which should be tinyblob , but I won't tackle this on this task as that operation isn't online so it would require a master swap everywhere):

 mysql.py -hdb1089 enwiki -e "show create table text\G"
*************************** 1. row ***************************
       Table: text
Create Table: CREATE TABLE `text` (
  `old_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `old_text` mediumblob NOT NULL,
  `old_flags` tinyblob NOT NULL,
  PRIMARY KEY (`old_id`)
) ENGINE=InnoDB AUTO_INCREMENT=973935578 DEFAULT CHARSET=binary PACK_KEYS=1

Mentioned in SAL (#wikimedia-operations) [2020-06-12T04:54:28Z] <marostegui> Deploy schema change on s6 codfw - T250066

Marostegui updated the task description. (Show Details)Fri, Jun 12, 7:14 AM
Marostegui added a comment.EditedFri, Jun 12, 8:15 AM

s6 eqiad progress

  • dbstore1005
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
daniel removed a subscriber: daniel.Fri, Jun 12, 11:11 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-15T08:17:15Z] <marostegui> Deploy schema change on db1131 (s6 master) - T250066

Marostegui updated the task description. (Show Details)Mon, Jun 15, 8:49 AM
Marostegui updated the task description. (Show Details)

Mentioned in SAL (#wikimedia-operations) [2020-06-15T08:55:12Z] <marostegui> Deploy schema change on db2123 (s5 codfw master) - T250066

Marostegui updated the task description. (Show Details)Mon, Jun 15, 9:18 AM
Marostegui added a comment.EditedMon, Jun 15, 9:32 AM

s5 eqiad progress

  • dbstore1003
  • db1130
  • db1113
  • db1110
  • db1102
  • db1100
  • db1096
  • db1082
Marostegui updated the task description. (Show Details)Mon, Jun 15, 9:33 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-15T13:54:39Z] <marostegui> Deploy schema change on db1100 (s5 master) - T250066

Marostegui updated the task description. (Show Details)Mon, Jun 15, 2:19 PM

Mentioned in SAL (#wikimedia-operations) [2020-06-15T14:24:23Z] <marostegui> Deploy schema change on db2107 (s2 codfw master) - T250066

Marostegui updated the task description. (Show Details)Mon, Jun 15, 3:46 PM
Marostegui added a comment.EditedMon, Jun 15, 4:32 PM

s2 eqiad progress

  • dbstore1004
  • db1146
  • db1129
  • db1122
  • db1105
  • db1095
  • db1090
  • db1076
  • db1074

Mentioned in SAL (#wikimedia-operations) [2020-06-17T05:29:14Z] <marostegui> Deploy schema change on s7 codfw (lag will appear) - T250066

Marostegui updated the task description. (Show Details)Wed, Jun 17, 8:03 AM
Marostegui updated the task description. (Show Details)Wed, Jun 17, 9:52 AM
Marostegui added a comment.EditedWed, Jun 17, 10:58 AM

s7 eqiad progress

  • dbstore1003
  • db1136
  • db1127
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
Marostegui updated the task description. (Show Details)Thu, Jun 18, 9:30 AM

Mentioned in SAL (#wikimedia-operations) [2020-06-18T09:34:28Z] <marostegui> Deploy schema change on s3 codfw master (this will create lag on codfw) - T250066

Marostegui updated the task description. (Show Details)Thu, Jun 18, 10:51 AM
Marostegui added a comment.EditedThu, Jun 18, 11:49 AM

s3 eqiad progress

  • dbstore1004
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075

Only s3 master pending, I will do it on Monday as for s3 master, any schema change even if it is a small one generates quite lots of IO (as it has 900 wikis) so it needs a generous sleep between ALTERs.
On Monday I will start it with 2 minutes between wikis, so it will take around 30h, and I don't want to leave that running over the weekend.

Mentioned in SAL (#wikimedia-operations) [2020-06-22T04:50:22Z] <marostegui> Deploy schema change on s3 primary master with a big sleep between wikis - T250066

Marostegui closed this task as Resolved.Tue, Jun 23, 11:47 AM
Marostegui updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)

All done