Page MenuHomePhabricator

Drop old oathauth_users columns
Closed, ResolvedPublic

Description

Following on from T225643: Schema change to oathauth_users, the migration scripts have now been run

Command:

ALTER TABLE /*_*/oathauth_users
	DROP COLUMN secret,
	DROP COLUMN scratch_tokens;

When: At DBA's leisure. Not blocked on anything, just removing superfluous columns

Is backwards compatible: Is compatible with current code

has been tested already: Locally, yes. https://gerrit.wikimedia.org/r/#/c/mediawiki/extensions/OATHAuth/+/517729/ to be merged to cleanup beta

labs replicas: n/a - table isn't currently replicated and this is removing a column anyway

Where:

  • centralauth.oathauth_users
  • labswiki.oathauth_users
  • labtestwiki.oathauth_users

Private:

  • advisorswiki.oathauth_users
  • arbcom_cswiki.oathauth_users
  • arbcom_dewiki.oathauth_users
  • arbcom_enwiki.oathauth_users
  • arbcom_fiwiki.oathauth_users
  • arbcom_nlwiki.oathauth_users
  • auditcomwiki.oathauth_users
  • boardgovcomwiki.oathauth_users
  • boardwiki.oathauth_users
  • chairwiki.oathauth_users
  • chapcomwiki.oathauth_users
  • checkuserwiki.oathauth_users
  • collabwiki.oathauth_users
  • ecwikimedia.oathauth_users
  • electcomwiki.oathauth_users
  • execwiki.oathauth_users
  • fdcwiki.oathauth_users
  • grantswiki.oathauth_users
  • id_internalwikimedia.oathauth_users
  • iegcomwiki.oathauth_users
  • ilwikimedia.oathauth_users
  • internalwiki.oathauth_users
  • legalteamwiki.oathauth_users
  • movementroleswiki.oathauth_users
  • noboard_chapterswikimedia.oathauth_users
  • officewiki.oathauth_users
  • ombudsmenwiki.oathauth_users
  • otrs_wikiwiki.oathauth_users
  • projectcomwiki.oathauth_users
  • searchcomwiki.oathauth_users
  • spcomwiki.oathauth_users
  • stewardwiki.oathauth_users
  • techconductwiki.oathauth_users
  • transitionteamwiki.oathauth_users
  • wg_enwiki.oathauth_users
  • wikimaniateamwiki.oathauth_users
  • zerowiki.oathauth_users

Fishbowl:

  • amwikimedia.oathauth_users
  • cnwikimedia.oathauth_users
  • donatewiki.oathauth_users
  • fixcopyrightwiki.oathauth_users
  • foundationwiki.oathauth_users
  • hiwikimedia.oathauth_users
  • idwikimedia.oathauth_users
  • maiwikimedia.oathauth_users
  • nostalgiawiki.oathauth_users
  • punjabiwikimedia.oathauth_users
  • romdwikimedia.oathauth_users
  • rswikimedia.oathauth_users
  • votewiki.oathauth_users
  • wbwikimedia.oathauth_users

Event Timeline

Reedy created this task.Jun 28 2019, 11:45 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 28 2019, 11:45 AM
jcrespo moved this task from Triage to Next on the DBA board.Jun 28 2019, 12:05 PM

I have altered this table on db2054 on centralauth and will leave the columns renamed for a few days to make sure nothing uses it.

root@db2054.codfw.wmnet[centralauth]> alter table oathauth_users change secret TO_DROP_secret varbinary(255) DEFAULT NULL, change scratch_tokens TO_DROP_scratch_tokens varbinary(511) DEFAULT NULL;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db2054.codfw.wmnet[centralauth]> show create table oathauth_users\G
*************************** 1. row ***************************
       Table: oathauth_users
Create Table: CREATE TABLE `oathauth_users` (
  `id` int(11) NOT NULL,
  `TO_DROP_secret` varbinary(255) DEFAULT NULL,
  `TO_DROP_scratch_tokens` varbinary(511) DEFAULT NULL,
  `module` varbinary(255) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.04 sec)
Marostegui triaged this task as Normal priority.
Marostegui moved this task from Next to In progress on the DBA board.

Also changed on an eqiad host (so we can check if there is something reading from those):

root@db1094.eqiad.wmnet[centralauth]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

root@db1094.eqiad.wmnet[centralauth]> alter table oathauth_users change secret TO_DROP_secret varbinary(255) DEFAULT NULL, change scratch_tokens TO_DROP_scratch_tokens varbinary(511) DEFAULT NULL;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0
Reedy added a comment.Jul 1 2019, 12:31 PM

Also changed on an eqiad host (so we can check if there is something reading from those):

root@db1094.eqiad.wmnet[centralauth]> set session sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
root@db1094.eqiad.wmnet[centralauth]> alter table oathauth_users change secret TO_DROP_secret varbinary(255) DEFAULT NULL, change scratch_tokens TO_DROP_scratch_tokens varbinary(511) DEFAULT NULL;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thanks!

There might be some stuff doing SELECT * (I'd have to double check the code, though that might be only the migration/back compat code I've seen), but there shouldn't be any explicit usages SELECT secret, scratch_tokens

Cool, I will give it some more 24h - so far nothing on logstash for db1094

Mentioned in SAL (#wikimedia-operations) [2019-07-03T07:06:10Z] <marostegui> Drop secret and scratch_tokens from fishbowl wiki list T226826

Marostegui updated the task description. (Show Details)Jul 3 2019, 7:07 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-03T07:10:44Z] <marostegui> Drop secret and scratch_tokens from labswiki (wikitech) and labstestwiki - T226826

Marostegui updated the task description. (Show Details)Jul 3 2019, 7:11 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-03T10:00:03Z] <marostegui> Drop secret and stratch_tokens columns from the private wiki list T226826

Marostegui updated the task description. (Show Details)Jul 3 2019, 10:07 AM

Mentioned in SAL (#wikimedia-operations) [2019-07-03T12:55:48Z] <marostegui> Drop secret and stratch_tokens columns from centralauth (s7) T226826

Marostegui added a comment.EditedJul 3 2019, 12:58 PM

centralauth progress

  • labsdb1012
  • labsdb1011
  • labsdb1010
  • labsdb1009
  • dbstore1003
  • db2120
  • db2118
  • db2100
  • db2095
  • db2087
  • db2086
  • db2077
  • db2068
  • db2061
  • db2054
  • db2047
  • db1136
  • db1125
  • db1116
  • db1101
  • db1098
  • db1094
  • db1090
  • db1086
  • db1079
  • db1062
Marostegui closed this task as Resolved.Jul 3 2019, 1:06 PM
Marostegui updated the task description. (Show Details)