use mysql client which does not support authentication protocol can connect to tiproxy

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

#  connect tidb directly
root@dbdeployer-0:/tools#  /root/opt/mysql/5.0.96/bin/mysql -u sha2 -h tc-tidb.testbed-endless-client-5lgf5 -P 4000 -psha --default-character-set utf8
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client
root@dbdeployer-0:/tools#  /root/opt/mysql/5.1.72/bin/mysql -u sha2 -h tc-tidb.testbed-endless-client-5lgf5 -P 4000 -psha --default-character-set utf8
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client

# connect to tiproxy
root@dbdeployer-0:/tools#  /root/opt/mysql/5.1.72/bin/mysql -u sha2 -h tc-tiproxy.testbed-endless-client-5lgf5 -P 6000 -psha --default-character-set utf8

root@dbdeployer-0:/tools#  /root/opt/mysql/5.0.96/bin/mysql -u sha2 -h tc-tiproxy.testbed-endless-client-5lgf5 -P 6000 -psha --default-character-set utf8

2. What did you expect to see? (Required)

3. What did you see instead (Required)

root@dbdeployer-0:/tools#  /root/opt/mysql/5.0.96/bin/mysql -u sha2 -h tc-tidb.testbed-endless-client-5lgf5 -P 4000 -psha --default-character-set utf8
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client
root@dbdeployer-0:/tools#  /root/opt/mysql/5.1.72/bin/mysql -u sha2 -h tc-tidb.testbed-endless-client-5lgf5 -P 4000 -psha --default-character-set utf8
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client
root@dbdeployer-0:/tools#  /root/opt/mysql/5.1.72/bin/mysql -u sha2 -h tc-tiproxy.testbed-endless-client-5lgf5 -P 6000 -psha --default-character-set utf8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 100
Server version: 5.7.25-TiDB-v7.3.0-alpha

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select tidb_version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    100
Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye
root@dbdeployer-0:/tools#  /root/opt/mysql/5.0.96/bin/mysql -u sha2 -h tc-tiproxy.testbed-endless-client-5lgf5 -P 6000 -psha --default-character-set utf8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 100
Server version: 5.7.25-TiDB-v7.3.0-alpha

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select tidb_version();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    100
Current database: *** NONE ***

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye
root@dbdeployer-0:/tools#  /root/opt/mysql/5.0.96/bin/mysql -u sha2 -h tc-tiproxy.testbed-endless-client-5lgf5 -P 6000 -psha --default-character-set utf8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 100
Server version: 5.7.25-TiDB-v7.3.0-alpha

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
/root/opt/mysql/5.0.96/bin/mysql  Ver 14.12 Distrib 5.0.96, for unknown-linux-gnu (x86_64) using readline 5.1

Connection id:          100
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.25-TiDB-v7.3.0-alpha
Protocol version:       10
Connection:             tc-tiproxy.testbed-endless-client-5lgf5 via TCP/IP
Client characterset:    utf8
Server characterset:    utf8
TCP port:               6000
--------------

[2023/07/17 08:38:10.524 +00:00] [INFO] [main.proxy] [proxy/proxy.go:170] [new connection] [connID=374] [client_addr=10.233.105.34:46090]
[2023/07/17 08:38:10.524 +00:00] [WARN] [main.proxy.conn.be.authenticator] [backend/authenticator.go:148] [frontend may not support plugin auth] [connID=374] [client_addr=10.233.105.34:46090] [capability=CLIENT_LONG_PASSWORD|CLIENT_LONG_FLAG|CLIENT_LOCAL_FILES|CLIENT_PROTOCOL_41|CLIENT_INTERACTIVE|CLIENT_TRANSACTIONS|CLIENT_SECURE_CONNECTION|CLIENT_MULTI_STATEMENTS|CLIENT_MULTI_RESULTS]
[2023/07/17 08:38:10.526 +00:00] [INFO] [main.proxy.conn.be] [backend/backend_conn_mgr.go:229] [connected to backend] [connID=374] [client_addr=10.233.105.34:46090] [ns=default] [backend_addr=tc-tidb-0.tc-tidb-peer.testbed-endless-client-5lgf5.svc:4000]
[2023/07/17 08:38:10.568 +00:00] [INFO] [main.proxy] [proxy/proxy.go:181] [connection closed] [connID=374] [client_addr=10.233.105.34:46090]
[2023/07/17 08:38:24.628 +00:00] [INFO] [main.proxy] [proxy/proxy.go:170] [new connection] [connID=375] [client_addr=10.233.105.34:46146]
[2023/07/17 08:38:24.629 +00:00] [WARN] [main.proxy.conn.be.authenticator] [backend/authenticator.go:148] [frontend may not support plugin auth] [connID=375] [client_addr=10.233.105.34:46146] [capability=CLIENT_LONG_PASSWORD|CLIENT_LONG_FLAG|CLIENT_LOCAL_FILES|CLIENT_PROTOCOL_41|CLIENT_INTERACTIVE|CLIENT_TRANSACTIONS|CLIENT_SECURE_CONNECTION|CLIENT_MULTI_STATEMENTS|CLIENT_MULTI_RESULTS]
[2023/07/17 08:38:24.631 +00:00] [INFO] [main.proxy.conn.be] [backend/backend_conn_mgr.go:229] [connected to backend] [connID=375] [client_addr=10.233.105.34:46146] [ns=default] [backend_addr=tc-tidb-0.tc-tidb-peer.testbed-endless-client-5lgf5.svc:4000]
[2023/07/17 08:38:24.658 +00:00] [INFO] [main.proxy] [proxy/proxy.go:181] [connection closed] [connID=375] [client_addr=10.233.105.34:46146]

4. What is your version? (Required)

[djshow832]

Prerequisite:

• The user is created with caching_sha2_password.

The reason:

• The client doesn't support ClientPluginAuth and TiProxy adds the capability for it because of backend: always set ClientPluginAuth #222
• TiProxy sends AuthSwitchRequest but the client doesn't recognize it and thinks the handshake succeeds
• The client resets the packet sequence and sends a SQL to TiProxy but TiProxy doesn't reset the sequence and thinks that the sequence doesn't match

I think there's no perfect way to work around if we keep #222.

[djshow832]

MySQL 5.5.7 supports ClientPluginAuth: https://downloads.mysql.com/docs/mysql-5.5-relnotes-en.pdf
So TiDB only supports MySQL Client 5.5.7+.