Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Supported by

Linking JATOS and MySQL running in separate Docker containers

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/;
    }

}

Comments

  • 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

  • edited August 2022

    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.

  • 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'@'%';
    


  • 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

  • edited August 2022

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

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

Sign In or Register to comment.

agen judi bola , sportbook, casino, togel, number game, singapore, tangkas, basket, slot, poker, dominoqq, agen bola. Semua permainan bisa dimainkan hanya dengan 1 ID. minimal deposit 50.000 ,- bonus cashback hingga 10% , diskon togel hingga 66% bisa bermain di android dan IOS kapanpun dan dimana pun. poker , bandarq , aduq, domino qq , dominobet. Semua permainan bisa dimainkan hanya dengan 1 ID. minimal deposit 10.000 ,- bonus turnover 0.5% dan bonus referral 20%. Bonus - bonus yang dihadirkan bisa terbilang cukup tinggi dan memuaskan, anda hanya perlu memasang pada situs yang memberikan bursa pasaran terbaik yaitu http://45.77.173.118/ Bola168. Situs penyedia segala jenis permainan poker online kini semakin banyak ditemukan di Internet, salah satunya TahunQQ merupakan situs Agen Judi Domino66 Dan BandarQ Terpercaya yang mampu memberikan banyak provit bagi bettornya. Permainan Yang Di Sediakan Dewi365 Juga sangat banyak Dan menarik dan Peluang untuk memenangkan Taruhan Judi online ini juga sangat mudah . Mainkan Segera Taruhan Sportbook anda bersama Agen Judi Bola Bersama Dewi365 Kemenangan Anda Berapa pun akan Terbayarkan. Tersedia 9 macam permainan seru yang bisa kamu mainkan hanya di dalam 1 ID saja. Permainan seru yang tersedia seperti Poker, Domino QQ Dan juga BandarQ Online. Semuanya tersedia lengkap hanya di ABGQQ. Situs ABGQQ sangat mudah dimenangkan, kamu juga akan mendapatkan mega bonus dan setiap pemain berhak mendapatkan cashback mingguan. ABGQQ juga telah diakui sebagai Bandar Domino Online yang menjamin sistem FAIR PLAY disetiap permainan yang bisa dimainkan dengan deposit minimal hanya Rp.25.000. DEWI365 adalah Bandar Judi Bola Terpercaya & resmi dan terpercaya di indonesia. Situs judi bola ini menyediakan fasilitas bagi anda untuk dapat bermain memainkan permainan judi bola. Didalam situs ini memiliki berbagai permainan taruhan bola terlengkap seperti Sbobet, yang membuat DEWI365 menjadi situs judi bola terbaik dan terpercaya di Indonesia. Tentunya sebagai situs yang bertugas sebagai Bandar Poker Online pastinya akan berusaha untuk menjaga semua informasi dan keamanan yang terdapat di POKERQQ13. Kotakqq adalah situs Judi Poker Online Terpercayayang menyediakan 9 jenis permainan sakong online, dominoqq, domino99, bandarq, bandar ceme, aduq, poker online, bandar poker, balak66, perang baccarat, dan capsa susun. Dengan minimal deposit withdraw 15.000 Anda sudah bisa memainkan semua permaina pkv games di situs kami. Jackpot besar,Win rate tinggi, Fair play, PKV Games