Druid Coordinator Failing seconds after starting

[#1]

Issue

Soon after starting Druid services, coordinator keeps going down. This is mostly experienced on a new setup. The exception in the coordinator log looks like this

2018-02-02T00:37:24,123 ERROR [main] io.druid.cli.CliCoordinator - Error when starting up.  Failing.
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'druid.druid_rules' doesn't exist [statement:"SELECT id from druid_rules where datasource=:dataSource", located:"SELECT id from druid_rules where datasource=:dataSource", rewritten:"SELECT id from druid_rules where datasource=?", arguments:{ positional:{}, named:{dataSource:'_default'}, finder:[]}]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:284) ~[jdbi-2.63.1.jar:2.63.1]
        at io.druid.metadata.SQLMetadataRuleManager.createDefaultRule(SQLMetadataRuleManager.java:83) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataRuleManagerProvider$1.start(SQLMetadataRuleManagerProvider.java:72) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.java.util.common.lifecycle.Lifecycle.start(Lifecycle.java:263) ~[java-util-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:156) ~[druid-api-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:103) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.ServerRunnable.run(ServerRunnable.java:41) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.Main.main(Main.java:108) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'druid.druid_rules' doesn't exist [statement:"SELECT id from druid_rules where datasource=:dataSource", located:"SELECT id from druid_rules where datasource=:dataSource", rewritten:"SELECT id from druid_rules where datasource=?", arguments:{ positional:{}, named:{dataSource:'_default'}, finder:[]}]
        at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1334) ~[jdbi-2.63.1.jar:2.63.1]
        at org.skife.jdbi.v2.Query.fold(Query.java:173) ~[jdbi-2.63.1.jar:2.63.1]
        at org.skife.jdbi.v2.Query.list(Query.java:82) ~[jdbi-2.63.1.jar:2.63.1]
        at org.skife.jdbi.v2.Query.list(Query.java:75) ~[jdbi-2.63.1.jar:2.63.1]
        at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:97) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:85) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]

Cause

Possible cause for the issue is that druid database may have been created manually in mysql and uses "latin1" as the default character set.

Resolution

This is a two-step resolution.

• Change the character set in the backend db to "utf8"

mysql> alter database druid character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)mysql> use druid;
Database changed mysql> show variables like "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.02 sec)

• Once you restart Druid coordinator post this change, it may start up, however, you might still get similar error (like tables missing OR stack similar to this

2018-02-02T19:42:06,305 WARN [main] io.druid.java.util.common.RetryUtils - Failed on try 9, retrying in 51,342ms.
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:"SELECT @@character_set_database = 'utf8'", located:"SELECT @@character_set_database = 'utf8'", rewritten:"SELECT @@character_set_database = 'utf8'", 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.metadata.SQLMetadataConnector$2.call(SQLMetadataConnector.java:130) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.java.util.common.RetryUtils.retry(RetryUtils.java:63) [java-util-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.java.util.common.RetryUtils.retry(RetryUtils.java:81) [java-util-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:134) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:143) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:184) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector.createRulesTable(SQLMetadataConnector.java:282) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector.createRulesTable(SQLMetadataConnector.java:476) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataRuleManagerProvider$1.start(SQLMetadataRuleManagerProvider.java:71) [druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.java.util.common.lifecycle.Lifecycle.start(Lifecycle.java:263) [java-util-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:156) [druid-api-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:103) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.ServerRunnable.run(ServerRunnable.java:41) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.cli.Main.main(Main.java:108) [druid-services-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:"SELECT @@character_set_database = 'utf8'", located:"SELECT @@character_set_database = 'utf8'", rewritten:"SELECT @@character_set_database = 'utf8'", 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.metadata.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:101) ~[?:?]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:190) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:186) ~[druid-server-0.10.1.2.6.4.0-91.jar:0.10.1.2.6.4.0-91]
        at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
        ... 14 more

• The issue is Druid uses a slightly older JDBC jar (5.1.14)

/usr/hdp/current/druid-coordinator/extensions/mysql-metadata-storage/mysql-jdbc-driver.jar

• Download 5.1.45 from here and replace it with existing mysql-jdbc-driver.jar

cp mysql-connector-java-5.1.45-bin.jar /usr/hdp/2.6.4.0-91/druid/extensions/mysql-metadata-storage/mysql-jdbc-driver.jar

• Restarting druid coordinator service post this change should work.

[#2]

Thanks Shyam,

solved my issue with only copying the latest jar file .