Page MenuHomePhabricator

MySQL 8.0.4+ uses "caching_sha2_password", which is incompatible with older clients and raises error 2054 ("The server requested authentication method unknown to the client") with virtually no setup guidance
Open, NormalPublic

Description

I'm configuring a new laptop and installed MySQL 8.0.11 "Oracle is Benevolent Edition". This may have been a mistake, but it's a mistake that normal users could easily make.

In the default configuration, I'm getting an error #2054, "The server requested authentication method unknown to the client") when trying to connect. Phabricator doesn't handle this specially so it looks like a generic connection failure. This is misleading because the server is running and, e.g., mysql -uroot ... works fine to connect to it.

Revisions and Commits

Restricted Differential Revision
rP Phabricator
D19454

Event Timeline

epriestley triaged this task as Normal priority.May 16 2018, 1:02 PM
epriestley created this task.

In MySQL 8.0.4:

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

...the default authentication plugin became caching_sha2_password. It seems like PHP clients can't use this yet (e.g., see https://bugs.php.net/bug.php?id=76243).

The fix appears to be to set:

[mysqld]
default_authentication_plugin=mysql_native_password

So we should likely catch error #2054 and raise guidance about this.

An adjacent issue is that the actual connection exception doesn't make it up to setup guidance. It's somewhat tricky to raise it through the stack since the mechanics are substantially delegated to a bunch of cluster connection management, but we should make more of an effort to get it up to top level.

This is involved, and default_authentication_plugin=mysql_native_password doesn't fix it.

I had to do something like this:

DROP USER root@localhost;
CREATE USER root@localhost IDENTIFIED WITH mysql_native_password BY '';

Then, for good measure, I did this stuff, although maybe it's just cargo culting:

GRANT ALL PRIVILEGES ON *.* TO root@localhost;
FLUSH PRIVILEGES;

My limited understanding here is that the account itself is using a newer password hash algorithm, so you can't access the account at all if the client doesn't understand the hash. This makes sense from a security point of view but feels curiously disruptive from a "give users error messages which point them toward success" sort of point of view.

For now, I'm going to land D19454, which is at least a moderate improvement here, and will show errors the right error message. It's possible that PHP will support caching_sha2_password before anyone else really installs MySQL 8 and moot this, or let us get away with a very simple piece of support text ("Upgrade PHP").

The companion "provide more guidance" patch would be something like this:

diff --git a/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php b/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php
index 0806615..8a816e9 100644
--- a/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php
+++ b/src/aphront/storage/connection/mysql/AphrontBaseMySQLDatabaseConnection.php
@@ -293,6 +293,8 @@ abstract class AphrontBaseMySQLDatabaseConnection
     $message = pht('#%d: %s', $errno, $error);
 
     switch ($errno) {
+      case 2054: // Unknown authentication method. See T13141.
+        throw new AphrontUnknownAuthenticationMethodQueryException($message);
       case 2013: // Connection Dropped
         throw new AphrontConnectionLostQueryException($message);
       case 2006: // Gone Away
diff --git a/src/aphront/storage/exception/AphrontUnknownAuthenticationMethodQueryException.php b/src/aphront/storage/exception/AphrontUnknownAuthenticationMethodQueryException.php
new file mode 100644
index 0000000..aa97b93
--- /dev/null
+++ b/src/aphront/storage/exception/AphrontUnknownAuthenticationMethodQueryException.php
@@ -0,0 +1,17 @@
+<?php
+
+final class AphrontUnknownAuthenticationMethodQueryException
+  extends AphrontQueryException {
+  
+  public function __construct($message) {
+    $instructions = pht(
+      "If you are running MySQL 8.0.4 or newer, the default authentication ".
+      "plugin has changed (to \"caching_sha2_password\"). This version of ".
+      "Phabricator does not suupport the newer authentication plugin.");
+    
+    $message = $message."\n\n".$instructions;
+
+    parent::__construct($message);
+  }
+    
+}

However, I'm hesitant to try to provide the "destroy and recreate your user accounts" guidance until some users start to hit this, since that's a pretty big hammer.

epriestley renamed this task from Recent (?) MySQL may raise Error 2054 ("The server requested authentication method unknown to the client") with little setup guidance to MySQL 8.0.4+ uses "caching_sha2_password", which is incompatible with older clients and raises error 2054 ("The server requested authentication method unknown to the client") with virtually no setup guidance.May 18 2018, 4:01 PM

The GRANT syntax also appears to have changed. We currently do this (in Phacility-specific code):

GRANT ... ON ... TO user@host IDENTIFIED BY password

In recent MySQL, it seems like this must be separate CREATE USER user@host IDENTIFIED BY password and GRANT ... ON ... TO user@host. I'm going to just update this.

GRANT ALL (above) also doesn't actually grant GRANT itself so now my root can't GRANT. Yikes.

I fixed this with:

  • skip_grant_tables=TRUE in my.cnf
  • Restart.
  • Update Grant_priv in mysql.user.
  • Get rid of skip_grant_tables.
  • Restart.

It also looks like DELETE FROM mysql.user ... is no longer good enough (in some cases?) and DROP USER ... has no [IF EXISTS] option, so this may take some finesse eventually. I just glossed over it for now.

epriestley added a revision: Restricted Differential Revision.Jul 20 2018, 9:44 PM
epriestley added a commit: Restricted Diffusion Commit.Jul 21 2018, 10:53 AM