Adding mysql to Apache-Druid as metadata store using docker-compose

Question

Has anyone been able to use MySql for Druid's metadata using docker-compose file please? I have spent hours but I still get errors in the druid broker component.

The reason I want to use mysql instead of postgres is because a vendor's docker container is mistakenly connecting to Druid's postgres server instead of its own and changing postgres' port didn't help.

Below are the errors I see in 'broker'.

2021-02-23T15:15:22,464 DEBUG [qtp575779772-162] org.apache.druid.jetty.RequestLog - 172.16.1.16 POST //172.16.1.13:8082/druid/v2/sql HTTP/1.1
2021-02-23T15:15:23,063 ERROR [sql[402982ef-7105-490a-83d5-8528580c36d3]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2021-02-23T15:15:23,063 ERROR [sql[18a217cf-13a1-444b-99b2-726a55152af1]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/coordinator]
2021-02-23T15:15:23,063 ERROR [sql[2e472014-8050-4886-81e4-4583fa601b40]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/coordinator]
2021-02-23T15:15:23,063 ERROR [sql[41ce0e01-9e49-40ae-a7df-bc01a1c0b11c]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2021-02-23T15:15:23,072 INFO [NodeRoleWatcher[HISTORICAL]] org.apache.druid.curator.discovery.CuratorDruidNodeDiscoveryProvider$NodeRoleWatcher - Node[http://172.16.1.14:8083] of role[historical] detected.
2021-02-23T15:15:23,073 INFO [NodeRoleWatcher[HISTORICAL]] org.apache.druid.curator.discovery.CuratorDruidNodeDiscoveryProvider$NodeRoleWatcher - Node watcher of role[historical] is now initialized.
2021-02-23T15:15:23,064 WARN [sql[18a217cf-13a1-444b-99b2-726a55152af1]] org.apache.druid.sql.http.SqlResource - Failed to handle query: SqlQuery{query='SELECT datasource FROM sys.segments GROUP BY 1', resultFormat=OBJECT, header=false, context={}, parameters=[]}
java.lang.RuntimeException: org.apache.druid.java.util.common.IOE: No known server
        at org.apache.druid.sql.calcite.schema.SystemSchema.getThingsFromLeaderNode(SystemSchema.java:1008) ~[druid-sql-0.20.0.jar:0.20.0]

Answer 1

I found the error details. It was in the druid coordinator logs. The MySql JDBC jar file is not shipped with Druid docker image per below link. https://druid.apache.org/docs/latest/development/extensions-core/mysql.html

So I had to copy it to my Druid containers who use metadata store. It all worked like a charm after that.

Below are the details in my docker-compose file and environment file being used:

docker-compose.yml

#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#
version: "2.2"

volumes:
  metadata_data: {}
  middle_var: {}
  historical_var: {}
  broker_var: {}
  coordinator_var: {}
  router_var: {}
  mysql-data: {}

services:
  mysql:
    image: mysql
    restart: always
    environment:
      - MYSQL_RANDOM_ROOT_PASSWORD='yes'
      - MYSQL_USER=druid
      - MYSQL_PASSWORD=diurd
      - MYSQL_DATABASE=druid
    command:
      - --character-set-server=utf8
      - --collation-server=utf8_unicode_ci      
    volumes:
      - 'mysql-data:/var/lib/mysql:rw'
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.10

  # Need 3.5 or later for container nodes
  druid_zookeeper:
    container_name: zookeeper
    hostname: druid_zookeeper
    image: zookeeper:3.5
    environment:
      - ZOO_MY_ID=1
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.11

  coordinator:
    image: apache/druid:0.20.0
    container_name: coordinator
    volumes:
      - ./storage:/opt/data
      - coordinator_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar        
    depends_on: 
      - druid_zookeeper
      - mysql
    ports:
      - "8081:8081"
    command:
      - coordinator
    env_file:
      - environment
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.12

  broker:
    image: apache/druid:0.20.0
    container_name: broker
    volumes:
      - broker_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8082:8082"
    command:
      - broker
    env_file:
      - environment
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.13

  historical:
    image: apache/druid:0.20.0
    container_name: historical
    volumes:
      - ./storage:/opt/data
      - historical_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8083:8083"
    command:
      - historical
    env_file:
      - environment
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.14

  middlemanager:
    image: apache/druid:0.20.0
    container_name: middlemanager
    volumes:
      - ./storage:/opt/data
      - middle_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8091:8091"
    command:
      - middleManager
    env_file:
      - environment
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.15

  router:
    image: apache/druid:0.20.0
    container_name: router
    volumes:
      - router_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8888:8888"
    command:
      - router
    env_file:
      - environment
    networks:
      cc_subnet:
        ipv4_address: 172.16.1.16
        
networks:
  cc_subnet:
    external: true

environment

#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Java tuning
DRUID_XMX=1g
DRUID_XMS=1g
DRUID_MAXNEWSIZE=250m
DRUID_NEWSIZE=250m
DRUID_MAXDIRECTMEMORYSIZE=6172m

druid_emitter_logging_logLevel=debug

druid_extensions_loadList=["druid-histogram", "druid-datasketches", "druid-lookups-cached-global", "mysql-metadata-storage"]

druid_zk_service_host=druid_zookeeper

druid_metadata_storage_host=
druid_metadata_storage_type=mysql
druid_metadata_storage_connector_connectURI=jdbc:mysql://mysql:3306/druid?allowPublicKeyRetrieval=true&useSSL=false
druid_metadata_storage_connector_user=druid
druid_metadata_storage_connector_password=diurd

druid_coordinator_balancer_strategy=cachingCost

druid_indexer_runner_javaOptsArray=["-server", "-Xmx1g", "-Xms1g", "-XX:MaxDirectMemorySize=3g", "-Duser.timezone=UTC", "-Dfile.encoding=UTF-8", "-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager"]
druid_indexer_fork_property_druid_processing_buffer_sizeBytes=268435456

druid_storage_type=local
druid_storage_storageDirectory=/opt/data/segments
druid_indexer_logs_type=file
druid_indexer_logs_directory=/opt/data/indexing-logs

druid_processing_numThreads=2
druid_processing_numMergeBuffers=2

DRUID_LOG4J=<?xml version="1.0" encoding="UTF-8" ?><Configuration status="WARN"><Appenders><Console name="Console" target="SYSTEM_OUT"><PatternLayout pattern="%d{ISO8601} %p [%t] %c - %m%n"/></Console></Appenders><Loggers><Root level="info"><AppenderRef ref="Console"/></Root><Logger name="org.apache.druid.jetty.RequestLog" additivity="false" level="DEBUG"><AppenderRef ref="Console"/></Logger></Loggers></Configuration>

Answer 2

I have followed the following steps: And this way worked for me

1- Created docker-compose.yml like the below:

version: "2.2"

volumes:
  metadata_data: {}
  middle_var: {}
  historical_var: {}
  broker_var: {}
  coordinator_var: {}
  router_var: {}
  druid_shared: {}

services:
  mysql:
    container_name: mysql
    image: mysql
    volumes:
      - 'metadata_data:/var/lib/mysql:rw'

    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=druid_root
      - MYSQL_USER=druid
      - MYSQL_PASSWORD=diurd
      - MYSQL_DATABASE=druid
    command:
      - --character-set-server=utf8
      - --collation-server=utf8_unicode_ci      

  # Need 3.5 or later for container nodes
  druid_zookeeper:
    container_name: zookeeper
    hostname: druid_zookeeper
    image: zookeeper:3.5
    ports:
      - "2181:2181"
    environment:
      - ZOO_MY_ID=1

  coordinator:
    image: apache/druid:0.23.0
    container_name: coordinator
    volumes:
      - druid_shared:/opt/shared
      - coordinator_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar        
    depends_on: 
      - druid_zookeeper
      - mysql
    ports:
      - "8081:8081"
    command:
      - coordinator
    env_file:
      - environment

  broker:
    image: apache/druid:0.23.0
    container_name: broker
    volumes:
      - broker_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8082:8082"
    command:
      - broker
    env_file:
      - environment

  historical:
    image: apache/druid:0.23.0
    container_name: historical
    volumes:
      - druid_shared:/opt/shared
      - historical_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8093:8083"
    command:
      - historical
    env_file:
      - environment

  middlemanager:
    image: apache/druid:0.23.0
    container_name: middlemanager
    volumes:
      - druid_shared:/opt/shared
      - middle_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8091:8091"
    command:
      - middleManager
    env_file:
      - environment

  router:
    image: apache/druid:0.23.0
    container_name: router
    volumes:
      - router_var:/opt/druid/var
      - ./extensions/mysql-connector-java-5.1.48.jar:/opt/druid/extensions/mysql-metadata-storage/mysql-connector-java-5.1.48.jar
    depends_on:
      - druid_zookeeper
      - mysql
      - coordinator
    ports:
      - "8888:8888"
    command:
      - router
    env_file:
      - environment

2- created the environment file like the below:

#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#

# Java tuning
DRUID_XMX=2g
DRUID_XMS=2g
DRUID_MAXNEWSIZE=600m
DRUID_NEWSIZE=600m
DRUID_MAXDIRECTMEMORYSIZE=8172m

druid_emitter_logging_logLevel=debug

druid_extensions_loadList=["druid-histogram", "druid-datasketches", "druid-lookups-cached-global", "mysql-metadata-storage"]

druid_zk_service_host=druid_zookeeper

druid_metadata_storage_host=
druid_metadata_storage_type=mysql
druid_metadata_storage_connector_connectURI=jdbc:mysql://mysql:3306/druid?allowPublicKeyRetrieval=true&useSSL=false
druid_metadata_storage_connector_user=druid
druid_metadata_storage_connector_password=diurd

druid_coordinator_balancer_strategy=cachingCost

druid_indexer_runner_javaOptsArray=["-server", "-Xmx1g", "-Xms1g", "-XX:MaxDirectMemorySize=3g", "-Duser.timezone=UTC", "-Dfile.encoding=UTF-8", "-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager"]
druid_indexer_fork_property_druid_processing_buffer_sizeBytes=268435456

druid_storage_type=local
druid_storage_storageDirectory=/opt/shared/segments
druid_indexer_logs_type=file
druid_indexer_logs_directory=/opt/shared/indexing-logs

druid_processing_numThreads=2
druid_processing_numMergeBuffers=2

DRUID_LOG4J=<?xml version="1.0" encoding="UTF-8" ?><Configuration status="WARN"><Appenders><Console name="Console" target="SYSTEM_OUT"><PatternLayout pattern="%d{ISO8601} %p [%t] %c - %m%n"/></Console></Appenders><Loggers><Root level="info"><AppenderRef ref="Console"/></Root><Logger name="org.apache.druid.jetty.RequestLog" additivity="false" level="DEBUG"><AppenderRef ref="Console"/></Logger></Loggers></Configuration>

3- downloaded the MySQL connector under the extensions directory

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.48/mysql-connector-java-5.1.48.jar