QLMetadataConnector - Exception creating table

Hi,

I set up a new druid cluster. I get the below on my coordinatior node. How do I get druid working with mysql? Thanks

In mysql 5.7 I do the below:
CREATE SCHEMA IF NOT EXISTS druid;
ALTER DATABASE druid charset=utf8;

druid.host=xxx.xxx.xxx.xxx8082
druid.service=coordinator
druid.port=8082
druid.zk.service.host:2181=1-zookeeper.com
druid.extensions.coordinates=[“io.druid.extensions:druid-s3-extensions:0.8.2”,“io.druid.extensions:mysql-metadata-storage:0.8.2”]
druid.metadata.storage.type=mysql
druid.metadata.storage.connector.connectURI=jdbc:mysql://primary-druid-:3306/druid
druid.metadata.storage.connector.user=adfadfadf
druid.metadata.storage.connector.password=dfadfadf

druid.coordinator.startDelay=PT70s

2016-01-12T21:18:40,339 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@374f2c4e].
2016-01-12T21:18:40,955 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: Unknown column ‘value’ in ‘where clause’ [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:284) ~[jdbi-2.63.1.jar:2.63.1]
at io.druid.metadata.SQLMetadataConnector$2.call(SQLMetadataConnector.java:108) ~[druid-server-0.8.2.jar:0.8.2]
at com.metamx.common.RetryUtils.retry(RetryUtils.java:38) ~[java-util-0.27.4.jar:?]
at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:113) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:157) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createConfigTable(SQLMetadataConnector.java:231) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createConfigTable(SQLMetadataConnector.java:374) [druid-server-0.8.2.jar:0.8.2]
at io.druid.guice.JacksonConfigManagerModule$1.start(JacksonConfigManagerModule.java:56) [druid-common-0.8.2.jar:0.8.2]
at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:244) [java-util-0.27.4.jar:?]
at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:155) [druid-api-0.3.13.jar:0.8.2]
at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:71) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.ServerRunnable.run(ServerRunnable.java:38) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.Main.main(Main.java:91) [druid-services-0.8.2.jar:0.8.2]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’ [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: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.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:163) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:159) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 12 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_75]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_75]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_75]
at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_75]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[?:?]
at com.mysql.jdbc.Util.getInstance(Util.java:360) ~[?:?]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) ~[?:?]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) ~[?:?]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[?:?]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[?:?]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[?:?]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199) ~[?:?]
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:1328) ~[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.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:163) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:159) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 12 more
2016-01-12T21:18:40,983 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.common.config.ConfigManager.start()] on object[io.druid.common.config.ConfigManager@4c75d658].
2016-01-12T21:18:41,028 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: Unknown column ‘value’ in ‘where clause’ [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:284) ~[jdbi-2.63.1.jar:2.63.1]
at io.druid.metadata.SQLMetadataConnector$2.call(SQLMetadataConnector.java:108) ~[druid-server-0.8.2.jar:0.8.2]
at com.metamx.common.RetryUtils.retry(RetryUtils.java:38) ~[java-util-0.27.4.jar:?]
at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:113) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:157) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createAuditTable(SQLMetadataConnector.java:445) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createAuditTable(SQLMetadataConnector.java:470) [druid-server-0.8.2.jar:0.8.2]
at io.druid.server.audit.SQLAuditManagerProvider$1.start(SQLAuditManagerProvider.java:67) [druid-server-0.8.2.jar:0.8.2]
at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:244) [java-util-0.27.4.jar:?]
at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:155) [druid-api-0.3.13.jar:0.8.2]
at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:71) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.ServerRunnable.run(ServerRunnable.java:38) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.Main.main(Main.java:91) [druid-services-0.8.2.jar:0.8.2]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’ [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: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.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:163) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:159) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 12 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_75]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_75]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_75]
at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_75]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[?:?]
at com.mysql.jdbc.Util.getInstance(Util.java:360) ~[?:?]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) ~[?:?]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) ~[?:?]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[?:?]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[?:?]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[?:?]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199) ~[?:?]
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:1328) ~[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]

Hi David, make sure you follow the steps listed here for setting up mysql as a metadata storage:
http://druid.io/docs/latest/dependencies/metadata-storage.html

Below is my updated runtime.properties file. So why am i still getting the below? I am using mysql 5.7 and fabric with master and slave but I am my pointing only to the master mysql server.

What am i missing?

druid.host=208.111.111.111:8082
druid.service=coordinator
druid.port=8082

druid.zk.service.host:2181=1-zookeeper-forex-do-development-ny.test.com
druid.extensions.coordinates=[“io.druid.extensions:mysql-metadata-storage”]

druid.metadata.storage.type=mysql
druid.metadata.storage.connector.connectURI=jdbc:mysql://primary-mysql-forex-do-development-ny-druid.ftest.com/druid
druid.metadata.storage.connector.user=druid
druid.metadata.storage.connector.password=diurd

druid.coordinator.startDelay=PT70s

Here is how I create the user and schema:

CREATE DATABASE druid DEFAULT CHARACTER SET utf8;
CREATE USER ‘druid’@’%’ IDENTIFIED BY ‘diurd’;
GRANT ALL ON druid.* TO ‘druid’@’%’ IDENTIFIED BY ‘diurd’;
FLUSH PRIVILEGES;

2016-01-16T05:02:01,572 INFO [main] org.apache.zookeeper.ZooKeeper - Initiating client connection, connectString:2181=1-zookeeper-forex-do-development-ny.test.com sessionTimeout=30000 watcher=org.apache.curator.ConnectionState@d0a6349
2016-01-16T05:02:01,555 ERROR [DatabaseSegmentManager-Exec–0] io.druid.metadata.SQLMetadataSegmentManager - Problem polling DB.: {class=io.druid.metadata.SQLMetadataSegmentManager, exceptionType=class org.skife.jdbi.v2.exceptions.CallbackFailedException, exceptionMessage=org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘druid.druid_segments’ doesn’t exist [statement:“SELECT payload FROM druid_segments WHERE used=true”, located:“SELECT payload FROM druid_segments WHERE used=true”, rewritten:“SELECT payload FROM druid_segments WHERE used=true”, arguments:{ positional:{}, named:{}, finder:}]}
org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘druid.druid_segments’ doesn’t exist [statement:“SELECT payload FROM druid_segments WHERE used=true”, located:“SELECT payload FROM druid_segments WHERE used=true”, rewritten:“SELECT payload FROM druid_segments WHERE used=true”, 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.SQLMetadataSegmentManager.poll(SQLMetadataSegmentManager.java:428) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataSegmentManager$1.run(SQLMetadataSegmentManager.java:121) [druid-server-0.8.2.jar:0.8.2]
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:471) [?:1.7.0_75]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304) [?:1.7.0_75]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178) [?:1.7.0_75]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:1.7.0_75]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [?:1.7.0_75]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [?:1.7.0_75]
at java.lang.Thread.run(Thread.java:745) [?:1.7.0_75]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘druid.druid_segments’ doesn’t exist [statement:“SELECT payload FROM druid_segments WHERE used=true”, located:“SELECT payload FROM druid_segments WHERE used=true”, rewritten:“SELECT payload FROM druid_segments WHERE used=true”, arguments:{ positional:{}, named:{}, 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.SQLMetadataSegmentManager$8.withHandle(SQLMetadataSegmentManager.java:434) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataSegmentManager$8.withHandle(SQLMetadataSegmentManager.java:430) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘druid.druid_segments’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_75]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_75]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_75]
at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_75]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) ~[?:?]
at com.mysql.jdbc.Util.getInstance(Util.java:360) ~[?:?]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) ~[?:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) ~[?:?]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) ~[?:?]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[?:?]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[?:?]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[?:?]
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199) ~[?:?]
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:1328) ~[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.SQLMetadataSegmentManager$8.withHandle(SQLMetadataSegmentManager.java:434) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataSegmentManager$8.withHandle(SQLMetadataSegmentManager.java:430) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 10 more
2016-01-16T05:02:01,637 INFO [main-SendThread(208.68.36.21:2181)] org.apache.zookeeper.ClientCnxn - Opening socket connection to server 208.68.36.21/208.68.36.21:2181. Will not attempt to authenticate using SASL (unknown error)
2016-01-16T05:02:01,639 INFO [main] com.metamx.common.lifecycle.Lifecycle$AnnotationBasedHandler - Invoking start method[public void io.druid.client.ServerInventoryView.start() throws java.lang.Exception] on object[io.druid.client.BatchServerInventoryView@2b807abd].
2016-01-16T05:02:01,653 INFO [main-SendThread(208.68.36.21:2181)] org.apache.zookeeper.ClientCnxn - Socket connection established to 208.68.36.21/208.68.36.21:2181, initiating session
2016-01-16T05:02:01,672 INFO [main-SendThread(208.68.36.21:2181)] org.apache.zookeeper.ClientCnxn - Session establishment complete on server 208.68.36.21/208.68.36.21:2181, sessionid = 0x15240f81c5d017a, negotiated timeout = 30000
2016-01-16T05:02:01,682 INFO [main-EventThread] org.apache.curator.framework.state.ConnectionStateManager - State change: CONNECTED
2016-01-16T05:02:01,662 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: Unknown column ‘value’ in ‘where clause’ [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:284) ~[jdbi-2.63.1.jar:2.63.1]
at io.druid.metadata.SQLMetadataConnector$2.call(SQLMetadataConnector.java:108) ~[druid-server-0.8.2.jar:0.8.2]
at com.metamx.common.RetryUtils.retry(RetryUtils.java:38) ~[java-util-0.27.4.jar:?]
at io.druid.metadata.SQLMetadataConnector.retryWithHandle(SQLMetadataConnector.java:113) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createTable(SQLMetadataConnector.java:157) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createRulesTable(SQLMetadataConnector.java:211) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector.createRulesTable(SQLMetadataConnector.java:367) [druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataRuleManagerProvider$1.start(SQLMetadataRuleManagerProvider.java:70) [druid-server-0.8.2.jar:0.8.2]
at com.metamx.common.lifecycle.Lifecycle.start(Lifecycle.java:244) [java-util-0.27.4.jar:?]
at io.druid.guice.LifecycleModule$2.start(LifecycleModule.java:155) [druid-api-0.3.13.jar:0.8.2]
at io.druid.cli.GuiceRunnable.initLifecycle(GuiceRunnable.java:71) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.ServerRunnable.run(ServerRunnable.java:38) [druid-services-0.8.2.jar:0.8.2]
at io.druid.cli.Main.main(Main.java:91) [druid-services-0.8.2.jar:0.8.2]
Caused by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’ [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: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.storage.mysql.MySQLConnector.tableExists(MySQLConnector.java:79) ~[?:?]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:163) ~[druid-server-0.8.2.jar:0.8.2]
at io.druid.metadata.SQLMetadataConnector$4.withHandle(SQLMetadataConnector.java:159) ~[druid-server-0.8.2.jar:0.8.2]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:281) ~[jdbi-2.63.1.jar:2.63.1]
… 12 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’

Hmm, interesting, the default Druid tables did not seem to get created once the coordinator service started up.

Do you have the full coordinator logs? I’m wondering if there was another exception earlier on reporting why there were problems creating tables.

Hi,

am still getting no resolution.

On the coordinator server I installed mysql client. The cooridnator server has access with user root and Password Test101.

Why am I still having an issue? I dont get it. do I need to create tbhe tables in the druid database?

druid.host=192.34.56.66:8082
druid.service=coordinator
druid.port=8082
druid.zk.service.host:2181=1-zookeeper-forex-do-development-ny.forexhui.com
druid.extensions.coordinates=[“io.druid.extensions:mysql-metadata-storage”]
druid.metadata.storage.type=mysql
druid.metadata.storage.connector.connectURI=jdbc:mysql://208.68.36.97:3306/druid
druid.metadata.storage.connector.user=root
druid.metadata.storage.connector.password=Test101
druid.coordinator.startDelay=PT70s

mysql -h 208.68.36.97 -u root -pTest101
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 203
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright © 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

I am still getting this error on a new cluster and new mysql server

org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘value’ in ‘where clause’ [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:284) ~[jdbi-2.63.1.jar:2.63.1]

In mysql I ran the below:

Hi David, I wonder if perhaps are you hitting https://github.com/druid-io/druid/issues/1701