1169698 - Please deploy Splice 1.1.19 to Production

Status: VERIFIED FIXED

(Content Services Graveyard :: Tiles: Ops, defect)

Description

[Karl Thiessen [:kthiessen, he/him]]

+++ This bug was initially created as a clone of Bug #1169398 +++

Fixes a bug in 1.1.18 where the server sent a wrong data validation schema to the JS front-end

Details of the deployment are in bug 1169398.

Comment 1

[Benson Wong [:mostlygeek]]

There are SQL schema changes. Could they be listed here in the order they are to be applied?

Comment 2

[Karl Thiessen [:kthiessen, he/him]]

That's certainly a reasonable request.  The order I applied them in staging is:

    Starting from directory mozilla/splice/ddl/migrations/redshift:
    $ psql -h redshift.tiles.stage.mozaws.net -U splice tiles < 1431479872.sql
    $ psql -h redshift.tiles.stage.mozaws.net -U splice tiles < 1431479872.sql
    $ psql -h redshift.tiles.stage.mozaws.net -U splice tiles < 1431545807.sql
    $ psql -h redshift.tiles.stage.mozaws.net -U splice tiles < 1432220165.sql
    $ psql -h redshift.tiles.stage.mozaws.net -U splice tiles < 1432589405.sql

This seems to have worked correctly.  However, since I was informed that the changes are not necessarily in filename-as-timestamp order, I will defer to Tim to specify which order should be used in production.

Tim, do you see any problem with this order?

Comment 3

[Daniel Thorn [:relud]]

this list seems to duplicate 1431479872.sql, and lack 1431100409.sql from Bug 1169377

Comment 4

[Benson Wong [:mostlygeek]]

It seems we should start naming the schema change files to this format: 

- revision-<num>.sql
- rollback-<num>.sql

At least until splice moves off of Redshift into it's own postgres db.

Comment 5

[Daniel Thorn [:relud]]

if by <num> you mean the release revision that first included the migration (i.e. revision-1.1.18.sql), +100

Comment 6

[Daniel Thorn [:relud]]

correction: if by <num> you mean the release revision that first included the migration, plus a counter (i.e. revision-1.1.148-1.sql, revision-1.1.148-2.sql), +100

Comment 7

[Karl Thiessen [:kthiessen, he/him]]

(In reply to Daniel Thornton [:relud] from comment #3)
> this list seems to duplicate 1431479872.sql, and lack 1431100409.sql from
> Bug 1169377

ARGH!  This is why I always want two sets of eyes on changes.  Daniel is correct.

It looks like 1431100409.sql did not get added on staging; the 'blacklist' boolean is not in the table impression_stats_daily.  Did any of the testing test whether that blacklist boolean actually works?

Comment 8

[Karl Thiessen [:kthiessen, he/him]]

And yes, version-sequence numbering for migrations sounds like a splendid idea to me.  Will greatly reduce this sort of error.

Comment 9

[Benson Wong [:mostlygeek]]

By <num> I mean revision-0001, revision-0002, revision-0003, etc. It doesn't need to be based on the release version. It only needs to make it easy for us to identify what order to apply them in.

Comment 10

[Tim Spurway [:tspurway]]

hey guys,

sorry i haven't responded sooner.  I noticed the error, and responded to the bug via email, but obviously didn't realize bugzilla doesn't like that!

agreed on all counts of having working migrations.  actually, the real issue is that the migration system we have doesn't work with RedShift, so we jerry-rigged one, which is almost *completely* manual, and, as we have seen severely prone to errors!

One possible solution is to try to fix our processes around migrations, though, I really think we should at least explore actually fixing SQLAlchemy so that it works properly with RedShift!  Much FLOSSy karma is to be gained on this path, and ultimately will yield a superior (more reliable) result.

We have a 'hack week' coming up next week; Suggested Tiles has been unceremoniously pushed back to release 40, so we might just have the time to pull it off as well!

Comment 11

[Karl Thiessen [:kthiessen, he/him]]

This is the order used in stage:

1431479872.sql
1431545807.sql
1432220165.sql
1432589405.sql
1431100409.sql

Looking at the changelogs back to 1.12, this looks ok to me.

Comment 12

[Daniel Thorn [:relud]]

migration order looks correct to me

Comment 13

[Benson Wong [:mostlygeek]]

Performing the migration and deployment now.

Comment 14

[Benson Wong [:mostlygeek]]

New splice deployed successfully.

Comment 15

[Benson Wong [:mostlygeek]]

:mardak please deploy a set of tiles to verify everything works

Comment 16

[Ed Lee :Mardak]

I deployed new tiles for bug 1169658 and they are indeed live with 1.1.19 per attachment 8613785 [details]

Comment 17

[Daniel Thorn [:relud]]

migration 1431100409.sql created a table that infernyx needs access to. GRANT migrations will be run as per https://mana.mozilla.org/wiki/display/SVCOPS/Tiles+Runbooks#TilesRunbooks-RedshiftSQLmigration steps 4 and 5, with the following grants on step 5:
> GRANT SELECT ON public.blacklisted_ips TO infernyx;
> GRANT DELETE ON public.blacklisted_ips TO infernyx;

Comment 18

[Daniel Thorn [:relud]]

step 4 migration completed:
> $ echo "
> SELECT
>   'GRANT INSERT ON public.' ||
>     table_name ||
>     ' TO infernyx;' ||
>     ' GRANT SELECT ON public.' ||
>     table_name ||
>     ' TO read_only;'
>   FROM information_schema.tables WHERE table_schema LIKE 'public';
> " | psql | grep -Fw GRANT | psql
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT
> GRANT

step 5 migration completed:
> $ echo "
> GRANT SELECT ON public.blacklisted_ips TO infernyx;
> GRANT DELETE ON public.blacklisted_ips TO infernyx;
> " | psql
> GRANT
> GRANT

Comment 19

[Tim Spurway [:tspurway]]

Permission for infernyx user are OK