search

traccar / traccar-docker

Traccar Docker
301 stars 148 forks source link

can't connect to mysql/postgres #103

Closed ippocratis closed 1 year ago

ippocratis commented 1 year ago

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 commented 1 year ago

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 https://github.com/traccar/traccar-docker/issues/61#issue-710506456 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 commented 1 year ago

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

https://github.com/traccar/traccar/issues/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 commented 1 year ago

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