mysql connection using 'hub.db.password' failed and alternative solution

[a3626a]

Bug description

With yaml configuration like this :

hub:
  db:
    type: mysql
    url: mysql+pymysql://<username>@<host>:<port>/<databse>
    password: <PASSWORD>

Expected behaviour

Database should be connected and the hub process should not terminate.

Actual behaviour

Jupyter Hub fails to connect the database

Loading /usr/local/etc/jupyterhub/secret/values.yaml                                                                                                                                                                                                     
No config at /usr/local/etc/jupyterhub/existing-secret/values.yaml                                                                                                                                                                                       
Loading extra config: <extra config 1>
Loading extra config: <extra config 2>                                                                                             
Loading extra config: <extra config 2>
[I 2022-01-17 09:53:09.851 JupyterHub app:2766] Running JupyterHub version 2.0.1
[I 2022-01-17 09:53:09.851 JupyterHub app:2796] Using Authenticator: oauthenticator.generic.GenericOAuthenticator-14.2.0
[I 2022-01-17 09:53:09.851 JupyterHub app:2796] Using Spawner: kubespawner.spawner.KubeSpawner-2.0.0
[I 2022-01-17 09:53:09.851 JupyterHub app:2796] Using Proxy: jupyterhub.proxy.ConfigurableHTTPProxy-2.0.1
[E 2022-01-17 09:53:09.874 JupyterHub app:1783] Failed to connect to db: mysql+pymysql://<username>@<host>:<port>/<database>
[C 2022-01-17 09:53:09.874 JupyterHub app:1787] If you recently upgraded JupyterHub, try running
        jupyterhub upgrade-db
    to upgrade your JupyterHub database schema

How to reproduce

See the yaml config above.

Your personal set up

• EKS Node OS: AWS AMI AL2_x86_64 1.21.5-20211206

• Version(s): Z2JH 1.1.3-n254.h9b546a54

• EKS : eks.3, k8s 1.21,

• Database : AWS RDS MySQL 8.0.26

• Configuration

  extraConfig:
    01-enable-collaborative: |
      c.Spawner.cmd = ['jupyter-labhub']
      c.Spawner.args = ['--collaborative']
    02-authenticator: |
      from oauthenticator.generic import GenericOAuthenticator
      c.JupyterHub.authenticator_class = GenericOAuthenticator
      c.GenericOAuthenticator.oauth_callback_url = ...
      c.GenericOAuthenticator.client_id = ...
      c.GenericOAuthenticator.client_secret = ...
      c.GenericOAuthenticator.login_service = ...
      c.GenericOAuthenticator.authorize_url = ...
      c.GenericOAuthenticator.userdata_url = ...
      c.GenericOAuthenticator.token_url = ...
      c.GenericOAuthenticator.username_key = ...
    03-init-groups-roles-services: |
      c.JupyterHub.load_groups = ...
      c.JupyterHub.services = ...
      c.JupyterHub.load_roles =...

My Investigation on bug :

Secret is properly created :

• # Please edit the object below. Lines beginning with a '#' will be ignored,
  # and an empty file will abort the edit. If an error occurs while saving this file will be
  # reopened with the relevant failures.
  #
  apiVersion: v1
  data:
    hub.config.ConfigurableHTTPProxy.auth_token: ...
    hub.config.CryptKeeper.keys: ...
    hub.config.JupyterHub.cookie_secret: ...
    hub.db.password: ...
    values.yaml: ...
  kind: Secret
  (...)

• import z2jh
  z2jh.get_secret_value("hub.db.password", None)
  # password is returned

Database Type is also properly created :

• import z2jh
  z2jh.get_config("hub.db.type")
  # mysql is returned

Password is correct. This configuration perfectly works :

• hub:
    db:
      type: mysql
      url: mysql+pymysql://<username>:<PASSWORD>@<host>:<port>/<databse>

Password is fed into MYSQL_PWD at jupyterhub_config.py

MYSQL_PWD is deprecated and will be removed.

• https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html

MYSQL_PWD is supported by MySQL Command-Line Client. However, there is no evidence that sqlalchemy or pymysql supporting this variable.

• PyMySQL is "pure python" client, it doesn't rely on MySQL Command-Line Client.

Using c.JupyterHub.db_kwargs works. I think this should be the default configuration, not using MYSQL_PWD

• hub:
    db:
      type: mysql
      url: mysql+pymysql://<username>@<host>/<database>
      password: <password>
    extraConfig:
      00-database-setting: |
        c.JupyterHub.db_kwargs = {
          'connect_args': {
            'password': get_secret_value("hub.db.password", None)
          }
        }

[welcome]

Thank you for opening your first issue in this project! Engagement like this is essential for open source projects! 🤗

If you haven't done so already, check out Jupyter's Code of Conduct. Also, please try to follow the issue template as it helps other other community members to contribute more effectively.

You can meet the other Jovyans by joining our Discourse forum. There is also an intro thread there where you can stop by and say Hi! 👋

Welcome to the Jupyter community! 🎉

[a3626a]

I will create PR for this soon.

[zezaeoh]

Is there any update? 🥲

[mynkyu]

I will create PR for this soon.

Where is PR?

[joncotton]

Thanks for this ticket. I have the same issue and there's a lot of good info here. db_kwargs didn't work for me. I tried in the extraConfig and in the hub ConfigMap, jupyterhub_config.py data field.

Z2JH 1.2.0
MySQL 8.0.30 (Oracle Cloud hosted)

What did work for me is similar though:

hub:
  db:
    type: mysql
    url: mysql+pymysql://<username>@<host>:<port>/<database>
    password: <password>
  extraConfig:
    00-database-setting: |
      import urllib.parse
      if get_config("hub.db.type") == "mysql":
        db_url = c.JupyterHub.db_url.split("@")
        db_password = urllib.parse.quote(get_secret_value("hub.db.password", None))
        c.JupyterHub.db_url = f"{db_url[0]}:{db_password}@{db_url[1]}"

Depending on the password characters, escaping is required. This works for Alembic to setup a fresh database and then for Hub via SQLAlchemy to connect.

side note

I was having issues where only Alembic would connect, make its first pass of tables, then Hub would fail. Here are the Hub pod startup logs in that scenario. Verbatim. I'm not redacting this, the logger properly does it.

[I <date> JupyterHub app:2509] Using Proxy: jupyterhub.proxy.ConfigurableHTTPProxy-1.5.0
[I <date> alembic.runtime.migration migration:164] Context impl MySQLImpl.
[I <date> alembic.runtime.migration migration:167] Will assume non-transactional DDL.
[I <date> alembic.runtime.migration migration:556] Running stamp_revision  -> 4dc2d5a8c53c
[I <date> alembic.runtime.migration migration:164] Context impl MySQLImpl.
[I <date> alembic.runtime.migration migration:167] Will assume non-transactional DDL.
[E <date> JupyterHub app:1731] Failed to connect to db: mysql+pymysql://hub:[redacted]@10.100.100.20:3306/jupyterhub

[jimdigriz]

...hijacking issue as closest place anyone has discussed this here.

As this is completely undocumented and not easy to discover, maybe of interest to others how to push your password into a k8s secret and use SSL with SQL (and a root CA):

hub:
  db:
    type: postgres
    url: postgresql+psycopg2://jupyterhub@192.0.2.1:5432/jupyterhub
    upgrade: true
  ...
    extraVolumes:
    - name: sql-ca
      configMap:
        name: sql-ca
        defaultMode: 222
        items:
        - key: ca.crt
          path: ca.crt
    - name: sql-password
      secret:
        secretName: sql-password
        defaultMode: 222
  extraVolumeMounts:
  - name: sql-ca
    mountPath: /run/secrets/db/ssl
    readOnly: true
  - name: sql-password
    mountPath: /run/secrets/db/password
    readOnly: true
  ...
    extraConfig:
    db.py: |
      from pathlib import Path
      from urllib.parse import quote, urlencode
      db_url = c.JupyterHub.db_url.split('@', 1)
      db_password = quote(Path('/run/secrets/db/password/password').read_text())
      qs = urlencode([
          ('sslmode', 'verify-ca'),
          ('sslrootcert', '/run/secrets/db/ssl/ca.crt')
      ])
      c.JupyterHub.db_url = f'{db_url[0]}:{db_password}@{db_url[1]}?{qs}'

Populate those k8s values with:

kubectl create configmap sql-ca --save-config --dry-run=client --from-file=ca.crt -o yaml | kubectl apply -n jupyterhub -f -
# remember no trailing whitespace!
kubectl create secret generic sql-password --save-config --dry-run=client --from-file=password -o yaml | kubectl apply -n jupyterhub -f -