Namespace Lookup loaded but not available at querytime

I am using druid-lookups-cached-global extension and loading the lookup using POST as mention in this page.

Here is my lookup configuration:
{

“__tier1”: {

“product_to_product_id”: {

“version”: “v0”,

“lookupExtractorFactory”: {

“type”: “cachedNamespace”,

“extractionNamespace”: {

“type”: “jdbc”,

“connectorConfig”: {

“createTables”: true,

“connectURI”: “jdbc:mysql://localhost:3306/lookupDB?autoReconnect=true&useSSL=false”,

“user”: “user”,

“password”: “pwd”

},

“table”: “lookups”,

“keyColumn”: “product”,

“valueColumn”: “product_id”,

“tsColumn”: “timestamp”

},

“firstCacheTimeout”: 120000,

“injective”:true

}

}

}

}

``

When I POST it on coordinator its successful.

And when I request GET for status using following command
curl -X GET http://localhost:8081/druid/coordinator/v1/lookups/status?detailed=true

``

output is

{"__default":{},"__tier1":{“product_to_product_id”:{“loaded”:true,“pendingNodes”:}}}

``

But when I execute the following Query

{

“queryType”: “select”,

“dataSource”: “example”,

“descending”: “false”,

“dimensions”: [

“user”,

“latencyMs”,

“user_id”,

{

“type”: “extraction”,

“dimension”: “product”,

“outputName”: “product_id”,

“extractionFn”: {

“type”: “registeredLookup”,

“lookup”: “product_to_product_id”,

“retainMissingValue”: true,

“injective”: false

}

}

],

“aggregations”: [

{

“type”: “longSum”,

“fieldName”: “latencyMs”,

“name”: “latencyMsCount”

}

],

“granularity”: “none”,

“intervals”: [

“2018-02-22T11:50:00Z/2018-02-22T11:55:00Z”

],

“pagingSpec”:{“pagingIdentifiers”: {}, “threshold”:10}

}

``

Output is

{

“error” : “Unknown exception”,

“errorMessage” : “Lookup [product_to_product_id] not found”,

“errorClass” : “java.lang.NullPointerException”,

“host” : “192.168.2.129:8083”

}

``

So at query time my lookup product_to_product_id is not available.

What could be the reason for this ?

Thank You.

Hi Abhi,

Did you set druid.lookup.lookupTier to __tier1 in your historical node’s runtime properties?

Thanks,

Jon

Hi Jonathan

I set that property after you asked, but now that lookup is not loading

status gives me
{"__tier1":{“product_to_product_id”:{“loaded”:false,“pendingNodes”:[“192.168.2.129:8083”]}}}

``

and 192.168.2.129:8083 is Historical
So its pending to load on historical.

Thank You.

This the the Error log from my Historical:

2018-02-23T08:05:28,394 ERROR [NamespaceExtractionCacheManager-0] io.druid.server.lookup.namespace.cache.CacheScheduler - Failed to update namespace [JdbcExtractionNamespace{connectorConfig=DbConnectorConfig{createTables=true, connectURI=‘jdbc:mysql://localhost:3306/lookupDB?autoReconnect=true&useSSL=false’, user=‘root’, passwordProvider=io.druid.metadata.DefaultPasswordProvider}, table=‘lookups’, keyColumn=‘product’, valueColumn=‘product_id’, tsColumn=‘timestamp’, filter=‘null’, pollPeriod=PT0S}] : io.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl@10002b63

org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:“SELECT MAX(timestamp) FROM lookups”, located:“SELECT MAX(timestamp) FROM lookups”, rewritten:“SELECT MAX(timestamp) FROM lookups”, arguments:{ positional:{}, named:{}, finder:}]

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

at io.druid.server.lookup.namespace.JdbcCacheGenerator.lastUpdates(JdbcCacheGenerator.java:177) ~[druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.JdbcCacheGenerator.generateCache(JdbcCacheGenerator.java:66) ~[druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.JdbcCacheGenerator.generateCache(JdbcCacheGenerator.java:50) ~[druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl.tryUpdateCache(CacheScheduler.java:225) [druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl.updateCache(CacheScheduler.java:204) [druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl.access$600(CacheScheduler.java:140) [druid-lookups-cached-global-0.11.0.jar:0.11.0]

at io.druid.server.lookup.namespace.cache.CacheScheduler$EntryImpl$2.run(CacheScheduler.java:186) [druid-lookups-cached-global-0.11.0.jar:0.11.0]

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_162]

at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_162]

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_162]

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:1.8.0_162]

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_162]

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_162]

at java.lang.Thread.run(Thread.java:748) [?:1.8.0_162]

Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:“SELECT MAX(timestamp) FROM lookups”, located:“SELECT MAX(timestamp) FROM lookups”, rewritten:“SELECT MAX(timestamp) FROM lookups”, arguments:{ positional:{}, named:{}, finder:}]

at org.skife.jdbi.v2.BaseStatement.cleanup(BaseStatement.java:105) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.fold(Query.java:191) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.first(Query.java:273) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.first(Query.java:264) ~[jdbi-2.63.1.jar:2.63.1]

at io.druid.server.lookup.namespace.JdbcCacheGenerator$2.withHandle(JdbcCacheGenerator.java:191) ~[?:?]

at io.druid.server.lookup.namespace.JdbcCacheGenerator$2.withHandle(JdbcCacheGenerator.java:179) ~[?:?]

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

… 14 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘OPTION SQL_SELECT_LIMIT=DEFAULT’ at line 1

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_162]

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_162]

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_162]

at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[?:1.8.0_162]

at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.ConnectionImpl.unsetMaxRows(ConnectionImpl.java:5424) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2489) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:3091) ~[mysql-connector-java-5.1.18.jar:?]

at com.mysql.jdbc.PreparedStatement.close(PreparedStatement.java:1156) ~[mysql-connector-java-5.1.18.jar:?]

at org.skife.jdbi.v2.DefaultStatementBuilder.close(DefaultStatementBuilder.java:69) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Cleanables$StatementBuilderCleanable.cleanup(Cleanables.java:219) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.BaseStatement$StatementCleaningCustomizer.cleanup(BaseStatement.java:127) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.BaseStatement.cleanup(BaseStatement.java:102) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.fold(Query.java:191) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.first(Query.java:273) ~[jdbi-2.63.1.jar:2.63.1]

at org.skife.jdbi.v2.Query.first(Query.java:264) ~[jdbi-2.63.1.jar:2.63.1]

at io.druid.server.lookup.namespace.JdbcCacheGenerator$2.withHandle(JdbcCacheGenerator.java:191) ~[?:?]

at io.druid.server.lookup.namespace.JdbcCacheGenerator$2.withHandle(JdbcCacheGenerator.java:179) ~[?:?]

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

… 14 more

``

This could be a mismatch between the provided Druid lookup configuration and the schema of the MySQL lookup table, what happens if you run “SELECT MAX(timestamp) FROM lookups” manually on your lookup table in MySQL?

  • Jon

It was problem with the version of mysql-connector jar.
Its discussed here.
https://groups.google.com/forum/#!topic/druid-user/FEy_46r_yEI

Thank you very much for your help.