Page MenuHomePhabricator

Apply schema changes for OAuth 2.0
Closed, ResolvedPublic

Description

From the checklist at https://wikitech.wikimedia.org/wiki/Schema_changes:

  1. ALTERs to run:
  2. Where to run those changes: Only where these tables already exist: foundationwiki, labswiki, mediawikiwiki, metawiki, and maybe labtestwiki
  3. When to run those changes: From a technical standpoint, there is no time constraint. @CCicalese_WMF or @eprodromou will have to speak to whether there's any product deadline.
  4. If the schema change is backwards compatible: Yes. No fields or indexes are being removed or changed. All added fields have default values or are nullable.
  5. If the schema change has been tested already on some of the test/beta wikis: It will be auto-deployed to Beta. No testing has been done there yet.
  6. If the data should be made available on the labs replicas and/or dumps: No.

Progress:

Tables created on:

Schema change progress:

Progress:

  • foundationwiki (s3)
  • mediawikiwiki (s3)
  • metawiki (s7)
  • labswiki
  • labtestwiki T238370#5673010

Details

Related Gerrit Patches:

Event Timeline

Anomie created this task.Thu, Nov 14, 8:50 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptThu, Nov 14, 8:50 PM
Marostegui triaged this task as Medium priority.Fri, Nov 15, 6:13 AM
Marostegui moved this task from Triage to Next 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

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).

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.

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.

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

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.

Anomie updated the task description. (Show Details)Mon, Nov 18, 5:26 PM

@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)Mon, Nov 18, 5:31 PM

I applied all those changes to labtestwiki

Marostegui updated the task description. (Show Details)Tue, Nov 19, 5:37 AM

I applied all those changes to labtestwiki

Thank you!

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

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

Marostegui updated the task description. (Show Details)Tue, Nov 19, 1:48 PM

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)Tue, Nov 19, 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)Tue, Nov 19, 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
Marostegui closed this task as Resolved.Tue, Nov 19, 1:59 PM
Marostegui claimed this task.
Marostegui updated the task description. (Show Details)

This is all done

Anomie updated the task description. (Show Details)Fri, Nov 22, 4:35 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!