Failed to setup JDBC connection

1

While trying to create VDS from another PDS I got that error "The JDBC storage plugin failed while trying setup the SQL query. "

The case was as following:

  • I connected to mysql DB as a datasource
  • The connection DB user has a limited access to a fields in the DB table “employer”.
  • I created a reflection on that table by selecting a specific columns not all columns and everything was ok.
  • I created another virtual dataset in another virtual datasource. This VDS select a specific columns from the physical Dataset.
  • When I run select * from VDS.employer I got the mentioned error.
  • It seems like dremio skipped the created reflection on the physical dataset and ignored the selected columns in the query and went directly to the database with Select * from employer query

here is the thrown exception.

DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query.

sql SELECT *
FROM forasna_production.employer
plugin Forasna DB
SqlOperatorImpl JDBC_SUB_SCAN
Location 1:0:6
SqlOperatorImpl JDBC_SUB_SCAN
Location 1:0:6
Fragment 1:0

[Error Id: 8211deca-e7ab-4468-9a23-9f18547bd47c on dremio-wo1:-1]

(java.sql.SQLSyntaxErrorException) (conn=45759) SELECT command denied to user ‘data_analysis_limited’@‘172.31.34.186’ for table ‘employer’
org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get():236
org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException():165
org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue():238
org.mariadb.jdbc.MariaDbStatement.executeInternal():356
org.mariadb.jdbc.MariaDbStatement.executeQuery():530
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():200
com.dremio.exec.store.CoercionReader.setup():118
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():214
com.dremio.sabot.op.scan.ScanOperator.setupReader():187
com.dremio.sabot.op.scan.ScanOperator.setup():173
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():563
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():533
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.Pipeline.setup():68
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():372
com.dremio.sabot.exec.fragment.FragmentExecutor.run():258
com.dremio.sabot.exec.fragment.FragmentExecutor.access$1200():87
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():658
com.dremio.sabot.task.AsyncTaskWrapper.run():104
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():226
com.dremio.sabot.task.slicing.SlicingThread.run():156
Caused By (java.sql.SQLException) SELECT command denied to user ‘data_analysis_limited’@‘172.31.34.186’ for table ‘employer’
Query is: SELECT *
FROM forasna_production.employer
java thread: e1 - 2247ca2d-699f-5226-4a31-e4c92d7e9b00:frag:1:0
org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery():126
org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery():222
org.mariadb.jdbc.MariaDbStatement.executeInternal():350
org.mariadb.jdbc.MariaDbStatement.executeQuery():530
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():200
com.dremio.exec.store.CoercionReader.setup():118
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():214
com.dremio.sabot.op.scan.ScanOperator.setupReader():187
com.dremio.sabot.op.scan.ScanOperator.setup():173
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():563
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():533
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.Pipeline.setup():68
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():372
com.dremio.sabot.exec.fragment.FragmentExecutor.run():258
com.dremio.sabot.exec.fragment.FragmentExecutor.access$1200():87
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():658
com.dremio.sabot.task.AsyncTaskWrapper.run():104
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():226
com.dremio.sabot.task.slicing.SlicingThread.run():156

2

@elshafey

It looks like the user data_analysis_limited coming from 172.31.34.186 does not have access on table employer.

https://dev.mysql.com/doc/refman/8.0/en/grant.html

Thanks
@balaji.ramaswamy

3

As i mentioned that is right, data_analysis_limited has limited access to employer table. But my question is, as I select partial fields not all fields, why dremio convert it to select * on the data source level. My expectation is to add the selected fields to the select query.

4

@elshafey

Can you please send me the profile?

Thanks
@balaji.ramaswamy