Failed to execute tidb.initsql

[shonge]

Bug Report

What version of Kubernetes are you using?
1.12

What version of TiDB Operator are you using?

v1.0.0

What storage classes exist in the Kubernetes cluster and what are used for PD/TiKV pods?

What's the status of the TiDB cluster pods?

What did you do?

1. Edit tidb-cluster/values.yaml like:

tidb:
  # The secret name of root password, you can create secret with following command:
  # kubectl create secret generic tidb-secret --from-literal=root=<root-password> --namespace=<namespace>
  # If unset, the root password will be empty and you can set it after connecting
  # passwordSecretName: tidb-secret
  # initSql is the SQL statements executed after the TiDB cluster is bootstrapped.
  initSql: |-
    create database app;

2. helm install tidb-cluster -n tidb123 --namespace tidb123

What did you expect to see?
Execute initsql is successful.

What did you see instead?

Error: release tidb123 failed: Job.batch "tidb123-tidb-initializer" is invalid: [spec.template.spec.volumes[0].secret.secretName: Required value, spec.template.spec.containers[0].volumeMounts[0].name: Not found: "password"]

kubectl -n tidb123 get secrets tidb-secret
Error from server (NotFound): secrets "tidb-secret" not found

[shonge]

I think here is the problem:

tidb-operator/charts/tidb-cluster/templates/tidb-initializer-job.yaml

Lines 40 to 43 in 8bda658

	volumes:
	- name: password
	secret:
	secretName: {{ .Values.tidb.passwordSecretName }}

And I will try to fix it.

[tennix]

@shonge You need to create the secret ahead of time as documented here https://pingcap.com/docs/v3.0/tidb-in-kubernetes/initialize-cluster/

[shonge]

tidb-operator/charts/tidb-cluster/templates/tidb-initializer-job.yaml

Lines 1 to 3 in 8bda658

	{{- if or .Values.tidb.passwordSecretName .Values.tidb.initSql }}
	apiVersion: batch/v1
	kind: Job

Create tidb-secret is an option?

[tennix]

Ah, right the secret is actually required. That if statement needs to be refactored. The secret is prerequisite, and initSql is optional.

[Yisaer]

Fix in #922.
Now passwordSecretName is optional.

[axot]

I'm keep seeing this error.

$ helm upgrade tidb-demo --version=v1.0.6 ./tidb-cluster
UPGRADE FAILED
Error: failed to create resource: Job.batch "tidb-demo-tidb-initializer" is invalid: [spec.template.spec.volumes[0].secret.secretName: Required value, spec.template.spec.containers[0].volumeMounts[0].name: Not found: "password"]
Error: UPGRADE FAILED: failed to create resource: Job.batch "tidb-demo-tidb-initializer" is invalid: [spec.template.spec.volumes[0].secret.secretName: Required value, spec.template.spec.containers[0].volumeMounts[0].name: Not found: "password"]

For using initSql, we also have to set passwordSecretName: tidb-secret.

[weekface]

@axot what's the output of the cmd:

helm template ./tidb-cluster | grep -A 100 tidb-initializer-job.yaml

[axot]

After add passwordSecretName the error fixed, but the job get errors,

Traceback (most recent call last):
  File "<string>", line 6, in <module>
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/__init__.py", line 84, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 179, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'root'@'10.210.129.201' (using password: NO)")

Here is what I'm doing.

kubectl create secret generic tidb-secret --from-literal=root=hello --namespace=tidb
helm upgrade tidb-demo --version=v1.0.6 ./tidb-cluster

mysql client also got similar error,

mysql -h 10.210.134.7 -P 4000 -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'10.250.0.5' (using password: NO)

helm template result,

# Source: tidb-cluster/templates/tidb-initializer-job.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: release-name-tidb-initializer
  labels:
    app.kubernetes.io/name: tidb-cluster
    app.kubernetes.io/managed-by: Tiller
    app.kubernetes.io/instance: release-name
    app.kubernetes.io/component: tidb-initializer
    helm.sh/chart: tidb-cluster-dev
spec:
  backoffLimit: 1000
  template:
    metadata:
      labels:
        app.kubernetes.io/name: tidb-cluster
        app.kubernetes.io/instance: release-name
        app.kubernetes.io/component: tidb-initializer
    spec:
      restartPolicy: OnFailure
      containers:
      - name: mysql-client
        image: asia.gcr.io/k8s-common-224708/tnir/mysqlclient
        imagePullPolicy: IfNotPresent
        command:
        - python
        - -c
        - |
          import os, MySQLdb
          host = 'release-name-tidb'
          permit_host = "%"
          port = 4000
          password_dir = '/etc/tidb/password'
          conn = MySQLdb.connect(host=host, port=port, user='root', connect_timeout=5)
          for file in os.listdir(password_dir):
              if file.startswith('.'):
                  continue
              user = file
              with open(os.path.join(password_dir, file), 'r') as f:
                  password = f.read()
              if user == 'root':
                  conn.cursor().execute("set password for 'root'@'%%' = %s;", (password,))
              else:
                  conn.cursor().execute("create user %s@%s identified by %s;", (user, permit_host, password,))
          with open('/data/init.sql', 'r') as sql:
              for line in sql.readlines():
                  conn.cursor().execute(line)
                  conn.commit()
          if permit_host != '%%':
              conn.cursor().execute("update mysql.user set Host=%s where User='root';", (permit_host,))
          conn.cursor().execute("flush privileges;")
          conn.commit()

        volumeMounts:
        - name: password
          mountPath: /etc/tidb/password
          readOnly: true
        - name: init-sql
          mountPath: /data
          readOnly: true
        resources:
          {}

      volumes:
      - name: password
        secret:
          secretName: tidb-secret
      - name: init-sql
        configMap:
          name: release-name-tidb
          items:
          - key: init-sql
            path: init.sql

---
# Source: tidb-cluster/templates/tidb-cluster.yaml
apiVersion: pingcap.com/v1alpha1
kind: TidbCluster
metadata:
  name: release-name
  annotations:
    pingcap.com/pd.release-name-pd.sha: "cfa0d77a"
    pingcap.com/tikv.release-name-tikv.sha: "172cca1d"
    pingcap.com/tidb.release-name-tidb.sha: "ec80f6c1"
  labels:
    app.kubernetes.io/name: tidb-cluster
    app.kubernetes.io/managed-by: Tiller
    app.kubernetes.io/instance: release-name
    app.kubernetes.io/component: tidb-cluster
    helm.sh/chart: tidb-cluster-dev
spec:
  pvReclaimPolicy: Retain
  timezone: UTC
  services:
    - name: pd
      type: ClusterIP

  schedulerName: default-scheduler
  pd:

[axot]

After some tries, the error fixed.

1. add this to tidb config

[security]
skip-grant-table = true

2. delete the initializer job
3. helm upgrade

[Yisaer]

@axot It seems the first problem you met is caused by missing setting the password in secret.
And for the second problem, could you provide the tidb and tidb-operator version for us?

[axot]

@Yisaer Hi, I'm using v1.0.6 for tidb-operator, and upgraded v3.0.5 to v3.0.9 for tidb today.

[axot]

I use back to original v3.0.5, and get a fresh install, however the password seems not setup correctly?

password is hello,

❯ k get secrets tidb-secret -o yaml
apiVersion: v1
data:
  root: aGVsbG8=
kind: Secret

values.yaml

tidb:
  # Please refer to https://github.com/pingcap/tidb/blob/master/config/config.toml.example for the default
  # tidb configurations(change to the tags of your tidb version),
  # just follow the format in the file and configure in the 'config' section
  # as below if you want to customize any configuration.
  # Please refer to https://pingcap.com/docs-cn/v3.0/reference/configuration/tidb-server/configuration-file/
  # (choose the version matching your tidb) for detailed explanation of each parameter.
  config: |
    [log]
    level = "error"
    [prepared-plan-cache]
    enabled = true
    [tikv-client]
    max-batch-wait-time = 2000000
#    [security]
#    skip-grant-table = true

  replicas: 2
  # The secret name of root password, you can create secret with following command:
  # kubectl create secret generic tidb-secret --from-literal=root=<root-password> --namespace=<namespace>
  # If unset, the root password will be empty and you can set it after connecting
  # passwordSecretName: tidb-secret
  # permitHost is the host which will only be allowed to connect to the TiDB.
  # If unset, defaults to '%' which means allow any host to connect to the TiDB.
  # permitHost: 127.0.0.1
  # initSql is the SQL statements executed after the TiDB cluster is bootstrapped.
  passwordSecretName: tidb-secret
  permitHost: '%'
  initSql: |-
    create database shaotest;
    set global tidb_hashagg_final_concurrency=1;
    set global tidb_hashagg_partial_concurrency=1;
    set global tidb_disable_txn_auto_retry=0;

pod status,

tidb-demo-discovery-5654f74c86-s76g8   1/1     Running     0          7m28s
tidb-demo-monitor-5fc9f98df5-rggcx     3/3     Running     0          7m28s
tidb-demo-pd-0                         1/1     Running     0          7m27s
tidb-demo-pd-1                         1/1     Running     0          7m27s
tidb-demo-pd-2                         1/1     Running     0          7m27s
tidb-demo-tidb-0                       2/2     Running     0          5m24s
tidb-demo-tidb-1                       2/2     Running     0          5m24s
tidb-demo-tidb-initializer-lzcl2       0/1     Completed   5          7m28s
tidb-demo-tikv-0                       1/1     Running     0          6m24s
tidb-demo-tikv-1                       1/1     Running     0	      6m24s
tidb-demo-tikv-2                       1/1     Running     0	      6m24s

connect failed.

❯ mysql -uroot -h 10.210.133.9 -P 4000 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'10.250.0.5' (using password: NO)