can't connect to mysql/postgres

[ippocratis]

i use traccar client on android and running the server on a raspberrypi400

long story short : i used docker-compose and had my embedded H2 database volume maped on a host dir. db got corrupted so i tried to switch to porttgres or mysql with no luck

my dockerr-compose.yml

version: '2.4'

services:
  mariadb:
    container_name: mariadb-traccar
    hostname: mariadb
    restart: unless-stopped
    image: yobasystems/alpine-mariadb
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - /run/media/ippo/TOSHIBA/traccar/db:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: pass
    ports:
      - 3306:3306
    healthcheck:
      test:  mysqladmin ping -h 127.0.0.1 -u user --password=pass
      interval: 60s
      timeout: 5s
      retries: 5
      start_period: 30s
    networks:
     - net
    
  traccar:
     image: myimage
     container_name: traccar
     hostname: xxx.xxx.xxx.duckdns.org
     restart: unless-stopped
     ports:
     - 5055:5055/tcp
     - 82:8082
     environment:
      MYSQL_DATABASE: db
      MYSQL_USER: user
      MYSQL_PASSWORD: pass
    
     volumes:
      - /run/media/ippo/TOSHIBA/traccar/conf/traccar.xml:/opt/traccar/conf/traccar.xml
      - /run/media/ippo/TOSHIBA/traccar/logs:/opt/traccar/logs:rw
      - /run/media/ippo/TOSHIBA/traccar/data:/opt/traccar/web:rw
     depends_on:
       - mariadb
     networks:
     - net
  
networks:
  net:
    driver: bridge
    enable_ipv6: false
    ipam:
      config:
        - subnet: 192.168.1.24/32

my traccar.xml

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE properties SYSTEM 'http://java.sun.com/dtd/properties.dtd'>

<properties>

    <entry key='config.default'>./conf/default.xml</entry>

    <!--

    This is the main configuration file. All your configuration parameters should be placed in this file.

    Default configuration parameters are located in the "default.xml" file. You should not modify it to avoid issues
    with upgrading to a new version. Parameters in the main config file override values in the default file. Do not
    remove "config.default" parameter from this file unless you know what you are doing.

    For list of available parameters see following page: https://www.traccar.org/configuration-file/

    -->

    <entry key='database.driver'>com.mysql.cj.jdbc.Driver</entry>
    <entry key='database.url'>jdbc:mysql:/mariadb:3306/traccar?serverTimezone=UTC&amp;allowPublicKeyRetrieval=true&amp;useSSL=false&amp;allowMultiQueries=true&amp;autoReconnect=true&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;sessionVariables=sql_mode=''</entry>
    <entry key='database.user'>user</entry>
    <entry key='database.password'>pass</entry>

</properties>

docker-compose up throws error

Exception in thread "main" java.lang.RuntimeException: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

i tried my host ip "192.168.1.24" instead of hostname of my mysql service in this case "mariadb" in the docker-compose.yml

also localhost does not work

i tried to follow this
https://www.traccar.org/forums/topic/traccar-is-not-connecting-to-mysql-in-docker-compose-setup/

also tried commenting "skip networking" with docker exec -it mariadb-traccar nano /etc/my.cnf.d/mariadb-server.cnf

according to this

https://www.traccar.org/forums/topic/traccar-accessing-remote-database/#post-54192

all without luck

so

what would be a proper mysqlurl for a mariadb container and whatt would be the propper enviromental variables for docker-compose to spin up traccar?

[ippocratis]

ok
there where mixed issues
i managed to make postgres to work
i head to docker inspect network net (net was my network name)
and grab the subnet and gateway

"Config": [
                {
                    "Subnet": "192.168.96.0/20",
                    "Gateway": "192.168.96.1"
                }

i ued the subnet on the

ipam:
      config:
        - subnet:192.168.96.0/20 

in the docker-compose

and the gateway in the database url in the traccar.xml jdbc:postgresql://192.168.96.1:5432/db

beyond that i had to use the official traccar debian arm64 image for my raspberrypi 400 so i can have the traccar web interface
Digest:sha256:f09791c90dbfe9ea5adeb5c5c1f657cfdb1eccaf56bda2194f2a84abfb4b6412

i tried the images from dockerhub before and they threw errors
unfortunately i dont remember which ones
maybe 5x
so i ended up using the dockerfile from this guy and it worked with the embeded db
#61 (comment)
but when using this dockerfile with postgres i couldnt get the traccar UI on port 8082 instead i was getting the database dir

and last i would like to suggest to provide an "official" docker-compose for completely dockerised spinups

i'll leave "mine" here for reference

version: "3"
services:
  traccar:
    image: traccar/traccar:debian
    hostname: my.dyn-dns.domain.com
    container_name: traccar
    restart: unless-stopped
    environment:
      POSTGRES_USER: user
      POSTGRES_DB: db

    depends_on:
      - traccar-db
    volumes:
      - /run/media/ippo/TOSHIBA/traccar/logs:/opt/traccar/logs:rw
      - /run/media/ippo/TOSHIBA/traccar/conf/traccar.xml:/opt/traccar>
      - /run/media/ippo/TOSHIBA/traccar/data:/opt/traccar/web:rw
    ports:
      - "82:8082"
      - "5055:5055/tcp"
    networks:
      - net


  traccar-db:
    container_name: traccar-db
    image: postgres:13
    restart: unless-stopped
    ports:
      - 5432:5432
    volumes:
      - /run/media/ippo/TOSHIBA/traccar/db:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: imus
    networks:
     - net
  
networks:
  net:
    driver: bridge
    enable_ipv6: false
    ipam:
      config:
        - subnet: 192.168.96.0/20

thanks and keep up

[ippocratis]

I'll re open this as it looks like traccar doesnot want to use postgres

So I managed to start the 2 images without errors and thought all was OK

But when I docker exec -it traccar-db bash I see that there are no tables created

And when I docker exec -it traccar ls /opt/traccar/data I see the database.mv.db database.trace.db the h2 creates

So traccar still uses h2

The mounted data volume does not contain any db files though and it makes.no diference if I mount traccar/data volume or not

Also I read similar issues

traccar/traccar#480

that contain "fixes" like decompile>edit>recompile. Jar files

Creating tables scripts

etc

The traccar documentation for psql
https://www.traccar.org/postgresql/
All it States is reaplace [DATABASE], [USER], [PASSWORD] in the configuration xml

[ippocratis]

Ok
I think I finaly made mysql to work
I had to create the db and user manualy after all
I thought docker-compose would do that for me
Anyway....

docker exec -it traccar-db mysql -u root -p

CREATE DATABASE IF NOT EXISTS traccar-db

utf8mb4

grant all privileges on `traccar-db`.* TO `user'@'%` identified by `pass`

flush privileges

\q

Traccar is up and running and /opt/traccar/data is empty so I guess it uses the mariadb