MySQL-JS Module¶

Enabling MySQL-JS module¶

Important! If reenabling the mysql module, remove the mysql folder: rm -r data/mysql

Step 1¶

Ensure the variables from the MySQL section are in your customized config.sh file.

Mysql¶

T_MYSQL_CONTAINER_NAME="mysql" # Either the name of the mysql Docker container or the hostname of a mysql server or AWS RDS MySQL instance.
T_MYSQL_USER="admin" # Username for mysql credentials
T_MYSQL_PASSWORD="password" # Password for mysql credentials
T_USE_MYSQL_CONTAINER="true" # If using a Docker container, set to true. This will automatically start a mysql container when using a Tangerine launch script.

Step 2¶

Ensure the T_MYSQL_PASSWORD variable is set to a sufficiently secure string. Failure to properly secure this password will without a doubt result in ransomware bots hacking your database.

Step 3¶

Add the mysql module to T_MODULES_ENABLED in config.sh.

For example:

T_MODULES="['csv','mysql-js']"

Step 4¶

Run the start script to load in new configuration. Do this even if your server is already running. Note that restarting the container will not work, we have to run ./start.sh to recreate the container with the new configuration.

./start.sh <version>

Note: Upgrading an older version of Tangerine may require running docker exec tangerine push-all-groups-views after to enable indexes used for mysql

Step 5¶

Clear reporting cache to start generating a MySQL database for each group.

docker exec tangerine reporting-cache-clear

You can check in on the progress of generating the mysql database using the mysql-report command. (Warning The mysql-report command creates a heavy workload to an instance so do not use it when mysql is trying to process a lot of data from couchdb. See the "Troubleshooting" section below.) It will return for each kind of case data and form, how many records are in the source database vs. how many have made it over to mysql. Note that if your system is under heavy load during the processing of this, this command may stress it out even more so it may be best to wait until you see a load of less than one using a tool like top or htop.

docker exec -it tangerine bash 
mysql-report <groupId> | json_pp

Step 6¶

In the reboot instruction in crontab that to starts Tangerine on reboot, add mysql container to the containers that start before tangerine and increase the sleep command to 60 seconds. Failure to implement this will result in tangerine failing to start on reboot.

@reboot docker start couchdb mysql && sleep 60 && docker start tangerine

Also add a cron job to run mysql-report at 1 a.m every day - this will keep the mysql indexes current.

# Run mysql-report at 1 a.m every day:
# 0 1 * * * docker exec tangerine mysql-report group-479f455e-b1bd-481b-8bd7-0d985a07431c

Step 7¶

The most basic way to access MySQL would be to use the MySQL CLI.

docker exec -it tangerine bash
mysql -u"$T_MYSQL_USER" -p"$T_MYSQL_PASSWORD" -hmysql

On the mysql command line, list the available databases using show databases;. Note how the database names are similar to the Group ID's these correspond with except with dashes removed. For example, if the group ID was group-abc-123, the corresponding MySQL database would be groupabc123. To select a database, type use <database ID>; then show tables; to list out the available tables.

Step 8¶

To set up remote encrypted connections to mysql, three options:

TLS: In the tangerine/data/mysql/databases folder you will find files ca.pem, client-cert.pem, and client-key.pem. Distribute those files to your MySQL users so they may connnect to your server's IP addres port 3306 using these certificates. For example, mysql -u admin -p"you-mysql-password" --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem.
SSH: For each person using MySQL, they will need SSH access to the server. When granted, they may use tunneling of mysql port 3306 over SSH to access mysql at 127.0.0.1:3306. For example, to set up an SSH port forwarding on Mac or Linux, run ssh -L 3306:your-server:3306 your-server.
VPN: If you connect to MySQL via the IP address of the server, using a VPN will ensure that communication with MySQL is encrypted. Note however that the traffic will be visible to those also on your VPN so make sure it's a trusted VPN only used by those who have permission to access the data.

Resetting MySQL databases¶

If you need to reset the mysql database, do the following: - stop the mysql docker instance: docker stop mysql - delete ./data/mysql - remove 'mysql-js' from T_MODULES - Run ./start.sh or ./develop.sh. This will remove the mysql-js module from enabledModules in the app couch database's modules doc. See "Disabling modules: mysql-js" in the console to confirm. - add 'mysql-js' to T_MODULES - this will init the mysql databases. - Run ./start.sh or ./develop.sh. This will add the mysql-js module from enabledModules in the app couch database's modules doc and create the databases. See "Enabling modules: mysql-js" in the console to confirm.

Configuration¶

You may add configuration options to ./server/src/mysql-js/conf.d/config-file.js.
If you are using the mysql container and are having errors with very large forms, the new settings in ./server/src/mysql-js/conf.d/config-file.js should help. You will need to completely rebuild the mysql database. Stop the Tangerine and mysql containers. Delete (or -rename) the ./data/mysql directory.
Then restart Tangerine using the ./start.sh or develop.sh script.
Important: If you already have a mysql instance running and don't want to rebuild the mysql database, delete the innodb-page-size=64K line from ./server/src/mysql-js/conf.d/config-file.js; otherwise, your mysql instance will not start.
If making changes to the innodb-page-size option, you must delete the ./data/mysql directory.

Troubleshooting¶

Issue: Data on the Mysql db is far behind the Couchdb.¶

This scenario can happen when replicating data from a Production database on another server instance. Step to triage and resolve this issue:

run docker ps -a to see if the tangerine and couchdb instances are up
Bring back up those instance by using the start.sh script.
Confirm using docker logs -f tangerine that the docker containers are back up and processing data correctly.
If the server must catch up more than a day's worth of documents, use the wedge pre-warm-views at the end of the day to hit all views in the couchdb to pre-warm them (i.e. index those views).
After the indexes have been built, use the mysql-report groupID command to see if the mysql and couchdb databases are caught up.