Reset demo environment daily

[kaihendry]

We need to be able reset the {dev,demo} environments to a clean slate, with the correct username/passwords et al: https://media.dev.unee-t.com/2019-02-14/Users_we_need_in_a_sane_DEV_Staging_environment.txt

Approaches to achieve that:

1. Golden image

A snapshot is made of both the Meteor & Bugzilla database at the same time. We use that to restore.

Pros:

• Easy and fast if the backup is local. Note the backup directories in frontend and bugzilla.

Cons:

• In the past the schema has been old and it needs updating. This can be difficult to co-ordinate.
• Strictly speaking it doesn't start from scratch. There could be some consistency issue we are simply unaware of since the golden cut might be bad for one reason or another.
• The data could just come polluted with garbage over time
• We would need to advertise the SQL dump somewhere, so that developers can download it and use that to prime their database

2. Reset to clean schema

Pros:

• Minimal
• Clean
• Sane
• Precise
• Easier to support local development

Cons:

• In practice the "clean" schema isn't usable, since it's not maintained
• Often misses little nuances and correct users and working data
• Requires some manual setup with the frontend

3. Have a system to clear out "bad data"

Pros:

• No fiddling with the schema
• No downtime

Cons:

• hard to define or even find bad data like zombie users/units
• there are tools to test with transactions that roll back after the test complete, but they are difficult to use

[nbiton]

Here are the "zombie" units. They get listed when a new user is created (and that user should see no units)

[kaihendry]

I've reset the dev environment to a snapshot I made on Feb 14th. I thought the UIlicious test passed, but it hasn't. Argh. https://client-aysiq7n9tw8hfu5rvr6mzf.uilicious.com/studio/project/NAigEgKD6y8qeNMrAZ65oF/monitoring/job/9iYLSHxEJPPxRDQttVSZzz/testRun/list

Reran and it worked.

[franck-boullier]

@kaihendry I just update the DB schema for the BZ database to the latest version (v4.33.1) it wasn't the correct version (was v4.31)

Maybe you want to take another snapshot of the environment so we have an updated "Golden Image" until we figure out the right way to do the Reset.

[franck-boullier]

In practice the "clean" schema isn't usable, since it's not maintained

@kaihendry what makes you say that? Until proven wrong both version of the "clean" DB schema are always up to date to the latest version of the BZ DB schema.
See:

• https://github.com/unee-t/bz-database/blob/master/db%20snapshots/unee-t_BZDb_clean_with_demo_users_and_unit_current.sql
• https://github.com/unee-t/bz-database/blob/master/db%20snapshots/unee-t_BZDb_clean_current.sql

[kaihendry]

This issue is related unee-t/frontend#203 in the sense we need to document a baseline "fresh" demo environment.

I just tried https://github.com/unee-t/bz-database/blob/master/db%20snapshots/unee-t_BZDb_clean_with_demo_users_and_unit_current.sql but I hit ERROR 1449 (HY000) at line 16391: The user specified as a definer ('unee_t_root'@'%') does not exist.

Previously I removed them in #127 but I am not 100% sure that is the right approach.

[franck-boullier]

Previously I removed them in #127 but I am not 100% sure that is the right approach.

Removing the DEFINER is the right approach in that situation.

[kaihendry]

Before I did a gnarly

`sed 's|/\*!50017 DEFINER=`unee_t_root`@`%`\*/||g'`` 

However in this dump these role lines with:

/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`unee_t_root`@`%` SQL SECURITY DEFINER VIEW

50001 instead of 50017 🤷‍♂️

To avoid errors in the pipeline, couldn't https://github.com/unee-t/bz-database/blob/master/db%20snapshots/unee-t_BZDb_clean_with_demo_users_and_unit_current.sql just be ready to go and not need processing? I.e. what #127 asks of.

[kaihendry]

After cleaning out the DEFINER lines, now I am hitting Duplicate column name 'visibility_value_id'

[franck-boullier]

After cleaning out the DEFINER lines, now I am hitting Duplicate column name 'visibility_value_id'

My guess is that this is is because you are running the checksetup.pl BZ script.

The DB seed script assumes that it's run on an existing BZ vanilla installation (I think).

You should only restore the Seed DB script AFTER the BZ vanilla is setup.

[franck-boullier]

To create a Unee-T envo you need to:
1- get a clean bugzilla install
2- customize the BZ install
3- run the DB seed script
4- run the script to make sure the lambda are correctly configured for whichever environment you're creating.

Is there a tried and tested documentation somewhere of how you setup a Unee-T environment from scratch?

[kaihendry]

1. Initialise uneet/bugzilla-customisation with a blank DB (i.e. no bugzilla database)
2. bugzilla's checksetup.pl creates tables
3. ./restore.sh -l upgrade_schema_from_bz_5_0_6_to_BZFE_5_39_1.sql
4. ./restore.sh -l unee-t_BZDb_clean_current.sql

And now we have baseline bugzilla plus Unee-T customisations: https://media.dev.unee-t.com/2019-09-13/localhost-backup-2019-09-13.sql

TODO

1. 'arn:aws:lambda:ap-southeast-1:192458993663:function:ut_lambda2sqs_push' rewrite with appropriate account number
2. Set admin key

UPDATE `user_api_keys`
SET `api_key` = '[API_ACCESS_TOKEN]'
WHERE `id` = 1
;

I just ran through the steps and restarted bugzilla & we still have the duplicate column issue as mentioned earlier #125 (comment)

https://s.natalian.org/2019-09-13/dup-col-issue.mp4

This appears fixed by #142

[kaihendry]

Just tested https://media.dev.unee-t.com/2019-09-13/localhost-backup-1568357621.sql

    db:
        image: mysql:5.7
        ports:
            - 3306:3306
        environment:
            MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
            MYSQL_DATABASE: ${MYSQL_DATABASE}
            MYSQL_USER: ${MYSQL_USER}
            MYSQL_PASSWORD: ${MYSQL_PASSWORD}
        volumes:
            - /home/hendry/unee-t/bugzilla-customisation/backup/localhost-backup-1568357621.sql:/docker-entrypoint-initdb.d/main.sql
            - ./sql-conf.d:/etc/mysql/conf.d

And it works. So next steps for readying for demo table are to:

1 'arn:aws:lambda:ap-southeast-1:192458993663:function:ut_lambda2sqs_push' rewrite with appropriate account number 915001051872, done in https://media.dev.unee-t.com/2019-09-13/demo-baseline-2019-09-13.sql
2. Set BUGZILLA_ADMIN_KEY (this is a secret) in Bugzilla's user_api_keys for user_id 1
3. Work out what changes are needed in Mongo @nbiton
4. Work out what changes are needed in UNTE @franck-boullier
5. Worry about fine grained permissions aka CREATE DEFINER=root@% that should be unee_t_root? #138

[kaihendry]

I managed to export a dump without

ALTER DATABASE `bugzilla` CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

using adminer which worked in a restore here: https://media.dev.unee-t.com/2019-09-13/168-fix.sql I did the restore as root btw.

[kaihendry]

I did a connect -d for demo and used meteor and issued a db.dropDatabase(). And then when into ECS to restart the meteor service by scaling to 0 tasks and then backup 2.

On restart meteor appeared to recreate the meteor collection.

I created an account to check and also ran [CASE_DEMO] new-user-flow - user journey - chrome (demo laptop) which all passed.

Next steps is try automate these steps. Not easy to co-ordinate restarting the ECS service aws ecs update-service --force-new-deployment --service my-service

[kaihendry]

Over the weekend I've noticed a lot of Delivery Status Notification (Failure) with bodies like so:

An error occurred while trying to deliver the mail to the following recipients:
[email protected]

These are triggered by pre-seeded users getting alerts to new sign ins generated by https://uilicious.com of the demo environment.

Solutions is to update https://media.dev.unee-t.com/2019-09-13/168-fix.sql with test emails that can handle the notifications. Or come up with some other procedure on how to handle notifications for the pre-existing users.

[kaihendry]

I edited the emails and use https://media.dev.unee-t.com/2019-09-16/demo-baseline.sql to restore. I also created some scripts to stop/start the application:

[hendry@t480s backup]$ cat stop-services.sh
for s in meteor bugzilla
do
	aws ecs --profile uneet-demo update-service --cluster master --service ecscompose-service-$s --desired-count 0
done
[hendry@t480s backup]$ cat start-services.sh
for s in meteor bugzilla
do
	aws ecs --profile uneet-demo update-service --cluster master --service ecscompose-service-$s --desired-count 2
done