Unable to connect with mysql database and errors in API server

[suru71]

Setting up dependency tracker in Production. Getting below error and it is not progressing ahead -
Below is the API Server Logs

2022-05-05 21:13:06,323 INFO [EmbeddedJettyServer] Alpine Executable WAR v1.3.0 (49dc89aa-4f58-404e-b0d6-4001902c5fa1) built on: 2022-02-13T03:18:56Z
2022-05-05 21:13:08,452 INFO [Config] --------------------------------------------------------------------------------
2022-05-05 21:13:08,454 INFO [Config] OS Name: Linux
2022-05-05 21:13:08,455 INFO [Config] OS Version: 3.10.0-1160.62.1.el7.x86_64
2022-05-05 21:13:08,456 INFO [Config] OS Arch: amd64
2022-05-05 21:13:08,457 INFO [Config] CPU Cores: 4
2022-05-05 21:13:08,465 INFO [Config] Max Memory: 9.6 GB (10,309,074,944.0 bytes)
2022-05-05 21:13:08,465 INFO [Config] Java Vendor: Eclipse Adoptium
2022-05-05 21:13:08,468 INFO [Config] Java Version: 11.0.14.1+1
2022-05-05 21:13:08,468 INFO [Config] Java Home: /opt/java/openjdk
2022-05-05 21:13:08,469 INFO [Config] Java Temp: /tmp
2022-05-05 21:13:08,469 INFO [Config] User: dtrack
2022-05-05 21:13:08,469 INFO [Config] User Home: /data/
2022-05-05 21:13:08,470 INFO [Config] --------------------------------------------------------------------------------
2022-05-05 21:13:08,470 INFO [Config] Initializing Configuration
2022-05-05 21:13:08,471 INFO [Config] System property alpine.application.properties not specified
2022-05-05 21:13:08,471 INFO [Config] Loading application.properties from classpath
2022-05-05 21:13:08,482 INFO [Config] --------------------------------------------------------------------------------
2022-05-05 21:13:08,484 INFO [Config] Application: Dependency-Track
2022-05-05 21:13:08,485 INFO [Config] Version: 4.4.2
2022-05-05 21:13:08,485 INFO [Config] Built-on: 2022-03-04T20:43:17Z
2022-05-05 21:13:08,485 INFO [Config] --------------------------------------------------------------------------------
2022-05-05 21:13:08,486 INFO [Config] Framework: Alpine
2022-05-05 21:13:08,486 INFO [Config] Version : 1.10.5
2022-05-05 21:13:08,487 INFO [Config] Built-on: 2022-03-02T21:59:26Z
2022-05-05 21:13:08,487 INFO [Config] --------------------------------------------------------------------------------
2022-05-05 21:13:08,514 INFO [RequirementsVerifier] Initializing requirements verifier
2022-05-05 21:13:08,514 INFO [UpgradeInitializer] Initializing upgrade framework
2022-05-05 21:13:10,249 WARN [Datastore] Default type for java type of java.math.BigInteger was previously jdbc-type=NUMERIC but this is not provided by the JDBC driver! Please report this to the DataNucleus developers
2022-05-05 21:13:11,554 INFO [PersistenceManagerFactory] Initializing persistence framework
2022-05-05 21:13:11,821 WARN [Datastore] Default type for java type of java.math.BigInteger was previously jdbc-type=NUMERIC but this is not provided by the JDBC driver! Please report this to the DataNucleus developers
2022-05-05 21:13:12,676 INFO [EventSubsystemInitializer] Initializing asynchronous event subsystem
2022-05-05 21:13:12,851 INFO [DefaultObjectGenerator] Initializing default object generator
2022-05-05 21:13:12,864 INFO [DefaultObjectGenerator] Synchronizing permissions to datastore
2022-05-05 21:13:12,972 INFO [DefaultObjectGenerator] Synchronizing SPDX license definitions to datastore
2022-05-05 21:13:14,487 WARN [Persist] Insert of object "org.dependencytrack.model.License@684372d0" using statement "INSERT INTO "LICENSE" ("COMMENT","ISDEPRECATED","FSFLIBRE","HEADER","LICENSEID","NAME","ISOSIAPPROVED","SEEALSO","TEMPLATE","TEXT","UUID") VALUES (?,?,?,?,?,?,?,?,?,?,?)" failed : Incorrect string value: '\xE2\x88\x92";m...' for column 'TEMPLATE' at row 1
2022-05-05 21:13:14,498 WARN [General] ExecutionContext closed with active transaction, so rolling back the active transaction
2022-05-05 21:13:22,701 INFO [MetricsUpdateTask] Executing portfolio metrics update
2022-05-05 21:13:22,702 INFO [MetricsUpdateTask] Executing metrics update on vulnerability database
2022-05-05 21:13:22,752 INFO [MetricsUpdateTask] Completed metrics update on vulnerability database
2022-05-05 21:13:22,780 INFO [ClearComponentAnalysisCacheTask] Clearing ComponentAnalysisCache
2022-05-05 21:13:22,796 INFO [ClearComponentAnalysisCacheTask] Complete
2022-05-05 21:13:22,837 INFO [MetricsUpdateTask] Completed portfolio metrics update
2022-05-05 21:14:12,706 INFO [VulnDbSyncTask] Starting VulnDB mirror synchronization task
2022-05-05 21:14:12,707 INFO [VulnDbSyncTask] VulnDB mirror directory does not exist. Skipping.
2022-05-05 21:14:12,713 ERROR [EventService] An error occurred while informing subscriber: java.lang.reflect.InvocationTargetException

@stevespringett - Can you help me resolve this issue ?

Below is the docker-compose.yml configuration -

version: '4.4.2'

networks:
dependency_track_network:

services:
dependencytrack.xxx.com:
image: mysql:5.7.38
environment:
MYSQL_ROOT_PASSWORD: Password
MYSQL_DATABASE: dtrack
MYSQL_ROOT_HOST: '%'
ports:
- "3306:3306"
command: ['mysqld', '--sql_mode=ANSI_QUOTES,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION', '--character-set-server=ucs2', --collation-server=ucs2_general_ci]
volumes:
- mysql-data-2:/var/lib/mysql
restart: unless-stopped
networks:
- dependency_track_network

dtrack-apiserver:
image: dependencytrack/apiserver
environment:
- ALPINE_DATABASE_MODE=external
- ALPINE_DATABASE_URL=jdbc:mysql://dependencytrack.xxx.com:3306/dtrack?autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
- ALPINE_DATABASE_DRIVER=com.mysql.cj.jdbc.Driver
- ALPINE_DATABASE_DRIVER_PATH=/extlib/mysql-connector-java-5.1.49.jar
- ALPINE_DATABASE_USERNAME=root
- ALPINE_DATABASE_PASSWORD=Password
- ALPINE_DATABASE_POOL_ENABLED=true
- ALPINE_DATABASE_POOL_MAX_SIZE=20
- ALPINE_DATABASE_POOL_IDLE_TIMEOUT=500000
- ALPINE_DATABASE_POOL_MAX_LIFETIME=600000
ports:
- '8081:8080'
volumes:
- dt-data-2:/data
restart: unless-stopped
deploy:
resources:
limits:
memory: 12288m
reservations:
memory: 8192m
networks:
- dependency_track_network
depends_on:
- dependencytrack.xxx.com
restart: unless-stopped

dtrack-frontend:
image: dependencytrack/frontend
depends_on:
- dtrack-apiserver
environment:
- API_BASE_URL=https://dependencytrackapi.xxx.com:8081
ports:
- "8080:8080"
restart: unless-stopped
volumes:
dt-data-2:
driver: local
driver_opts:
type: none
device: /data/dt-data-2
o: bind
mysql-data-2:
driver: local
driver_opts:
type: none
device: /data/mysql-data-2

[stevespringett]

Ensure the SQL mode matches what is required
https://docs.dependencytrack.org/getting-started/database-support/

[suru71]

Ensure the SQL mode matches what is required
https://docs.dependencytrack.org/getting-started/database-support/

All the SQL modes are set as shown in the link.

I am using CentOS machine so SQL server is out of scope and I am not much familiar with Postgres.

Is there any other way to get the mysql error troubleshooted?

[stevespringett]

Not sure. Most users frequent the Slack channel.

I know @olafz has done a lot of research in this area lately with MySQL and MariaDB. Perhaps he can shed some light on what might be the cause.

[olafz]

This is what goes wrong in your case:

2022-05-05 21:13:14,487 WARN [Persist] Insert of object "org.dependencytrack.model.License@684372d0" using statement "INSERT INTO "LICENSE" ("COMMENT","ISDEPRECATED","FSFLIBRE","HEADER","LICENSEID","NAME","ISOSIAPPROVED","SEEALSO","TEMPLATE","TEXT","UUID") VALUES (?,?,?,?,?,?,?,?,?,?,?)" failed : Incorrect string value: '\xE2\x88\x92";m...' for column 'TEMPLATE' at row 1

During the startup-process of DependencyTrack, it tries to insert all licenses. This particular one has contents that doesn't match the ucs2 character set (and can't be converted to it).

Currently, the only way to get this to work is with a manual schema change, which converts the schema to UTF-8 and let the startup process continue. For more information, see #271 (comment). I've offered help to @stevespringett to get this to work out-of-the-box and with MySQL 5.7, 8.0 and MariaDB.

[suru71]

@olafz - Thanks for the steps. I will surely try it. But just wanted to confirm if you are running Dependency Track and MySQL database - both on docker ?

[olafz]

But just wanted to confirm if you are running Dependency Track and MySQL database - both on docker ?

Yes, I am. Below is the MySQL and API-server part of my docker-compose file. I'm running a vanilla MySQL container; all DependencyTrack specifics are in the apiserver containers' configuration:

services:
  mysql:
    image: mysql:8.0
    container_name: dependencytrack_dtrack-mysql
    restart: always
    environment:
      MYSQL_DATABASE: '...'
      MYSQL_USER: '...'
      MYSQL_PASSWORD: '...'
      MYSQL_ROOT_PASSWORD: '...'
    cap_add:
      - SYS_NICE  # CAP_SYS_NICE
    volumes:
      - '/srv/docker/dependencytrack/mysql:/var/lib/mysql'

  dtrack-apiserver:
    image: dependencytrack/apiserver
    depends_on:
      - mysql
    environment:
      - ALPINE_DATABASE_MODE=external
      - ALPINE_DATABASE_DRIVER=com.mysql.cj.jdbc.Driver
      - ALPINE_DATABASE_DRIVER_PATH=/extlib/mysql-connector-java-8.0.22.jar
      - ALPINE_DATABASE_URL=jdbc:mysql://mysql:3306/dtrack?useUnicode=true&serverTimezone=Europe/Amsterdam&autoReconnect=true&useSSL=false&sessionVariables=sql_mode='ANSI_QUOTES,STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION'
      - ALPINE_DATABASE_POOL_ENABLED=true
      - ALPINE_DATABASE_USERNAME=...
      - ALPINE_DATABASE_PASSWORD=...
    ports:
      - '8081:8080'
    volumes:
      - '/srv/docker/dependencytrack/data:/data'
    restart: unless-stopped