Incorrect reading of SQLite database fields

Hi there,

I’ve set up using docker, have added an SQLite database with no error, however when I go to browse or ask questions about the data, I see the error Distinct, non-empty sequence of Field clauses (see details below). While I can access the data through a raw SQL query, no fields are detected in the interface (described below). However, a total count of rows is available through the data x-ray.

This is a very confusing situation. If anyone knows of a solution, or advice on how to debug this, it would be deeply appreciated :slight_smile:

The SQLite database I’m using is downloadable here https://morph.io/austccr/australian_enterprise_bargaining_agreements (you need to log in with a GitHub account and then select "Download SQLite Database 99.7 MB).

Error when selecting ‘Browse Data’

Start the docker container and add the database.

Select ‘Browse the data’, select the database.

This error appears:

There was a problem with your question

Most of the time this is caused by an invalid selection or bad input value. Double check your inputs and retry your query.

Show error details

Here’s the full error message

Distinct, non-empty sequence of Field clauses

Data accessible via raw query, however fields not detected

When making a raw SQL query SELECT * FROM data LIMIT 10; I can see the column headers in the resulting table.

However, when I select ‘Learn about your data’ and open the database, there are no fields detected.

A total count of rows is available in ‘x-ray this table’

Using the ‘x-ray this table’ feature, I can get a count of the total number of rows

Here’s a quick look at your Data table

Summary

162,069 Total Data

Full logs when running sync

[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:32+11:00 INFO metabase.driver Initializing driver :sqlite...
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:32+11:00 INFO metabase.sync.util STARTING: Sync metadata for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:32+11:00 INFO metabase.plugins.classloader Added URL file:/plugins/sqlite.metabase-driver.jar to classpath
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:32+11:00 DEBUG metabase.plugins.init-steps Loading plugin namespace metabase.driver.sqlite...
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:32+11:00 INFO metabase.driver Registered driver :sqlite (parents: [:sql-jdbc]) 🚚
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 DEBUG metabase.plugins.jdbc-proxy Registering JDBC proxy driver for class org.sqlite.JDBC...
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util STARTING: step 'sync-timezone' for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util FINISHED: step 'sync-timezone' for sqlite Database 2 'fairwork' (143.2 ms)
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util STARTING: step 'sync-tables' for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util FINISHED: step 'sync-tables' for sqlite Database 2 'fairwork' (24.0 ms)
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util STARTING: step 'sync-fields' for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.driver.sql-jdbc.sync Don't know how to map column type '' to a Field base_type, falling back to :type/*.
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 ERROR metabase.sync.util Error syncing Fields for Table 'Table 5 'data'': Output of table-metadata does not match schema: 

	   {:fields #{{:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))} {:database-type (not ("Non-blank string" ""))}}}  


("--> sync.fetch_metadata$fn__50131$table_metadata__50136.invoke(fetch_metadata.clj:15)"
 "sync.sync_metadata.fields.fetch_metadata$fn__50453$db_metadata__50458$fn__50459.invoke(fetch_metadata.clj:82)"
 "sync.sync_metadata.fields.fetch_metadata$fn__50453$db_metadata__50458.invoke(fetch_metadata.clj:78)"
 "sync.sync_metadata.fields$fn__50957$sync_fields_for_table_BANG___50966$fn__50972$fn__50974.invoke(fields.clj:91)"
 "sync.util$do_with_error_handling.invokeStatic(util.clj:149)"
 "sync.util$do_with_error_handling.invoke(util.clj:144)"
 "sync.sync_metadata.fields$fn__50957$sync_fields_for_table_BANG___50966$fn__50972.invoke(fields.clj:90)"
 "sync.sync_metadata.fields$fn__50957$sync_fields_for_table_BANG___50966.invoke(fields.clj:83)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008$iter__51009__51013$fn__51014$fn__51015.invoke(fields.clj:117)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008$iter__51009__51013$fn__51014.invoke(fields.clj:116)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008.invoke(fields.clj:116)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007.invoke(fields.clj:111)"
 "sync.util$fn__41836$run_step_with_metadata__41841$fn__41845$fn__41847.invoke(util.clj:356)"
 "sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:104)"
 "sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:98)"
 "sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:121)"
 "sync.util$with_start_and_finish_debug_logging.invoke(util.clj:118)"
 "sync.util$fn__41836$run_step_with_metadata__41841$fn__41845.invoke(util.clj:353)"
 "sync.util$fn__41836$run_step_with_metadata__41841.invoke(util.clj:348)"
 "sync.util$fn__42028$run_sync_operation__42033$fn__42034$fn__42035.invoke(util.clj:441)"
 "sync.util$fn__42028$run_sync_operation__42033$fn__42034.invoke(util.clj:441)"
 "sync.util$fn__42028$run_sync_operation__42033.invoke(util.clj:435)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694$fn__51695$fn__51696.invoke(sync_metadata.clj:51)"
 "sync.util$do_with_error_handling.invokeStatic(util.clj:149)"
 "sync.util$do_with_error_handling.invoke(util.clj:144)"
 "sync.util$do_with_error_handling.invokeStatic(util.clj:147)"
 "sync.util$do_with_error_handling.invoke(util.clj:144)"
 "driver$fn__18927.invokeStatic(driver.clj:673)"
 "driver$fn__18927.invoke(driver.clj:673)"
 "sync.util$sync_in_context$fn__41742.invoke(util.clj:140)"
 "sync.util$with_db_logging_disabled$fn__41739.invoke(util.clj:131)"
 "sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:104)"
 "sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:98)"
 "sync.util$with_start_and_finish_logging$fn__41728.invoke(util.clj:116)"
 "sync.util$with_sync_events$fn__41723.invoke(util.clj:90)"
 "sync.util$with_duplicate_ops_prevented$fn__41714.invoke(util.clj:69)"
 "sync.util$do_sync_operation.invokeStatic(util.clj:168)"
 "sync.util$do_sync_operation.invoke(util.clj:165)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694$fn__51695.invoke(sync_metadata.clj:50)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694.invoke(sync_metadata.clj:47)"
 "api.database$fn__62047$fn__62049.invoke(database.clj:521)")

[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 ERROR metabase.sync.util Error running sync step: Output of sync-fields-for-table! does not match schema: 

	   (not (map? a-clojure.lang.ExceptionInfo))  


("--> sync.sync_metadata.fields$fn__50957$sync_fields_for_table_BANG___50966.invoke(fields.clj:83)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008$iter__51009__51013$fn__51014$fn__51015.invoke(fields.clj:117)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008$iter__51009__51013$fn__51014.invoke(fields.clj:116)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007$fn__51008.invoke(fields.clj:116)"
 "sync.sync_metadata.fields$fn__51002$sync_fields_BANG___51007.invoke(fields.clj:111)"
 "sync.util$fn__41836$run_step_with_metadata__41841$fn__41845$fn__41847.invoke(util.clj:356)"
 "sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:104)"
 "sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:98)"
 "sync.util$with_start_and_finish_debug_logging.invokeStatic(util.clj:121)"
 "sync.util$with_start_and_finish_debug_logging.invoke(util.clj:118)"
 "sync.util$fn__41836$run_step_with_metadata__41841$fn__41845.invoke(util.clj:353)"
 "sync.util$fn__41836$run_step_with_metadata__41841.invoke(util.clj:348)"
 "sync.util$fn__42028$run_sync_operation__42033$fn__42034$fn__42035.invoke(util.clj:441)"
 "sync.util$fn__42028$run_sync_operation__42033$fn__42034.invoke(util.clj:441)"
 "sync.util$fn__42028$run_sync_operation__42033.invoke(util.clj:435)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694$fn__51695$fn__51696.invoke(sync_metadata.clj:51)"
 "sync.util$do_with_error_handling.invokeStatic(util.clj:149)"
 "sync.util$do_with_error_handling.invoke(util.clj:144)"
 "sync.util$do_with_error_handling.invokeStatic(util.clj:147)"
 "sync.util$do_with_error_handling.invoke(util.clj:144)"
 "driver$fn__18927.invokeStatic(driver.clj:673)"
 "driver$fn__18927.invoke(driver.clj:673)"
 "sync.util$sync_in_context$fn__41742.invoke(util.clj:140)"
 "sync.util$with_db_logging_disabled$fn__41739.invoke(util.clj:131)"
 "sync.util$with_start_and_finish_logging_STAR_.invokeStatic(util.clj:104)"
 "sync.util$with_start_and_finish_logging_STAR_.invoke(util.clj:98)"
 "sync.util$with_start_and_finish_logging$fn__41728.invoke(util.clj:116)"
 "sync.util$with_sync_events$fn__41723.invoke(util.clj:90)"
 "sync.util$with_duplicate_ops_prevented$fn__41714.invoke(util.clj:69)"
 "sync.util$do_sync_operation.invokeStatic(util.clj:168)"
 "sync.util$do_sync_operation.invoke(util.clj:165)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694$fn__51695.invoke(sync_metadata.clj:50)"
 "sync.sync_metadata$fn__51689$sync_db_metadata_BANG___51694.invoke(sync_metadata.clj:47)"
 "api.database$fn__62047$fn__62049.invoke(database.clj:521)")

[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util FINISHED: Sync metadata for sqlite Database 2 'fairwork' (964.5 ms)
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util STARTING: Analyze data for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 INFO metabase.sync.util STARTING: step 'fingerprint-fields' for sqlite Database 2 'fairwork'
[1e8dd49c-f227-47e5-a129-cb5877716702] 2019-10-16T10:31:33+11:00 WARN metabase.util.date Possible timezone conflict found on database fairwork. JVM timezone is Australia/Sydney and detected database timezone is Etc/GMT.

Similar issues

This seems to be a similar error to the following issues/threads:

Hi @equivalentideas

Great detailed report - just missing the Metabase version, but I’m guessing 0.33.4 :wink:

Besides the issue you’re referencing, I don’t have a magic solution, but I can reproduce using the small dataset (free download) in this issue, so I have a feeling that they might be related:
https://github.com/metabase/metabase/issues/8630