Page MenuHomePhabricator

Remove image.img_deleted column from production
Closed, ResolvedPublic

Description

@Ladsgroup recently run a script to find the differences between production and code (T104459#6048024) and looks like img_deleted exists on most wikis and most replicas:

"image img_deleted field-mismatch-prod-extra": {
    "s1": [
        "db1083.eqiad.wmnet",
        "db1106.eqiad.wmnet",
        "db1080.eqiad.wmnet",
        "db1089.eqiad.wmnet",
        "db1119.eqiad.wmnet",
        "db1118.eqiad.wmnet",
        "db1107.eqiad.wmnet",
        "db1099.eqiad.wmnet",
        "db1134.eqiad.wmnet",
        "db1105.eqiad.wmnet"
    ],
    "s10": [
        "db1133.eqiad.wmnet"
    ],
    "s2": [
        "db1122.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1076.eqiad.wmnet",
        "db1129.eqiad.wmnet",
        "db1074.eqiad.wmnet",
        "db1105.eqiad.wmnet"
    ],
    "s3": [
        "db1123.eqiad.wmnet",
        "db1075.eqiad.wmnet",
        "db1112.eqiad.wmnet",
        "db1078.eqiad.wmnet"
    ],
    "s4": [
        "db1138.eqiad.wmnet",
        "db1091.eqiad.wmnet",
        "db1121.eqiad.wmnet",
        "db1103.eqiad.wmnet",
        "db1081.eqiad.wmnet",
        "db1084.eqiad.wmnet",
        "db1097.eqiad.wmnet"
    ],
    "s5": [
        "db1100.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1130.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1110.eqiad.wmnet",
        "db1097.eqiad.wmnet",
        "db1082.eqiad.wmnet"
    ],
    "s6": [
        "db1131.eqiad.wmnet",
        "db1088.eqiad.wmnet",
        "db1085.eqiad.wmnet",
        "db1093.eqiad.wmnet",
        "db1096.eqiad.wmnet",
        "db1113.eqiad.wmnet",
        "db1098.eqiad.wmnet"
    ],
    "s7": [
        "db1086.eqiad.wmnet",
        "db1079.eqiad.wmnet",
        "db1098.eqiad.wmnet",
        "db1136.eqiad.wmnet",
        "db1090.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1094.eqiad.wmnet"
    ],
    "s8": [
        "db1109.eqiad.wmnet",
        "db1101.eqiad.wmnet",
        "db1126.eqiad.wmnet",
        "db1111.eqiad.wmnet",
        "db1099.eqiad.wmnet",
        "db1087.eqiad.wmnet",
        "db1092.eqiad.wmnet",
        "db1104.eqiad.wmnet"
    ]

Full results: https://phabricator.wikimedia.org/P10956

I have done some checks on enwiki and wikidatawiki and there are no rows with img_deleted =! 0:

root@db1087.eqiad.wmnet[wikidatawiki]> select * from image where img_deleted != 0;
Empty set (0.00 sec)


root@db1089.eqiad.wmnet[enwiki]> select * from image where img_deleted != 0;
Empty set (1.53 sec)

There are no references on code to this column: https://codesearch.wmflabs.org/search/?q=img_deleted&i=nope&files=&repos=
It needs to be dropped in production

alter table image drop column if exists img_deleted;
  • wikitech
  • labtestwiki - img_deleted isn't there.

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 13 2020, 8:58 AM
Marostegui triaged this task as Medium priority.
Marostegui moved this task from Triage to Next on the DBA board.

I am going to delete this column from a s1 (enwiki), s4 (commonswiki) and s8 (wikidatawiki) host only and leave it like that for a week before going ahead for the rest. Just in case.

Mentioned in SAL (#wikimedia-operations) [2020-04-20T08:14:04Z] <marostegui> Remove img_deleted column from db1089 (enwiki), db1081 (commonswiki, db1111 (wikidatawiki) - T250055

db1089 (enwiki):

mysql.py -hdb1089 enwiki -A -e "show create table image\G"
*************************** 1. row ***************************
       Table: image
Create Table: CREATE TABLE `image` (
  `img_name` varbinary(255) NOT NULL DEFAULT '',
  `img_size` int(8) unsigned NOT NULL DEFAULT '0',
  `img_width` int(5) NOT NULL DEFAULT '0',
  `img_height` int(5) NOT NULL DEFAULT '0',
  `img_metadata` mediumblob NOT NULL,
  `img_bits` int(3) NOT NULL DEFAULT '0',
  `img_media_type` enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D') DEFAULT NULL,
  `img_major_mime` enum('unknown','application','audio','image','text','video','message','model','multipart') NOT NULL DEFAULT 'unknown',
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `img_description_id` bigint(20) unsigned NOT NULL,
  `img_actor` bigint(20) unsigned NOT NULL,
  `img_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `img_sha1` varbinary(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`img_name`),
  KEY `img_size` (`img_size`),
  KEY `img_timestamp` (`img_timestamp`),
  KEY `img_media_mime` (`img_media_type`,`img_major_mime`,`img_minor_mime`),
  KEY `img_actor_timestamp` (`img_actor`,`img_timestamp`),
  KEY `img_sha1` (`img_sha1`(10))
) ENGINE=InnoDB DEFAULT CHARSET=binary
Marostegui updated the task description. (Show Details)Apr 20 2020, 8:21 AM

db1101 (wikidatawiki):

root@db1101.eqiad.wmnet[wikidatawiki]> show create table image\G
*************************** 1. row ***************************
       Table: image
Create Table: CREATE TABLE `image` (
  `img_name` varbinary(255) NOT NULL DEFAULT '',
  `img_size` int(10) unsigned NOT NULL DEFAULT '0',
  `img_width` int(11) NOT NULL DEFAULT '0',
  `img_height` int(11) NOT NULL DEFAULT '0',
  `img_metadata` mediumblob NOT NULL,
  `img_bits` int(11) NOT NULL DEFAULT '0',
  `img_media_type` enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D') DEFAULT NULL,
  `img_major_mime` enum('unknown','application','audio','image','text','video','message','model','multipart') NOT NULL DEFAULT 'unknown',
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `img_description_id` bigint(20) unsigned NOT NULL,
  `img_actor` bigint(20) unsigned NOT NULL,
  `img_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `img_sha1` varbinary(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`img_name`),
  KEY `img_size` (`img_size`),
  KEY `img_timestamp` (`img_timestamp`),
  KEY `img_sha1` (`img_sha1`(10)),
  KEY `img_media_mime` (`img_media_type`,`img_major_mime`,`img_minor_mime`),
  KEY `img_actor_timestamp` (`img_actor`,`img_timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)
Marostegui updated the task description. (Show Details)Apr 20 2020, 12:42 PM

db1081 (commonswiki):

root@cumin1001:~# mysql.py -hdb1081 -e "show create table commonswiki.image\G"
*************************** 1. row ***************************
       Table: image
Create Table: CREATE TABLE `image` (
  `img_name` varbinary(255) NOT NULL DEFAULT '',
  `img_size` int(8) unsigned NOT NULL DEFAULT '0',
  `img_width` int(5) NOT NULL DEFAULT '0',
  `img_height` int(5) NOT NULL DEFAULT '0',
  `img_metadata` mediumblob NOT NULL,
  `img_bits` int(3) NOT NULL DEFAULT '0',
  `img_media_type` enum('UNKNOWN','BITMAP','DRAWING','AUDIO','VIDEO','MULTIMEDIA','OFFICE','TEXT','EXECUTABLE','ARCHIVE','3D') DEFAULT NULL,
  `img_major_mime` enum('unknown','application','audio','image','text','video','message','model','multipart') NOT NULL DEFAULT 'unknown',
  `img_minor_mime` varbinary(100) NOT NULL DEFAULT 'unknown',
  `img_description_id` bigint(20) unsigned NOT NULL,
  `img_actor` bigint(20) unsigned NOT NULL,
  `img_timestamp` varbinary(14) NOT NULL DEFAULT '',
  `img_sha1` varbinary(32) NOT NULL DEFAULT '',
  PRIMARY KEY (`img_name`),
  KEY `img_size` (`img_size`),
  KEY `img_timestamp` (`img_timestamp`),
  KEY `img_media_mime` (`img_media_type`,`img_major_mime`,`img_minor_mime`),
  KEY `img_actor_timestamp` (`img_actor`,`img_timestamp`),
  KEY `img_sha1` (`img_sha1`(10))
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED
Marostegui updated the task description. (Show Details)Apr 20 2020, 1:16 PM
Marostegui moved this task from Next to In progress on the DBA board.Apr 20 2020, 1:49 PM

Mentioned in SAL (#wikimedia-operations) [2020-04-20T13:50:12Z] <marostegui> Deploy schema change on codfw master - T250055

Marostegui updated the task description. (Show Details)EditedApr 20 2020, 1:50 PM

s8 eqiad (empty table):

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1126
  • db1124
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1092
  • db1087
Marostegui updated the task description. (Show Details)Apr 20 2020, 2:01 PM
Marostegui updated the task description. (Show Details)

Mentioned in SAL (#wikimedia-operations) [2020-04-21T05:19:28Z] <marostegui> Deploy schema change on s2 codfw - T250055

Marostegui updated the task description. (Show Details)Apr 21 2020, 5:22 AM
Marostegui added a comment.EditedApr 21 2020, 5:32 AM

s2 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1129
  • db1125
  • db1122
  • db1105
  • db1103
  • db1095
  • db1090
  • db1076
  • db1074
Marostegui updated the task description. (Show Details)Apr 21 2020, 5:43 AM
Marostegui updated the task description. (Show Details)Apr 21 2020, 5:49 AM

s6 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1139
  • db1131
  • db1125
  • db1113
  • db1098
  • db1096
  • db1093
  • db1088
  • db1085
Marostegui updated the task description. (Show Details)Apr 21 2020, 6:01 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-27T08:26:12Z] <marostegui> Deploy schema change on s5 codfw, lag will show up - T250055

Marostegui updated the task description. (Show Details)Apr 27 2020, 8:27 AM
Marostegui added a comment.EditedApr 27 2020, 8:30 AM

s5 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1130
  • db1124
  • db1113
  • db1110
  • db1102
  • db1100
  • db1097
  • db1096
  • db1082
Marostegui updated the task description. (Show Details)Apr 27 2020, 8:31 AM
Marostegui updated the task description. (Show Details)Apr 27 2020, 8:55 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-27T09:11:32Z] <marostegui> Deploy schema change on s1 codfw, lag will show up - T250055

Marostegui updated the task description. (Show Details)EditedApr 27 2020, 10:50 AM

s1 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1139
  • db1134
  • db1124
  • db1119
  • db1118
  • db1107
  • db1106
  • db1105
  • db1099
  • db1089
  • db1083
  • db1080
Marostegui updated the task description. (Show Details)Apr 27 2020, 12:49 PM

Mentioned in SAL (#wikimedia-operations) [2020-04-27T12:50:02Z] <marostegui> Removed img_deleted from s1 (enwiki) T250055

Mentioned in SAL (#wikimedia-operations) [2020-04-27T13:09:05Z] <marostegui> Deploy schema change on s7 codfw, lag will show up - T250055

Marostegui updated the task description. (Show Details)Apr 27 2020, 1:18 PM
Marostegui added a comment.EditedApr 27 2020, 1:35 PM

s7 eqiad

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

Mentioned in SAL (#wikimedia-operations) [2020-04-27T13:45:54Z] <marostegui> Drop img_deleted column from s7 eqiad - T250055

Mentioned in SAL (#wikimedia-operations) [2020-04-27T13:46:04Z] <marostegui> Drop img_deleted column from wikitech - T250055

Marostegui updated the task description. (Show Details)Apr 27 2020, 1:46 PM

Mentioned in SAL (#wikimedia-operations) [2020-04-27T13:47:40Z] <marostegui> Deploy schema change on s3 codfw, lag will show up - T250055

Marostegui updated the task description. (Show Details)Apr 27 2020, 1:58 PM
Marostegui added a comment.EditedApr 27 2020, 2:09 PM

s3 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1124
  • db1123
  • db1112
  • db1095
  • db1078
  • db1075
Marostegui updated the task description. (Show Details)Apr 28 2020, 6:05 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-28T06:06:27Z] <marostegui> Deploy schema change on s4 codfw, this will generate lag on codfw - T250055

Marostegui updated the task description. (Show Details)EditedApr 28 2020, 12:36 PM

s4 eqiad

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1138
  • db1125
  • db1121
  • db1103
  • db1102
  • db1097
  • db1091
  • db1084
  • db1081

Mentioned in SAL (#wikimedia-operations) [2020-05-06T05:03:41Z] <marostegui@cumin1001> dbctl commit (dc=all): 'Pool db1103:3314 in vslow on s4 while db1121 is out T250055', diff saved to https://phabricator.wikimedia.org/P11157 and previous config saved to /var/cache/conftool/dbconfig/20200506-050340-marostegui.json

Marostegui changed the task status from Open to Stalled.May 6 2020, 10:07 AM

Only s4 master is pending: T250055#6088674
But given how busy and big the image table is on s4, I am not going to attempt to do this high risk operation until the master is switched over or eqiad is passive, as this clean up isn't really blocking anything.

Marostegui updated the task description. (Show Details)May 6 2020, 10:08 AM
Marostegui changed the task status from Stalled to Open.May 29 2020, 5:19 AM

db1138 is no longer a master, so I am going to run the schema change there so we can get this over with!

Mentioned in SAL (#wikimedia-operations) [2020-05-29T05:20:56Z] <marostegui> Deploy schema change on db1138 (no longer s4 master) - T250055

Marostegui closed this task as Resolved.May 29 2020, 10:01 AM
Marostegui updated the task description. (Show Details)