SQLSTATE[hy000][2054] The server requested authentication method unknown to the client

1

Using MySQL version 8.0.11, latest version of Dreamfactory.
Got the above error with root and an admin account I created.

2

More info: Windows 7.DreamFactory installed from the download installer.

3

Hi @Gunnar_Swan

Can you check your php.ini file to see if the file “extension=mysqli” is commented?

Also, is this a remote database (not the DF system database)?

4

Eric,

Thank you for your time on this. Trying to do as much as I can without interfering.

To answer your questions …

  • mysqli is not commented. mysql is commented.
  • This MySQL is not the DF system database. It is located on my development machine.
  • but maybe this is a good question.

Steps To Replicate Issue

  • Open browser to http://127.0.0.1:81
  • Login
  • Click ‘Schema’ button
  • Select a Service I previously created … ‘test3’, a MySQL Service.
  • I then get the error. (you should see a screenshot of the error)

Evidence about PHP version on box …

From the php.ini in the folder shown below.

extension=C:/PHP/php-5.6.10-nts-vc11-x86/ext/php_mysqli.dll

;extension=php_mysql.dll

To assure I got the right INI from the right PHP version …

From phpinfo()

Loaded Configuration File
C:\PHP\php-5.6.10-nts-vc11-x86\php.ini

Path From Environment:

C:\PHP\php-5.6.10-nts-vc11-x86;

PHP Version From Command Line

C:>php --version
PHP 5.6.10 (cli) (built: Jun 10 2015 15:59:37)
Copyright © 1997-2015 The PHP Group
Zend Engine v2.6.0, Copyright © 1998-2015 Zend Technologies

5

@Gunnar_Swan

Thanks for the additional details. Will update ASAP.

6

HI Gunnar,

I believe I see the problem. As of 2.11.1 you need to be on PHP 7.x or 7.1.x. Can you install PHP 7 and then unlink the 5.6? That should solve your issues. Here are some helpful links:

http://php.net/manual/en/install.windows.requirements.php

https://windows.php.net/download

http://pecl.php.net/

Hope this is helpful.

7

I’ll get on it and will advise. Thx.

1 Like
8

For some reason it still will not authenticate even with PHP 7.x.
Since ultimately I am using SQL Server (and using the community version is just a proof of concept), I switched to PostgreSQL and it worked. PostgreSQL is ANSI complian, so the DDF statements are nearly identical to SQL Server.

Moving to PostgreSQL, all tests are passing, so MySQL is no longer an issue. Thanks!

9

Based on the given information I assume you are running DreamFactory version 2.12 and with the release of MySQL 8 it introduced some new security protocols that created compatibility issues with PHP 5.6. This has all been sorted in new releases of DreamFactory as we now require PHP 7 to run the application.

Alternatively, since MySQL 8 default authentication plugin is auth_socket you can change it to the original native password authentication. The command below will change the user you are connecting to DreamFactory with to utilize standard authentication.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password
BY 'password';

As a side note, if you would like this change to be made global for all your MySQL users you can edit the MySQL my.cnf file and under the mysqld section add the following:

[mysqld]
default_authentication_plugin= mysql_native_password

and be sure to flush the privileges:

FLUSH PRIVILEGES;