Unable to configure jdbc namespace lookup

[Torche Guillaume]

Hi all,

I am trying to configure a jdbc lookup for test purposes. 

I am using Druid 0.8.1. Here is my common config file:

druid.s3.accessKey=XXXXXXXXXXXX
druid.s3.secretKey=XXXXXXXXXXXXXXXXXXXXXXXXXXX

druid.storage.type=local
druid.storage.storageDirectory=/tmp/druid/localStorage


druid.cache.type=local
druid.cache.sizeInBytes=10000000


# Indexing service discovery
druid.selectors.indexing.serviceName=overlord


# Monitoring (disabled for examples, if you enable SysMonitor, make sure to include sigar jar in your cp)
# druid.monitoring.monitors=["com.metamx.metrics.SysMonitor","com.metamx.metrics.JvmMonitor"]


# Metrics logging (disabled for examples - change this to logging or http in production)
druid.emitter=noop


druid.extensions.coordinates=["io.druid.extensions:druid-namespace-lookup:0.8.1", "io.druid.extensions:druid-s3-extensions:0.8.1", "io.druid.extensions:mysql-metadata-storage:0.8.1"]


druid.query.extraction.namespace.cache.type=onHeap


druid.query.extraction.namespace.lookups=\
[{\
  "type":"jdbc",\
  "namespace":"mysql_lookup",\
  "connectorConfig":{\
    "createTables":false,\
    "connectURI":"XXXXXXXXXXXXXXXXX",\
    "user":"XXXXX",\
    "password":"XXXXXX"\
  },\
  "table":"countries",\
  "keyColumn":"code",\
  "valueColumn":"name",\
  "pollPeriod":600000\
}]

For some reason I get the following error when starting my coordinator node:

2016-01-27T03:33:54,850 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.server.namespace.NamespacedExtractionModule$NamespaceStaticConfiguration.start()] on object[io.druid.server.namespace.NamespacedExtractionModule$NamespaceStaticConfiguration@72976b4].
2016-01-27T03:33:54,850 INFO [main] io.druid.server.namespace.NamespacedExtractionModule - Loading configuration as static configuration
2016-01-27T03:33:54,855 INFO [main] io.druid.server.namespace.NamespacedExtractionModule - Loaded 1 namespace-lookup configuration
2016-01-27T03:33:54,855 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.metadata.MetadataStorage.start()] on object[io.druid.metadata.NoopMetadataStorageProvider$1@3069a360].
2016-01-27T03:33:54,859 ERROR [NamespaceExtractionCacheManager-0] io.druid.server.namespace.cache.NamespaceExtractionCacheManager - Error in listener for namespace [mysql_lookup]
org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: No suitable driver found for XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 at org.skife.jdbi.v2.DBI.open(DBI.java:210) ~[jdbi-2.32.jar:?]
 at org.skife.jdbi.v2.DBI.withHandle(DBI.java:257) ~[jdbi-2.32.jar:?]
 at io.druid.server.namespace.JDBCExtractionNamespaceFunctionFactory$3.call(JDBCExtractionNamespaceFunctionFactory.java:105) ~[druid-namespace-lookup-0.8.1.jar:0.8.1]
 at io.druid.server.namespace.JDBCExtractionNamespaceFunctionFactory$3.call(JDBCExtractionNamespaceFunctionFactory.java:96) ~[druid-namespace-lookup-0.8.1.jar:0.8.1]
 at io.druid.server.namespace.cache.NamespaceExtractionCacheManager$5.run(NamespaceExtractionCacheManager.java:356) [druid-namespace-lookup-0.8.1.jar:0.8.1]
 at com.google.common.util.concurrent.MoreExecutors$ScheduledListeningDecorator$NeverSuccessfulListenableFutureTask.run(MoreExecutors.java:582) [guava-16.0.1.jar:?]
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_45]
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [?:1.8.0_45]
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_45]
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [?:1.8.0_45]
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_45]
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_45]
 at java.lang.Thread.run(Thread.java:745) [?:1.8.0_45]
Caused by: java.sql.SQLException: No suitable driver found for XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 at java.sql.DriverManager.getConnection(DriverManager.java:689) ~[?:1.8.0_45]
 at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:1.8.0_45]
 at org.skife.jdbi.v2.DBI$3.openConnection(DBI.java:136) ~[jdbi-2.32.jar:?]
 at org.skife.jdbi.v2.DBI.open(DBI.java:192) ~[jdbi-2.32.jar:?]
 ... 12 more
2016-01-27T03:33:54,866 ERROR [NamespaceExtractionCacheManager-0] io.druid.server.namespace.cache.NamespaceExtractionCacheManager - Failed update namespace [JDBCExtractionNamespace = { namespace = mysql_lookup, connectorConfig = { DbConnectorConfig{createTables=false, connectURI='xxxxxxxxxxxxxxxxxxxxxx', user='xxxxx', passwordProvider=io.druid.metadata.DefaultPasswordProvider} }, table = countries, keyColumn = code, valueColumn = name, tsColumn = null, pollPeriod = PT10M}]
org.skife.jdbi.v2.exceptions.UnableToObtainConnectionException: java.sql.SQLException: No suitable driver found for XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 at org.skife.jdbi.v2.DBI.open(DBI.java:210) ~[jdbi-2.32.jar:?]
 at org.skife.jdbi.v2.DBI.withHandle(DBI.java:257) ~[jdbi-2.32.jar:?]
 at io.druid.server.namespace.JDBCExtractionNamespaceFunctionFactory$3.call(JDBCExtractionNamespaceFunctionFactory.java:105) ~[?:?]
 at io.druid.server.namespace.JDBCExtractionNamespaceFunctionFactory$3.call(JDBCExtractionNamespaceFunctionFactory.java:96) ~[?:?]
 at io.druid.server.namespace.cache.NamespaceExtractionCacheManager$5.run(NamespaceExtractionCacheManager.java:356) ~[?:?]
 at com.google.common.util.concurrent.MoreExecutors$ScheduledListeningDecorator$NeverSuccessfulListenableFutureTask.run(MoreExecutors.java:582) [guava-16.0.1.jar:?]
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_45]
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [?:1.8.0_45]
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_45]
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [?:1.8.0_45]
 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_45]
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_45]
 at java.lang.Thread.run(Thread.java:745) [?:1.8.0_45]
Caused by: java.sql.SQLException: No suitable driver found for XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 at java.sql.DriverManager.getConnection(DriverManager.java:689) ~[?:1.8.0_45]
 at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[?:1.8.0_45]
 at org.skife.jdbi.v2.DBI$3.openConnection(DBI.java:136) ~[jdbi-2.32.jar:?]
 at org.skife.jdbi.v2.DBI.open(DBI.java:192) ~[jdbi-2.32.jar:?]
 ... 12 more

It looks like it doesn't find a suitable driver to connect to MySQL. However my coordinator is able to connect to my MySQL metadata storage later on. Any idea what is the issue here? Am I missing a Druid extension?

Thanks for your help!

[charles.allen]

Hi Torche, thanks for the information.

Can you expose the URI scheme PRIOR to the host? aka. the part up to the //  ?

[Torche Guillaume]

Hi Charles,

The part up to the host is: jdbc:mysql://

...

[charles.allen]

Thanks Torche, I looked into this a bit and filed https://github.com/druid-io/druid/issues/2345 for further investigation.

There are some improvements to the query time lookups coming very soon, and I want to make sure this is addressed in there.

...

[Torche Guillaume]

Thanks Charles. Will keep an eye on this issue.

...

[Jesal]

FYI I'm getting the same error for jdbc:postgresql. Tracking the issue.

[Miquel de Arcayne]

Was this solved in the end? I'm facing the same exact problem!

[rohit kochar]

I am also facing the same problem,not sure if a workaround exist for the same.

I have tried both the extensions :"druid-namespace-lookup" and "lookups-cached-global" and get the same exception.

Any help would be grateful.

[rohit kochar]

I have modified the code of "lookups-cached-global" and hardcoded the Mysql driver in the code.

After doing that it has worked for me.
I know it is a bad hack but i needed this feature urgently and didn't know the good fix for it.

If somebody else also wants to workaround this issue can try the same.

One needs to modify the "ensureDBI" method of "JDBCExtractionNamespaceCacheFactory" to achieve the same.