.. _mysql setup:

=======================================
Configuring MySQL Database for Cromwell
=======================================

.. role:: bash(code)
   :language: bash


When developing or testing your WDL scripts with the Cromwell command, it's beneficial, though not mandatory, 
to take advantage of `call-caching <https://cromwell.readthedocs.io/en/stable/cromwell_features/CallCaching/>`_. 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?
****************************************

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

**************************************
Installing MySQL Database for Cromwell
**************************************

Cromwell can be run in two different ways, :bash:`Server` mode, and :bash:`Run` mode. Although JAWS is using :bash:`Server` mode, we are only concerned with :bash:`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 MySQL downloads <https://dev.mysql.com/downloads/mysql/>`_

.. dropdown::  Download on MacOS Example
    :color: info
    :animate: fade-in

    `MacOS installer <https://dev.mysql.com/downloads/mysql/>`_

    Here is an example for installing on MacOS Big Sur:

    1. 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.

    2. 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 installation guide <https://dev.mysql.com/doc/refman/5.7/en/macos-installation-pkg.html>`_ if you want more detail.  Remember to save your root mysql password that was generated during the installation.

    3. From your terminal, type :bash:`mysql -u root -p` and enter the root password you created during installation.


.. dropdown::  Download on Dori Example
    :color: info
    :animate: fade-in

    1. Download for linux

    .. code-block:: text

        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
    
    2. Create a :bash:`.mysql.cnf` file and add your $HOME path or wherever you want to install
    
    .. code-block:: text

        [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


    3. Initialize the data directory (do this once)
    
    .. code-block:: text

        mysqld --initialize-insecure --datadir=$HOME/mysql/data

    .. code-block:: text

        # start the server
        mysqld --defaults-file=$HOME/mysql/.mysql.cnf &

    4. 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)

    .. code-block:: text

        mysql --host=localhost --socket="$HOME/mysql/mysqld.sock" -u root

        ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
        quit

    5. Restart the mysql-server so it will see previous changes.

    .. code-block:: text

        # 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 :bash:`cromwell_dori.conf` file (see below). For this example we use the db name, :bash:`cromwell`.
You also need to create a non-root username & password for yourself with full privileges.

1. Reconnect to mysql server and use root password

.. code-block:: text

    # 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.

2. At the bottom of the `cromwell.config <https://code.jgi.doe.gov/official-jgi-workflows/jaws-tutorial-examples/-/blob/main/config_files/cromwell_docker.conf>`_ file, add

.. code-block:: text

    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
    }


3. Then run cromwell (e.g. that was installed locally). :ref:`Installing Cromwell <install cromwell>`.

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

.. code-block:: text

  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 :bash:`config_files`.  You'd use the :bash:`cromwell_docker.conf` file if you installed on a Mac.


.. code-block:: text

    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 :bash:`dockerRoot` and :bash:`root` in the :bash:`cromwell_dori.conf` file, which
  would simplify the command.