Page MenuHomePhabricator

Schema change for dropping default of img_timestamp and making it binary(14)
Open, MediumPublic

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

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