I am trying to connect to my MemSQL server. It uses MySQL wire protocol, and so any MySQL client should be able to connect to it. But when I use Dremio to connect, it gives an Authentication error. I tried the same user provided in the server.log file that had authentication issue using the MySQL client, and it worked. Any inputs would be helpful.
I created a new user in memsql today to test. The memsql installation is on the same host as the dremio server. I am able to log in with mysql client using the same user and credentials.
Please see the log below :
2020-01-16 22:43:30,905 [Plugin Startup: memsql_test] ERROR c.d.e.store.jdbc.JdbcStoragePlugin - Connection is not valid.
java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘dremio’@‘localhost’ (using password: YES)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:232) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.MariaDbDataSource.getConnection(MariaDbDataSource.java:290) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.MariaDbDataSource.getPooledConnection(MariaDbDataSource.java:455) ~[mariadb-java-client-2.3.0.jar:na]
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.testCPDS(InstanceKeyDataSource.java:1018) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.datasources.SharedPoolDataSource.registerPool(SharedPoolDataSource.java:167) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(SharedPoolDataSource.java:127) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:884) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:858) ~[commons-dbcp2-2.2.0.jar:2.2.0]
at com.dremio.exec.store.jdbc.CloseableDataSource$DatasourceWrapper.getConnection(CloseableDataSource.java:87) ~[dremio-ce-jdbc-plugin-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.store.jdbc.JdbcStoragePlugin.getState(JdbcStoragePlugin.java:350) ~[dremio-ce-jdbc-plugin-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.ManagedStoragePlugin.setLocals(ManagedStoragePlugin.java:455) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.ManagedStoragePlugin.access$600(ManagedStoragePlugin.java:81) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.ManagedStoragePlugin$1.run(ManagedStoragePlugin.java:258) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.RenamingRunnable.run(RenamingRunnable.java:36) [dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.SingletonRunnable.run(SingletonRunnable.java:41) [dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.SafeRunnable.run(SafeRunnable.java:40) [dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.Runnables$1.run(Runnables.java:45) [dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
Caused by: java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘dremio’@‘localhost’ (using password: YES)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:232) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1199) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:560) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:174) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.MariaDbDataSource.getConnection(MariaDbDataSource.java:288) ~[mariadb-java-client-2.3.0.jar:na]
… 15 common frames omitted
Caused by: java.sql.SQLException: Could not connect: Access denied for user ‘dremio’@‘localhost’ (using password: YES)
at org.mariadb.jdbc.internal.com.send.AbstractAuthSwitchSendResponsePacket.handleResultPacket(AbstractAuthSwitchSendResponsePacket.java:100) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authentication(AbstractConnectProtocol.java:919) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.handleConnectionPhases(AbstractConnectProtocol.java:850) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:507) ~[mariadb-java-client-2.3.0.jar:na]
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1195) ~[mariadb-java-client-2.3.0.jar:na]
… 18 common frames omitted
2020-01-16 22:43:30,908 [qtp1697142212-39353] INFO c.d.exec.catalog.CatalogServiceImpl - User Error Occurred [ErrorId: ddc7b1e4-77ab-4335-a605-e01201fac65c]
com.dremio.common.exceptions.UserException: Failure while configuring source [memsql_test].
at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:776) ~[dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.CatalogServiceImpl.createOrUpdateSource(CatalogServiceImpl.java:671) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.CatalogServiceImpl.createSource(CatalogServiceImpl.java:406) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.CatalogServiceImpl.access$600(CatalogServiceImpl.java:105) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.CatalogServiceImpl$SourceModifier.createSource(CatalogServiceImpl.java:1021) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.CatalogImpl.createSource(CatalogImpl.java:597) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.exec.catalog.DelegatingCatalog.createSource(DelegatingCatalog.java:201) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:157) [dremio-dac-backend-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:148) [dremio-dac-backend-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.dac.service.source.SourceService.registerSourceWithRuntime(SourceService.java:144) [dremio-dac-backend-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.dac.resource.PutSourceResource.putSource(PutSourceResource.java:80) [dremio-dac-backend-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_212]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_212]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_212]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_212]
at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.internal.Errors.process(Errors.java:315) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.internal.Errors.process(Errors.java:297) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.internal.Errors.process(Errors.java:267) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317) [jersey-common-2.25.1.jar:na]
at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154) [jersey-server-2.25.1.jar:na]
at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473) [jersey-container-servlet-core-2.25.1.jar:na]
at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427) [jersey-container-servlet-core-2.25.1.jar:na]
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388) [jersey-container-servlet-core-2.25.1.jar:na]
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341) [jersey-container-servlet-core-2.25.1.jar:na]
at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228) [jersey-container-servlet-core-2.25.1.jar:na]
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:873) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1623) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at com.dremio.dac.server.SecurityHeadersFilter.doFilter(SecurityHeadersFilter.java:52) [dremio-dac-backend-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:51) [jetty-servlets-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1610) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:540) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1345) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480) [jetty-servlet-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1247) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:56) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.Server.handle(Server.java:502) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267) [jetty-server-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305) [jetty-io-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103) [jetty-io-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117) [jetty-io-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:333) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:310) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:168) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:126) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:366) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:765) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:683) [jetty-util-9.4.15.v20190215.jar:9.4.15.v20190215]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_212]
Caused by: java.util.concurrent.ExecutionException: java.lang.Exception: Unavailable: Could not connect: Access denied for user ‘dremio’@‘localhost’ (using password: YES)
at com.google.common.util.concurrent.AbstractFuture.getDoneValue(AbstractFuture.java:500) ~[guava-20.0.jar:na]
at com.google.common.util.concurrent.AbstractFuture.get(AbstractFuture.java:401) ~[guava-20.0.jar:na]
at com.google.common.util.concurrent.AbstractFuture$TrustedFuture.get(AbstractFuture.java:83) ~[guava-20.0.jar:na]
at com.google.common.util.concurrent.ForwardingFuture.get(ForwardingFuture.java:68) ~[guava-20.0.jar:na]
at com.google.common.util.concurrent.AbstractCheckedFuture.checkedGet(AbstractCheckedFuture.java:104) ~[guava-20.0.jar:na]
at com.dremio.exec.catalog.CatalogServiceImpl.createOrUpdateSource(CatalogServiceImpl.java:659) [dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
… 65 common frames omitted
Caused by: java.lang.Exception: Unavailable: Could not connect: Access denied for user ‘dremio’@‘localhost’ (using password: YES)
at com.dremio.exec.catalog.ManagedStoragePlugin$1.run(ManagedStoragePlugin.java:263) ~[dremio-sabot-kernel-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.RenamingRunnable.run(RenamingRunnable.java:36) ~[dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.SingletonRunnable.run(SingletonRunnable.java:41) ~[dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.SafeRunnable.run(SafeRunnable.java:40) ~[dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
at com.dremio.concurrent.Runnables$1.run(Runnables.java:45) ~[dremio-common-3.3.1-201907291852280797-df23756.jar:3.3.1-201907291852280797-df23756]
Does Dremio use mysql client or does it try to connect natively to the mysql server ?
Did you try the below command from mysql shell? Is you database running in safe mode? This error usually means more of an access issue
mysql -u dremio -p
https://dev.mysql.com/doc/refman/5.7/en/problems-connecting.html
Yes. Please see the output below :
[root@elkib memsql_install]# mysql -u dremio -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.58 MemSQL source distribution (compatible; MySQL Enterprise & MySQL Commercial)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.