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

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?

I didn't get anything. Maybe pinging again?

@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.

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.

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

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

s6 eqiad progress

  • dbstore1005
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085

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

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

s5 eqiad progress

  • dbstore1003
  • db1130
  • db1113
  • db1110
  • db1102
  • db1100
  • db1096
  • db1082

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

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

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

s7 eqiad progress

  • dbstore1003
  • db1136
  • db1127
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079

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

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 updated the task description. (Show Details)
Marostegui updated the task description. (Show Details)

All done