Update docker to use Aurora instead of MariaDB

[franck-boullier]

Since we need/want to use Aurora to benefit from AWS Lambda notification capabilities, we need to make sure that the docker uses Aurora at build time.

This is a Major update and will make Unee-T more dependent on AWS (until further releases) but this seems to be the most efficient and scalable way forward at this point.

We probably also need to update the README wherever relevant.

@kaihendry feel free to add/comment if necessary

[kaihendry]

I'm struggling to migrate the mariadb "bugzilla" content to the new "abugzilla" Aurora one I've created in the staging environment.

ERROR 2013 (HY000) at line 13878: Lost connection to MySQL server during query
ERROR 2013 (HY000) at line 13849: Lost connection to MySQL server during query

I backed up as root (or am I supposed to use a the user account?):

mysqldump -h db.dev.unee-t.com -P 3306 -u root --password=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_ROOT_PASSWORD --with-decryption --query Parameters[0].Value --output text) --all-databases > dev-root-backup.sql

dev-root-backup.sql is 17GB btw.

restore to Aurora is failing with Lost connection to MySQL server during query:
mysql -h abugzilla.c5eg6u2xj9yy.ap-southeast-1.rds.amazonaws.com -P 3306 -u root --password=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_ROOT_PASSWORD --with-decryption --query Parameters[0].Value --output text) < dev-root-backup.sql

https://ap-southeast-1.console.aws.amazon.com/rds/home?region=ap-southeast-1#dbinstance:id=abugzilla

[franck-boullier]

it might be a packet size issue actually...
Let me do some research on that

[kaihendry]

Btw once the Aurora DB is prepared, no Docker config changes are needed.

IIUC db.dev.unee-t.com CNAME needs to be updated to abugzilla.c5eg6u2xj9yy.ap-southeast-1.rds.amazonaws.com. Though the security group should be double checked.

[franck-boullier]

2 things:
1- "db.dev.unee-t.com CNAME needs to be updated"
I am VERY scared by this approach: what will happen to the cases which are created while the DNS propagates? There is no way to know in which instance they will end up...
I'd prefer to have a "instantaneous" solution even if this creates a few minutes downtime, can you think of a way to do that?

2- "no Docker config changes are needed"
What about the scenario when a developer needs to create the environment to contribute? TOday dockers builds a MariaDB instance and this will NOT work once we implement the lambda calls. There is a very high chance that MariaDB will not know how to handle these lambda call and throw an error each time someone wants to update a case.
Dockers needs to make it clear that it is a mandatory requirement to deploy the BZFE on Aurora only IMO

[franck-boullier]

There is also a mention of Aurora not liking MyISAM too so we probably need to consider how we can force the BZ checksetup script to accomodate that too...

https://aws.amazon.com/premiumsupport/knowledge-center/migrate-mysql-aurora/

This migration is non trivial I agree but do we have a better alternative?

[kaihendry]

1. We can create a new CNAME. auroradb.dev.unee-t.com and trigger a bugzilla re-deployment. That will be more instant. On production you probably have to have downtime to be 100% sure the data is synced.

2. I assumed the stored procedures that trigger lambda would just failed on mariadb on a local instance. Tbh I don't know. This is big down side to integrating with AWS, it is very difficult if not impossible to replicate the functionality locally (off cloud).

[franck-boullier]

"We can create a new CNAME. auroradb.dev.unee-t.com and trigger a bugzilla re-deployment"
I like that idea!

"I assumed the stored procedures that trigger lambda would just failed on mariadb on a local instance."
Yeah, really not sure about that so I'm super cautious here...

"it is very difficult if not impossible to replicate the functionality locally (off cloud)."
I understand that so that's why we probably need to strongly recommend/assume by default that it needs an Aurora RDS setup first inside AWS and use the parameter store logic (tell them that they need to have this parameter somewhere?

[kaihendry]

This is working now in the dev (Aurora MySQL 5.7.12 db.t2.medium) and demo (Aurora MySQL 5.7.12
db.t2.small) environment. Dev has a working Lambda.

[hendry@t480s ~]$ mysql -h auroradb.dev.unee-t.com -P 3306 -u root --password=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_ROOT_PASSWORD --with-decryption --query Parameters[0].Value --output text)
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 222954
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> GRANT EXECUTE ON *.* TO 'bugzilla'@'%';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> Bye
[hendry@t480s ~]$ mysql -h auroradb.dev.unee-t.com -P 3306 -u bugzilla --password=$(aws --profile uneet-dev ssm get-parameters --names MYSQL_PASSWORD --with-decryption --query Parameters[0].Value --output text) bugzilla
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 MySQL connection id is 222960
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [bugzilla]> CALL mysql.lambda_async( 'arn:aws:lambda:ap-southeast-1:812644853088:function:alambda_simple',  '{ "operation" : "ping" }' );
Query OK, 0 rows affected (0.10 sec)

MySQL [bugzilla]>

Local Docker mariadb will obviously not work since mysql.lambda_async is only available on AWS Aurora.

[kaihendry]

At this point, local DB development is limited. i.e. it will be very confusing for new contributors when triggers fail to run Aurora proprietary lambda procedures. No Docker image exists to avoid this.

For day to day local development, perhaps the errors can ignored, since they would only be pertaining to notifications.

Giving new contributors access to an AWS staging environment should be considered.