TidbInitializer failed when enable-sem is true

Bug Report

What version of Kubernetes are you using?

Client Version: v1.25.4
Kustomize Version: v4.5.7
Server Version: v1.25.3

What version of TiDB Operator are you using?

TiDB Operator Version: version.Info{GitVersion:"v1.4.1", GitCommit:"fc5ed457df45359d2d25cae488081b5cafc8f160", GitTreeState:"clean", BuildDate:"2023-01-13T11:55:07Z", GoVersion:"go1.16.4", Compiler:"gc", Platform:"linux/arm64"}

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

standard

NAME                        STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS   AGE
pd-test-cluster1-pd-0       Bound    pvc-13f4ae38-1e09-4158-97cc-257df7f18c8d   1Gi        RWO            standard       30m
pd-test-cluster1-pd-1       Bound    pvc-f438bfdd-8e1a-4490-8974-8174178652e2   1Gi        RWO            standard       30m
pd-test-cluster1-pd-2       Bound    pvc-0833278d-b244-4b15-b62c-daaa7b651807   1Gi        RWO            standard       30m
tikv-test-cluster1-tikv-0   Bound    pvc-e3efd16a-ebbd-4015-a642-f20c5c6039cc   20Gi       RWO            standard       28m
tikv-test-cluster1-tikv-1   Bound    pvc-06d6f920-4f2c-4072-89f3-25c7d2e5080b   20Gi       RWO            standard       28m
tikv-test-cluster1-tikv-2   Bound    pvc-db599afe-b0f9-4c75-ad8a-77a2c77bedfa   20Gi       RWO            standard       28m

What's the status of the TiDB cluster pods?

All running healthy.

What did you do?

I followed this instruction https://docs.pingcap.com/tidb-in-kubernetes/dev/initialize-a-cluster/
I created password for root user and create another user as well.

apiVersion: pingcap.com/v1alpha1
kind: TidbInitializer
metadata:
  name: initialize-demo
spec:
  image: tnir/mysqlclient
  imagePullPolicy: IfNotPresent
  cluster:
    name: test2-cluster
  initSql: "create database hello;"
  # initSqlConfigMap: tidb-initsql
  passwordSecret: "tidb-secret2"

enable-sem is turned on and use tidbinitializer.
The job will have error

 ❯ k logs test2-cluster-tidb-initializer-k7xsg
Defaulted container "mysql-client" out of: mysql-client, wait (init)
Traceback (most recent call last):
  File "/usr/local/bin/start_script.py", line 34, in <module>
    conn.cursor().execute("update mysql.user set Host=%s where User='root';", (permit_host,))
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/local/lib/python3.8/site-packages/MySQLdb/connections.py", line 239, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (8121, "privilege check for 'Update' fail")

start_script.py is mounted as a configmap

if permit_host != '%%':
    conn.cursor().execute("update mysql.user set Host=%s where User='root';", (permit_host,))

The update statement on mysql.user table failed.
root user cannot update even though it has all the grants.

Even thought TiDBInitialization return failure, the users were setup. So I connect the database using root and try the following experiment, which shows that root can update the user if it is using "rename user" statement instead of directly update mysql.user table.

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@%         |
+----------------+
1 row in set (0.00 sec)

mysql> update mysql.user set Host='127.0.0.1' where User='user4';
ERROR 8121 (HY000): privilege check for 'Update' fail

mysql> select * from information_schema.user_privileges;
+-------------+---------------+-------------------------+--------------+
| GRANTEE     | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-------------+---------------+-------------------------+--------------+
| 'root'@'%'  | def           | SELECT                  | YES          |
| 'root'@'%'  | def           | INSERT                  | YES          |
| 'root'@'%'  | def           | UPDATE                  | YES          |
| 'root'@'%'  | def           | DELETE                  | YES          |
| 'root'@'%'  | def           | CREATE                  | YES          |
| 'root'@'%'  | def           | DROP                    | YES          |
| 'root'@'%'  | def           | PROCESS                 | YES          |
| 'root'@'%'  | def           | REFERENCES              | YES          |
| 'root'@'%'  | def           | ALTER                   | YES          |
| 'root'@'%'  | def           | SHOW DATABASES          | YES          |
| 'root'@'%'  | def           | SUPER                   | YES          |
| 'root'@'%'  | def           | EXECUTE                 | YES          |
| 'root'@'%'  | def           | INDEX                   | YES          |
| 'root'@'%'  | def           | CREATE USER             | YES          |
| 'root'@'%'  | def           | CREATE TABLESPACE       | YES          |
| 'root'@'%'  | def           | TRIGGER                 | YES          |
| 'root'@'%'  | def           | CREATE VIEW             | YES          |
| 'root'@'%'  | def           | SHOW VIEW               | YES          |
| 'root'@'%'  | def           | CREATE ROLE             | YES          |
| 'root'@'%'  | def           | DROP ROLE               | YES          |
| 'root'@'%'  | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'  | def           | LOCK TABLES             | YES          |
| 'root'@'%'  | def           | CREATE ROUTINE          | YES          |
| 'root'@'%'  | def           | ALTER ROUTINE           | YES          |
| 'root'@'%'  | def           | EVENT                   | YES          |
| 'root'@'%'  | def           | SHUTDOWN                | YES          |
| 'root'@'%'  | def           | RELOAD                  | YES          |
| 'root'@'%'  | def           | FILE                    | YES          |
| 'root'@'%'  | def           | CONFIG                  | YES          |
| 'root'@'%'  | def           | REPLICATION CLIENT      | YES          |
| 'root'@'%'  | def           | REPLICATION SLAVE       | YES          |
| 'user2'@'%' | def           | USAGE                   | NO           |
| 'user3'@'%' | def           | USAGE                   | NO           |
+-------------+---------------+-------------------------+--------------+
33 rows in set (0.01 sec)

mysql> rename user 'user2' to 'user4';
Query OK, 0 rows affected (0.06 sec)

mysql> select user, host from mysql.user
    -> ;
+-------+------+
| user  | host |
+-------+------+
| root  | %    |
| user3 | %    |
| user4 | %    |
+-------+------+
3 rows in set (0.02 sec)


mysql> rename user 'user4'@'%' to 'user4'@'127.0.0.1';
Query OK, 0 rows affected (0.05 sec)

mysql> select user, host from mysql.user;
+-------+-----------+
| user  | host      |
+-------+-----------+
| root  | %         |
| user3 | %         |
| user4 | 127.0.0.1 |
+-------+-----------+

What did you expect to see?

No failure in TiDBInitialization when enable-sem is turned on.

I don't know if this is a bug with privilege check in TiDB when enable-sem is on or not.
However, for TiDBInitialization issue, i think it should be an easy fix if the script use rename user instead of direct update to mysql.user table to update host.

Start with the mounted start_script.py used by the TidbInitializer and inspect how the mysql-client container is configured when enable-sem is enabled. Reproduce the failure with the shown update against mysql.user, then verify the initializer completes successfully and the requested host update still occurs.