setup issues with coordinator

I’ve been following the tutorials, and I’ve got an environment set up with Docker (using druid 0.8.0). I’ve got the druid processes running in a single docker container, and zookeeper, mysql, and kafka are each in their own containers.

My issue is that the coordinator is failing to stay running, because of exceptions related to missing tables:

2015-09-01T16:33:45,996 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:262) ~[jdbi-2.32.jar:?]

at io.druid.metadata.SQLMetadataRuleManager.createDefaultRule(SQLMetadataRuleManager.java:78) ~[druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataRuleManagerProvider$1.start(SQLMetadataRuleManagerProvider.java:71) ~[druid-server-0.8.0.jar:0.8.0]

at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:250) ~[java-util-0.27.0.jar:?]

at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:136) ~[druid-api-0.3.8.jar:0.8.0]

at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:71) [druid-services-0.8.0.jar:0.8.0]

at io.druid.cli.ServerRunnable.run(ServerRunnable.java:38) [druid-services-0.8.0.jar:0.8.0]

at io.druid.cli.Main.main(Main.java:91) [druid-services-0.8.0.jar:0.8.0]

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:1306) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.fold(Query.java:172) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:84) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:78) ~[jdbi-2.32.jar:?]

at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:92) ~[druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:80) ~[druid-server-0.8.0.jar:0.8.0]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]

… 7 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘druid.druid_rules’ doesn’t exist

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_76]

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_76]

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_76]

at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_76]

at com.mysql.jdbc.Util.handleNewInstance(Util.java:400) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.Util.getInstance(Util.java:383) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[commons-dbcp2-2.0.1.jar:2.0.1]

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[commons-dbcp2-2.0.1.jar:2.0.1]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1300) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.fold(Query.java:172) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:84) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:78) ~[jdbi-2.32.jar:?]

at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:92) ~[druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataRuleManager$1.withHandle(SQLMetadataRuleManager.java:80) ~[druid-server-0.8.0.jar:0.8.0]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]

… 7 more

[root@acf2eaf3c6a6 druid]#

I’ve been following the documentation here: http://druid.io/docs/latest/dependencies/metadata-storage.html, which indicates I need to create the database, create the user, and grant access. I’ve verified that the database exists, that the druid user has access:

I also manually set the following property (even though the documentation says this is the default):

druid.metadata.storage.connector.createTables=true

It didn’t change the result.

So I dug farther up the stack, and the errors started here:

2015-09-02T21:17:04,064 WARN [main] io.druid.metadata.SQLMetadataConnector - Exception creating table

org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user ‘druid’@‘172.17.0.9’ for table ‘session_variables’ [statement:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, located:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, rewritten:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, arguments:{ positional:{}, named:{}, finder:}]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:262) ~[jdbi-2.32.jar:?]

at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:88) [druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataConnector.createConfigTable(SQLMetadataConnector.java:164) [druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataConnector.createConfigTable(SQLMetadataConnector.java:311) [druid-server-0.8.0.jar:0.8.0]

at io.druid.guice.JacksonConfigManagerModule$1.start(JacksonConfigManagerModule.java:56) [druid-common-0.8.0.jar:0.8.0]

at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:250) [java-util-0.27.0.jar:?]

at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:136) [druid-api-0.3.8.jar:0.8.0]

at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:71) [druid-services-0.8.0.jar:0.8.0]

at io.druid.cli.ServerRunnable.run(ServerRunnable.java:38) [druid-services-0.8.0.jar:0.8.0]

at io.druid.cli.Main.main(Main.java:91) [druid-services-0.8.0.jar:0.8.0]

Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user ‘druid’@‘172.17.0.9’ for table ‘session_variables’ [statement:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, located:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, rewritten:“SHOW VARIABLES where variable_name = ‘character_set_database’ and value = ‘utf8’”, arguments:{ positional:{}, named:{}, finder:}]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1306) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.fold(Query.java:172) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:84) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:78) ~[jdbi-2.32.jar:?]

at io.druid.metadata.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]

at io.druid.metadata.SQLMetadataConnector$1.withHandle(SQLMetadataConnector.java:94) ~[druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataConnector$1.withHandle(SQLMetadataConnector.java:90) ~[druid-server-0.8.0.jar:0.8.0]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]

… 9 more

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: SELECT command denied to user ‘druid’@‘172.17.0.9’ for table ‘session_variables’

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_76]

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_76]

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_76]

at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_76]

at com.mysql.jdbc.Util.handleNewInstance(Util.java:400) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.Util.getInstance(Util.java:383) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1901) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1193) ~[mysql-connector-java-5.1.36-bin.jar:5.1.36]

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[commons-dbcp2-2.0.1.jar:2.0.1]

at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:198) ~[commons-dbcp2-2.0.1.jar:2.0.1]

at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1300) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.fold(Query.java:172) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:84) ~[jdbi-2.32.jar:?]

at org.skife.jdbi.v2.Query.list(Query.java:78) ~[jdbi-2.32.jar:?]

at io.druid.metadata.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]

at io.druid.metadata.SQLMetadataConnector$1.withHandle(SQLMetadataConnector.java:94) ~[druid-server-0.8.0.jar:0.8.0]

at io.druid.metadata.SQLMetadataConnector$1.withHandle(SQLMetadataConnector.java:90) ~[druid-server-0.8.0.jar:0.8.0]

at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]

… 9 more

Which is easily google-able to find an answer here:

To quote the stack overflow answer:

Workaround is setting show_compatibility_56 = on in /etc/my.cnf and restart mysql server.

This is sort of non ideal, not sure if its something that can/should be fixed in druid.

Hi Kevin, thanks for this catch. What version of MySQL are you using? I haven’t seen this error before but I think it’d be good to understand how to trigger it.

Version 5.7

Even I faced this error, when I ran realtime nodes druid_segments table was missing. I fixed it by manually creating tables(followed this post) https://groups.google.com/forum/#!topic/druid-development/yT2FsDXs9Hc
My MySQL version 5.1.73.

Setting the properties is not helping.

I’ve filed https://github.com/druid-io/druid/issues/1701 to look further into this.

This fixed the issue (I also needed to set the default encoding to utf8; the default installation was setting it to latin1, but the error messages on that one were pretty clear).