When I add a SQL server as datasource in Dremio Community edition Windows, I can only see 4 out of the many databases after establishing the connection to the server. Even when I explicitly give the databasename, still get the same result.
This does not occur with other tools such as e.g. dbvisualizer. Then I see all my databases correctly using the same connection configuration over JDBC.
Could you check server.log/server.out logs to see if there were errors that arose?
I get the following error in server.log, it seems the schema cannot be fetched, including the schema of the database I requested:
2018-07-24 15:46:46,804 [metadata-refresh-Order] WARN c.d.e.store.jdbc.JdbcSchemaFetcher - Failed to fetch schema for [DBA-ADMIN, DBA-ADMIN, model, model, (... many databases), **SCORD**, (... more databases) ].I am assuming it is likely a permission error. The tables need SELECT privileges in order for Dremio to get the metadata. Can you compare/contrast the user + table permissions on ones that are working properly and ones that are missing?
Hello, I doublechecked privileges in SQL Server, all seems correctly configured.
Also, from Dbvisualizer I use the same credentials and don’t have this error. The user has select privileges.
The user also has rights to read the INFORMATION_SCHEMA to get e.g. the list of tables etc.
I also tried from within the Dremio query panel on my ‘Order’ dataset:
SELECT *
FROM “Order”.“SCORD”.“Order”.“OrderItem”
I get nicely sampled results, and the database now appears (only with one table OrderItem though).
Very strange behaviour.
Hello,
I have just updated to the newest version of Dremio (2.1.4), on Windows. The issue with the JDBC connection to read the full MS SQL server schema still persists.
Other Java applications like dbvisualizer do not have problems reading the entire information schema from the SQL Server.
The error message logged remains the same.
Any other clues how this can be fixed?
Can you share your full server.log please? @jduong any insight here?
@anthony you can find the full server.log attached here:
server.log.zip (6,5 KB)
Hi @orlandob,
Is the name of the first missing database
**SCORD**
? Do you have any database/schema names with special character sequences? Do these all hold regular tables/views only?
Hello @jduong
No this is not the case. In fact the order of the databases that are missing corresponds with the log file errors for “failed to fetch schema for…”.
e.g. BASE_AG4 is skipped before master, then model is skipped.
The SCORD databases are indeed missing, but are only a few of missing databases. Also SCMAT-NUM is being skipped even before the SCORD.
In SCMAT-NUM there are no special tables. I added the schema in export.zip (3,5 KB)
For BASE_AG4, it could, which is also skipped, could be expected, as in dbvisualizer (using the same mssql jdbc driver) the database is listed, but its schema cannot be read: because it is part of an availability group. However, it would be nice that Dremio can also notify when this is the case.
In DBVisualizer
An error occurred while performing the operation:
The target database, ‘BASE_AG4’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
For SCMAT-NUM I don’t get any errors in DBVisualizer.
@anthony @jduong any thoughts here?
I just did the test again with the new release 3.0. The issue persists.
Since the new update 3.06,
I also get the following error message:
I think somethings is going wrong with the ‘-’ still:
2019-01-07 09:00:32,335 [metadata-refresh-Order] WARN c.d.e.store.jdbc.JdbcSchemaFetcher - Took longer than 5 seconds to query row count for TEST-TB.Order.Item, Using default value of 1000000000.
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '-'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:865) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:762) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:661) ~[microsoft-sqljdbc41-4.2.6420.100.jar:na]
at com.dremio.exec.store.jdbc.JdbcSchemaFetcher.getRowCount(JdbcSchemaFetcher.java:409) [dremio-extra-plugin-jdbc-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.store.jdbc.JdbcSchemaFetcher.getTableDetails(JdbcSchemaFetcher.java:372) [dremio-extra-plugin-jdbc-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.store.jdbc.JdbcTableBuilder.buildIfNeeded(JdbcTableBuilder.java:103) [dremio-extra-plugin-jdbc-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.store.jdbc.JdbcTableBuilder.getDataset(JdbcTableBuilder.java:58) [dremio-extra-plugin-jdbc-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.store.jdbc.JdbcStoragePlugin.checkReadSignature(JdbcStoragePlugin.java:255) [dremio-extra-plugin-jdbc-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.catalog.SourceMetadataManager.refreshFull(SourceMetadataManager.java:384) [dremio-sabot-kernel-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.catalog.SourceMetadataManager.doNextRefresh(SourceMetadataManager.java:265) [dremio-sabot-kernel-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.catalog.SourceMetadataManager.access$200(SourceMetadataManager.java:63) [dremio-sabot-kernel-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.exec.catalog.SourceMetadataManager$RefreshTask.run(SourceMetadataManager.java:587) [dremio-sabot-kernel-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.concurrent.RenamingRunnable.run(RenamingRunnable.java:36) [dremio-common-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.concurrent.SingletonRunnable.run(SingletonRunnable.java:41) [dremio-common-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.concurrent.SafeRunnable.run(SafeRunnable.java:40) [dremio-common-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at com.dremio.service.scheduler.LocalSchedulerService$CancellableTask.run(LocalSchedulerService.java:94) [dremio-services-scheduler-3.0.6-201812082352540436-1f684f9.jar:3.0.6-201812082352540436-1f684f9]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_181]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [na:1.8.0_181]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_181]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [na:1.8.0_181]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_181]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_181]
at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181]Hi @orlandob
seems like the “-” is giving issues. Are you able to select from a different database that has no special characters in it?
Thanks
@balaji.ramaswamy
Yes I am,
and in fact when I add a table from this database with the ‘-’ in the name, I do have access to it, without problems and then it also appears in the list of databases of this server.
see previous post in this thread, where I used a query on a specific table, rather than browsing via the data source.
Hi @orlandob
This is a bug we are working on and not yet fixed. Is the table failing has many rows like 1B+?
Thanks
@balaji.ramaswamy
Not that many rows actually. But it is spread across different availibility groups as I mentioned (and included the schema by your request) here before:
Hello, I would like to inform that with the latest version of Dremio (3.2.8-201907180222520740-08f2b54), we do not experience this problem anymore