Auth failed for u1@127.%

[djshow832]

Bug Report

Please answer these questions before submitting your issue. Thanks!

This is a bug introduced by #24991.

1. Minimal reproduce step (Required)

mysql -h127.1 -uroot -P4000
mysql> create user u1@'127.%';
Query OK, 0 rows affected (0.00 sec)

mysql -h127.1 -uu1 -P4000
ERROR 2000 (HY000): Unknown MySQL error

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

In MySQL and older TiDB versions, u1 can log in as usual.

3. What did you see instead (Required)

mysql -h127.1 -uu1 -P4000
ERROR 2000 (HY000): Unknown MySQL error

In TiDB log:

[2021/11/12 09:58:37.805 +08:00] [WARN] [conn.go:712] ["failed to check the user authplugin"] [conn=5] [error="Failed to get user record"]
[2021/11/12 09:58:37.806 +08:00] [ERROR] [conn.go:757] ["authSha packet read failed"] [conn=5] [error=EOF]
[2021/11/12 09:58:37.806 +08:00] [ERROR] [terror.go:307] ["encountered error"] [error=EOF] 

4. What is your TiDB version? (Required)

Release Version: None
Edition: Community
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[dveeden]

I wasn't able to reproduce this on my first try. This is on Linux. Can you share the version of the MySQL Client and TiDB?

Could you try with #29577 as that might fix this?

[dvaneeden@dve-carbon ~]$ mysql -h127.1 -uroot -P4000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-TiDB-v5.4.0-alpha-95-g31a4f6abb TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> create user u1@'127.%';
Query OK, 0 rows affected (0.02 sec)

mysql> ^DBye
[dvaneeden@dve-carbon ~]$ mysql -h127.1 -uu1 -P4000
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-TiDB-v5.4.0-alpha-95-g31a4f6abb TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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 USER(),CURRENT_USER();
+--------------+----------------+
| USER()       | CURRENT_USER() |
+--------------+----------------+
| u1@127.0.0.1 | u1@127.%       |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v5.4.0-alpha-95-g31a4f6abb
Edition: Community
Git Commit Hash: 31a4f6abbb2d32460aa844a9641af7c30a7d2cda
Git Branch: master
UTC Build Time: 2021-11-12 07:28:28
GoVersion: go1.16.8
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql> ^DBye
[dvaneeden@dve-carbon ~]$ mysql --version
mysql  Ver 8.0.27 for Linux on x86_64 (MySQL Community Server - GPL)

[djshow832]

mysql --version
mysql  Ver 8.0.25 for macos10.14 on x86_64 (Homebrew)

I'm trying again with #29577 .
This is a bug reported by a user and I reproduced it easily. But another guy also told me that he can't reproduce it. Maybe it only happens in specific situations.

[djshow832]

Great! It's just fixed by #29577 !
I tried it and it's OK now. And I revert the commit then the bug happens again.

[dveeden]

So #29577 fixes part of the problem. However this is still a slightly different issue left:

mysql> SELECT user,host,plugin,authentication_string FROM mysql.user;
+------+-------+-----------------------+-----------------------+
| user | host  | plugin                | authentication_string |
+------+-------+-----------------------+-----------------------+
| root | %     | mysql_native_password |                       |
| u1   | 120.% | mysql_native_password |                       |
+------+-------+-----------------------+-----------------------+
2 rows in set (0.00 sec)

mysql> ^DBye
[dvaneeden@dve-carbon ~]$ ~/opt/mysql/8.0.25/bin/mysql -h 127.1 -uu1 -P4000
ERROR 2000 (HY000): Unknown MySQL error
[dvaneeden@dve-carbon ~]$ ~/opt/mysql/8.0.27/bin/mysql -h 127.1 -uu1 -P4000
ERROR 1045 (28000): Access denied for user 'u1'@'127.0.0.1' (using password: NO)

In Wireshark I noticed that the 8.0.25 tries to login with caching_sha2_password and that 8.0.27 tries mysql_native_password.

[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.