View Issue Details

IDProjectCategoryView StatusLast Update
0027794mantisbtdb mysqlpublic2021-01-16 05:01
Reporterrogueresearch Assigned Todregad  
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionno change required 
Summary0027794: "The server requested authentication method unknown to the client" error with root with MySQL 8.0.22
Description

I'm doing a fresh install of:

https://mantisbt.org/builds/mantisbt-2.25.0-dev-master-a16bb24.tar.gz

on a basic/vanilla FAMP server.

As per the Admin Guide, after basic setup I go here:

http://yoursite/mantisbt/admin/install.php

and (after getting past 0027793) the setup page showed up.

I forget the exact field names, but for the "high-privileged database account" I used "root" and the correct password (mysql -u root -p works from ssh) but the mantis install would fail with:

Does administrative user have access to the database? ( The server requested authentication method unknown to the client )

I found the presence of the word "unknown" to be suspicious. It reminded me of similar authentication issues I had with other web apps with MySQL 8 (vs earlier versions). I found my solution here:

https://mantisbt.org/forums/viewtopic.php?p=68769#p68769

which was to use my already-created mantis user instead of root, though I had to give that user root-like permissions.

I'm not sure if there's a mantis bug here exactly. But at the very least it would be nice to improve the Admin Guide and/or the error message mantis produces. I could probably submit a patch to the Admin Guide, after some discussion here.

Thanks.

TagsNo tags attached.

Activities

dregad

dregad

2020-12-23 10:33

developer   ~0064832

I've been using Mantis with MySQL 8.0.22 for several months on my dev box, without any issues.

I just double-checked by installing with an account having caching_sha2_password authentication type, and database was created successfully, no errors or warnings.

According to PHP Documentation, caching_sha2_password, [...] is not recognized by the older PHP (mysqlnd) releases so maybe this is caused by your PHP version ?

As things stand, the problem is not reproducible on my end, but I would agree it may be useful to add a warning to the admin guide, referencing the case of using MySQL 8 with PHP <7.1.16 or < 7.2.4, if you can confirm this is the root cause of your problem.

In that case, the workaround would be to create a privileged account using mysql_native_password authentication e.g.

CREATE USER mantis_root@localhost IDENTIFIED WITH  mysql_native_password BY 'xxxxxxx';

Grant privileges as appropriate (see tohttps://mantisbt.org/docs/master/en-US/Admin_Guide/html-desktop/#admin.install.new)

rogueresearch

rogueresearch

2020-12-23 14:32

reporter   ~0064833

@dregad thanks for the fast response.

I'm using the following versions of things:

  • FreeBSD 12.2p1
  • mysql 8.0.22
  • PHP 7.4.13

My mantis user I created as follows:

CREATE USER 'mantis'@'localhost' IDENTIFIED WITH mysql_native_password BY '*****';
grant all privileges on bugtracker.* to 'mantis'@'localhost';

and indeed specifying that user as the "high-privileged database account" resulted in success.

So maybe something is different about my mysql root user? I'm no mysql expert, is there a way I can introspect the user?

I installed mysql very plainly:

pkg install mysql80-client-8.0.22_1 mysql80-server-8.0.22_1
sysrc mysql_enable=&quot;YES&quot;
service mysql-server start
mysql_secure_installation (followed prompts, answered mostly yes, except to first VALIDATE PASSWORD COMPONENT question)

Per the comment in the link you gave, I checked the list of supported authentication methods in the "Loaded plugins" row of the "mysqlnd" section in phpinfo(). I see: mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password

Should I be expecting to see caching_sha2_password in that list?

rogueresearch

rogueresearch

2020-12-23 14:48

reporter   ~0064834

After additional searching, I'm starting to see this mysql 8 password change is a well-known compatibility issue with PHP! But most of what I've read says it should be fixed in PHP 7.4.

I've also confirmed the difference between my mysql users:

> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mantis           | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
dregad

dregad

2020-12-24 06:01

developer   ~0064840

Last edited: 2020-12-24 06:03

well-known compatibility issue with PHP! But most of what I've read says it should be fixed in PHP 7.4.

Indeed I came to the same conclusion.

As mentioned, I also confirmed yesterday that I can make a successful connection using a user identified with caching_sha2_password plugin, both on PHP 7.4.13 and PHP 8.0.0.

Just to confirm this is not somehow caused by MantisBT or the underlying ADOdb library, you may want to check if you can connect successfully with your root user, using equivalent low-level API calls:

$c = mysqli_init();
mysqli_options($c, MYSQLI_READ_DEFAULT_GROUP, 0);
mysqli_real_connect($c, 'localhost', 'root', 'PASSWORD', null, 3306, '', 0);

EDIT: 2nd command is probably not necessary/relevant, but since ADOdb calls this, we might as well do exactly the same.

This works for me, and my root user is defined just like yours.

rogueresearch

rogueresearch

2020-12-26 12:43

reporter   ~0064846

So I tried those 3 lines, with the correct root password, and find the following in my apache error log:

PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /usr/local/etc/apache24/mantest.rogue-research.com/bugs/test.php on line 4
PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /usr/local/etc/apache24/mantest.rogue-research.com/bugs/test.php on line 4

Removing the middle line gives the exact same.

Which I guess means no mantis bug here.

What OS is your dev environment?

The original PHP bug seems to be this one:
https://bugs.php.net/bug.php?id=76243

dregad

dregad

2020-12-26 15:45

developer   ~0064847

Which I guess means no mantis bug here.

Indeed...

What OS is your dev environment?

Ubuntu 20.04 with PHP installed via ondrej's PPA

dregad

dregad

2020-12-30 06:14

developer   ~0064869

@rogueresearch, based on your feedback in 0027794:0064846, this is not a bug in MantisBT. I am therefore resolving this issue as "no change required".

Out of curiosity, if you happen to find the solution to the problem, please let me know.

rogueresearch

rogueresearch

2020-12-30 12:56

reporter   ~0064882

Could you share the list of supported authentication methods in the "Loaded plugins" row of the "mysqlnd" section in phpinfo() on your system please?

rogueresearch

rogueresearch

2021-01-04 16:04

reporter   ~0064926

I think it's a FreeBSD issue, and have created a ticket with them: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=252420

dregad

dregad

2021-01-04 16:16

developer   ~0064927

Could you share the list of supported authentication methods in the "Loaded plugins" row of the "mysqlnd" section in phpinfo() on your system please?

mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password