Tidb: Can't connect using mysql client (using password: NO)

[OuSatoru]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   connect using mysql -h x.x.x.x -P 4000 -u root -p mysql -h x.x.x.x -P 4000 -u root -p mypassword mysql -h x.x.x.x -P 4000 -u root -pmypassword

2. What did you expect to see?
   connect success

3. What did you see instead?
   Access denied for user 'root'@'xx.xx.xx.xx' (using password: NO)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v3.0.7
   Git Commit Hash: 84e4386c7a77d4b8df5db7f2303fb7fd3370eb9a
   Git Branch: HEAD
   UTC Build Time: 2019-12-04 10:08:24
   GoVersion: go version go1.13 linux/amd64
   Race Enabled: false
   TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
   Check Table Before Drop: false

mysql -V
mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)

Can connect when tidb installed without password.
Can't connect after set password with SET PASSWORD FOR 'root'@'%' = 'xxx';
Can connect using database tools like DataGrip.

[crazycs520]

@OuSatoru which tikv,pd version you used, cann't reproduce this problem in 84e4386 version of tidb-server with mockTiKV.

[OuSatoru]

@OuSatoru which tikv,pd version you used, cann't reproduce this problem in 84e4386 version of tidb-server with mockTiKV.

I successfully logged in using mysql 5.7 client, while 8.0 client can't log in.

[crazycs520]

@OuSatoru, I can't reproduce this issue with mysql Ver 8.0.18 for Linux on x86_64 too.
which pd, tikv version you used?

[kennytm]

Reproducible on MySQL 8 client bundled with Ubuntu with master version of TiDB.

The problem is authentication plugin mismatch. While TiDB wants the client to authenticate using mysql_native_password, the client still replies using caching_sha2_password which TiDB doesn't support yet (#9411).

You can force the MySQL client to use the native password plugin with:

mysql --default-auth=mysql_native_password -h ::1 -P 4000 -u user -pPassword ...
#     ^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[OuSatoru]

Thank you very much! I didn't see the question! My mysql version was "mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)" on Centos7, successfully connect to tidb with _mysql_native_password_!

[nullnotnil]

This has also been added to the TiDB FAQ.

With the root cause identified, I am going to go ahead and close this issue. Thanks!