"FATAL" icingadb when using official compose.yml

[rimat420]

Hi

icingadb-1 | 2025-06-03T13:23:32.232Z FATAL icingadb Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"service" WHERE "environment_id" = ?' at line 1 icingadb-1 | can't perform "SELECT \"properties_checksum\", \"id\" FROM \"service\" WHERE \"environment_id\" = :environment_id" icingadb-1 | github.com/icinga/icinga-go-library/database.CantPerformQuery icingadb-1 | github.com/icinga/icinga-go-library@v0.6.3/database/utils.go:16 icingadb-1 | github.com/icinga/icinga-go-library/database.(*DB).YieldAll.func1

tried to run your docker-compose.yaml "as is" on 2 different machines - on both i ran into the same error.

did i miss smthng or does the .yml need to be updated?

thx & br

[ckbaker10]

Update the MariaDB Command to use this:

   command: 
      - --sql-mode=TRADITIONAL,ANSI_QUOTES # FIXED: Removed quotes around the value
      - --default-authentication-plugin=mysql_native_password

I think the repo is abandoned but it does still work nicely

See here for pullrequest #55

Full file, be aware I've changed the ports to be used with an apache reverse proxy:

  &icinga-db-web-config
  icingaweb.modules.icingadb.config.icingadb.resource: icingadb
  icingaweb.modules.icingadb.redis.redis1.host: icingadb-redis
  icingaweb.modules.icingadb.redis.redis1.port: 6379
  icingaweb.modules.icingadb.commandtransports.icinga2.host: icinga2
  icingaweb.modules.icingadb.commandtransports.icinga2.port: 5665
  icingaweb.modules.icingadb.commandtransports.icinga2.password: ${ICINGAWEB_ICINGA2_API_USER_PASSWORD:-icingaweb}
  icingaweb.modules.icingadb.commandtransports.icinga2.transport: api
  icingaweb.modules.icingadb.commandtransports.icinga2.username: icingaweb
  icingaweb.resources.icingadb.charset: utf8mb4
  icingaweb.resources.icingadb.db: mysql
  icingaweb.resources.icingadb.dbname: icingadb
  icingaweb.resources.icingadb.host: mysql
  icingaweb.resources.icingadb.password: ${ICINGADB_MYSQL_PASSWORD:-icingadb}
  icingaweb.resources.icingadb.type: db
  icingaweb.resources.icingadb.username: icingadb

x-icinga-director-config:
  &icinga-director-config
  icingaweb.modules.director.config.db.resource: director-mysql
  icingaweb.modules.director.kickstart.config.endpoint: icinga2
  icingaweb.modules.director.kickstart.config.host: icinga2
  icingaweb.modules.director.kickstart.config.port: 5665
  icingaweb.modules.director.kickstart.config.username: icingaweb
  icingaweb.modules.director.kickstart.config.password: ${ICINGAWEB_ICINGA2_API_USER_PASSWORD:-icingaweb}
  icingaweb.resources.director-mysql.charset: utf8mb4
  icingaweb.resources.director-mysql.db: mysql
  icingaweb.resources.director-mysql.dbname: director
  icingaweb.resources.director-mysql.host: mysql
  icingaweb.resources.director-mysql.password: ${ICINGA_DIRECTOR_MYSQL_PASSWORD:-director}
  icingaweb.resources.director-mysql.type: db
  icingaweb.resources.director-mysql.username: director

x-icinga-web-config:
  &icinga-web-config
  icingaweb.authentication.icingaweb2.backend: db
  icingaweb.authentication.icingaweb2.resource: icingaweb-mysql
  icingaweb.config.global.config_backend: db
  icingaweb.config.global.config_resource: icingaweb-mysql
  icingaweb.config.global.module_path: /usr/share/icingaweb2/modules
  icingaweb.config.logging.log: php
  icingaweb.groups.icingaweb2.backend: db
  icingaweb.groups.icingaweb2.resource: icingaweb-mysql
  icingaweb.passwords.icingaweb2.icingaadmin: icinga
  icingaweb.resources.icingaweb-mysql.charset: utf8mb4
  icingaweb.resources.icingaweb-mysql.db: mysql
  icingaweb.resources.icingaweb-mysql.dbname: icingaweb
  icingaweb.resources.icingaweb-mysql.host: mysql
  icingaweb.resources.icingaweb-mysql.password: icingaweb
  icingaweb.resources.icingaweb-mysql.type: db
  icingaweb.resources.icingaweb-mysql.username: icingaweb
  icingaweb.roles.Administrators.groups: Administrators
  icingaweb.roles.Administrators.permissions: '*'
  icingaweb.roles.Administrators.users: icingaadmin

x-icinga2-environment:
  &icinga2-environment
  ICINGA_CN: icinga2
  ICINGA_MASTER: 1

x-logging:
  &default-logging
  driver: "json-file"
  options:
    max-file: "10"
    max-size: "1M"

networks:
  default:
    name: icinga-playground

services:
  director:
    command:
      - /bin/bash
      - -ce
      - |
        echo "Testing the database connection. Container could restart."
        (echo > /dev/tcp/mysql/3306) >/dev/null 2>&1
        echo "Testing the Icinga 2 API connection. Container could restart."
        (echo > /dev/tcp/icinga2/5665) >/dev/null 2>&1
        icingacli director migration run
        (icingacli director kickstart required && icingacli director kickstart run && icingacli director config deploy) || true
        echo "Starting Icinga Director daemon."
        icingacli director daemon run
    entrypoint: []
    logging: *default-logging
    image: icinga/icingaweb2
    restart: unless-stopped
    volumes:
      - icingaweb:/data
    # ADDED: Depend on mysql and icinga2 to ensure they're ready before starting the director
    depends_on:
      mysql:
        condition: service_healthy
      icinga2:
        condition: service_healthy

  # The Icinga 2 docker image does not support configuration via env vars at the moment.
  # So, we have to ship some configs with this little init container. Referenced in depends_on of the icinga2 service.
  init-icinga2:
    command: [ "/config/init-icinga2.sh" ]
    environment: *icinga2-environment
    image: icinga/icinga2
    logging: *default-logging
    volumes:
      - icinga2:/data
      - ./icingadb.conf:/config/icingadb.conf
      - ./icingaweb-api-user.conf:/config/icingaweb-api-user.conf
      - ./init-icinga2.sh:/config/init-icinga2.sh
    # UNIFIED: Depend on healthy services before running the init script
    depends_on:
      icingadb-redis:
        condition: service_healthy
      mysql:
        condition: service_healthy

  icinga2:
    command: [ "sh", "-c", "sleep 5 ; icinga2 daemon" ]
    environment: *icinga2-environment
    image: icinga/icinga2
    logging: *default-logging
    ports:
      - "127.0.0.1:3070:5665"
    restart: unless-stopped
    volumes:
      - icinga2:/data
      - ./icinga2.conf.d:/custom_data/custom.conf.d
    # UNIFIED: Use condition-based dependency for init completion and redis readiness
    depends_on:
      init-icinga2:
        condition: service_completed_successfully
      icingadb-redis:
        condition: service_healthy
    healthcheck:
      # Use HTTPS (-k), fail on error (-f), and include Basic Auth (-u)
      test: ["CMD", "curl", "-k", "-f", "-u", "icingaweb:${ICINGAWEB_ICINGA2_API_USER_PASSWORD:-icingaweb}", "https://localhost:5665/v1/"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 90s

  icingadb:
    environment:
      ICINGADB_DATABASE_HOST: mysql
      ICINGADB_DATABASE_PORT: 3306
      ICINGADB_DATABASE_DATABASE: icingadb
      ICINGADB_DATABASE_USER: icingadb
      ICINGADB_DATABASE_PASSWORD: ${ICINGADB_MYSQL_PASSWORD:-icingadb}
      ICINGADB_REDIS_HOST: icingadb-redis
      ICINGADB_REDIS_PORT: 6379
    image: icinga/icingadb
    logging: *default-logging
    restart: unless-stopped
    # UNIFIED: Use condition-based dependency for database and redis readiness
    depends_on:
      mysql:
        condition: service_healthy
      icingadb-redis:
        condition: service_healthy

  icingadb-redis:
    image: redis
    logging: *default-logging
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "redis-cli", "ping"]
      interval: 1s
      timeout: 3s
      retries: 5

  icingaweb:
    environment:
      icingaweb.enabledModules: director, icingadb, incubator
      <<: [*icinga-db-web-config, *icinga-director-config, *icinga-web-config]
    logging: *default-logging
    image: icinga/icingaweb2
    ports:
      - "127.0.0.1:3065:8080"
    # Restart Icinga Web container automatically since we have to wait for the database to be ready.
    # Please note that this needs a more sophisticated solution.
    restart: unless-stopped
    volumes:
      - icingaweb:/data
    # UNIFIED: Use condition-based dependency for database readiness
    depends_on:
      mysql:
        condition: service_healthy

  mysql:
    image: mariadb:10.7
    command:
      - --sql-mode=TRADITIONAL,ANSI_QUOTES # FIXED: Removed quotes around the value
      - --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_RANDOM_ROOT_PASSWORD: 1
      ICINGADB_MYSQL_PASSWORD: ${ICINGADB_MYSQL_PASSWORD:-icingadb}
      ICINGAWEB_MYSQL_PASSWORD: ${ICINGAWEB_MYSQL_PASSWORD:-icingaweb}
      ICINGA_DIRECTOR_MYSQL_PASSWORD: ${ICINGA_DIRECTOR_MYSQL_PASSWORD:-director}
    logging: *default-logging
    restart: unless-stopped
    volumes:
      - mysql:/var/lib/mysql
      - ./env/mysql/:/docker-entrypoint-initdb.d/
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-p$MYSQL_RANDOM_ROOT_PASSWORD"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 20s

volumes:
  icinga2:
  icingaweb:
  mysql:

Apache config:

<VirtualHost *:443>
    ServerName mon.yourdomain.de

    SSLEngine On
    SSLCertificateFile      /etc/letsencrypt/live/mon.yourdomain.de/fullchain.pem
    SSLCertificateKeyFile   /etc/letsencrypt/live/mon.yourdomain.de/privkey.pem

    ProxyPreserveHost On
    RequestHeader set X-Forwarded-Proto "https"

    ProxyPass /v1/ http://127.0.0.1:3070/v1/
    ProxyPassReverse /v1/ http://127.0.0.1:3070/v1/

    ProxyPass / http://127.0.0.1:3065/
    ProxyPassReverse / http://127.0.0.1:3065/


    ErrorLog ${APACHE_LOG_DIR}/mon.yourdomain.de-error.log
    CustomLog ${APACHE_LOG_DIR}/mon.yourdomain.de-access.log combined
</VirtualHost>