Linking JATOS and MySQL running in separate Docker containers

AlexTen

Hello,

I'm trying to set up a system that runs JATOS and MySQL on separate containers. JATOS is running locally in its respective container and talks to the "outside" via a NGINX reverse proxy.

My setup seems to work fine: both containers are running and seem to be functioning. I can access the JATOS admin page and when attached to the mysql container shell, I can see that the appropriate databases are there and that the jatos user gets created (albeit without all grants).

The problem seems to be in connecting the two services. When the appropriate settings in the production.conf files are commented, my JATOS server seems to run fine, but as soon as I uncomment the following lines:

db.default.url = "jdbc:mysql://172.21.0.7:3306/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"
db.default.username = "root"
db.default.password = ***********
db.default.driver = "com.mysql.cj.jdbc.Driver"

I start getting a 502 Bad Gateway error from Nginx. My guess is that I am not setting up the proxy network correctly. Particularly, I have no idea what to put in the db.default.url field. As you can see, I've tried entering the IP:port of MySQL's container. I've tried other things, but frankly, I have no idea how this setting is used and what should go there.

In case it is needed, here is how my JATOS and MySQL containers are docker-composed (I am not a Docker expert, so ignore weird things if you spot them):

version: '3.7'


volumes:
    jatos:


services:
    jatos:
        container_name: jatos
        build:
            context: ./
            dockerfile: Dockerfile
        volumes:
            - ./:/app
        command: /app/loader.sh start
        depends_on:
            - "mysql"
        restart: always
        expose:
            - "9000"
        networks:
            - "reverse-proxy-net"
    mysql:
        image: mysql:8
        ports:
            - 3306:3306
        expose:
            - "3306"
        volumes:
            - ~/app/mysql:/var/lib/mysql
        environment:
            - MYSQL_ROOT_PASSWORD=mysq1rootpassw0rd
            - MYSQL_PASSWORD=mysq1jatospassw0rd
            - MYSQL_USER=jatos_user
            - MYSQL_DATABASE=jatos-db
            - MYSQL_ROOT_HOST=jatos
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        networks:
            - "reverse-proxy-net"


networks:
    reverse-proxy-net:
        external: true

And here is the proxy configuration (I removed some stuff for readability):

# HTTP 1.1 support
proxy_http_version 1.1;
proxy_buffering off;
proxy_set_header Host $http_host;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection $proxy_connection;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $proxy_x_forwarded_proto;
proxy_set_header X-Forwarded-Ssl $proxy_x_forwarded_ssl;
proxy_set_header X-Forwarded-Port $proxy_x_forwarded_port;

# Mitigate httpoxy attack (see README for details)
proxy_set_header Proxy "";

upstream jatos-backend {
    server jatos:9000;
}

map $http_upgrade $connection_upgrade {
    default upgrade;
    '' close;
}


server {
    listen                81;
    listen                443 ssl;
    server_name           memo.hib.uni-tuebingen.de;
    ssl_certificate       /etc/nginx/certs/memo-hib_cert.pem;
    ssl_certificate_key   /etc/nginx/certs/memo-hib_key_np.pem;

    # Websocket location (JATOS' group and batch channel and the test page)
    location ~ "^/(jatos/testWebSocket|publix/[a-z0-9-]+/(group/join|batch/open))" {
        proxy_pass              http://jatos-backend;
        proxy_http_version      1.1;
        proxy_set_header        Upgrade $http_upgrade;
        proxy_set_header        Connection $connection_upgrade;
        proxy_connect_timeout   7d; # keep open for 7 days even without any transmission
        proxy_send_timeout      7d;
        proxy_read_timeout      7d;
    }

# restrict access to JATOS' GUI to local network
#location /jatos {
#       allow           192.168.1.0/24;
#       deny            all;
#       proxy_pass      http://jatos-backend;
#       proxy_connect_timeout   300;
#       proxy_send_timeout      300;
#       proxy_read_timeout      300;
#       send_timeout            300;
#}


    # all other traffic
    location / {
            proxy_pass              http://jatos-backend;
            proxy_connect_timeout   300;
            proxy_send_timeout      300;
            proxy_read_timeout      300;
            send_timeout            300;
    }


    location /jatos-mysql {
        proxy_pass                  http://jatos-mysql:3306/;
    }

}

kri

Hi,

I spend some time with docker-compose but I'm far from being an expert there. I think you don't need to define an extra network - the default network would do just fine. But it also doesn't hurt. But from looking at this doc I think you have to use the hostname instead of the IP for the MySQL in your JATOS config. In docker-compose the hostname is equal to the service's name. So in your case it is 'mysql' and the whole db.default.url should look like:

db.default.url = "jdbc:mysql://mysql:3306/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC"

This db.default.url field specifies a JDBC URI and has the format:

protocol//[hosts][/database][?properties]

The only parts you should change are 'hosts' and 'database', while 'database' is the name of your JATOS database within MySQL.

I think your Nginx config looks fine.

My best to Tübingen :)

Kristian

AlexTen

Thanks to Kristian's input, I was able to put up a JATOS server running in a docker container and communicating with a MySQL server running in a separate container.

Since both containers, as well as the reverse-proxy container, are in the same docker network, the db.default.url setting needs to refer to the host name known in the network. This should correspond to the service name (e.g., mysql) in the docker-compose file.

You also need to make sure that the database name matches the one you provide when you compose the MySQL server. In my case, I name the database inside the docker compose using the environment directive:

version: '3.7'
volumes:
    jatos:
services:
    jatos:
        container_name: jatos
        build:
            context: ./
            dockerfile: Dockerfile
        volumes:
            - ./:/app
        command: /app/loader.sh start
        depends_on:
            - "mysql"
        restart: always
        expose:
            - "9000"
        networks:
            - "reverse-proxy-net"
    mysql:
        image: mysql:8
        ports:
            - "3306:3306"
        volumes:
            - ~/app/mysql:/var/lib/mysql
        environment:
            - MYSQL_ROOT_PASSWORD=mysq1rootpassw0rd
            - MYSQL_PASSWORD=mysq1jatospassw0rd
            - MYSQL_USER=jatos_user
            - MYSQL_DATABASE=jatos_db
            - MYSQL_ROOT_HOST=jatos
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        networks:
            - "reverse-proxy-net"
networks:
    reverse-proxy-net:
        external: true

In case it helps others, here is the docker-compose for the reverse proxy (just note that this service also uses the same network):

version: '2
services:
  nginx-proxy:
    image: nginxproxy/nginx-proxy
    container_name: "reverse-proxy"
    volumes:
        - /var/run/docker.sock:/tmp/docker.sock:ro
        - ./proxy.conf:/etc/nginx/proxy.conf:ro
        - /home-link/sebdw02/certs:/etc/nginx/certs
    restart: "always"
    networks:
        - "reverse-proxy-net"
    ports:
        - "81:81"
        - "443:443"
volumes:
  certs:
networks:
  reverse-proxy-net:
    external: true

And there is no need to set up traffic routing in the reverse-proxy configs for me because all communication with the database is supposed to happen internally in the docker network.

AlexTen

For completeness, here are additional tips that might make your life easier.

It is not enough to correctly reference mysql server's hostname in the docker-compose file. The JATOS container will try to access the db through the specified port, but mysql will deny the jatos user access unless you grant it to users from hosts other than the localhost.

I achieved this by adding a directive to docker-compose.yaml to run an init file for mysql (you'll need to create a volume for this):

version: '3.7'

volumes:
    jatos:

services:
    jatos:
        container_name: jatos
        build:
            context: ./
            dockerfile: Dockerfile
        volumes:
            - ./:/app
        command: /app/loader.sh start
        depends_on:
            - "mysql"
        restart: always
        expose:
            - 9000
        networks:
            - "reverse-proxy-net"

    mysql:
        image: mysql:8
        ports:
            - 3306:3306
        volumes:
            - ./mysql:/var/lib/mysql
            - ./init.sql:/app/data/init.sql          # <- create volume
        environment:
            - MYSQL_ROOT_USER=root
            - MYSQL_ROOT_PASSWORD=.........
        command: --init-file /app/data/init.sql      # <- read init file
        networks:
            - "reverse-proxy-net"

networks:
    reverse-proxy-net:
        external: true

in the init file set up the db, including granting privileges to the JATOS user on a "wildcard" host (there might be a more precise way to specify the host, but I haven't tried it):

CREATE DATABASE jatos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'jatosuser'@'localhost' IDENTIFIED BY 'jatosUser205';
CREATE USER 'jatosuser'@'%' IDENTIFIED BY 'jatosUser205';
GRANT ALL PRIVILEGES ON jatos.* TO 'jatosuser'@'localhost';
GRANT ALL PRIVILEGES ON jatos.* TO 'jatosuser'@'%';

kri

Thank you for posting so others might profit from it.

I have a question about the mysql directives in your docker-compose: The command: --init-file /app/data/init.sql is it executed with every docker-compose up? And if yes, wouldn't it try to create a new 'jatos' database and new 'jatosuser' each time the host reboots or your container is restarted? Maybe environment variables are a better way to go here?

Best,

Kristian

AlexTen

I haven't checked yet, but it might be the case that `command: --init-file ...` line overwrites the existing database. I wasn't sure how to grant privileges to a JATOS user with environment variables, so I went with the init-file approach, which I learned about here. One way to avoid overwriting the existing database when running docker-compose is to add a condition to MySQL instructions, e.g.,:

CREATE DATABASE IF NOT EXISTS jatos;
ALTER jatos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'jatosuser'@'localhost' IDENTIFIED BY 'jatosUser205';
CREATE USER 'jatosuser'@'%' IDENTIFIED BY 'jatosUser205';
GRANT ALL PRIVILEGES ON jatos.* TO 'jatosuser'@'localhost';
GRANT ALL PRIVILEGES ON jatos.* TO 'jatosuser'@'%';

However, it seems possible to allow root-logins from any host. Thus, JATOS could access the database as root, provided it is not critical to create a jatosuser like the documentation instructs...

kri

If most people on SO agree with the init file ... who am I to disagree :) . And with the 'IF NOT EXISTS' you should be on the save side.

Thus, JATOS could access the database as root, provided it is not critical to create a jatosuser like the documentation instructs

It's not critical. JATOS doesn't care for the username. You just have to specify the username in JATOS' production.conf under db.default.username .