Querying MotherDuck does not work in Cube Core and Cube Cloud

[igorlukanin]

Describe the bug
If CUBEJS_DB_DUCKDB_MOTHERDUCK_TOKEN is specified for the duckdb driver so that it connects to MotherDuck, then any query would result in an error. In Cube Cloud, the query would run indefinitely. In Cube Core, a container will exit after an interval.

To Reproduce
Steps to reproduce the behavior:

1. Take the data model
2. Specify env vars
3. Run any query
4. See errors in the logs

cube-1  | E1106 13:48:04.302609835      40 ssl_utils.cc:593]                     load_file: UNKNOWN:Failed to load file {created_time:"2024-11-06T13:48:04.302598418+00:00", filename:"/usr/share/grpc/roots.pem", children:[UNKNOWN:No such file or directory {created_time:"2024-11-06T13:48:04.302597418+00:00", errno:2, os_error:"No such file or directory", syscall:"fopen"}]}
cube-1  | E1106 13:48:04.302628460      40 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:04.302630085      40 tls_security_connector.cc:461]        Update handshaker factory failed.
cube-1  | E1106 13:48:04.421059877     177 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:04.421074502     177 tls_security_connector.cc:461]        Update handshaker factory failed.

cube-1  | E1106 13:48:34.422520752     179 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:34.422562960     179 tls_security_connector.cc:461]        Update handshaker factory failed.
cube-1  | terminate called after throwing an instance of 'duckdb::InternalException'
cube-1  |   what():  {"exception_type":"INTERNAL","exception_message":"Attempted to dereference unique_ptr that is NULL!"}

Expected behavior
Querying MotherDuck just works.

Screenshots

Minimally reproducible Cube Schema

cubes:
  - name: test
    sql: SELECT 123 AS value

    measures:
      - name: count
        type: count

    dimensions:
      - name: value
        sql: value
        type: number

Setting CUBEJS_DB_SSL=true does not change anything.

CUBEJS_DEV_MODE=true
CUBEJS_API_SECRET=SECRET
# CUBEJS_DB_SSL=true

CUBEJS_DB_TYPE=duckdb
CUBEJS_DB_DUCKDB_MOTHERDUCK_TOKEN=<token>

Version:
v1.1.2, both in Cube Core and Cube Cloud

Additional context
Reported by at least two users in Slack: https://cube-js.slack.com/archives/C04NYBJP7RQ/p1723151261882389

[akatov]

I examined connecting to MotherDuck from Cube Cloud and also from the DuckDB CLI again - there is an additional authentication step after the initial connection with the token - I get prompted to enter a generated 8-character code into web UI before the connection can be established successfully.

I am also looking at the source code for the duckdb-driver and trying to run some of the commands in a node process.

From node I can connect to a local db using

db = new Database(localPath)

as well as to MotherDuck using

db = new Database('md:motherduck_token=' + token)

without needing the second authentication step, but I can only query using

db.all(QUERY, (err, res) => ...)

whereas the source for the duckdb-driver seems to first create a connection using

defaultConnection = db.connect()

and then use

defaultConnection.exec(QUERY, callback)

to run queries.

[igorlukanin]

Thanks you @akatov, this is very helpful! 🙌 Would you mind contributing a fix?

[Josh-a-e]

I spent some time investigating this issue and found that it was solvable just by installing the ca-certificates apt-get package.

Specifically, running this in the exec tab of the running cube container on Docker for mac, then restarting the container results successfully connecting to Motherduck:

$ apt-get update
$ apt-get install ca-certificates

I can see that there are a few Dockerfile that include other apt-get install calls and the dev.Dockerfile already installs ca-certificates, but I suspect it should be a very simple change for someone with a good understanding of them.

I also found that the additional authentication step you mention @akatov is actually duck db complaining that it can't find a token at all. If you follow the steps through, it prompts you to create a new token, then sets it (and/or suggests you set it as an environment variable).

I noticed that the .open duckdb command takes ?motherduck_token=... parameter token and works as expected, but ATTACH, whilst similar in syntax, goes through the token prompt, and then when set complains that it can't find a table with the name of the token specified.

Related, it seems hard/impossible to set the token once the connection is open:

Attempting to automatically open the SSO authorization page in your default browser.
1. Please open this link to login into your account: https://auth.motherduck.com/activate
2. Enter the following code: XXXX-XXXX

...

⚠️ Token not found, please use the SET command:
  SET motherduck_token=<token>

And when trying to do this

Settings Error:
setting 'motherduck_token' can only be set during initialization

(this mainly comes from my experimentation with the initSql option on DuckDBDriver which eventually lead to this PR)

[ClorisYT]

I encountered a similar issue described on GitHub: #9273. Do you have any thoughts or solutions for this?

The issue isrelated to connecting Cube to MotherDuck with DuckDB, and I am also experiencing problems with the deployment. I would greatly appreciate any suggestions or guidance you can offer. Thank you!