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
Download on MacOS Example
Here is an example for installing on MacOS Big Sur:
Download the appropriate installer. I had to install the x86, 64-bit version because ARM, 64-bit didn’t work. You may need to left click on the download button so you can choose “Open with” -> intaller. This will give you an “Open” option when you have “unvarified” 3rd-party software.
After installation, start the mysql server by opening the “settings” for your MAC and looking for a new “mysql” icon. These are the installation instructions I followed from oracle if you want more detail. Remember to save your root mysql password that was generated during the installation.
From your terminal, type
mysql -u root -p
and enter the root password you created during installation.
Download on Dori Example
Download for linux
cd $HOME
wget https://dev.mysql.com/get/Downloads/MySQL-8.2/mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz
tar xvf mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz
mv mysql-8.2.0-linux-glibc2.28-x86_64 mysql
cd mysql
export PATH=$HOME/mysql/bin:$PATH # maybe put this in your ~/.bashrc
Create a
.mysql.cnf
file and add your $HOME path or wherever you want to install
[server]
user=jsmith
basedir=/path/to/homes/jsmith/mysql
datadir=/path/to/homes/jsmith/mysql/data
[mysqld]
pid-file=/path/to/homes/jsmith/mysql/mysqld.pid
socket=/path/to/homes/jsmith/mysql/mysqld.sock
port=3306
[client]
socket=/path/to/homes/jsmith/mysql/socket
Initialize the data directory (do this once)
mysqld --initialize-insecure --datadir=$HOME/mysql/data
# start the server
mysqld --defaults-file=$HOME/mysql/.mysql.cnf &
In another termina, BUT ON THE SAME NODE, connect to the mysql server and create some passwords
(You need to be on the same node because you are using localhost to communicate with the mysql-server)
Replace ‘newpassword’ with something for root. (replace $HOME if necessary)
mysql --host=localhost --socket="$HOME/mysql/mysqld.sock" -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
quit
Restart the mysql-server so it will see previous changes.
# from the terminal with the mysql-server
kill %1
mysqld --defaults-file=$HOME/mysql/.mysql.cnf &
Continue to next step “Create a Cromwell database”
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.