Schema change for watchlist.wl_notificationtimestamp going binary(14) from varbinary(14)
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Ladsgroup
	Nov 22 2020, 12:35 AM

Details

	Subject	Repo	Branch	Lines +/-
	db1121,db1155,clouddb*s4: Disable notifications	operations/puppet	production	+5 -0

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
		Restricted Task
Resolved	Legoktm	T281515 June 2021 Datacenter switchover
Resolved	Marostegui	T285139 DB maintenance work to do while eqiad is passive (June 2021)
Resolved	Marostegui	T268392 Schema change for watchlist.wl_notificationtimestamp going binary(14) from varbinary(14)
Resolved	• Kormat	T283131 Upgrade s3 to Debian Buster and MariaDB 10.4
Resolved	• Kormat	T284648 Switchover s3 from db1123 to db1157
Resolved	Trizek-WMF	T284650 Read-only window needed for s3 (~900 wikis)
Resolved	Marostegui	T283235 Upgrade s5 to Debian Buster and MariaDB 10.4
Resolved	Marostegui	T284529 Switchover s5 from db1100 to db1130
Resolved	sgrabarczuk	T284530 Read only time window needed for s5

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 22 2020, 12:35 AM

Marostegui triaged this task as Medium priority.Nov 23 2020, 7:19 AM

Marostegui added a project: DBA.

Marostegui moved this task from Triage to Ready on the DBA board.

Ladsgroup mentioned this in T277116: fa_deleted_timestamp and fa_timestamp are binary(14) in code but varbinary(14) in production.Apr 13 2021, 5:53 AM

Marostegui claimed this task.Apr 28 2021, 6:43 AM

Marostegui moved this task from Backlog to In progress on the Schema-change-in-production board.

Marostegui moved this task from Ready to In progress on the DBA board.

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

Stashbot mentioned this in T273360: Schema change for dropping default of img_timestamp and making it binary(14).Apr 28 2021, 7:03 AM

Stashbot mentioned this in T266486: Schema change to turn user_last_timestamp.user_newtalk to binary(14).

Marostegui updated the task description. (Show Details)Apr 28 2021, 7:04 AM

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 watchlist\G"
*************************** 1. row ***************************
       Table: watchlist
Create Table: CREATE TABLE `watchlist` (
  `wl_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `wl_user` int(5) unsigned NOT NULL DEFAULT 0,
  `wl_namespace` int(11) NOT NULL DEFAULT 0,
  `wl_title` varbinary(255) NOT NULL DEFAULT '',
  `wl_notificationtimestamp` binary(14) DEFAULT NULL,
  PRIMARY KEY (`wl_id`),
  UNIQUE KEY `wl_user` (`wl_user`,`wl_namespace`,`wl_title`),
  KEY `wl_user_notificationtimestamp` (`wl_user`,`wl_notificationtimestamp`),
  KEY `wl_namespace_title` (`wl_namespace`,`wl_title`)
) ENGINE=InnoDB AUTO_INCREMENT=42264822 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

WRT this patch it looks okay while wl_user shouldn't have a default (at least comparing with https://github.com/wikimedia/mediawiki/blob/master/maintenance/tables-generated.sql#L317). I'll check that further. It's unrelated to this schema change.

Thanks, removing a default is an online DDL so it can be done in a different task and it will be easier than changing a data type

It seems it's a drift back from 2007. I will create a separate ticket for that later (it'll just show up in the drift report).

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

Marostegui updated the task description. (Show Details)May 4 2021, 7:04 AM

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

Marostegui updated the task description. (Show Details)May 5 2021, 1:57 PM

Marostegui updated the task description. (Show Details)May 5 2021, 2:05 PM

Marostegui updated the task description. (Show Details)

Marostegui updated the task description. (Show Details)May 6 2021, 5:58 AM

s5 is done, pending the master.

Marostegui updated the task description. (Show Details)May 7 2021, 6:16 AM

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

Marostegui updated the task description. (Show Details)May 7 2021, 6:45 AM

Marostegui updated the task description. (Show Details)May 7 2021, 8:07 AM

s2 is done, pending the master.

Marostegui updated the task description. (Show Details)May 11 2021, 11:01 AM

Marostegui updated the task description. (Show Details)May 11 2021, 12:23 PM

Marostegui updated the task description. (Show Details)May 12 2021, 5:25 AM

Marostegui updated the task description. (Show Details)May 17 2021, 11:33 AM

Marostegui updated the task description. (Show Details)May 17 2021, 11:51 AM

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

Marostegui updated the task description. (Show Details)May 17 2021, 12:24 PM

Marostegui updated the task description. (Show Details)May 17 2021, 12:39 PM

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

Marostegui updated the task description. (Show Details)May 19 2021, 5:50 AM

Marostegui updated the task description. (Show Details)May 19 2021, 7:00 AM

Marostegui updated the task description. (Show Details)May 19 2021, 7:29 AM

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

Marostegui updated the task description. (Show Details)May 19 2021, 9:36 AM

Marostegui updated the task description. (Show Details)May 19 2021, 11:26 AM

Marostegui updated the task description. (Show Details)May 20 2021, 10:13 AM

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)

Marostegui updated the task description. (Show Details)May 20 2021, 1:38 PM

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

Marostegui updated the task description. (Show Details)May 26 2021, 9:02 AM

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

Maintenance_bot removed a project: Patch-For-Review.Jun 3 2021, 6:10 AM

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

Marostegui moved this task from In progress to Unclear/blocked to proceed on the Schema-change-in-production board.Jun 4 2021, 5:15 AM

Marostegui added subtasks: T283131: Upgrade s3 to Debian Buster and MariaDB 10.4, T283235: Upgrade s5 to Debian Buster and MariaDB 10.4.Jun 4 2021, 8:59 AM

Marostegui mentioned this in T284529: Switchover s5 from db1100 to db1130.Jun 8 2021, 6:27 AM

Marostegui changed the status of subtask T283235: Upgrade s5 to Debian Buster and MariaDB 10.4 from Open to Stalled.Jun 11 2021, 4:45 AM

Marostegui added a parent task: T285139: DB maintenance work to do while eqiad is passive (June 2021).Jun 18 2021, 11:14 AM

Mentioned in SAL (#wikimedia-operations) [2021-06-21T08:56:31Z] <marostegui> Deploy T266486 T268392 T273360 on db1123

Marostegui updated the task description. (Show Details)Jun 21 2021, 11:15 AM

Marostegui changed the status of subtask T283235: Upgrade s5 to Debian Buster and MariaDB 10.4 from Stalled to Open.Jun 22 2021, 5:13 AM

Marostegui updated the task description. (Show Details)Jun 22 2021, 5:41 AM

Marostegui closed subtask T283235: Upgrade s5 to Debian Buster and MariaDB 10.4 as Resolved.Jun 28 2021, 8:36 AM

• jcrespo changed the status of subtask T283131: Upgrade s3 to Debian Buster and MariaDB 10.4 from Stalled to Open.Jun 28 2021, 9:27 AM

• jcrespo closed subtask T283131: Upgrade s3 to Debian Buster and MariaDB 10.4 as Resolved.

s8 eqiad master is done

[email protected][wikidatawiki]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

[email protected][wikidatawiki]> ALTER TABLE /*_*/watchlist
    ->   MODIFY wl_notificationtimestamp BINARY(14) DEFAULT NULL;
Query OK, 48690834 rows affected (37 min 29.572 sec)
Records: 48690834  Duplicates: 0  Warnings: 0

Marostegui updated the task description. (Show Details)Jul 1 2021, 10:28 AM

Marostegui changed the task status from Stalled to Open.Jul 1 2021, 2:22 PM

Marostegui moved this task from Blocked to In progress on the DBA board.

Marostegui moved this task from Unclear/blocked to proceed to In progress on the Schema-change-in-production board.

Marostegui updated the task description. (Show Details)Jul 2 2021, 6:36 AM

Marostegui updated the task description. (Show Details)Jul 5 2021, 6:12 AM

Marostegui updated the task description. (Show Details)Jul 5 2021, 6:39 AM