search

starburstdata / metabase-driver

Starburst Metabase driver
Apache License 2.0
65 stars 10 forks source link

Metabase can't sync tables #37

Closed cemsbr closed 2 years ago

cemsbr commented 2 years ago

(As reported in https://github.com/metabase/metabase/issues/23665)

Describe the bug Right after connecting Trino to Metabase, "Syncing tables..." has been in the bottom-right corner for days. When browsing data, "This database doesn't have any tables". However, queries work fine.

Logs

2022-07-01 11:25:00,017 INFO sync.util :: STARTING: Sync metadata for starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,018 INFO driver.impl :: Inicializando driver :starburst...
2022-07-01 11:25:00,019 INFO plugins.classloader :: Added URL file:/plugins/starburst-1.0.4.metabase-driver.jar to classpath
2022-07-01 11:25:00,021 DEBUG plugins.init-steps :: Carregando namespace de plugin metabase.driver.starburst...
2022-07-01 11:25:00,035 INFO driver.impl :: Driver abstrato registrado :metabase.driver.sql-jdbc.execute.legacy-impl/use-legacy-classes-for-read-and-set  🚚
2022-07-01 11:25:00,043 INFO driver.impl :: Driver registrado :starburst (pais: [:metabase.driver.sql-jdbc.execute.legacy-impl/use-legacy-classes-for-read-and-set]) 🚚
2022-07-01 11:25:00,176 DEBUG plugins.jdbc-proxy :: Registrando driver de proxy JDBC para io.trino.jdbc.TrinoDriver...
2022-07-01 11:25:00,180 INFO metabase.util :: Carregando Metabase driver :starburst took 159.7 ms
2022-07-01 11:25:00,181 INFO sync.util :: STARTING: passo 'sync-timezone' para starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,181 INFO driver.impl :: Inicializando driver :metabase.driver.sql-jdbc.execute.legacy-impl/use-legacy-classes-for-read-and-set...
2022-07-01 11:25:00,922 INFO sync.util :: FINISHED: passo 'sync-timezone' para starburst Banco de dados 6 'Trino' (740.8 ms)
2022-07-01 11:25:00,923 INFO sync.util :: STARTING: passo 'sync-tables' para starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,938 INFO sync.util :: FINISHED: passo 'sync-tables' para starburst Banco de dados 6 'Trino' (14.6 ms)
2022-07-01 11:25:00,939 INFO sync.util :: STARTING: passo 'sync-fields' para starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,941 INFO sync.util :: FINISHED: passo 'sync-fields' para starburst Banco de dados 6 'Trino' (1.7 ms)
2022-07-01 11:25:00,941 INFO sync.util :: STARTING: passo 'sync-fks' para starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,942 INFO sync.util :: FINISHED: passo 'sync-fks' para starburst Banco de dados 6 'Trino' (1.2 ms)
2022-07-01 11:25:00,943 INFO sync.util :: STARTING: passo 'sync-metabase-metadata' para starburst Banco de dados 6 'Trino'
2022-07-01 11:25:00,961 WARN sync.util :: Error syncing _metabase_metadata table for starburst Banco de dados 6 'Trino'
java.sql.SQLException: Query failed (#20220701_142500_00059_xr5qr): line 1:43: mismatched input '<EOF>'. Expecting: <identifier>
    at io.trino.jdbc.AbstractTrinoResultSet.resultsException(AbstractTrinoResultSet.java:1937)
    at io.trino.jdbc.TrinoResultSet.getColumns(TrinoResultSet.java:285)
    at io.trino.jdbc.TrinoResultSet.create(TrinoResultSet.java:61)
    at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:262)
    at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:240)
    at io.trino.jdbc.TrinoPreparedStatement.<init>(TrinoPreparedStatement.java:119)
    at io.trino.jdbc.TrinoConnection.prepareStatement(TrinoConnection.java:159)
    at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:567)
    at clojure.java.jdbc$prepare_statement.invokeStatic(jdbc.clj:679)
    at clojure.java.jdbc$prepare_statement.invoke(jdbc.clj:626)
    at clojure.java.jdbc$db_query_with_resultset_STAR_.invokeStatic(jdbc.clj:1105)
    at clojure.java.jdbc$db_query_with_resultset_STAR_.invoke(jdbc.clj:1093)
    at clojure.java.jdbc$reducible_query$reify__28497.reduce(jdbc.clj:1357)
    at clojure.core$transduce.invokeStatic(core.clj:6885)
    at clojure.core$into.invokeStatic(core.clj:6901)
    at clojure.core$into.invoke(core.clj:6889)
    at metabase.driver.implementation.sync$all_schemas.invokeStatic(sync.clj:115)
    at metabase.driver.implementation.sync$all_schemas.invoke(sync.clj:110)
    at metabase.driver.implementation.sync$fn__79308.invokeStatic(sync.clj:126)
    at metabase.driver.implementation.sync$fn__79308.invoke(sync.clj:121)
    at clojure.lang.MultiFn.invoke(MultiFn.java:234)
    at metabase.sync.fetch_metadata$fn__64168$db_metadata__64173$fn__64174.invoke(fetch_metadata.clj:14)
    at metabase.sync.fetch_metadata$fn__64168$db_metadata__64173.invoke(fetch_metadata.clj:11)
    at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150$fn__65151$fn__65152.invoke(metabase_metadata.clj:100)
    at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
    at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
    at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150$fn__65151.invoke(metabase_metadata.clj:93)
    at metabase.sync.sync_metadata.metabase_metadata$fn__65145$sync_metabase_metadata_BANG___65150.invoke(metabase_metadata.clj:88)
    at clojure.lang.AFn.applyToHelper(AFn.java:154)
    at clojure.lang.AFn.applyTo(AFn.java:144)
    at clojure.core$apply.invokeStatic(core.clj:669)
    at clojure.core$apply.invoke(core.clj:662)
    at metabase.sync.util$fn__41548$run_step_with_metadata__41553$fn__41557$fn__41559.doInvoke(util.clj:388)
    at clojure.lang.RestFn.invoke(RestFn.java:397)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
    at metabase.sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:117)
    at metabase.sync.util$with_start_and_finish_debug_logging.invoke(util.clj:114)
    at metabase.sync.util$fn__41548$run_step_with_metadata__41553$fn__41557.invoke(util.clj:383)
    at metabase.sync.util$fn__41548$run_step_with_metadata__41553.invoke(util.clj:378)
    at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770$fn__41778.invoke(util.clj:489)
    at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770.invoke(util.clj:487)
    at metabase.sync.util$fn__41764$run_sync_operation__41769.invoke(util.clj:481)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507$fn__66508.invoke(sync_metadata.clj:50)
    at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
    at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
    at clojure.core$partial$fn__5859.invoke(core.clj:2634)
    at metabase.driver$fn__32609.invokeStatic(driver.clj:592)
    at metabase.driver$fn__32609.invoke(driver.clj:592)
    at clojure.lang.MultiFn.invoke(MultiFn.java:239)
    at metabase.sync.util$sync_in_context$fn__41457.invoke(util.clj:136)
    at metabase.sync.util$with_db_logging_disabled$fn__41454.invoke(util.clj:127)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
    at metabase.sync.util$with_start_and_finish_logging$fn__41443.invoke(util.clj:112)
    at metabase.sync.util$with_sync_events$fn__41438.invoke(util.clj:86)
    at metabase.sync.util$with_duplicate_ops_prevented$fn__41429.invoke(util.clj:65)
    at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:179)
    at metabase.sync.util$do_sync_operation.invoke(util.clj:176)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507.invoke(sync_metadata.clj:49)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506.invoke(sync_metadata.clj:46)
    at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invokeStatic(sync_databases.clj:68)
    at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invoke(sync_databases.clj:59)
    at metabase.task.sync_databases.SyncAndAnalyzeDatabase.execute(sync_databases.clj:76)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: io.trino.sql.parser.ParsingException: line 1:43: mismatched input '<EOF>'. Expecting: <identifier>
    at io.trino.sql.parser.ErrorHandler.syntaxError(ErrorHandler.java:109)
    at org.antlr.v4.runtime.ProxyErrorListener.syntaxError(ProxyErrorListener.java:41)
    at org.antlr.v4.runtime.Parser.notifyErrorListeners(Parser.java:544)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportInputMismatch(DefaultErrorStrategy.java:327)
    at org.antlr.v4.runtime.DefaultErrorStrategy.reportError(DefaultErrorStrategy.java:139)
    at io.trino.sql.parser.SqlBaseParser.identifier(SqlBaseParser.java:19791)
    at io.trino.sql.parser.SqlBaseParser.statement(SqlBaseParser.java:4348)
    at io.trino.sql.parser.SqlBaseParser.statement(SqlBaseParser.java:4769)
    at io.trino.sql.parser.SqlBaseParser.singleStatement(SqlBaseParser.java:320)
    at io.trino.sql.parser.SqlParser.invokeParser(SqlParser.java:145)
    at io.trino.sql.parser.SqlParser.createStatement(SqlParser.java:85)
    at io.trino.execution.QueryPreparer.prepareQuery(QueryPreparer.java:55)
    at io.trino.dispatcher.DispatchManager.createQueryInternal(DispatchManager.java:180)
    at io.trino.dispatcher.DispatchManager.lambda$createQuery$0(DispatchManager.java:149)
    at io.trino.$gen.Trino_387____20220629_024228_2.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.antlr.v4.runtime.InputMismatchException
    at org.antlr.v4.runtime.DefaultErrorStrategy.sync(DefaultErrorStrategy.java:270)
    at io.trino.sql.parser.SqlBaseParser.identifier(SqlBaseParser.java:19553)
    ... 12 more
2022-07-01 11:25:00,970 INFO sync.util :: FINISHED: passo 'sync-metabase-metadata' para starburst Banco de dados 6 'Trino' (27.2 ms)
2022-07-01 11:25:00,971 WARN sync.util :: Erro no passo de sincronização: Sync metadata for starburst Banco de dados 6 'Trino'
java.lang.ClassCastException: class java.sql.SQLException cannot be cast to class clojure.lang.Associative (java.sql.SQLException is in module java.sql of loader 'platform'; clojure.lang.Associative is in unnamed module of loader 'app')
    at clojure.lang.RT.assoc(RT.java:827)
    at clojure.core$assoc__5433.invokeStatic(core.clj:195)
    at clojure.core$assoc__5433.doInvoke(core.clj:192)
    at clojure.lang.RestFn.invoke(RestFn.java:573)
    at metabase.sync.util$fn__41548$run_step_with_metadata__41553$fn__41557.invoke(util.clj:394)
    at metabase.sync.util$fn__41548$run_step_with_metadata__41553.invoke(util.clj:378)
    at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770$fn__41778.invoke(util.clj:489)
    at metabase.sync.util$fn__41764$run_sync_operation__41769$fn__41770.invoke(util.clj:487)
    at metabase.sync.util$fn__41764$run_sync_operation__41769.invoke(util.clj:481)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507$fn__66508.invoke(sync_metadata.clj:50)
    at metabase.sync.util$do_with_error_handling.invokeStatic(util.clj:158)
    at metabase.sync.util$do_with_error_handling.invoke(util.clj:151)
    at clojure.core$partial$fn__5859.invoke(core.clj:2634)
    at metabase.driver$fn__32609.invokeStatic(driver.clj:592)
    at metabase.driver$fn__32609.invoke(driver.clj:592)
    at clojure.lang.MultiFn.invoke(MultiFn.java:239)
    at metabase.sync.util$sync_in_context$fn__41457.invoke(util.clj:136)
    at metabase.sync.util$with_db_logging_disabled$fn__41454.invoke(util.clj:127)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:100)
    at metabase.sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:94)
    at metabase.sync.util$with_start_and_finish_logging$fn__41443.invoke(util.clj:112)
    at metabase.sync.util$with_sync_events$fn__41438.invoke(util.clj:86)
    at metabase.sync.util$with_duplicate_ops_prevented$fn__41429.invoke(util.clj:65)
    at metabase.sync.util$do_sync_operation.invokeStatic(util.clj:179)
    at metabase.sync.util$do_sync_operation.invoke(util.clj:176)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506$fn__66507.invoke(sync_metadata.clj:49)
    at metabase.sync.sync_metadata$fn__66501$sync_db_metadata_BANG___66506.invoke(sync_metadata.clj:46)
    at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invokeStatic(sync_databases.clj:68)
    at metabase.task.sync_databases$sync_and_analyze_database_BANG_.invoke(sync_databases.clj:59)
    at metabase.task.sync_databases.SyncAndAnalyzeDatabase.execute(sync_databases.clj:76)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
2022-07-01 11:25:00,971 INFO sync.util :: FINISHED: Sync metadata for starburst Banco de dados 6 'Trino' (954.0 ms)
2022-07-01 11:25:00,972 INFO sync.util :: STARTING: Analyze data for starburst Banco de dados 6 'Trino'

To Reproduce Steps to reproduce the behavior:

  1. Install Trino plugin from Starburst
  2. Add a Trino data source
  3. Notice the Syncing warning in the bottom-right corner that never ends
  4. Click in "Browse data"
  5. Select the Trino database
  6. No tables are displayed.

Expected behavior Database sync process finishes and tables can be browsed.

Screenshots image

Information about your Metabase Installation:

You can get this information by going to Admin -> Troubleshooting.

Severity Only native SQL queries are working. Thus, the majority of our users can't use Metabase.

aalbu commented 2 years ago

@cemsbr, could you identify the failed query the stack trace is referencing (20220701_142500_00059_xr5qr in your example) and post its text? You can find it in the web UI.

andrewdibiasio6 commented 2 years ago

these are not steps to reproduce the issue because we sync trino data sources during our E2E tests. I suggest you look at the trino UI and see if there are any errors there from the sync.

Also, when I see sync issues, it is usually an issue with my configs when creating a data source.

cemsbr commented 2 years ago

Thanks for the tips, @aalbu. The query is PREPARE statement232 FROM SHOW SCHEMAS FROM.

andrewdibiasio6 commented 2 years ago

Hello @cemsbr, unfortunately I cannot reproduce on my side. It looks like there may be some sort of syntax error based on this exception: Caused by: io.trino.sql.parser.ParsingException: line 1:43: mismatched input '<EOF>'. Expecting: <identifier> .

Can you please provide the following info:

cemsbr commented 2 years ago

For the record, when I manually run SHOW CATALOGS, for example, it works. But PREPARE statement232 FROM SHOW SCHEMAS FROM has syntax error and it is expected to fail. Where is the prepare statement written? The requested info follows:

andrewdibiasio6 commented 2 years ago

I think I can reproduce. As a test can, you try to connect to a specific schema, not just the top level catalog. I expect connecting to a schema should work for you on your side. I believe the issue is related to connecting to a catalog directly.

Example, if the trino postgres connector is named PostgresConnector and a database in postgres is named MyDB, add the following to the metabase starburst driver: Catalog: PostgresConnector Schema: MyDB

cemsbr commented 2 years ago

That's it, @andrewdibiasio6! Catalog seems to be a required field, unless you never use it. I wrote one of our catalogs (Postgres) and it started syncing (schema was left empty), but I can't erase the catalog due to the required-field validation.

Shouldn't it support more than one catalog? Afterall, that's the point of Trino. I tried multiple catalogs separated by comma and semicolon, but it didn't work.

andrewdibiasio6 commented 2 years ago

@cemsbr So to clarify, you were able to get this working with what config? Catalog, or Catalog + Schema?

cemsbr commented 2 years ago

It worked only by specifying catalog.

andrewdibiasio6 commented 2 years ago

Great! And final question, so you had entered the wrong catalog when creating DB in metabase, and this resulted in metabase syncing forever. To resolve the issue, were you able to edit the existing DB, or did you delete it and create a new DB? @cemsbr

cemsbr commented 2 years ago

I did more testing.

cemsbr commented 2 years ago

@andrewdibiasio6, I didn't set the wrong catalog at first. It was just left blank. To make it work, I wrote the catalog and clicked in save. No need to create a new DB.

cemsbr commented 2 years ago

But I'm still wondering how can I join tables from different catalogs.

andrewdibiasio6 commented 2 years ago

@cemsbr I appreciate all the info! I believe as of driver version1.0.4 you cannot click save when creating a DB with an empty catalog. Likely there may have been white space. On my side, catalog is required.

But I'm still wondering how can I join tables from different catalogs.

Not possible at the moment, I created this issue, feel free to look it over: https://github.com/starburstdata/metabase-driver/issues/38

What you can do right now is in trino, create the views/tables you want to be available in metabase.

andrewdibiasio6 commented 2 years ago

Resolution

Incorrect catalog resulted in sync failures. Specifying correct catalog allowed syncs to operate as normal.

cemsbr commented 2 years ago

I just tried to add a new DB with empty catalog and I couldn't. Maybe it was fixed in newer versions or there could be a white space, I don't know for sure, but I remember to see a gray "tpch" in the catalog input by the time.

Thank you for creating the other issue. I'll try the suggested workaround to work with different catalogs in a single query.