Configuring MySQL Database for Cromwell

When developing or testing your WDL scripts with the Cromwell command, it’s beneficial, though not mandatory, to take advantage of call-caching. This feature enables you to re-execute a WDL, but only the tasks that previously failed are recomputed. Successfully completed tasks are saved in a database, preventing the need to re-run them. This approach can significantly reduce processing time. To implement this, you must set up a MySQL database for Cromwell.

How do you know if a task(s) was cached?

check for a line similar to this in the cromwell stdout Job results retrieved (CallCached): '<myWorkflow>.<myTask>' (scatter index: None, attempt 1)
under the cromwell-execution directory, you should see a folder called cacheCopy for the task in question.

Installing MySQL Database for Cromwell

Cromwell can be run in two different ways, Server mode, and Run mode. Although JAWS is using Server mode, we are only concerned with Run mode for all the examples in this documentation.

Unless you are submitting WDLs to a cromwell server, a pre-requisite for call-caching to work is to set up a database (not exactly true but the alternative is problematic as the time of this writing).

Here is step-by-step tutorial on how to set up the database.

Install the mysql server from oracle

Create a Cromwell database

You need to create a database with the same name as what is to be used in the cromwell_dori.conf file (see below). For this example we use the db name, cromwell. You also need to create a non-root username & password for yourself with full privileges.

Reconnect to mysql server and use root password

# for Mac
mysql -u root -p

# for Dori
mysql --host=localhost --socket="$HOME/mysql/mysqld.sock" -u root -p

# create database for cromwell to use
create database cromwell;

# create user with all privileges.
# if your username is jsmith then run the following to create a new user and password.
CREATE USER 'jsmith'@'localhost' IDENTIFIED BY 'someuserpassword';
GRANT ALL PRIVILEGES on cromwell.* to 'jsmith'@'localhost';
(DROP USER 'jsmith'@'localhost';) if you make a mistake

quit

You should now have two user passwords, one for root and one for your username. Your username and user-password are used in the cromwell config file, not the root password.

At the bottom of the cromwell.config file, add

database
{
  profile = "slick.jdbc.MySQLProfile$"
  db
  {
    driver = "com.mysql.cj.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/cromwell?rewriteBatchedStatements=true&useSSL=false&autoReconnect=true&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&allowPublicKeyRetrieval=true"
    user = "jsmith"
    password = "someuserpassword"
    connectionTimeout = 5000
  }
  insert-batch-size = 2000
}

Then run cromwell (e.g. that was installed locally). Installing Cromwell.

Remember to point to the cromwell config and override dockerRoot and root. Also, make sure the mysql server is running (see above).

java -Dconfig.file=cromwell_dori.conf \
  -Dbackend.providers.Local.config.dockerRoot=$(pwd)/cromwell-executions \
  -Dbackend.providers.Local.config.root=$(pwd)/cromwell-executions \
  -jar ~/cromwell/cromwell-83.jar run my.wdl

You can get the cromwell config files by cloning :bash:jaws-tutorial-examples and checking out config_files. You’d use the cromwell_docker.conf file if you installed on a Mac.

git clone https://code.jgi.doe.gov/official-jgi-workflows/wdl-specific-repositories/jaws-tutorial-examples.git
cd config_files

Note

Here we are employing a config file to point to our local version of the mysql database but we are using the command line to override some variables. You could instead hardcode the paths for dockerRoot and root in the cromwell_dori.conf file, which would simplify the command.