I am unable to connect to MemSQL database (based on MYSQL wire frame) from Dremio. I am encountering the error below.
2019-08-29 12:53:55,327 [Plugin Startup: DB2] ERROR c.d.e.store.jdbc.JdbcStoragePlugin - Connection is not valid.
java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘joe’@‘172.31.33.167’ (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.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.exec.store.jdbc.JdbcStoragePlugin.getState(JdbcStoragePlugin.java:360) ~[dremio-ce-jdbc-plugin-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.exec.catalog.ManagedStoragePlugin.setLocals(ManagedStoragePlugin.java:455) [dremio-sabot-kernel-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.exec.catalog.ManagedStoragePlugin.access$600(ManagedStoragePlugin.java:81) [dremio-sabot-kernel-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.exec.catalog.ManagedStoragePlugin$1.run(ManagedStoragePlugin.java:258) [dremio-sabot-kernel-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.concurrent.RenamingRunnable.run(RenamingRunnable.java:36) [dremio-common-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.concurrent.SingletonRunnable.run(SingletonRunnable.java:41) [dremio-common-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.concurrent.SafeRunnable.run(SafeRunnable.java:40) [dremio-common-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
at com.dremio.concurrent.Runnables$1.run(Runnables.java:45) [dremio-common-3.3.2-201908142136370993-d60145d.jar:3.3.2-201908142136370993-d60145d]
Caused by: java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘joe’@‘172.31.33.167’ (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 ‘joe’@‘172.31.33.167’ (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
This is an issue on the MySQL side, try the below
SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'joe'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Current-Root-Password';
FLUSH PRIVILEGES;
Or try
update user set authentication_string=password(‘password’) where user=‘joe’;
flush privileges;
Then restart MySQL