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

root@db1104.eqiad.wmnet[wikidatawiki]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.000 sec)

root@db1104.eqiad.wmnet[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