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

Handshake error connecting from client 8.0.17 to mysql server 5.1.7 #156

Open
ibrierley opened this issue Feb 25, 2020 · 11 comments
Open

Handshake error connecting from client 8.0.17 to mysql server 5.1.7 #156

ibrierley opened this issue Feb 25, 2020 · 11 comments

Comments

@ibrierley
Copy link

See also this issue...

perl5-dbi/DBD-mysql#320 (comment)

Which has a stack trace, version info etc.

@cBrou
Copy link

cBrou commented Jul 4, 2021

Same issue. Any updates?

@ibrierley
Copy link
Author

I gave up with Perl and Mariadb in the end, bit of a shame. I ended up removing mariadb and installing mysql on centos. I also ran an older version of mysql in a docker container in some cases to transition versions.

@pali
Copy link
Member

pali commented Jul 14, 2023

Here is an update: #190
This pull request implements a new connection parameter mariadb_auth_plugin which forces client to use specified authentication method when connecting to mysql server.

New mysql 8.x client versions use by default new authentication method not supported by the older mysql servers. Older servers support mysql_native_password auth method.

Example how to use this new mariadb_auth_plugin parameter with mysql_native_password method:

my $dbh = DBI->connect("DBI:MariaDB:$db", $user, $pass, { mariadb_auth_plugin => 'mysql_native_password' });

@cBrou or @ibrierley or @ShyLionTjmn Could you test this change if it helps for you?

@pali
Copy link
Member

pali commented Jul 23, 2023

Above mentioned pull request add an option mariadb_auth_plugin for setting password authentication method (plugin) and it does it via mysql_options(sock, MYSQL_DEFAULT_AUTH, auth_plugin) call. But unfortunately MySQL 8.x client library is buggy and this pull request does not help with connecting to MySQL 5.1 servers (without additional modifications).

MySQL 8.x client library ignores the MYSQL_DEFAULT_AUTH connection option set by library/program if the server does not announce CLIENT_PLUGIN_AUTH capability in greeting packet. CLIENT_PLUGIN_AUTH capability indicates that server may support other authentication methods than mysql_native_password.

MySQL client library since version 8.0.4 uses by default caching_sha2_password authentication method (previous versions used mysql_native_password).

So when MySQL 8.0.4+ client library try to connect to MySQL pre-5.5.7 servers (which do not support CLIENT_PLUGIN_AUTH capability), library fallback to default authentication (which is caching_sha2_password) and because caching_sha2_password is not supported by MySQL pre-5.5.7 servers, the connection fails with ERROR 1043 (08S01): Bad handshake.

This is a clear bug in MySQL 8.0.4+ client library and I have not found any way how to workaround it without patching client library itself.

Here is the proper fix for the MySQL 8.x client library which is part of the MySQL server package:

--- mysql8/sql-common/client.cc	2023-07-23 15:21:07.545622792 +0200
+++ mysql8/sql-common/client.cc	2023-07-23 15:37:47.929255681 +0200
@@ -3954,7 +3956,14 @@ int run_plugin_auth(MYSQL *mysql, char *
               mysql, auth_plugin_name, MYSQL_CLIENT_AUTHENTICATION_PLUGIN)))
       DBUG_RETURN(1); /* oops, not found */
   } else {
-    auth_plugin = &caching_sha2_password_client_plugin;
+    /*
+     * If CLIENT_PLUGIN_AUTH capability is not announced by server (pre-5.5.7)
+     * then only the old native_password_client_plugin is supported by server.
+     */
+    if (mysql->server_capabilities & CLIENT_PLUGIN_AUTH)
+      auth_plugin = &caching_sha2_password_client_plugin;
+    else
+      auth_plugin = &native_password_client_plugin;
     auth_plugin_name = auth_plugin->name;
   }
 

With this change also command line mysql utility from MySQL 8.x package is able to connect to MySQL 5.1 and 4.1 servers.

You should report this bug to the MySQL developers or Oracle support channel.

@pali
Copy link
Member

pali commented Jul 23, 2023

As here is modified patch for the MySQL 8.0.26+:

--- mysql8/sql-common/client.cc	2023-07-23 15:52:40.715822119 +0200
+++ mysql8/sql-common/client.cc	2023-07-23 15:54:02.628415270 +0200
@@ -5759,8 +5759,13 @@ static mysql_state_machine_status authsm
   if (ctx->auth_plugin_name == nullptr || ctx->auth_plugin == nullptr) {
     /*
       If everything else fail we use the built in plugin
+      If CLIENT_PLUGIN_AUTH capability is not announced by server (pre-5.5.7)
+      then only the old native_password_client_plugin is supported by server.
     */
-    ctx->auth_plugin = &caching_sha2_password_client_plugin;
+    if (mysql->server_capabilities & CLIENT_PLUGIN_AUTH)
+      ctx->auth_plugin = &caching_sha2_password_client_plugin;
+    else
+      ctx->auth_plugin = &native_password_client_plugin;
     ctx->auth_plugin_name = ctx->auth_plugin->name;
   }
 

@pali
Copy link
Member

pali commented Aug 13, 2023

This mysql bug is already tracked in mysql issue tracker: https://bugs.mysql.com/bug.php?id=90994

@pali
Copy link
Member

pali commented Oct 11, 2023

After discussion with MySQL developers, this MySQL 8 client library issue should be fixed in the upcoming October MySQL 8.0.35 release (I guess in the next week). With that fixed release it should be able to connect to the MySQL pre-5.5.7 servers with MySQL 8 client library again.

cc: @cBrou @ibrierley @ShyLionTjmn

@ibrierley
Copy link
Author

Great, thanks for the update.

@choroba
Copy link
Member

choroba commented Nov 13, 2023

Should be fixed in upstream by mysql/mysql-server@f05a2da

@choroba
Copy link
Member

choroba commented Nov 13, 2023

It should be part of MYSQL versions 8.0.35 and 8.2.0

@choroba
Copy link
Member

choroba commented Nov 13, 2023

Can anyone please verify it's OK now?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants