Mysql 8.0: connect issue - "authentication method unknown to the client [caching_sha2_password]"

[sbulen]

Description

From this thread:
https://www.simplemachines.org/community/index.php?topic=574573.msg4066640#msg4066640

Two issues:

• The error shown to the user provides no helpful info. Had to insert a "die(mysqli_error($connection));" in there to get a meaningful error message to work with.
• The hidden error is:

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

The fix was to alter the user:
mysql> ALTER USER root IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

I believe this is the 2nd time we've seen this on the support forum - can't find the earlier thread...

Environment (complete as necessary)

• Version/Git revision: current
• Database Type: MySQL
• Database Version: 8.0
• PHP Version: 7.3

Additional information/references

Just looking at this the first time, but it looks like some mysql 8.0 instances need to be told to use the pre-8.0 "native" style of authentication:
https://dev.mysql.com/doc/refman/8.0/en/create-user.html
https://dev.mysql.com/doc/refman/8.0/en/authentication-plugins.html
https://dev.mysql.com/doc/refman/8.0/en/native-pluggable-authentication.html

[VBGAMER45]

What's the fix other than altering the user? Is it a PHP/MYSQL version connection issue? Is there a different way to connect?

Edit: Looks like fixed for PHP 7.4 :( Older PHP versions will require that. Bah hopefully they backport to 7.3

https://stackoverflow.com/questions/50026939/php-mysqli-connect-authentication-method-unknown-to-the-client-caching-sha2-pa

[sbulen]

At the very least, we need an actionable error message.

Yep, it does appear to be a gap in mysqli thru 7.3... If so, not much we can do other than provide a much clearer error message.

I believe this will affect 2.0 as well.

[albertlast]

And what should smf do?

[sbulen]

At the very least, we need an actionable error message.

[albertlast]

Is the error number known?

[SugarD-x]

This is a known issue with older versions of PHP and newer versions of MySQL Server. (I was following this particular one for quite some time myself from their end). The only fix for older versions is to revert the password system to the native one mentioned above, else you need to upgrade to a newer version of PHP. I forget the exact version, (you'll have to google it), but it was within the PHP 7.4.x series. The issue is that MySQL Server introduced a new password encryption method awhile back, and PHP made an attempt to support it in the 7.2.x series. This worked, however it was riddled with major issues that broke many specific setups. As a result, some time after PHP 7.2.8, this functionality was completely reverted temporarily, and wasn't fixed properly until much later in the PHP 7.4.x series. If you are using a version of PHP which does not support caching_sha2_password, using mysql_native_password is the only way around this issue.

Edit: SMF and other softwares should already be reporting that the method is unknown to them. There's a specific error message PHP will spit out if it is not supported. (It is listed in the first message above).

[sbulen]

Correct. For this to happen, 3 conditions must be met:

• Mysql 8
• php < 7.4(.something...)
• db user created with an authentication plugin other than mysql_native_password

The challenge here is that the installer uses SMF's normal DB routines. They operate in 1 of two modes: ignore errors & return control to the caller (the installer here) for a user-friendly message, or, full blown error logging & display. Since the installer hasn't built the system yet (no error table to log to, no caching system, etc...), it goes with ignoring the errors, and providing a simple friendly "Cannot connect to the database server with the supplied data.".

(The installer does attempt to query & display the errno, which normally works, but that is also failing here because there was no connection...)

At the moment, I can think of 2 options: add a 3rd error reporting mode in subs_db_mysql, or, clone & modify the connection functions for the installer. But these options, respectively, seem like overkill & hackish.

The error message displayed is correct...

Open to input.

[albertlast]

Got we an error code?

…

________________________________ Von: sbulen <notifications@github.com> Gesendet: Dienstag, 1. September 2020 05:46 An: SimpleMachines/SMF2.1 Cc: albertlast; Comment Betreff: Re: [SimpleMachines/SMF2.1] Mysql 8.0: connect issue - "authentication method unknown to the client [caching_sha2_password]" (#6242) Correct. For this to happen, 3 conditions must be met: * Mysql 8 * php < 7.4(.something...) * db user created with an authentication plugin other than mysql_native_password The challenge here is that the installer uses SMF's normal DB routines. They operate in 1 of two modes: ignore errors & return control to the caller (the installer here) for a user-friendly message, or, full blown error logging & display. Since the installer hasn't built the system yet (no error table to log to, no caching system, etc...), it goes with ignoring the errors, and providing a simple friendly "Cannot connect to the database server with the supplied data.". (The installer does attempt to query & display the errno, which normally works, but that is also failing here because there was no connection...) At the moment, I can think of 2 options: add a 3rd error reporting mode in subs_db_mysql, or, clone & modify the connection functions for the installer. But these options, respectively, seem like overkill & hackish. The error message displayed is correct... Open to input. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#6242 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AANTI6T7WZI6P27BLXETBWLSDRU7VANCNFSM4QPVX7GQ>.

[sbulen]

No. I bet you can google it...

[sbulen]

The problem isn't restricted to the installer... The app will do the same thing, with a generic authentication error message if something changes & you now meet the conditions specified above. It does the same thing across 2.0 & 2.1, app & installer.

[tinoest]

You should be able to move the connect to a try catch rather than suppressing the error.

I think that’s better for php 8 onwards anyway as they are changing that @ operator’s behaviour. Or I recall reading that.

[albertlast]

well the code should support stone age php 5.4 also,
so i guess you need to be careful what you doing...

[Butterflysaway]

Why not just support php 7+ You could get rid of a lot of ugly checks/function fillers. Nobody wants to run smf 2.1 on php 5 anymore. Especially since the only reason they upgrade to the latest smf is to get support for new php versions.

[VBGAMER45]

Kind of too far along in the process right now to stop PHP 5 support, since all the code already supports it. The next version a good chance. MyBB 1.8 requires PHP 5.2 or higher.

[tinoest]

https://www.php.net/manual/en/mysqli.connect-error.php

There is that function for connection errors btw, doesn’t need a valid database connection either. ( For obvious reasons )

[sbulen]

That looks tempting...

[albertlast]

https://www.php.net/manual/en/mysqli.connect-errno.php
would be better since it's undeppendet language

[tinoest]

Changing db_error to smf_db_error_check seems to work ok.

function smf_db_error_check($connection)
{
    global $db_connection;
    
    $connection = $connection === null ? $db_connection : $connection;
    
    if($connection)
    {
        return mysqli_error($connection);
    }   
    elseif(function_exists('mysqli_connect_error'))
    {
        return mysqli_connect_error();
    }   
    else
    {
        return false;
    }   
}

[jdarwood007]

If it produces a specific error number and we can detect the mysql server version, we could then present to the error message that this may be a possibility to their connectivity issues. But only for the installer or when in debug mode.

[sbulen]

The installer, upgrader & app all make the standard calls to smf_db_initiate in subs_db_*.php. In 2.0 & 2.1.

Given the recent changes in mysql, improving the reporting here is probably the right thing to do. It's no longer just "wrong id/password", it can also be any of a # of authentication policy issues, from expiration dates to authentication plugin not supported, etc... It feels like we are seeing more/deeper connection issues.