TiProxy is incompatible with go-sql-driver 1.3.0

[djshow832]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Start TiDB and TiProxy
2. Connect to TiProxy with go 1.3.0

import (
	"database/sql"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	mdb, err := sql.Open("mysql", "root@tcp(localhost:6001)/test")
	if err != nil {
		panic(err)
	}
	err = mdb.Ping()
	if err != nil {
		panic(err)
	}
}

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

No error

3. What did you see instead (Required)

Driver reports this user requires mysql native password authentication.

4. What is your version? (Required)

master.

The reason:

• Go-sql-driver sends the auth packet with mysql_native_password. But when TiProxy requests to switch auth method to mysql_native_password, go-sql-driver just reports ErrNativePassword. See https://github.com/go-sql-driver/mysql/blob/v1.3.0/packets.go#L495-L497. I think there's no way to avoid this problem on TiProxy side.
• Connecting to TiDB directly doesn't have this problem because TiDB won't send a switch auth request in this case.
• Go-sql-driver 1.4.0 works well.

[dveeden]

Why does TiProxy send an auth switch method if go-sql-driver sends a login with mysql_native_password? That should only happen if the target user isn't using mysql_native_password.

[xhebox]

Why does TiProxy send an auth switch method if go-sql-driver sends a login with mysql_native_password? That should only happen if the target user isn't using mysql_native_password.

Because clients handshaked with tiproxy first: clients got wrong salt data from tiproxy. So we must file auth-switch packets to let clients auth with the real server.

This is a hard limitation if tiproxy did not establish connections to server before handshaking with clients.

[dveeden]

Ah yes. That makes sense. Connecting to the backend when receiving a connection before responding to the client could fix this, but go-sql-driver v1.4.0 has been released many years ago so asking users to upgrade seems fine with me.

go-sql-driver v1.4.0 is also needed for caching_sha2_password support.

[xhebox]

Connecting to the backend when receiving a connection before responding to the client could fix this

Yes, that is the point. It is pretty much a problem of mysql protocol. I mean that, for mysql, server sent the first packet.. 🥲

[djshow832]

Connecting to the backend when receiving a connection before responding to the client could fix this

TiProxy did this before, but now TiProxy needs to connect to the correct TiDB server based on the user name, which is obtained in the response packet.
e.g. TiProxy connects to the right TiDB pod based on the user name in the serverless tier.