Superset 3.0 - Example charts throw SQLite Error - no such table: main.FCC 2018 Survey

[iamrohit07]

I have deployed Apache Superset 3.0 using Helm charts. The Helm chart version is superset-0.10.11, and the app version is 3.0.1. I've configured Superset to load example data (loadExamples set to true), but I'm encountering an issue on the dashboard with the error message 'no such table: main.FCC 2018 Survey' while opening one of the example charts. Below is the stack strace from app pods.

2023-11-01 14:46:36,834:WARNING:superset.connectors.sqla.models:Query SELECT country_live AS country_live
FROM main."FCC 2018 Survey"
GROUP BY country_live
LIMIT 1000
OFFSET 0 on schema main failed
Traceback (most recent call last):
  File "/app/superset/connectors/sqla/models.py", line 1214, in query
    df = self.database.get_df(sql, self.schema, mutator=assign_column_label)
  File "/app/superset/models/core.py", line 611, in get_df
    self.db_engine_spec.execute(cursor, sqls[-1])
  File "/app/superset/db_engine_specs/base.py", line 1535, in execute
    raise cls.get_dbapi_mapped_exception(ex) from ex
  File "/app/superset/db_engine_specs/base.py", line 1533, in execute
    cursor.execute(query)
sqlite3.OperationalError: no such table: main.FCC 2018 Survey

How to reproduce the bug

• Install Apache Superset Helm chart with version 0.10.11 and set loadExamples to true
• Login to Superset to app and open FCC New Coder Survey 2018 chart

Expected results

• It should load chart data

Actual results

• It throws error - no such table: main.FCC 2018 Survey

[sergiimk]

I also hit this issue on latest chart 0.10.14.

If you go to Settings / Database connections and do Test connection on examples database you will see an SQLite-related error saying it's not a secure source.

Per #17551 I added the following into my values.yaml:

configOverrides:
  allow_sqlite_for_examples: |
    PREVENT_UNSAFE_DB_CONNECTIONS = False

This made connection test pass, however I was still getting no such table errors.

When I exec into superset pod I see that superset_home/examples.db file has size of zero. I suspect that superset-init-db pod that according to logs seems to be setting up all the example tables is not writing to the same file superset pod is reading from...

[iamrohit07]

@sergiimk had PREVENT_UNSAFE_DB_CONNECTIONS set to False. Where does the superset-init-db pod is writing the data then ? Because, after installing the chart, when I execute a command to enter the application pod, I noticed that the 'examples.db' file itself is not present in the following location - sqlite:////app/superset_home/examples.db. When I tried to access the chart it get's created at that time with zero size. This is bit weired.

[sergiimk]

I think this is some regression in helm setup.

The superset load_examples command that populates examples.db is executed in superset_init.sh script which is only run by init job. Init job spawns superset-init-db which creates examples.db but only in its own file system, so the file dies with it as pod exits.

Workaround: Manually exec into superset pod and run superset load_examples inside it to populate examples.db.

I think the proper solution here is not to use SQLite in k8s setup at all and populate examples into Postgres (by reusing existing DB or creating a new one), but this is a bit beyond what I feel comfortable contributing.

[sebastianliebscher]

I could replicate this issue with version 3.0.1 and on master using Docker. The Docker stack uses Postgres as meta database, so I don't think the issue relates to the Helm chart or SQLite.

Steps to reproduce:

• git clone and checkout master / 3.0.1
• TAG=3.0.1 docker-compose -f docker-compose-non-dev.yml up
• navigate to Datasets / FCC 2018 Survey and click the Samples tab
• get the error:

Error: column "rsrc_pluralsight" does not exist
LINE 122:        rsrc_pluralsight AS rsrc_pluralsight,
                 ^
HINT:  Perhaps you meant to reference the column "FCC 2018 Survey.rsrc_pluralsght".

Looks like a typo somewhere? Unfortunately, a full-text search for rsrc_pluralsght in the repo yields nothing.

pinging to get some eyes on the issue @rusackas, @sfirke, @john-bodley

[john-bodley]

@sebastianliebscher I think there's a typo from your full-text search, i.e., you're missing an i from sight:

$ git checkout 3.0
Switched to branch '3.0'
Your branch is up to date with 'apache/3.0'.
$ git grep rsrc_pluralsight
superset/examples/configs/datasets/examples/FCC_2018_Survey.yaml:- column_name: rsrc_pluralsight

[john-bodley]

@craig-rueda do you have any insights with regards to the Helm charts issue?

[sebastianliebscher]

@john-bodley I intentionally full-text searched for rsrc_pluralsght (without the i) because that's what an unchanged/unconfigured Superset 3.0.1 / master out-of-the-box errors out when navigating to the FCC 2018 Survey dataset. I literally just git cloned and docker composed up Superset and got this error message with the typo.

[iamrohit07]

@john-bodley @craig-rueda @sebastianliebscher Is there any update on this issue ?

[michael-s-molina]

https://github.com/apache/superset/pull/26380

[iamrohit07]

@michael-s-molina I am still facing the the same issue. I have installed Superset 3.1.0 using helm chart superset-0.12.0. I am getting no such table error for all the charts. I went inside the superset pod to see examples.db file but it's not there. After running superset load_examples within pod I can see examples.db file and charts are getting loaded. Did we miss something while fixing this issue?

[tsndqst]

@iamrohit07 I think there ended up being two problems discussed in this issue. I don't think anything has been done to address the missing examples database. It seems that issue was introduced with this PR: https://github.com/apache/superset/pull/25003.

I've been trying to configure the examples to load into the main database but it looks like the example loading code has some dependencies on specific schema names. Hopefully someone with more knowledge of the code can help figure out how to get examples loaded into a database that isn't local to a container so they will work in environments like Kubernetes.

[michael-s-molina]

Hi @iamrohit07. As @tsndqst correctly pointed out, this seems like a different issue than the original one no such table: main.FCC 2018 Survey which was fixed by https://github.com/apache/superset/pull/26380. Please open a new issue and tag @dpgaspar which was the author of https://github.com/apache/superset/pull/25003.

[tsndqst]

Hi @iamrohit07. As @tsndqst correctly pointed out, this seems like a different issue than the original one no such table: main.FCC 2018 Survey which was fixed by #26380. Please open a new issue and tag @dpgaspar which was the author of #25003.

I think this issue correctly summarizes the symptoms of the issue. #26380 may have fixed an existing issue but I think this issue is still valid. In other words, if @iamrohit07 makes a new issue it will probably have the same summary as this one.

[rusackas]

We're trying to get to a sustainable Issues backlog, and it's extremely helpful when Issues cover one reproducible bug, which would preferably be fixed by one PR. When issues contain two bugs and multiple PRs, it gets confusing, long discussion threads ensue, and tend not to get closed (we're currently tidying up antique issues and are seeing a lot of this).

[tsndqst]

I think one thing that would help with that is to confirm a PR fixes the issue before closing the issue.

[michael-s-molina]

Re-opening the issue...

[iamrohit07]

@dpgaspar Could you please take a look at this. I am not able to access any of the charts and getting no such table error. When I exec into superset pod, I can see that superset_home/examples.db file has zero size. When I run superset load_examples within pod, examples.db file gets recreated and then I am able to access all the charts.

[AnhQKatalon]

I am getting precisely the same error with the most current Superset chart. I enabled loadExamples but all the Dashboards and Charts return no such table errors

[tomaszbozek]

@dpgaspar Could you please take a look at this. I am not able to access any of the charts and getting no such table error. When I exec into superset pod, I can see that superset_home/examples.db file has zero size. When I run superset load_examples within pod, examples.db file gets recreated and then I am able to access all the charts.

This helped me. I cleaned superset: removed docker images, volumes and after running docker-compose up with fresh 3.1.1 version I logged into container using command docker exec -it superset_app /bin/bash and invoked command superset load_examples. This recreated examples db.

[tomaszbozek]

@dpgaspar Could you please take a look at this. I am not able to access any of the charts and getting no such table error. When I exec into superset pod, I can see that superset_home/examples.db file has zero size. When I run superset load_examples within pod, examples.db file gets recreated and then I am able to access all the charts.

In my case the problem was with volumes. Both superset-app and superset-init have to have access to the same volume: superset_home:/app/superset_home If not superset init populates examples db in separate space not under superset_app.

[gioargyr]

I have the same problem "Error: no such table" since January. Currently, I am deploying version 3.1.1 and it's the same behavior: init-db job runs fine, and if I test the connection to DB is fine. It seems that init-db loads all the examples in the postgresql instance, while the superset pod is loading from a local sqlite (which cannot find, because none created it). So, yes, if I run superset load_examples, the local .db is created and superset can visualize the examples.

Can you verify that this is how it works? Is it normal to function this way?

[tomaszbozek]

I have the same problem "Error: no such table" since January. Currently, I am deploying version 3.1.1 and it's the same behavior: init-db job runs fine, and if I test the connection to DB is fine. It seems that init-db loads all the examples in the postgresql instance, while the superset pod is loading from a local sqlite (which cannot find, because none created it). So, yes, if I run superset load_examples, the local .db is created and superset can visualize the examples.

Can you verify that this is how it works? Is it normal to function this way?

IMHo both pod's should use same volume and same superset_config.py and what is value for SQLALCHEMY_EXAMPLES_URI - if its commented out then pod would use sqllite by default.

[craig-rueda]

AFAIK the default examples uri should be set to the postgres included in the chart. The examples should be loading via the init pod, which could possibly be failing to run to completion. Check the logs there to see if there's a failure

[gioargyr]

AFAIK the default examples uri should be set to the postgres included in the chart. The examples should be loading via the init pod, which could possibly be failing to run to completion. Check the logs there to see if there's a failure

I guess you are referring to me. No init pod is failing. However, I would like to try whatever your are proposing, but can you be more specific? What is the exact variable I need to define? default examples uri should be set to the postgres included in the chart AFAIK I changed nothing related to the postgres that comes by default and superset is able to connect to it.