Page MenuHomePhabricator

Schema change for dropping default of img_timestamp and making it binary(14)
Closed, ResolvedPublic

Description

Part of T42626: Standardise type of timestamp database fields (MySQL) and T230428: Migrate tables.sql to abstract schema

  1. ALTERs to run: ALTER TABLE image ALTER img_timestamp DROP DEFAULT; and ALTER TABLE /*_*/image MODIFY COLUMN img_timestamp BINARY(14) NOT NULL;
  2. Where to run those changes: all.dblist
  3. When to run those changes: At any time
  4. If the schema change is backwards compatible: Yes
  5. If the schema change has been tested already on some of the test/beta wikis: Tested in beta cluster.
  6. if the data should be made available on the labs replicas and/or dumps: Yes, data in this table is public.

Progress:

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Ladsgroup renamed this task from Schema change for dropping default of img_timestamp to Schema change for dropping default of img_timestamp and making it binary(14).Mar 1 2021, 7:55 PM
Ladsgroup updated the task description. (Show Details)
Marostegui added a project: DBA.
Marostegui moved this task from Triage to Ready on the DBA board.

@Ladsgroup can you confirm whether the second ALTER is meant to be for the image table and img_timestamp column? If it is for recentchanges table, that is being done at T276150

Yes. you're right. Copy pasta error

Will alter a host in s6 codfw and eqiad and leave it till next week before going for all of them

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db2141
  • db2129
  • db2124
  • db2117
  • db2114
  • db2097
  • db2095
  • db2089
  • db2087
  • db2076
  • db1180
  • db1173
  • db1168
  • db1165
  • db1155
  • db1140
  • db1139
  • db1131
  • db1113
  • db1098
  • db1096
  • db1085
  • clouddb1021
  • clouddb1019
  • clouddb1015

Mentioned in SAL (#wikimedia-operations) [2021-04-28T07:40:47Z] <marostegui> Deploy schema change on db1098:3316 and db1098:3316 T266486 T268392 T273360

@Ladsgroup this looks good?:

# mysql.py -hdb2089:3316 frwiki -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` binary(14) NOT NULL,
  `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 KEY_BLOCK_SIZE=8

That field looks okay. Didn't check the whole table in depth (if any issues, it's a separate from this one and will show up in drift reports)

Mentioned in SAL (#wikimedia-operations) [2021-05-04T05:36:56Z] <marostegui> Deploy schema change on s6 codfw, lag will appear - T266486 T268392 T273360

s6 is done, pending the master. It will be finished once we've completed the migration to 10.4 on T280751

s5 progress

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1161
  • db1154
  • db1150
  • db1145
  • db1144
  • db1130
  • db1113
  • db1110
  • db1100
  • db1096
  • db1082
  • clouddb1021
  • clouddb1020
  • clouddb1016

s5 is done, pending the master.

Mentioned in SAL (#wikimedia-operations) [2021-05-07T06:17:28Z] <marostegui> Deploy schema change on s2 codfw, lag will appear T266486 T268392 T273360

s2 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1004
  • db1182
  • db1171
  • db1170
  • db1162
  • db1156
  • db1155
  • db1146
  • db1129
  • db1122
  • db1105
  • db1102
  • db1074
  • clouddb1021
  • clouddb1018
  • clouddb1014

s7 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db1181
  • db1174
  • db1170
  • db1158
  • db1155
  • db1136
  • db1127
  • db1124
  • db1116
  • db1101
  • db1098
  • db1079
  • clouddb1021
  • clouddb1018
  • clouddb1014

Mentioned in SAL (#wikimedia-operations) [2021-05-17T11:55:23Z] <marostegui> Deploy schema change on s8 codfw, lag will appear in codfw T266486 T268392 T273360

s8 eqiad

  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1005
  • db1178
  • db1177
  • db1172
  • db1167
  • db1154
  • db1126
  • db1116
  • db1114
  • db1111
  • db1109
  • db1104
  • db1101
  • db1099
  • db1087
  • clouddb1021
  • clouddb1020
  • clouddb1016

Mentioned in SAL (#wikimedia-operations) [2021-05-18T07:05:59Z] <marostegui> Deploy schema change on s4 codfw, lag will appear in codfw T266486 T268392 T273360

s4 eqiad

  • dbstore1004
  • db1160
  • db1155
  • db1150
  • db1149
  • db1148
  • db1147
  • db1146
  • db1145
  • db1144
  • db1143
  • db1142
  • db1141
  • db1138
  • db1121
  • clouddb1021
  • clouddb1019
  • clouddb1015

Mentioned in SAL (#wikimedia-operations) [2021-05-19T07:31:44Z] <marostegui> Deploy schema change on s3 codfw, lag will appear in codfw T266486 T268392 T273360

s3 eqiad

  • dbstore1004
  • db1179
  • db1175
  • db1171
  • db1166
  • db1157
  • db1154
  • db1123
  • db1112
  • db1102
  • clouddb1021
  • clouddb1017
  • clouddb1013

Mentioned in SAL (#wikimedia-operations) [2021-05-20T10:15:00Z] <marostegui> Deploy schema change on s1 codfw, lag will appear in codfw T266486 T268392 T273360

s3 is done, only the master pending, to be done once the switchover is completed (T283131)

s1 eqiad

  • dbstore1003
  • db1184
  • db1169
  • db1164
  • db1163
  • db1154
  • db1140
  • db1139
  • db1135
  • db1134
  • db1133
  • db1119
  • db1118
  • db1106
  • db1105
  • db1099
  • clouddb1021
  • clouddb1017
  • clouddb1013

Mentioned in SAL (#wikimedia-operations) [2021-05-26T04:35:32Z] <marostegui> Deploy schema change on db1106, this will generate lag on s1 (enwiki) on wiki replicas T266486 T268392 T273360

s1 is fully done, only pending the master

Mentioned in SAL (#wikimedia-operations) [2021-06-03T05:20:04Z] <marostegui> Deploy schema change on db1121, lag will appear on s4 (commonswiki) wiki replicas - T266486 T268392 T273360

Change 697898 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] db1121,db1155,clouddb*s4: Disable notifications

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

Change 697898 merged by Marostegui:

[operations/puppet@production] db1121,db1155,clouddb*s4: Disable notifications

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

Marostegui changed the task status from Open to Stalled.Jun 4 2021, 5:13 AM
Marostegui updated the task description. (Show Details)
Marostegui moved this task from In progress to Blocked on the DBA board.

All done, pending master swap or master DC switch to complete all the masters that are pending

Marostegui changed the task status from Stalled to Open.Jul 5 2021, 6:34 AM
Marostegui updated the task description. (Show Details)
Marostegui moved this task from Blocked to In progress on the DBA board.

I have started the alter on commonswiki.image, this might take a while...

Marostegui updated the task description. (Show Details)

All done