404 on GET and DELETE on tableschemaview for Admin role user with all permissions on TableSchemaView for 3.0.0

[rajivpatki]

A clear and concise description of what the bug is:

I am the admin for Superset 3.0.0. When in SQL Lab I click on a table (from DuckDB), I get an error message at the bottom right corner that says "An error occurred while fetching table metadata" and on selecting two tables "An error occurred while removing the table schema. Please contact your administrator."

The logs show:

404 Not Found: The requested URL was not found on the server. If you entered the URL manually please check your spelling and try again.
superset | 192.168.176.1 - - [15/Dec/2023:18:59:38 +0000] "GET /tableschemaview/dtvBbokbR HTTP/1.1" 404 0 "-"
^ Same 404 error for DELETE on tableschemaview

How to reproduce the bug

This bug did not exist earlier for me and I could see a preview of the schema of the table I was requesting.

1. Go to 'SQL Lab, clikc on database (select a DuckDB database that has create table, view, DML, exploration' enabled
2. Click on 'schema duck.main'
3. Click on any table > "An error occurred while fetching table metadata". Remove table from the selection > "An error occurred while removing the table schema. Please contact your administrator." The logs show 404 errors on tableschemaview for GET and DELETE
4. See error

Expected results

The table schema preview loads in the left pane of the SQL Lab

Actual results

Error box in bottom right corner

Environment

• browser type and version: MS Edge Version 120.0.2210.77 (Official build) (64-bit)
• superset version: 3.0.0
• python version: from docker image superset:3.0.0
• any feature flags active: I have tried removing all caching and features from superset_config.py but the problem remains

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [ .] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [ .] I have reproduced the issue with at least the latest released version of superset.
• [ .] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

• Superset is running with built in google OAuth behind a nginx reverse proxy
• Queries, dashboard, charts, SQL Lab is working fine. This error is thrown only in SQL Lab

[michael-s-molina]

Hi @rajivpatki. Does this error happen with the latest stable release 3.0.2?

[rajivpatki]

Hi @rajivpatki. Does this error happen with the latest stable release 3.0.2?

Yes, I still get both errors along with an additional 404:

"GET /static/appbuilder/select2/select2-bootstrap-theme.css HTTP/1.1"

[rajivpatki]

After a little bit of digging this seems to an issue with DuckDB. The above works fine for Postgres but not for DuckDB. It throws an error if you do select * from "duck.main".table_name and asks if you meant "duck.table_name" instead.

select * from duck.main.table_name works fine too. I think the table metadata is fetched using "database.schema".table_name which is not handled by DuckDB. Any suggestions?

[michael-s-molina]

Tagging some folks that have more context on the DuckDB integration. @betodealmeida @rusackas @villebro @eschutho

[Mause]

There's a possibility this might have been caused by recent change to DuckDB-engine (sqlalchemy driver) as well: Mause/duckdb_engine#835

[guenp]

The problem is that <db name>.<schema name> is currently not supported by the duckdb SQLAlchemy engine.
These PRs should fix the issue: Mause/duckdb_engine#848 #26281

[rajivpatki]

@guenp @Mause duckdb-engine 0.9.5 solves for the metadata in the SQL Lab pane. Thank you for that!

For Preview within SQL Lab for the selected table the error in the Preview subtab on SQL Lab in Superset 3.0.2 I still get:

DuckDB Error
duckdb error: Catalog Error: Table with name table_name does not exist!
Did you mean "database_name.table_name"?
LINE 25: FROM "database_name.main".table_name

In logs:

superset | 2023-12-21 09:23:19,485:WARNING:superset.views.base:SupersetErrorsException
superset | Traceback (most recent call last):
superset | File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
superset | rv = self.dispatch_request()
superset | File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
superset | return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
superset | File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
superset | return f(self, *args, **kwargs)
superset | File "/app/superset/views/base_api.py", line 127, in wraps
superset | raise exnLINE
superset | File "/app/superset/views/base_api.py", line 121, in wraps
superset | duration, response = time_function(f, self, *args, **kwargs)
superset | File "/app/superset/utils/core.py", line 1526, in time_function
superset | response = func(*args, **kwargs)
superset | File "/app/superset/views/base_api.py", line 93, in wraps
superset | return f(self, *args, **kwargs)
superset | File "/app/superset/utils/log.py", line 255, in wrapper
superset | value = f(*args, **kwargs)
superset | File "/app/superset/sqllab/api.py", line 310, in execute_sql_query
superset | command_result: CommandResult = command.run()
superset | File "/app/superset/sqllab/commands/execute.py", line 121, in run
superset | raise ex
superset | File "/app/superset/sqllab/commands/execute.py", line 103, in run
superset | status = self._run_sql_json_exec_from_scratch()
superset | File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
superset | raise ex
superset | File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
superset | return self._sql_json_executor.execute(
superset | File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
superset | raise SupersetErrorsException(
superset | superset.exceptions.SupersetErrorsException: [SupersetError(message='duckdb error: Catalog Error: Table with name table_name does not exist!
Did you mean "database_name.table_name"?
LINE 25: FROM "database_name.main".table_name
^', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'DuckDB', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

I have tested this with docker image apache/superset:3.0.2; Current solution is to disable data_preview in database settings. However, you cannot define a dataset as a Physical (table or view) and have to instead use Virtual with a SQL SELECT * FROM table_name Defining datasets as physical tables is working fine now

[guenp]

@rajivpatki , Great news! The last bug you're seeing should be fixed by #26281, although I am not sure it will be included in superset 3.0.3. We might need to wait until the release after next or you could try apache/superset:latest-dev in the mean time.

[michael-s-molina]

@guenp @rajivpatki #26281 will be included in 3.0.3. It was already cherry-picked into the 3.0 branch.