Druid JDBC lookup not working (druid-lookups-cached-global)

Hi,

I’ve been trying to implement a simple JDBC lookup using the druid-lookups-cached-global with no success at all. The problem is always “No suitable driver found for jdbc:mysql://host:port/database”.

Now, I know this a known bug (https://github.com/druid-io/druid/issues/2345), so does this mean that we cannot use JDBC lookups AT ALL until the bug is fixed?

Just for the record, here is what I tried:

  • POST empty JSON to /druid/coordinator/v1/lookups in order to initialize the lookups config.

  • POST the attached JSON to druid/coordinator/v1/lookups/__default/lookup_name (Tried different JSONs for this, such as the example one from documentation with the same result)

  • At this point, the broker will throw the following exception:

  • 72016-10-04T09:49:08,314 ERROR [NamespaceExtractionCacheManager-0] io.druid.server.lookup.namespace.cache.NamespaceExtractionCacheManager - Failed update namespace [JDBCExtractionNamespace = { connectorConfig = { DbConnectorConfig{createTables=true, connectURI=‘jdbc:mysql://DB_HOST:DB_PORT/DB_NAME’, user=‘root’, passwordProvider=io.druid.metadata.DefaultPasswordProvider} }, table = table_name, keyColumn = id, valueColumn = display_name, tsColumn = null, pollPeriod = PT0S}]

    org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: No suitable driver found for jdbc:mysql://DB_HOST:DB_PORT/DB_NAME

    at org.skife.jdbi.v2.DBI.open(DBI.java:230) ~[jdbi-2.63.1.jar:2.63.1]

    at org.skife.jdbi.v2.DBI.withHandle(DBI.java:279) ~[jdbi-2.63.1.jar:2.63.1]

    at io.druid.server.lookup.namespace.JDBCExtractionNamespaceCacheFactory$2.call(JDBCExtractionNamespaceCacheFactory.java:83) ~[druid-lookups-cached-global-0.9.2-rc1.jar:0.9.2-rc1]

    at io.druid.server.lookup.namespace.JDBCExtractionNamespaceCacheFactory$2.call(JDBCExtractionNamespaceCacheFactory.java:73) ~[druid-lookups-cached-global-0.9.2-rc1.jar:0.9.2-rc1]

    at io.druid.server.lookup.namespace.cache.NamespaceExtractionCacheManager$4.run(NamespaceExtractionCacheManager.java:361) [druid-lookups-cached-global-0.9.2-rc1.jar:0.9.2-rc1]

    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [?:1.7.0_111]

    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [?:1.7.0_111]

    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [?:1.7.0_111]

    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [?:1.7.0_111]

    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:178) [?:1.7.0_111]

    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:292) [?:1.7.0_111]

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [?:1.7.0_111]

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [?:1.7.0_111]

    at java.lang.Thread.run(Thread.java:745) [?:1.7.0_111]

    Caused by: java.sql.SQLException: No suitable driver found for jdbc:mysql://DB_HOST:DB_PORT/DB_NAME

    at java.sql.DriverManager.getConnection(DriverManager.java:596) ~[?:1.7.0_111]

    at java.sql.DriverManager.getConnection(DriverManager.java:215) ~[?:1.7.0_111]

    at org.skife.jdbi.v2.DBI$3.openConnection(DBI.java:140) ~[jdbi-2.63.1.jar:2.63.1]

    at org.skife.jdbi.v2.DBI.open(DBI.java:212) ~[jdbi-2.63.1.jar:2.63.1]

    … 13 more

    ``

  • Obviously, when using the lookup from Pivot, I get:

  • com.metamx.common.ISE: Namespace vanished for [namespace-factory-JDBCExtractionNamespace = { connectorConfig = { DbConnectorConfig{createTables=true, connectURI=‘jdbc:mysql://DB_HOST:DB_PORT/DB_NAME’, user=‘root’, passwordProvider=io.druid.metadata.DefaultPasswordProvider} }, table = table_name, keyColumn = id, valueColumn = display_name, tsColumn = null, pollPeriod = PT0S}-0fb8efce-5b8e-4cfc-b15d-cecb454591e0]

    ``

Note that the cluster is currently connected to the same MySQL server to use as a metadata store, so there should be no problem with endpoints / permissions.

Has anyone made this work? An example would be highly appreciated!

Thanks!

lookup.json (352 Bytes)

Hi,
I have the exact same problem using latest release 0.9.2.

I posted a few lookups referencing mysql tables using mysql jdbc driver to /druid/coordinator/v1/lookups
It is the same mysql server where druid metadata are stored.

but same driver error :

I copied mysql-connector-java-5.1.38.jar from mysql-metadata-storage extension to lib directory and it is now working.

Can you file a bug about this? it should at least be documented if a fix can’t be accomplished quickly.

done. https://github.com/druid-io/druid/issues/3770