fxa - database changes needed for fxa-oauth train-41

RESOLVED FIXED

Status

RESOLVED FIXED
3 years ago
3 years ago

People

(Reporter: jrgm, Assigned: jrgm)

Tracking

Firefox Tracking Flags

(Not tracked)

Details

(Whiteboard: [qa+])

(Assignee)

Description

3 years ago
Putting this in as a placeholder for an actual change plan

The change is effectively this:
https://github.com/mozilla/fxa-oauth-server/blob/master/lib/db/mysql/patches/patch-005-006.sql

So, the new table is a no-op, and the alter on `codes` table has a "small" number of rows. The `tokens` table has a "medium" number of rows, so probably need to be careful with the add of the column based on past experience.
(Assignee)

Comment 1

3 years ago
Actually, I missed the UPDATE of all the rows. I think we should script that and do it lazily, and not in one single UPDATE statement.
(Assignee)

Comment 2

3 years ago
Change plan:

:ckolos - comments? Also, :rfkelly or :dcoates, can you give thumbs-up on the DEFAULT value/strategy in #5

1. Take a snapshot of the fxa RDS instance.

2. Connect via the prod bastion to an admin host and in a screen session connect to fxa-rds-prod mysql on RDS.

3. First the small stuff. Create the new table.

CREATE TABLE refreshTokens (
  token BINARY(32) PRIMARY KEY,
  clientId BINARY(8) NOT NULL,
  INDEX tokens_client_id(clientId),
  FOREIGN KEY (clientId) REFERENCES clients(id) ON DELETE CASCADE,
  userId BINARY(16) NOT NULL,
  INDEX tokens_user_id(userId),
  email VARCHAR(256) NOT NULL,
  scope VARCHAR(256) NOT NULL,
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

4. Alter the `codes` table:

ALTER TABLE codes ADD COLUMN offline BOOLEAN NOT NULL DEFAULT FALSE;

5. Check monitoring, etc., and if we're clear, begin the big alter. Continue to monitor.

ALTER TABLE codes ADD COLUMN createdAt2 TIMESTAMP NOT NULL DEFAULT '2015-08-01 00:00:00'

(Pending approval of above strategy for avoiding the UPDATE).

There should be some errors in the first 2-3 minutes (exact time unknown) but normal operation should resume after that.

6. When the ALTER completes, and all monitoring is green, final change.

UPDATE dbMetadata SET value = '6' WHERE name = 'schema-patch-level';

7. End.

In the event something goes badly wrong, we will need to restore from the
snapshot, which will take ~30 minutes; service will be unavailable.

The big ALTER may also fail and rollback. It's a "feature" - https://bugzilla.mozilla.org/show_bug.cgi?id=1038413#c4
If so, take a pause and then try the ALTER again.
(Assignee)

Comment 3

3 years ago
Getting the right tomlinson on CC:. Sorry nelsontomlinson for the noise. Please ignore.
(Assignee)

Comment 4

3 years ago
I'm looking at starting this change at 9am PDT.
(Assignee)

Comment 5

3 years ago
Okay rfk and ckolos gave thumbs up.
(Assignee)

Comment 6

3 years ago
By the way, typo. This should be 

ALTER TABLE tokens ADD COLUMN expiresAt TIMESTAMP NOT NULL DEFAULT '2015-08-01 00:00:00'
(Assignee)

Comment 7

3 years ago
MySQL [fxa_oauth]> CREATE TABLE refreshTokens (
  token BINARY(32) PRIMARY KEY,
  clientId BINARY(8) NOT NULL,
  INDEX tokens_client_id(clientId),
  FOREIGN KEY (clientId) REFERENCES clients(id) ON DELETE CASCADE,
  userId BINARY(16) NOT NULL,
  INDEX tokens_user_id(userId),
  email VARCHAR(256) NOT NULL,
  scope VARCHAR(256) NOT NULL,
  createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Query OK, 0 rows affected (0.20 sec)
MySQL [fxa_oauth]> MySQL [fxa_oauth]> ALTER TABLE codes ADD COLUMN offline BOOLEAN NOT NULL DEFAULT FALSE;
Query OK, 0 rows affected (10.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [fxa_oauth]> ALTER TABLE tokens ADD COLUMN expiresAt TIMESTAMP NOT NULL DEFAULT '2015-08-01 00:00:00';
Query OK, 0 rows affected (31 min 57.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [fxa_oauth]> desc tokens;
+-----------+--------------+------+-----+---------------------+-------+
| Field     | Type         | Null | Key | Default             | Extra |
+-----------+--------------+------+-----+---------------------+-------+
| token     | binary(32)   | NO   | PRI | NULL                |       |
| clientId  | binary(8)    | NO   | MUL | NULL                |       |
| userId    | binary(16)   | NO   | MUL | NULL                |       |
| email     | varchar(256) | NO   |     | NULL                |       |
| type      | varchar(16)  | NO   |     | NULL                |       |
| scope     | varchar(256) | NO   |     | NULL                |       |
| createdAt | timestamp    | NO   |     | CURRENT_TIMESTAMP   |       |
| expiresAt | timestamp    | NO   |     | 2015-08-01 00:00:00 |       |
+-----------+--------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)

MySQL [fxa_oauth]> desc refreshTokens;
+-----------+--------------+------+-----+-------------------+-------+
| Field     | Type         | Null | Key | Default           | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| token     | binary(32)   | NO   | PRI | NULL              |       |
| clientId  | binary(8)    | NO   | MUL | NULL              |       |
| userId    | binary(16)   | NO   | MUL | NULL              |       |
| email     | varchar(256) | NO   |     | NULL              |       |
| scope     | varchar(256) | NO   |     | NULL              |       |
| createdAt | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+-----------+--------------+------+-----+-------------------+-------+
6 rows in set (0.00 sec)

MySQL [fxa_oauth]> desc codes;
+-----------+--------------+------+-----+-------------------+-------+
| Field     | Type         | Null | Key | Default           | Extra |
+-----------+--------------+------+-----+-------------------+-------+
| code      | binary(32)   | NO   | PRI | NULL              |       |
| clientId  | binary(8)    | NO   | MUL | NULL              |       |
| userId    | binary(16)   | NO   | MUL | NULL              |       |
| email     | varchar(256) | NO   |     | NULL              |       |
| scope     | varchar(256) | NO   |     | NULL              |       |
| createdAt | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| authAt    | bigint(20)   | YES  |     | 0                 |       |
| offline   | tinyint(1)   | NO   |     | 0                 |       |
+-----------+--------------+------+-----+-------------------+-------+
8 rows in set (0.00 sec)

MySQL [fxa_oauth]> UPDATE dbMetadata SET value = '6' WHERE name = 'schema-patch-level';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [fxa_oauth]>
(Assignee)

Comment 8

3 years ago
Okay, all done. Aside from response times from e.g. /account/create being about doubled, no other problems.
Status: NEW → RESOLVED
Last Resolved: 3 years ago
Resolution: --- → FIXED
> Aside from response times from e.g. /account/create being about doubled, no other problems.

Was this expected due to the longish-running table changes, and did it go back to normal once they completed?
Flags: needinfo?(jrgm)
(Assignee)

Comment 10

3 years ago
> Was this expected due to the longish-running table changes, and did it go back to normal once they completed?

Yes, and yes.
Flags: needinfo?(jrgm)
You need to log in before you can comment on or make changes to this bug.