Tidb: Fail to connect to TiDB using the mysql 8.0 client

[meilihao]

1. What did you do?

$ mysql --version
mysql  Ver 8.0.12 for Linux on x86_64 (MySQL Community Server - GPL)
$ mysql -h 47.100.x.x -p -P 4000 -u root

1. What did you expect to see?
   mysql client works.

2. What did you see instead?

$ mysql -h 47.100.x.x -p -P 4000 -u root
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'221.12.20.22' (using password: YES)

tidb.log:

2018/09/04 17:43:20.146 terror.go:345: [error] lookup 22.20.12.221.in-addr.arpa. on 100.100.2.136:53: no such host
/home/jenkins/workspace/build_tidb_master/go/src/github.com/pingcap/tidb/session/session.go:1041: 
2018/09/04 17:43:20.146 session.go:1032: [error] User connection verification failed [email protected]
2018/09/04 17:43:31.199 gc_worker.go:293: [info] [gc worker] leaderTick on 596279b443c0003: gc interval (10m0s) haven't past since last run (2018-09-04 17:35:31 +0800 CST). no need to gc

And i found mysql-client-5.7 is ok:

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v2.1.0-rc.1-22-g55af7e1
Git Commit Hash: 55af7e147269dac05a9741aa461e736a264520c9
Git Branch: master
UTC Build Time: 2018-08-29 03:42:01
GoVersion: go version go1.11 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false 

[meilihao]

MariaDB client 5.5 is ok too.

mysql --version
mysql  Ver 15.1 Distrib 5.5.35-MariaDB, for Linux (x86_64) using readline 5.1

[zhexuany]

Per our source code, no such host is returned by net.LookupAddr(). It seems such ip cannot be accessed from your host.

[meilihao]

i show my public ip(221.12.20.22).

I don't know why connetion need net.LookupAddr("22.20.12.221"). what's different between mysql clinet 8.0 and client 5.x?

[zhexuany]

My bad, I overlook 8.0 version. Currently, TiDB is compatible with 5.7. ~https://github.com/pingcap/tidb/issues/6942 is a known issue~. ~Closing due to duplicate.~ Seems to be two different problems.

[meilihao]

duplicate? @zhexuany my question has nothing to do with the character set. It should be another problem.

[zhexuany]

func getHostByIP(ip string) []string {
    if ip == "127.0.0.1" {
        return []string{"localhost"}
    }
    addrs, err := net.LookupAddr(ip) // line 1040
    terror.Log(errors.Trace(err))
    return addrs
}

Above code snippet is where the err comes from. According to the comment of LookupAddr, and I quote below:

// LookupAddr performs a reverse lookup for the given address, returning a list
// of names mapping to that address.
//
// When using the host C library resolver, at most one result will be
// returned. To bypass the host resolver, use a custom Resolver.

A easy checking is writing a main.go calling net.LookupAddr("22.20.12.221") to see whether the error still persists or not.

[zhexuany]

src/net/net.go
563:    errNoSuchHost = errors.New("no such host")


▶ ag errNoSuchHost src/net
src/net/cgo_unix.go
164:            err = errNoSuchHost

src/net/lookup_test.go
868:    if !strings.HasSuffix(err.Error(), errNoSuchHost.Error()) {
869:        t.Fatalf("lookup error = %v, want %v", err, errNoSuchHost)

src/net/dnsclient_unix.go
198:                Err:    errNoSuchHost.Error(),
266:                return dnsmessage.Parser{}, "", &DNSError{Err: errNoSuchHost.Error(), Name: name, Server: server}
365:        return dnsmessage.Parser{}, "", &DNSError{Err: errNoSuchHost.Error(), Name: name}
541:        // See comment in func lookup above about use of errNoSuchHost.
542:        return nil, dnsmessage.Name{}, &DNSError{Err: errNoSuchHost.Error(), Name: name}

src/net/lookup_windows.go
20:     return errNoSuchHost

src/net/net.go
563:    errNoSuchHost = errors.New("no such host")

src/net/lookup.go
167:        return nil, &DNSError{Err: errNoSuchHost.Error(), Name: host}
195:        return nil, &DNSError{Err: errNoSuchHost.Error(), Name: host}

src/net/lookup_plan9.go
150:            err = errNoSuchHost

src/net/dnsclient_unix_test.go
668:        {false, &DNSError{Name: fqdn, Err: errNoSuchHost.Error()}},
1140:           Err:    errNoSuchHost.Error(),
1509:   if de.Err != errNoSuchHost.Error() {
1510:       t.Fatalf("Err = %#v; wanted %q", de.Err, errNoSuchHost.Error())

It is likely to be a dns resolver problem.

[meilihao]

Yes, I tried and got the error.

What I don't understand is why two public ip communications need it.

[zhexuany]

getHostByIP is called under *session.Auth. I guess this is how MySQL handle authentification, we just adopt it from MySQL. @tiancaiamao Could you please confirm this? I believe you are the point person point regarding this particular issue.

[tiancaiamao]

Have you ever change the root password? @meilihao
Make sure you're using the right password, I guess that's the problem.
Don't bother with the "no such host" in the error message, it's misleading.

[zhexuany]

If no such host message is misleading, why don't we fail fast when we once know the password is wrong? There is some room for optimizing.

[meilihao]

@tiancaiamao I am sure I have used the correct password.

[tiancaiamao]

So I suspect it's caused by this:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

For RPM packages and Docker RPM packages, the included my.cnf file now includes information indicating how to revert to the previous default authentication plugin (changing caching_sha2_password to mysql_native_plugin), for compatibility with older clients. (Bug #27454015, Bug #27675380)

MySQL 8.0 changes the default authentication plugin from mysql_native_password to caching_sha2_password.

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

We'll take a look to confirm that, thanks for your feedback. @meilihao

[meilihao]

Last weak, i upgraded tidb to 2.1 and got the same error on mysql client 8.0.13.

Today i find that connect tidb with --default-auth=mysql_native_password is ok:

mysql -h xxx.net -u root -p -P 4000 --default-auth=mysql_native_password

[tiancaiamao]

I think this should be added to FAQ for user to use mysql 8.0 client. @shenli
In addition to --default-auth=mysql_native_password , there is also a --default-character-set option that matters.