Cannot connect to mysql

Here’s the error code.
“error”:“Could not resolve type id ‘mysql’ as a subtype of org.apache.druid.metadata.SQLFirehoseDatabaseConnector: known type ids = (for POJO property ‘database’)\n at [Source: (org.eclipse.jetty.server.HttpInputOverHTTP); line: 43, column: 19] (through reference chain: org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexSupervisorTask[“spec”]->org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexIngestionSpec[“ioConfig”]->org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexIOConfig[“firehose”]->org.apache.druid.segment.realtime.firehose.SqlFirehoseFactory[“database”])”

I’ve installed mysql extension as per the documentation. Please note that I’m new to druid

Hey!

I did a rambling blog for a Raspberry Pi cluster where I talk through what I did to get it to work for Deep Storage – really it just expands on the docs but it might help you… but it may be good just to check that all has been installed oK?

You can also use the status APIs to check what extensions are loaded on each node. That can be helpful to just know that things are all running OK on each of your Druid processes:

Hi still getting an error :

HTTP ERROR 500 org.skife.jdbi.v2.exceptions.CallbackFailedException: com.fasterxml.jackson.databind.exc.ValueInstantiationException: Cannot construct instance of `org.apache.druid.segment.realtime.firehose.SqlFirehoseFactory`, problem: SQL Metadata Connector not configured! at [Source: (byte[])"{"type":"index_parallel","id":"index_parallel_some_datasource2_ddhbjgoo_2022-02-02T07:43:13.732Z","groupId":"index_parallel_some_datasource2_ddhbjgoo_2022-02-02T07:43:13.732Z","resource":{"availabilityGroup":"index_parallel_some_datasource2_ddhbjgoo_2022-02-02T07:43:13.732Z","requiredCapacity":1},"spec":{"dataSchema":{"dataSource":"some_datasource2","timestampSpec":null,"dimensionsSpec":null,"metricsSpec":[],"granularitySpec":{"type":"uniform","segmentGranularity":"DAY","queryGranularity":{"type"[truncated 2202 bytes]; line: 1, column: 1368] (through reference chain: org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexSupervisorTask["spec"]->org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexIngestionSpec["ioConfig"]->org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexIOConfig["firehose"])

Here’s the json spec :
{

“type”: “index_parallel”,

“spec”: {

"dataSchema": {

  "dataSource": "some_datasource2",

  "parser": {

    "parseSpec": {

      "format": "timeAndDims",

      "dimensionsSpec": {

        "dimensionExclusions": [],

        "dimensions": [

          "id",

          "name",

          "city"

        ]

      },

      "timestampSpec": {

        "format": "auto",

        "column": "modified_date"

      }

    }

  },

  "metricsSpec": [],

  "granularitySpec": {

    "type": "uniform",

    "segmentGranularity": "DAY",

    "queryGranularity": {

      "type": "none"

    },

    "rollup": false,

    "intervals": null

  },

  "transformSpec": {

    "filter": null,

    "transforms": []

  }

},

"ioConfig": {

  "type": "index_parallel",

  "firehose": {

    "type": "sql",

    "database": {

      "type": "mysql",

      "connectorConfig": {

        "connectURI": "jdbc:mysql://123.123.123:3306/test",

        "user":"admin",

        "password":"password"

      }

    },

    "sqls": [

      "SELECT * FROM something"

    ]

  }

},

"tuningConfig": {

  "type": "index_parallel"

}

}

}

SQL Metadata Connector not configured caught my eye.

In your setup, how did you set up the metadata database across your cluster?

Hi this is the following configuration of commom.runtime.settings. Please note that I’m using a single server deployment. My main objective is to transfer data from my mysql db to druid. When I uncomment the sql lines in the configuration provided below , druid shows a java runtime exception error.

# For Derby server on your Druid Coordinator (only viable in a cluster with a single Coordinator, no fail-over):
druid.metadata.storage.type=derby
druid.metadata.storage.connector.connectURI=jdbc:derby://localhost:1527/var/druid/metadata.db;create=true
druid.metadata.storage.connector.host=localhost
druid.metadata.storage.connector.port=1527

# For MySQL (make sure to include the MySQL JDBC driver on the classpath):
# druid.metadata.storage.type=mysql
# # druid.metadata.mysql.driver.driverClassName=org.mariadb.jdbc.Driver
# druid.metadata.storage.connector.connectURI=jdbc:mysql://root@localhost/druid
# druid.metadata.storage.connector.user=druid
# druid.metadata.storage.connector.password=diurd```

Ah OK: so the metadata database can be left as it is in the default quickstart configuration files. That means that the metadata database will be at the default, which is to use a local Derby database. You only need to change that if you want to use something other than Derby for your metadata database, which is what you deffo need to do when you ramp up from a single node quickstart.

I believe the first step is to get the extension:

You then need to make sure that you include the mySql extension in your common runtime properties configuration:

Just a side note to take care to amend the right configuration files for the quickstart – depending on which quickstart configuration that you use to start Druid, the specific files are in conf/druid/single-server/. Though it looks like you’re already doing that :slight_smile:

When you restart Druid, you can check that the extension is loaded OK in the console or by using the Status API.

There’s an example of the ingestion specification that you’d put together here:

I’d be interested to hear back when you get this to work: SQL Ingestion comes up now and again, and it’d be nice to find out how you get on :slight_smile:

Such as:

Hello mebinjoy,

Could you please confirm that you have added the sql extension “mysql-metadata-storage” to the extension loadlist in the common.runtime.properties file?

Hi,
I’ve got it to work. SQL Ingestion works for both MySQL and PostressSQL. I’m currently thinking of writing a MSSQL Server extension that can also be used as a method for MSSQL Data Injestion. The source code for MySQL Metadata Connector is available on github. So I’m thinking about modifying the code to work with MSSQL as well.

2 Likes

That would be cool! I dug these out in case they’re useful…

And here’s a little filter of the PRs done on the Microsoft SQL Metadata extension, just in case you find some other devs who may be interested to help.

https://github.com/apache/druid/pulls?q=is%3Apr+is%3Aclosed+sqlserver-metadata-storage