Dremio pointing to dremio server instead of database host for jdbc datasources

1

Hi, I’m trying to setup a connection to a remote MySQL host.
For some reason however, the connection fails pointing to the dremio server instead of the host.
This happens with the standard MySQL connection.

any idea where to look for the root cause?

error:

2022-01-10 14:51:56,294 [start-test] ERROR c.d.e.s.jdbc.JdbcSchemaFetcherImpl - Connection is not valid.
java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘dremio’@‘10.123.94.83’ (using password: YES)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:232)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
at org.mariadb.jdbc.MariaDbDataSource.getConnection(MariaDbDataSource.java:290)
at org.mariadb.jdbc.MariaDbDataSource.getPooledConnection(MariaDbDataSource.java:455)
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.testCPDS(InstanceKeyDataSource.java:1018)
at org.apache.commons.dbcp2.datasources.SharedPoolDataSource.registerPool(SharedPoolDataSource.java:167)
at org.apache.commons.dbcp2.datasources.SharedPoolDataSource.getPooledConnectionAndInfo(SharedPoolDataSource.java:127)
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:884)
at org.apache.commons.dbcp2.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:858)
at com.dremio.exec.store.jdbc.CloseableDataSource$DatasourceWrapper.getConnection(CloseableDataSource.java:87)
at com.dremio.exec.store.jdbc.JdbcSchemaFetcherImpl.getState(JdbcSchemaFetcherImpl.java:597)
at com.dremio.exec.store.jdbc.JdbcStoragePlugin.getState(JdbcStoragePlugin.java:335)
at com.dremio.exec.catalog.ManagedStoragePlugin.setLocals(ManagedStoragePlugin.java:847)
at com.dremio.exec.catalog.ManagedStoragePlugin.lambda$newStartSupplier$1(ManagedStoragePlugin.java:524)
at com.dremio.exec.catalog.ManagedStoragePlugin.lambda$nameSupplier$3(ManagedStoragePlugin.java:591)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1604)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLInvalidAuthorizationSpecException: Could not connect: Access denied for user ‘dremio’@‘10.123.94.83’ (using password: YES)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:232)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1199)
at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:560)
at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:174)
at org.mariadb.jdbc.MariaDbDataSource.getConnection(MariaDbDataSource.java:288)
… 16 common frames omitted
Caused by: java.sql.SQLException: Could not connect: Access denied for user ‘dremio’@‘10.123.94.83’ (using password: YES)
at org.mariadb.jdbc.internal.com.send.AbstractAuthSwitchSendResponsePacket.handleResultPacket(AbstractAuthSwitchSendResponsePacket.java:100)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.authentication(AbstractConnectProtocol.java:919)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.handleConnectionPhases(AbstractConnectProtocol.java:850)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connect(AbstractConnectProtocol.java:507)
at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1195)
… 19 common frames omitted
2022-01-10 14:51:56,295 [start-test] WARN c.d.e.catalog.ManagedStoragePlugin - Error starting new source: test
java.lang.Exception: Unavailable:
at com.dremio.exec.catalog.ManagedStoragePlugin.lambda$newStartSupplier$1(ManagedStoragePlugin.java:529)
at com.dremio.exec.catalog.ManagedStoragePlugin.lambda$nameSupplier$3(ManagedStoragePlugin.java:591)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1604)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2022-01-10 14:51:56,295 [start-test] INFO c.d.s.s.LocalSchedulerService - Cancelling task metadata-refresh-wakeup-test
2022-01-10 14:51:56,296 [qtp732929236-153] ERROR c.d.e.store.jdbc.JdbcStoragePlugin - JDBC source test has not been started.
2022-01-10 14:53:05,161 [UserServer-1] INFO c.d.sabot.rpc.user.UserRPCServer - [USER]: Channel closed /10.123.94.83:31010 <–> /10.123.17.193:34984 (user client)
2022-01-10 14:53:05,162 [UserServer-1] WARN c.d.e.s.o.SessionOptionManagerFactoryImpl - Could not find session with sessionId eedea26c-46d8-4cbf-86fd-343d59c3847f.
2022-01-10 14:53:05,162 [UserServer-1] INFO com.dremio.ConnectionLog - [eedea26c-46d8-4cbf-86fd-343d59c3847f] Connection Closed

2

@dbrys The error is telling us that MySQL is denying access to dremio@

See MySQL: Access denied for user 'test'@'localhost' (using password: YES) except root user - Stack Overflow

3

no, that’s not the issue: the user ‘dremio’ has full access on all tables from all databases in MySQL. Tested with dbeaver and jdbc driver. However, Dremio doesn’t translate the ip address of the MySQL host : it points to the ip address of the dremio server itself (‘dremio’@‘10.123.94.83’).
The ip address of the MySQL host was provided in dns format and ip format. Dremio never tries to connect to the correct ip address. Only when we replace Host with localhost we can see in the logs Dremio trying to connect to dremio@localhost. This also fails of course since MySQL is running on another server.
And we can connect to that server from all other locations and tools in the organization.

4

Also we can telnet to that server and port 3306.

Some more information: we’re running Dremio 12. MariaDB driver 2.3.

kind regards

Dirk

5

@dbrys

I think this is an error from mysql saying that user “dremio” from the host “10.123.94.83” (i.e. your dremio server) does not have access to the mysql server.

Check out mysql.user table to see if that username@host has privileges to access your db.

6

yes it has: the user dremio has access and it’s host parameter = %
As said we can connect with this user from other locations as well. This means that also the binding of the host is ok for each connection. There are no restrictions.

7

@dbrys This error message is printing the Dremio IP because it says that the dremio user coming from the dremio IP is not having access to MySQL. Again this error is coming from MySQL and not Dremio. I understand you have connected from other tools, let’s try this

  • Log on to 10.123.94.83
  • install mysql client
  • connect to mysql using user as “dremio”

Does this work?

Also do you see this IP when you run select Host, User from mysql.user;