SQLite no tables found

Hi,

I setup Metabase with Docker image on AWS and I want to connect to a SQLite database that I saved in the /tmp folder. It seems to work, but I cannot see any tables. However when I ssh into the server I can access the database and see the tables.

I already tried to sync, rescan field values and refresh the browser. Nothing seems to work.

Metabase version: 0.35.3
Path to connect to database: /tmp/output_2020.sqlite

Here are the logs when I sync the schema:

[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 DEBUG metabase.middleware.log POST /api/database/10/sync_schema 200 3.6 ms (1 DB calls) App DB connections: 1/4 Jetty threads: 3/50 (4 idle, 0 queued) (46 total active threads) Queries in flight: 0 (0 queued)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'sync-timezone' for sqlite Database 10 'Logs' (45.3 ms)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'sync-tables' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'sync-tables' for sqlite Database 10 'Logs' (2.3 ms)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'sync-fields' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'sync-fields' for sqlite Database 10 'Logs' (1.2 ms)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'sync-fks' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'sync-fks' for sqlite Database 10 'Logs' (1.2 ms)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'sync-metabase-metadata' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'sync-metabase-metadata' for sqlite Database 10 'Logs' (801.5 µs)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: Sync metadata for sqlite Database 10 'Logs' (70.6 ms)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: Analyze data for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'fingerprint-fields' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'classify-fields' for sqlite Database 10 'Logs' (284.2 µs)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'fingerprint-fields' for sqlite Database 10 'Logs' (368.9 µs)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'classify-fields' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: step 'classify-tables' for sqlite Database 10 'Logs' (293.2 µs)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util STARTING: step 'classify-tables' for sqlite Database 10 'Logs'
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T09:41:37+02:00 INFO metabase.sync.util FINISHED: Analyze data for sqlite Database 10 'Logs' (10.1 ms)

Hi @Franzi
If you make a Native/SQL query, does that work? Like SELECT * FROM table of the “Logs” (ID 10) database?
Do you see any tables in Admin > Data Model > Logs ?

Hi, thanks for the quick response. I get “[SQLITE_ERROR] SQL error or missing database (no such table: localyze_log)”

this is the log output:

[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T14:25:44+02:00 DEBUG metabase.middleware.log POST /api/dataset 202 [ASYNC: completed] 719.8 ms (4 DB calls) App DB connections: 0/4 Jetty threads: 2/50 (5 idle, 0 queued) (52 total active threads) Queries in flight: 1 (0 queued)
[fb3701f4-837e-45ea-be35-acfb72ca9a2e] 2020-05-02T14:26:01+02:00 ERROR metabase.query-processor.middleware.catch-exceptions Error processing query: null
{:database_id 10,
:started_at (t/zoned-date-time "2020-05-02T12:26:01.254063Z[GMT]"),
:state nil,
:json_query {:type "native", :native {:query " SELECT * FROM localyze_log", :template-tags {}}, :database 10, :parameters [], :middleware {:add-default-userland-constraints? true}},
:status :failed,
:class org.sqlite.SQLiteException,
:stacktrace
["org.sqlite.core.DB.newSQLException(DB.java:941)"
"org.sqlite.core.DB.newSQLException(DB.java:953)"
"org.sqlite.core.DB.throwex(DB.java:918)"
"org.sqlite.core.NativeDB.prepare_utf8(Native Method)"
"org.sqlite.core.NativeDB.prepare(NativeDB.java:134)"
"org.sqlite.core.DB.prepare(DB.java:257)"
"org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:47)"
"org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)"
"org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)"
"org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:35)"
"com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:267)"
"--> driver.sqlite$eval647$fn__648.invoke(sqlite.clj:329)"
"driver.sql_jdbc.execute$prepared_statement_STAR_.invokeStatic(execute.clj:257)"
"driver.sql_jdbc.execute$prepared_statement_STAR_.invoke(execute.clj:254)"
"driver.sql_jdbc.execute$execute_reducible_query.invokeStatic(execute.clj:387)"
"driver.sql_jdbc.execute$execute_reducible_query.invoke(execute.clj:377)"
"driver.sql_jdbc$fn__72711.invokeStatic(sql_jdbc.clj:50)"
"driver.sql_jdbc$fn__72711.invoke(sql_jdbc.clj:48)"
"query_processor.context$executef.invokeStatic(context.clj:59)"
"query_processor.context$executef.invoke(context.clj:48)"
"query_processor.context.default$default_runf.invokeStatic(default.clj:69)"
"query_processor.context.default$default_runf.invoke(default.clj:67)"
"query_processor.context$runf.invokeStatic(context.clj:45)"
"query_processor.context$runf.invoke(context.clj:39)"
"query_processor.reducible$pivot.invokeStatic(reducible.clj:34)"
"query_processor.reducible$pivot.invoke(reducible.clj:31)"
"query_processor.middleware.mbql_to_native$mbql__GT_native$fn__43003.invoke(mbql_to_native.clj:26)"
"query_processor.middleware.check_features$check_features$fn__42317.invoke(check_features.clj:42)"
"query_processor.middleware.optimize_datetime_filters$optimize_datetime_filters$fn__43168.invoke(optimize_datetime_filters.clj:133)"
"query_processor.middleware.wrap_value_literals$wrap_value_literals$fn__47065.invoke(wrap_value_literals.clj:137)"
"query_processor.middleware.annotate$add_column_info$fn__40946.invoke(annotate.clj:577)"
"query_processor.middleware.permissions$check_query_permissions$fn__42192.invoke(permissions.clj:64)"
"query_processor.middleware.pre_alias_aggregations$pre_alias_aggregations$fn__43667.invoke(pre_alias_aggregations.clj:40)"
"query_processor.middleware.cumulative_aggregations$handle_cumulative_aggregations$fn__42390.invoke(cumulative_aggregations.clj:61)"
"query_processor.middleware.resolve_joins$resolve_joins$fn__44199.invoke(resolve_joins.clj:183)"
"query_processor.middleware.add_implicit_joins$add_implicit_joins$fn__39133.invoke(add_implicit_joins.clj:245)"
"query_processor.middleware.limit$limit$fn__42989.invoke(limit.clj:38)"
"query_processor.middleware.format_rows$format_rows$fn__42970.invoke(format_rows.clj:81)"
"query_processor.middleware.desugar$desugar$fn__42456.invoke(desugar.clj:22)"
"query_processor.middleware.binning$update_binning_strategy$fn__41490.invoke(binning.clj:229)"
"query_processor.middleware.resolve_fields$resolve_fields$fn__41998.invoke(resolve_fields.clj:24)"
"query_processor.middleware.add_dimension_projections$add_remapping$fn__38669.invoke(add_dimension_projections.clj:270)"
"query_processor.middleware.add_implicit_clauses$add_implicit_clauses$fn__38889.invoke(add_implicit_clauses.clj:147)"
"query_processor.middleware.add_source_metadata$add_source_metadata_for_source_queries$fn__39282.invoke(add_source_metadata.clj:105)"
"query_processor.middleware.reconcile_breakout_and_order_by_bucketing$reconcile_breakout_and_order_by_bucketing$fn__43864.invoke(reconcile_breakout_and_order_by_bucketing.clj:98)"
"query_processor.middleware.auto_bucket_datetimes$auto_bucket_datetimes$fn__41131.invoke(auto_bucket_datetimes.clj:125)"
"query_processor.middleware.resolve_source_table$resolve_source_tables$fn__42045.invoke(resolve_source_table.clj:46)"
"query_processor.middleware.parameters$substitute_parameters$fn__43649.invoke(parameters.clj:97)"
"query_processor.middleware.resolve_referenced$resolve_referenced_card_resources$fn__42097.invoke(resolve_referenced.clj:80)"
"query_processor.middleware.expand_macros$expand_macros$fn__42712.invoke(expand_macros.clj:158)"
"query_processor.middleware.add_timezone_info$add_timezone_info$fn__39313.invoke(add_timezone_info.clj:15)"
"query_processor.middleware.splice_params_in_response$splice_params_in_response$fn__46949.invoke(splice_params_in_response.clj:32)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__43875$fn__43879.invoke(resolve_database_and_driver.clj:33)"
"driver$do_with_driver.invokeStatic(driver.clj:61)"
"driver$do_with_driver.invoke(driver.clj:57)"
"query_processor.middleware.resolve_database_and_driver$resolve_database_and_driver$fn__43875.invoke(resolve_database_and_driver.clj:27)"
"query_processor.middleware.fetch_source_query$resolve_card_id_source_tables$fn__42918.invoke(fetch_source_query.clj:243)"
"query_processor.middleware.store$initialize_store$fn__46958$fn__46959.invoke(store.clj:11)"
"query_processor.store$do_with_store.invokeStatic(store.clj:46)"
"query_processor.store$do_with_store.invoke(store.clj:40)"
"query_processor.middleware.store$initialize_store$fn__46958.invoke(store.clj:10)"
"query_processor.middleware.cache$maybe_return_cached_results$fn__41974.invoke(cache.clj:208)"
"query_processor.middleware.validate$validate_query$fn__46967.invoke(validate.clj:10)"
"query_processor.middleware.normalize_query$normalize$fn__43016.invoke(normalize_query.clj:22)"
"query_processor.middleware.add_rows_truncated$add_rows_truncated$fn__39151.invoke(add_rows_truncated.clj:36)"
"query_processor.middleware.results_metadata$record_and_return_metadata_BANG_$fn__46934.invoke(results_metadata.clj:128)"
"query_processor.middleware.constraints$add_default_userland_constraints$fn__42333.invoke(constraints.clj:42)"
"query_processor.middleware.process_userland_query$process_userland_query$fn__43738.invoke(process_userland_query.clj:136)"
"query_processor.middleware.catch_exceptions$catch_exceptions$fn__42276.invoke(catch_exceptions.clj:174)"
"query_processor.reducible$async_qp$qp_STAR___37952$thunk__37953.invoke(reducible.clj:101)"
"query_processor.reducible$async_qp$qp_STAR___37952.invoke(reducible.clj:107)"
"query_processor.reducible$sync_qp$qp_STAR___37961$fn__37964.invoke(reducible.clj:133)"
"query_processor.reducible$sync_qp$qp_STAR___37961.invoke(reducible.clj:132)"
"query_processor$process_userland_query.invokeStatic(query_processor.clj:218)"
"query_processor$process_userland_query.doInvoke(query_processor.clj:214)"
"query_processor$fn__47133$process_query_and_save_execution_BANG___47142$fn__47145.invoke(query_processor.clj:233)"
"query_processor$fn__47133$process_query_and_save_execution_BANG___47142.invoke(query_processor.clj:225)"
"query_processor$fn__47177$process_query_and_save_with_max_results_constraints_BANG___47186$fn__47189.invoke(query_processor.clj:245)"
"query_processor$fn__47177$process_query_and_save_with_max_results_constraints_BANG___47186.invoke(query_processor.clj:238)"
"api.dataset$fn__50475$fn__50478.invoke(dataset.clj:52)"
"query_processor.streaming$streaming_response_STAR_$fn__33990$fn__33991.invoke(streaming.clj:73)"
"query_processor.streaming$streaming_response_STAR_$fn__33990.invoke(streaming.clj:72)"
"async.streaming_response$do_f_STAR_.invokeStatic(streaming_response.clj:66)"
"async.streaming_response$do_f_STAR_.invoke(streaming_response.clj:64)"
"async.streaming_response$do_f_async$fn__23079.invoke(streaming_response.clj:85)"],
:context :ad-hoc,
:error "[SQLITE_ERROR] SQL error or missing database (no such table: localyze_log)",
:row_count 0,
:running_time 0,
:data {:rows [], :cols []}}

Executing the same query on the server, I am able to see the data. Here is what I am able to see

@Franzi Two questions:

Are you sure you’re querying the correct database?

And the Metabase Docker image doesn’t have sqlite3 installed, so either you’re using a different image or you’ve added it.

Remember that there’s no such thing as “same server”, when it comes to Docker - it’s containers and host. And containers don’t have access to anything on the other contains or the host unless that’s configured.

Hi @flamber

Thanks for the helpful infos!

Yes, I checked it several times.

Are there any “best practices” to add a SQLite db to Metabase? I also have a PostgresDB and MongoDB connected and its working smoothly.

@Franzi
You didn’t answer the question about which image you’re using, so I’m guessing you’re using your own and have added the Metabase JAR manually, and then added the SQLite file to the tmp directory.
There’s no “best practice” - it’s just a path to a file.
I’ve never seen that before, so I’m guessing it’s not the correct location.
Try to see if you can setup Chinook sample database in the same way, in similar location:
https://www.sqlitetutorial.net/sqlite-sample-database/

@flamber

sorry, missed that one. I just followed the official guide (https://www.metabase.com/docs/v0.35.3/operations-guide/running-metabase-on-elastic-beanstalk.html#running-metabase-on-aws-elastic-beanstalk), so I guess I am using the official Metabase Docker image from Dockerhub

@Franzi Okay, then you manually installed sqlite3 on that container - or you’re using different containers, which is why you’re not querying the correct database (hence; are you sure you’re querying the right database?)
Or you’re doing other things, which makes it difficult to troubleshoot your problem.