1225310 - [fxa] db changes needed for train-50

Status: RESOLVED FIXED

(Cloud Services :: Operations: Deployment Requests - DEPRECATED, task)

Description

[John Morrison [:jrgm]]

database change needed for train-50

db patch https://github.com/mozilla/fxa-auth-db-mysql/blob/master/lib/db/schema/patch-019-020.sql

alter devices table and 3 stored procedures

Question: do we need to resignal the error, or is that only when the stored procedure contains an explicit transaction?

Comment 1

[Ryan Kelly [:rfkelly]]

> do we need to resignal the error, or is that only when the stored procedure
> contains an explicit transaction?

I'm pretty sure this is only needed when there's an explicit transaction.  There are testcases that involve triggering errors from e.g. the unique constraint and AFAIK they pass OK on the mysql backend.

Comment 2

[John Morrison [:jrgm]]

Thanks.

> There are testcases that involve triggering errors from e.g. the unique
> constraint and AFAIK they pass OK on the mysql backend.

Small nit: they can pass and the transaction would be rolled back when the test ends and the connection is closed. But on a long-running connection, the transactions would accumulate, and then things get "fun".

Comment 3

[John Morrison [:jrgm]]

Done.

MySQL [fxa]> select * from fxa.dbMetadata where name = 'schema-patch-level';
+--------------------+-------+
| name               | value |
+--------------------+-------+
| schema-patch-level | 20    |
+--------------------+-------+
1 row in set (0.00 sec)

MySQL [fxa]> show create table devices;
| Table   | Create Table
| devices | CREATE TABLE `devices` (
  `uid` binary(16) NOT NULL,
  `id` binary(16) NOT NULL,
  `sessionTokenId` binary(32) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(16) DEFAULT NULL,
  `createdAt` bigint(20) unsigned DEFAULT NULL,
  `callbackURL` varchar(255) DEFAULT NULL,
  `callbackPublicKey` binary(32) DEFAULT NULL,
  PRIMARY KEY (`uid`,`id`),
  UNIQUE KEY `UQ_devices_sessionTokenId` (`uid`,`sessionTokenId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

1 row in set (0.00 sec)

MySQL [fxa]> select name from mysql.proc where db = 'fxa' and name in ('accountDevices_3','createDevice_1','updateDevice_1');
+------------------+
| name             |
+------------------+
| accountDevices_3 |
| createDevice_1   |
| updateDevice_1   |
+------------------+
3 rows in set (0.02 sec)

MySQL [fxa]>