Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

using MySQL 5.5 and 5.6 clients connecting with a passwordless account to tidb fail #32334

Open
seiya-annie opened this issue Feb 14, 2022 · 7 comments · Fixed by #32338
Open
Assignees
Labels
found/automation Found by automation tests severity/minor sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.

Comments

@seiya-annie
Copy link

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

  1. download 5.5 and 5.6 version MySQL client
    dbdeployer downloads get-unpack mysql-5.5.62.tar.xz
    dbdeployer downloads get-unpack mysql-5.6.44.tar.xz
  2. create use nopw with no password,
    CREATE USER 'nopw'@'%' IDENTIFIED WITH mysql_native_password
  3. use 5.5 and 5.6 MySQL client connect to tidb nightly version(v5.5.0-nightly-20220208) with this "nopw" user

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

connect successully

3. What did you see instead (Required)

root@wkload-0:/upgrade-test# /root/opt/mysql/5.5.62/bin/mysql -u nopw -h tiup-peer -P3390
ERROR 2012 (HY000): Error in server handshake

4. What is your TiDB version? (Required)

v5.5.0-nightly-20220208

@seiya-annie seiya-annie added type/bug The issue is confirmed as a bug. sig/sql-infra SIG: SQL Infra severity/moderate found/automation Found by automation tests labels Feb 14, 2022
@seiya-annie
Copy link
Author

confirming from Dveeden:

  1. Failure is expected for MySQL 5.5 and 5.6 clients for accounts with caching_sha2_password. I've updated the test for this.
  2. There was an issue with MySQL 5.5 and 5.6 clients connecting to a passwordless account. The fix for tidb-server seems easy, but needs more testing.

@seiya-annie
Copy link
Author

@dveeden
Copy link
Contributor

dveeden commented Feb 14, 2022

/assign

@dveeden
Copy link
Contributor

dveeden commented Feb 14, 2022

Note that there is no issue with mysql_native_password as default authentication plugin (the default config)

This only affects the situation where:

  • default authentication plugin: caching_sha2_password
  • MySQL 5.5 or MySQL 5.6 client
  • User has no password set
  • User has mysql_native_password as authentication plugin

I think the severity of this could be considered as minor instead of moderate, but I don't think that changes anything.

ti-chi-bot pushed a commit that referenced this issue Mar 3, 2022

Verified

This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
close #32334
@djshow832
Copy link
Contributor

I'm opening it again because #32338 is reverted by #33142

@djshow832 djshow832 reopened this Mar 16, 2022
@dveeden
Copy link
Contributor

dveeden commented Mar 30, 2022

The problem we need to solve only affects if when all the following are true:

  • Account with no password set
  • Account with mysql_native_password as authentication method
  • MySQL 5.5 and MySQL 5.6 clients (MySQL 5.1 and earlier and MySQL 5.7 and later are fine)
  • TiDB servers with default_authentication_plugin='caching_sha2_password' (not the default, not common)

The problem is that TiDB sends an OK early on in the handshake, as there is no password to check. This is fine for MySQL 5.7 and later. As MySQL 5.1 and earlier doesn't support plugable authentication this is also fine.

The fix in #32338 is to send an auth-switch-request and waiting on a reply before sending an OK.

However #33142 reverted this fix as this caused an issue with clients that use Go 1.3 and older. This is issue #33141.

I would expect there to be more installations that use Go 1.3 or older than there are installations that are affected by this issue. So reverting the fix was the right thing to do.

To move forward we could:

  1. Say MySQL 5.7 or newer is required for when servers are using caching_sha2_password as default authentication plugin.
  2. Merge the fix again and document Go 1.4 or newer as required.
  3. Merge the fix again, but only send the auth-switch-request if the default authentication is set to caching_sha2_password. I think this makes the code more complex than needed.
  4. Use handshakeResponse41.Attrs (_client_name and _client_version) to respond in a compatible way. This also increases complexity quite a bit. ( https://dev.mysql.com/doc/refman/8.0/en/performance-schema-connection-attribute-tables.html )

The https://github.com/dveeden/tidb_client_test tool isn't able to find the incompatibility as it only tests MySQL clients. As many MySQL connectors are based on libmysqlclient, this is often fine. However Go and Java are some of the examples that are not based on libmysqlclient.

@dveeden
Copy link
Contributor

dveeden commented Mar 30, 2022

I've created a very basic test for the Go client: https://github.com/dveeden/tidb_client_test/tree/master/go-mysql-driver

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
found/automation Found by automation tests severity/minor sig/sql-infra SIG: SQL Infra type/bug The issue is confirmed as a bug.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

3 participants