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
Comment From: 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...
Comment 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.
Comment From: 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.
Comment From: 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
Comment From: 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
**Comment From: john-bodley**
@craig-rueda do you have any insights with regards to the Helm charts issue?
**Comment From: 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.
**Comment From: iamrohit07**
@john-bodley @craig-rueda @sebastianliebscher Is there any update on this issue ?
**Comment From: michael-s-molina**
https://github.com/apache/superset/pull/26380
**Comment From: 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?
**Comment From: 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.
**Comment From: 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.
**Comment From: 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.
**Comment From: 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).
**Comment From: tsndqst**
I think one thing that would help with that is to confirm a PR fixes the issue before closing the issue.
**Comment From: michael-s-molina**
Re-opening the issue...
**Comment From: 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.
**Comment From: 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
**Comment From: 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.
**Comment From: 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.
**Comment From: 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?
**Comment From: 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.
**Comment From: 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
**Comment From: 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.
**Comment From: rusackas**
Is anyone still facing this? I was tempted to close it as stale (it hasn't been touched in nearly a year) but it already got re-opened once :)
**Comment From: MohamedRhimi25**
@rusackas Yes I'm still facing this. I've encountered this on v3.1.0 and today I upgraded to 4.1.1 and still facing it.
As the fellas above said, running superset load_examples directly on the pod used to fix the issue for me at v3.1.0. But on v4.1.1, this isn't a solution anymore.. Anyone encountering the same thing?
**Comment From: gioargyr**
Hello!
I am in version 4.0.1 and I still have the same problem and running `$ superset load_examples` still solves the problem.
**Comment From: jklaise**
Can confirm that the issue still exists on the 0.14.2 chart (version 4.1.2) and running `superset load_examples` in the Superset node pod fixes this.
Additionally, I had to add @sergiimk config override to be able to connect to the database at all:
```yaml
configOverrides:
allow_sqlite_for_examples: |
PREVENT_UNSAFE_DB_CONNECTIONS = False
Comment From: rusackas
I think those errors are more good advice than a bug, if I'm not mistaken 😅
You should not use SQLite in production (which Helm is geared toward) for your metadata database. See docs here.
Comment From: jklaise
True, I was just testing a specific flow that required a k8s setup and I just needed some sample data.
Comment From: rusackas
This feels like it's safe to close, but let me know if anyone feels otherwise, and how they suggest we might move forward if so :)