⚓ T238370 Apply schema changes for OAuth 2.0

	Subject	Repo	Branch	Lines +/-
	realm.pp: Add oauth2_access_tokens as a private table	operations/puppet	production	+1 -0

Anomie created this task.Nov 14 2019, 8:50 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 14 2019, 8:50 PM

• Marostegui triaged this task as Medium priority.Nov 15 2019, 6:13 AM

• Marostegui moved this task from Triage to Pending comment on the DBA board.

This schema change can probably be done directly on the master, as the tables are quite small. Maybe metawiki will be done on the replicas (96M on disk) but will most likely be done without depooling.

The table being created: oauth2_access_tokens I assume it will be private, right?
If so I will need to restart the sanitarium hosts to apply the new filters to include it. Please do not create until I give the green light for it (I assume the table will be created by you as it is not a schema change per se?)

cc @Andrew looks like these schema changes need to be applied to labswiki and labtestwiki as well.

Change 551140 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] realm.pp: Add oauth2_access_tokens as a private table

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

gerritbot added a project: Patch-For-Review.Nov 15 2019, 8:34 AM

In T238370#5666300, @Marostegui wrote:

The table being created: oauth2_access_tokens I assume it will be private, right?

Correct, I think. It should be treated the same as the existing oauth_accepted_consumer table.

(I assume the table will be created by you as it is not a schema change per se?)

I can if you want me to. But I can't seem to access labtestwiki's database from mwmaint1002, so I'll be unable to do that one (unless there's some other way to get to that wiki's database that you can teach me).

In T238370#5666914, @Anomie wrote:

In T238370#5666300, @Marostegui wrote:

The table being created: oauth2_access_tokens I assume it will be private, right?

Correct, I think. It should be treated the same as the existing oauth_accepted_consumer table.

Cool - I have the patch ready, will merge and reload filters next week.

(I assume the table will be created by you as it is not a schema change per se?)

I can if you want me to. But I can't seem to access labtestwiki's database from mwmaint1002, so I'll be unable to do that one (unless there's some other way to get to that wiki's database that you can teach me).

labswiki and labtestwiki are now maintained by cloud-services-team so probably @Andrew @Bstorm would need to point and/or create the table for you

For the record,

anomie@mwmaint1002:~$ sql labswiki
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 23811339
Server version: 10.1.39-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

wikiadmin@10.64.32.72(labswiki)> Bye
anomie@mwmaint1002:~$ sql labtestwiki
^C
^C^C^C
^Z
[1]+  Stopped                 sql labtestwiki
anomie@mwmaint1002:~$ kill %1
anomie@mwmaint1002:~$ 
[1]+  Terminated              sql labtestwiki
anomie@mwmaint1002:~$ mwscript shell.php --wiki=labswiki
Psy Shell v0.9.9 (PHP 7.2.24-1+0~20191026.31+debian9~1.gbpbbacde+wmf1 — cli) by Justin Hileman
>>> wfGetDB( DB_REPLICA )->selectRow( 'page', 1, '' )
=> {#2575
     +"1": "1",
   }
>>> ^D
Exit:  Ctrl+D
anomie@mwmaint1002:~$ mwscript shell.php --wiki=labtestwiki
Psy Shell v0.9.9 (PHP 7.2.24-1+0~20191026.31+debian9~1.gbpbbacde+wmf1 — cli) by Justin Hileman
>>> wfGetDB( DB_REPLICA )->selectRow( 'page', 1, '' )
Wikimedia/Rdbms/DBConnectionError with message 'Cannot access the database: Unknown error (10.192.20.11)'
>>> ^D
Exit:  Ctrl+D

I doubt labtestwiki has replicas...

labtestwiki lives somewhere within cloud land, I don't remember exactly the hostname, let's wait for @Andrew.
I guess sql tool should be patched to be able to get access to labtestwiki?
We should probably handle accesses to labtestwiki from mwmaint1002 in a different task.

In T238370#5667323, @Marostegui wrote:

labtestwiki lives somewhere within cloud land, I don't remember exactly the hostname, let's wait for @Andrew.

It's hosted on clouddb2001-dev.codfw.wmnet. Let me know if you need me to make firewall changes.

In T238370#5668442, @Andrew wrote:

In T238370#5667323, @Marostegui wrote:

labtestwiki lives somewhere within cloud land, I don't remember exactly the hostname, let's wait for @Andrew.

It's hosted on clouddb2001-dev.codfw.wmnet. Let me know if you need me to make firewall changes.

Created T238514: Access from mwmaint to labtestwiki not working for tracking this.
@Andrew please note that this schema change will need to be applied to labtestwiki by cloud-services-team.
I will take care of the core MW databases + labswiki.

Change 551140 merged by Marostegui:
[operations/puppet@production] realm.pp: Add oauth2_access_tokens as a private table

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

Mentioned in SAL (#wikimedia-operations) [2019-11-18T07:17:52Z] <marostegui> Upgrade and restart mysql on sanitarium hosts on codfw to pick up new replication filters: db2094 and db2095 - T238370

Maintenance_bot removed a project: Patch-For-Review.Nov 18 2019, 8:10 AM

Mentioned in SAL (#wikimedia-operations) [2019-11-18T09:03:20Z] <marostegui> Restart MySQL on db1124 and db1125 to apply new replication filters T238370

@Anomie you can create the tables whenever you like, the replication filters are in place. Let me know though when they are created so I can double check they worked as expected and the table never made it to the wiki replicas.

In T238370#5670422, @Marostegui wrote:

@Anomie you can create the tables whenever you like, the replication filters are in place. Let me know though when they are created so I can double check they worked as expected and the table never made it to the wiki replicas.

@Marostegui: Done on foundationwiki, labswiki, mediawikiwiki, and metawiki. @Andrew will have to do labtestwiki.

Thanks @Anomie, filters are working fine!

root@labsdb1009:/srv/sqldata# find . -name oauth2_access_tokens*
root@labsdb1009:/srv/sqldata#

• Marostegui updated the task description. (Show Details)Nov 18 2019, 5:31 PM

I applied all those changes to labtestwiki

In T238370#5673010, @Andrew wrote:

I applied all those changes to labtestwiki

Thank you!

• Marostegui updated the task description. (Show Details)Nov 19 2019, 1:44 PM

Mentioned in SAL (#wikimedia-operations) [2019-11-19T13:46:57Z] <marostegui> Deploy schema change on labswiki (wikitech) - T238370

labswiki is done:

root@db1133.eqiad.wmnet[labswiki]>  ALTER TABLE /*_*/oauth_accepted_consumer
    -> ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1133.eqiad.wmnet[labswiki]> ALTER TABLE /*_*/oauth_registered_consumer
    ->     ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (1.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1133.eqiad.wmnet[labswiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_allowed_grants BLOB NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1133.eqiad.wmnet[labswiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Mentioned in SAL (#wikimedia-operations) [2019-11-19T13:50:02Z] <marostegui> Deploy schema change on foundationwiki directly on s3 master - T238370

foundationwiki done directly on the master with replication enabled:

root@db1123.eqiad.wmnet[foundationwiki]> ALTER TABLE /*_*/oauth_accepted_consumer
    -> ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[foundationwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    ->     ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[foundationwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_allowed_grants BLOB NULL;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[foundationwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

• Marostegui updated the task description. (Show Details)Nov 19 2019, 1:51 PM

Mentioned in SAL (#wikimedia-operations) [2019-11-19T13:55:12Z] <marostegui> Deploy schema change on mediawikiwiki directly on s3 master T238370

mediawikiwiki done directly on the master with replication enabled:

root@db1123.eqiad.wmnet[mediawikiwiki]> ALTER TABLE /*_*/oauth_accepted_consumer
    -> ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[mediawikiwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    ->     ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[mediawikiwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_allowed_grants BLOB NULL;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1123.eqiad.wmnet[mediawikiwiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

• Marostegui updated the task description. (Show Details)Nov 19 2019, 1:56 PM

Mentioned in SAL (#wikimedia-operations) [2019-11-19T13:57:08Z] <marostegui> Deploy schema change on mediawikiwiki directly on s7 master T238370

Mentioned in SAL (#wikimedia-operations) [2019-11-19T13:57:22Z] <marostegui> Deploy schema change on metawiki directly on s7 master T238370

metawiki done directly on the master with replication enabled:

root@db1062.eqiad.wmnet[metawiki]> ALTER TABLE /*_*/oauth_accepted_consumer
    -> ADD oaac_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (6.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1062.eqiad.wmnet[metawiki]> ALTER TABLE /*_*/oauth_registered_consumer
    ->     ADD oarc_oauth_version TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (1.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1062.eqiad.wmnet[metawiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_allowed_grants BLOB NULL;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@db1062.eqiad.wmnet[metawiki]> ALTER TABLE /*_*/oauth_registered_consumer
    -> ADD oarc_oauth2_is_confidential TINYINT NOT NULL DEFAULT 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

This is all done

• Marostegui moved this task from Backlog to Done on the Schema-change-in-production board.Nov 19 2019, 2:00 PM

I added https://gerrit.wikimedia.org/r/c/mediawiki/extensions/OAuth/+/551839/7/schema/mysql/index_on_oaat_acceptance_id.sql to this task, and applied it on all the relevant wikis (per IRC approval from marostegui). Including labtestwiki, thanks for fixing that!

Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:41 PM

Apply schema changes for OAuth 2.0
Closed, ResolvedPublic
Actions

Description

Details

Related Objects

Event Timeline

Apply schema changes for OAuth 2.0Closed, ResolvedPublicActions

Description

Details

Related Objects

Event Timeline

Apply schema changes for OAuth 2.0
Closed, ResolvedPublic
Actions