Superset using duckdb connection encounter DB engine error

[ivanonair]

Bug description

When I use DuckDB to connect to MINIO to read data, I often encounter (not always) the following error:

"DB engine Error
This may be triggered by: Issue 1011 - Superset encountered an unexpected error."

or sometime error msg are

"upstream connect error or disconnect/reset before headers. reset reason: connection termination"

This seems to be related to having multiple filters on a dashboard. I observed that to generate the dropdown menu for each filter, a distinct SQL query is executed on the dataset for each filter. I suspect that this is causing many concurrent queries to be executed simultaneously when the dashboard is opened.

And there is also an error msg on superset server:

duckdb.duckdb.InvalidInputException: Invalid Input Error: No open result set.

The current probability of error occurrence is about fifty-fifty without changing any settings, not occur every time.

This leads to suspicions that it might be an issue with the rate limit or the number of threads. In the article below, I saw that when using duckdb.connect(:default:), it might induce error since running queries concurrently on the same connection is not supported.

duckdb.duckdb.InvalidInputException: Invalid Input Error: Attempting to execute an unsuccessful or closed pending query result

duckdb issue link

However, this is different from the error message I received, and I'm not sure if it's related. Has anyone else using Superset + DuckDB encountered a similar issue? Thanks."

Screenshots/recordings

No response

Superset version

master / latest-dev

Python version

3.10

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

Hey there, @ivanonair! I'm here to help you with any bugs, questions, or contributions you have in mind. Let's tackle this issue together!

The issue you're encountering with DuckDB in Superset might be related to how concurrent queries are handled. DuckDB does not support running multiple queries concurrently on the same connection, which could lead to the errors you're seeing when multiple filters are applied on a dashboard. This is consistent with the error message about "No open result set" and the potential connection termination.

To address this, you might consider the following approaches:

1. Connection Pooling: Ensure that each query is executed on a separate connection. This can help avoid conflicts that arise from concurrent query execution on a single connection.

2. Rate Limiting: Check if there are any settings related to rate limiting or the number of concurrent connections allowed. Adjusting these settings might help manage the load better.

3. Query Optimization: Review the queries generated by the filters to see if they can be optimized or combined to reduce the number of concurrent queries.

4. Superset Configuration: Look into Superset's configuration options to see if there are any settings that can help manage database connections more effectively when using DuckDB.

These steps might help mitigate the issue by ensuring that queries are handled in a way that aligns with DuckDB's capabilities.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[villebro]

Ping @guenp as I know you're involved with DuckDB

[vanandjiwala]

@guenp @ivanonair I created a POC using duckdb and apache superset on my local machine in the 2nd half of november. I had multiple filters and multiple KPIs. I was able to run and demo it smoothly. I revisited the same thing 4-5 days back and suddenly it has started to break and I keep encountering the same error.

INTERNAL Error: Attempted to dereference unique_ptr that is NULL!
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.
For more information, see https://duckdb.org/docs/dev/internal_errors

I am on superset 4.1.1 with PG 15 and redis 7 on local windows machine.

I am curious to know if there is a workaround for the same? It used to work fine but there is some change which is causing this functionality to break.

[johna-xceleration]

We've recently started experiencing Gunicorn worker crashes when loading dashboards with charts that serve data via DuckDB. This issue began in the past couple of weeks, coinciding with updates to duckdb-engine. Through testing, I determined that the issue was introduced in version 0.14.2 with this change. Pinning duckdb-engine to version 0.14.1 (e.g., duckdb-engine==0.14.1) resolves the problem for us.

The crash seems to stem from the following code, specifically the duckdb.execute() call:

        self.reserved_words.update(
            {
                keyword_name
                for (keyword_name,) in duckdb.execute(
                    "select keyword_name from duckdb_keywords() where keyword_category == 'reserved'"
                ).fetchall()
            }
        )

Although I’m not highly proficient in Python, I suspect the issue is related to concurrency and the direct use of duckdb.execute() on the module. When I modify the code to use duckdb.cursor().execute() instead, the dashboards load as expected.

[vanandjiwala]

@johna-xceleration the suggestion worked very well for me. I downgraded duckdb-engine to 0.14.1 and the issue got resolved. Thank you for the help, appreciate it.