tidb returns 'Unknown auth plugin' when some old clients to connect

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. deploy a tidb cluster
2. use mysql 5.1 client to connect the tidb
3. the client Connect success to v5.2.2, but can't connect to v5.3.0

related #27855 #27931

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

╭─tidb@localhost ~/tiup-cluster-config
╰─$ mysql --version                                                                                                                                                                    1 ↵
mysql  Ver 14.14 Distrib 5.1.73, for pc-linux-gnu (i686) using readline 5.1
╭─tidb@localhost ~/tiup-cluster-config
╰─$ mysql -u root -h 172.16.4.131 -P 4100
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25-TiDB-v5.2.2 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

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.

3. What did you see instead (Required)

╭─tidb@localhost ~/pingcap/tidb ‹master*›
╰─$ mysql --version                                                                                                                                                                    1 ↵
mysql  Ver 14.14 Distrib 5.1.73, for pc-linux-gnu (i686) using readline 5.1
╭─tidb@localhost ~/pingcap/tidb ‹master*›
╰─$ mysql -u root -h 172.16.4.131 -P 4100
ERROR 1105 (HY000): Unknown auth plugin

4. What is your TiDB version? (Required)

release-5.3(bdc9987)

[Defined2014]

@dveeden , could you take a look at this issue?

[dveeden]

/assign

[dveeden]

I can reproduce the issue:

[dvaneeden@dve-carbon ~]$ mysql -h 127.0.0.1 -u root -P 4000 -e QUIT
[dvaneeden@dve-carbon ~]$ mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)
[dvaneeden@dve-carbon ~]$ ~/opt/mysql/5.1.73/bin/mysql -h 127.0.0.1 -u root -P 4000 -e QUIT
ERROR 1105 (HY000): Unknown auth plugin
[dvaneeden@dve-carbon ~]$ ~/opt/mysql/5.1.73/bin/mysql --version
/home/dvaneeden/opt/mysql/5.1.73/bin/mysql  Ver 14.14 Distrib 5.1.73, for unknown-linux-gnu (x86_64) using readline 5.1
[dvaneeden@dve-carbon ~]$ mysql -h 127.0.0.1 -u root -P 4000 -Ne "SELECT tidb_version()\G"
*************************** 1. row ***************************
Release Version: v5.3.0-alpha-1297-gbdc998737
Edition: Community
Git Commit Hash: bdc9987370d6d393cb8acae22e238e3350e448b5
Git Branch: HEAD
UTC Build Time: 2021-11-12 09:01:11
GoVersion: go1.16.8
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[bb7133]

@dveeden Thanks.

BTW I've tried MySQL client v5.1 with MySQL server v8.0, it gives an error:

ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client

So the trouble in my opinion is, we are on the halfway between MySQL 5.7 and 8.0, so there are features adopted from MySQL 8 but we have to be compatible with MySQL 5.7(expect no error for this case).

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.