[BUG] wesqlscale cluster failed to ping MySQL server: Error 2012 (HY000): No authentication methods available for authentication

[JashBook]

Describe the bug

kbcli version
Kubernetes: v1.26.14-gke.1006000
KubeBlocks: 0.9.0-beta.0
kbcli: 0.9.0-beta.0

To Reproduce Steps to reproduce the behavior:

1. create wesqlscale cluster

   kbcli cluster create  mysql scale-xmrbjz --mode='raftGroup' --cpu=0.5 --memory=1 --storage=20 --availability-policy=none --termination-policy=WipeOut --monitoring-interval=0 --version=ac-mysql-8.0.30-1  --replicas=3 --proxy-enabled=true

2. config read_write_splitting_policy=random

   kbcli cluster configure scale-xmrbjz --auto-approve --set healthcheck_timeout=4s,read_write_splitting_policy=random --components vtgate --config-spec vtgate-config --config-file vtgate.cnf

3. bench

   
   kubectl get secrets  scale-xmrbjz-mysql-account-kbadmin -o jsonpath="{.data.username}"|base64 -d
   kbadmin                                                                                                                                                                                                                                                                 ➜  ~ kubectl get secrets  scale-xmrbjz-mysql-account-kbadmin -o jsonpath="{.data.password}"|base64 -d
   Ss82sK91q2

kbcli bench sysbench benchtest-scale-xmrbjz --cluster scale-xmrbjz --host scale-xmrbjz-vtgate.default.svc --user kbadmin --password "Ss82sK91q2" --database benchtest --namespace default --port 15306 --threads 4 --type oltp_read_write --tables 4 --size 1000 --duration 10 --driver mysql --extra-args="--auto_inc=false"

4. See error

kubectl get pod NAME READY STATUS RESTARTS AGE benchtest-scale-xmrbjz-precheck-wkpp5 0/1 Error 0 86s

logs error pod

kubectl logs benchtest-scale-xmrbjz-precheck-wkpp5 2024/04/02 03:42:22 failed to ping MySQL server: Error 2012 (HY000): No authentication methods available for authentication.

kbcli cluster connect scale-xmrbjz --component vtgate --as-user kbadmin Password: Connect to instance scale-xmrbjz-vtgate-0 Defaulted container "vtgate" out of: vtgate, wait-vtctld-ready (init) ERROR 2012 (HY000): No authentication methods available for authentication. command terminated with exit code 1

kbcli cluster connect scale-xmrbjz --component mysql --as-user kbadmin Password: Connect to instance scale-xmrbjz-mysql-1: out of scale-xmrbjz-mysql-1(leader), scale-xmrbjz-mysql-0(follower), scale-xmrbjz-mysql-2(follower) Defaulted container "mysql" out of: mysql, metrics, vttablet, lorry, init-data (init) mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'kbadmin'@'localhost' (using password: YES) command terminated with exit code 1

**Expected behavior**
A clear and concise description of what you expected to happen.

**Screenshots**
If applicable, add screenshots to help explain your problem.

**Desktop (please complete the following information):**
 - OS: [e.g. iOS]
 - Browser [e.g. chrome, safari]
 - Version [e.g. 22]

**Additional context**
Add any other context about the problem here.

[earayu]

Based on the reproduction steps, I found that there is no user named kbadmin. It seems that kubeblocks did not automatically create the kbadmin user.

MySQL [(none)]> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| u1               | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
6 rows in set (0.005 sec)

[github-actions[bot]]

This issue has been marked as stale because it has been open for 30 days with no activity