Docker Mysql official image not creating user in kubernetes

Question

I'm trying to deploy a mysql instance in k8s through a StatefulSet using the official Mysql image from DockerHub. I'm following the image documentation from DockerHub and providing MYSQL_ROOT_PASSWORD, MYSQL_USER and MYSQL_PASSWORD env vars, so the user should be automatically created, but it is not. The error I can see in container's logs is that root user is not able to connect at the point the user provided in MYSQL_USER is being created.

2021-09-14 17:28:20+00:00 [Note] [Entrypoint]: Creating user foo_user
2021-09-14T17:28:20.860763Z 5 [Note] Access denied for user 'root'@'localhost' (using password: YES)
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

After some investigation, I've noticed that the problem occurs when the values for the env vars are taken from k8s secrets, but if I hardcode their values in the StatefulSet's manifest, it works just fine. You can see my current code below:

---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: mysql
  labels:
    app: mysql
spec:
  replicas: 1
  serviceName: mysql-svc
  selector:
    matchLabels:
      app: mysql
  template:
    metadata:
      labels:
        app: mysql
    spec:
      containers:
      - name: mysql
        image: "mysql:latest"
        env:
        - name: MYSQL_DATABASE
          value: 'foo_db'
        - name: MYSQL_USER
          value: 'foo_user'
        - name: MYSQL_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-sec
              key: MYSQL_PASSWORD
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-sec
              key: MYSQL_ROOT_PASSWORD
        ports:
        - containerPort: 3306
          protocol: TCP
        volumeMounts:
        - name: mysql-db
          mountPath: /var/lib/mysql
          subPath: mysql
  volumeClaimTemplates:
  - metadata:
      name: mysql-db
    spec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 4Gi

And the secrets.yml file:

apiVersion: v1
kind: Secret
metadata:
  name: mysql-sec
  labels:
    app: mysql
type: Opaque
data:
  MYSQL_PASSWORD: ***************************
  MYSQL_ROOT_PASSWORD: ***************************

I've also tried to create the secrets first to make sure that the secrets already exist when the pod spins up, but without any success.

Any idea?

score 1 · Accepted Answer · answered Sep 15 '21 at 15:53

I was finally able to find out the root cause of the problem and it had nothing to do with secrets... The problem was related with the "complexity" of the value picked for the password. I chose a strong password autogenerated by an online tool, something similar to !6Y*]q~x+xG{9HQ~, and for some unknown reason this vale made the mysql docker's image's /entrypoint.sh script to fail with the aforementioned error Access denied for user 'root'@'localhost' (using password: YES). However, even though the script failed, the container and mysql server were up&running, and I was able to sneak into in and successfully execute mysql -u root --password="$MYSQL_ROOT_PASSWORD", so it seems pretty clear to me that the error is located in this script and the way it expands and use the value of this env var. After exchanging the password value by a "less complex" one, it worked like a charm.

score 0 · Answer 2 · answered Sep 15 '21 at 17:56

It may depend on how exactly you created the Secret. In fact password containing certain special characters shouldn't be a problem if you use single quotes when creating a Secret.

I believe this fragment of the official kubernetes documentation should answer your question:

Note:

Special characters such as $, \, *, =, and ! will be interpreted by your shell and require escaping. In most shells, the easiest way to escape the password is to surround it with single quotes ('). For example, if your actual password is S!B\*d$zDsb=, you should execute the command this way:
kubectl create secret generic dev-db-secret --from-literal=username=devuser --from-literal=password='S!B\*d$zDsb='
You do not need to escape special characters in passwords from files (--from-file).

Note that the values stored in Secret e.g. shown in the following fragment:

    type: Opaque
    data:
      MYSQL_PASSWORD: ***************************
      MYSQL_ROOT_PASSWORD: ***************************

are base64-encoded strings. So if you properly encode them before putting them into your secret.yaml:

echo '!6Y*]q~x+xG{9HQ~' | base64

you shouldn't run into any problems.

Thanks for your reply @mario! However, I doubt that's the problem in my case because I'm not creating the secret from the CLI with `kubectl create secret` but taking it from a yml manifest with `kubectl apply -f secrets.yml`. In addition, the file doesn't really contains the secrets but a "path to them" because we have a mutating web hook that resolves their values when they are created, taking them from a vault backend. — beni0888, Sep 16 '21 at 15:49
In addition, even though the entrypoint script fails to create the user given in `$MYSQL_USER` throwing the aforementioned error, after that, I can sneak into the container and successfully connect to mysql server with `mysql -u root --password="$MYSQL_ROOT_PASSWORD"`. So the secret is properly created by k8s. — beni0888, Sep 16 '21 at 15:52

Docker Mysql official image not creating user in kubernetes

2 Answers2