OroCommerce Forums

Covering OroCommerce topics, including community updates and company announcements.

Forums OroCommerce Oro (3.1.15) installation: Mysql (8.0.16) authent. method unknown to the client

This topic contains 11 replies, has 3 voices, and was last updated by  Frank 1 month ago.

  • Creator
    Topic
  • #38917

    Frank
    Participant

    Hi there,

    using installation guide like
    https://oroinc.com/orocrm/doc/current/install-upgrade/installation-quick-start-dev/platform

    php ./bin/console oro:install –env=prod –timeout=900
    terminates with a mysql authentication error:

    “An exception occurred in driver: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client.

    You can circumvent this by setting a ‘server_version’ configuration value

    For further information have a look at:

    https://github.com/doctrine/DoctrineBundle/issues/673


    I am using:

    – Server version: 8.0.16 MySQL Community Server – GPL
    – Centos 7

    In oroapp/config/config.yml – there was no doctrine/dbal-section before – I added at the end:

    doctrine:
    dbal:
    driver: pdo_mysql
    host: ‘%database_host%’
    port: ‘%database_port%’
    dbname: ‘%database_name%’
    user: ‘%database_user%’
    password: ‘%database_password%’
    server_version: 8.0.16
    charset: utf8mb4
    default_table_options:
    charset: utf8mb4
    collate: utf8mb4_unicode_ci

    oroapp/config/parameters.yml is:

    # This file is auto-generated during the composer install
    parameters:
    database_driver: pdo_mysql
    database_host: 127.0.0.1
    database_port: null
    database_name: oro
    database_user: root
    database_password: ****
    database_driver_options: { }
    mailer_transport: mail
    mailer_host: 127.0.0.1
    mailer_port: null
    mailer_encryption: null
    mailer_user: null
    mailer_password: null
    websocket_bind_address: 0.0.0.0
    websocket_bind_port: 8080
    websocket_frontend_host: ‘*’
    websocket_frontend_port: 8080
    websocket_frontend_path: ”
    websocket_backend_host: ‘*’
    websocket_backend_port: 8080
    websocket_backend_path: ”
    websocket_backend_transport: tcp
    websocket_backend_ssl_context_options: { }
    web_backend_prefix: /admin
    session_handler: session.handler.native_file
    locale: de_DE
    currency: EUR
    secret: ThisTokenIsNotSoSecretChangeIt
    installed: null
    assets_version: fbe5ba01
    assets_version_strategy: time_hash
    message_queue_transport: dbal
    message_queue_transport_config: null
    enable_price_sharding: false


    It is not working with root, or other oro.* ‘someuser’@’localhost’ having all privileges granted, and authentication checked successfully with:
    > mysql -uroot -p
    > mysql -u someuser -p

    It is not working with and without server-version in confi.yml.

    Obviously, installation procedure has some difficulties with MYSQL Server-version 8.0.16. But how to fix this?

    THanks a lot
    Frank

Viewing 11 replies - 1 through 11 (of 11 total)
  • Author
    Replies
  • #38926
    Michael
    Michael
    Keymaster

    Hi Frank,

    Have you tried specifying the server_version paremeter, as suggested in the comments to https://github.com/doctrine/DoctrineBundle/issues/673 in

    Thank you,
    Michael

    #38927

    Frank
    Participant

    Hi Michael,

    yes, I had (see above):

    in oroapp/config/config.yml – there was no doctrine/dbal-section before – I added at the end:

    doctrine:
    dbal:
    driver: pdo_mysql
    host: ‘%database_host%’
    port: ‘%database_port%’
    dbname: ‘%database_name%’
    user: ‘%database_user%’
    password: ‘%database_password%’
    server_version: 8.0.16
    charset: utf8mb4
    default_table_options:
    charset: utf8mb4
    collate: utf8mb4_unicode_ci

    My Server version (mysql -v) is 8.0.16.

    Thank you for help.

    Ragrds
    Frank

    #38928

    Frank
    Participant

    Replacing doctrine-key in oroapp/config/config.yml by only

    doctrine:
    dbal:
    connections:
    default:
    server_version: 8.0.16

    Does not changes anything (= same error).

    Regards
    Frank

    #38930
    Michael
    Michael
    Keymaster

    What is your PHP version?

    https://www.php.net/manual/en/ref.pdo-mysql.php

    When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server’s default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.

    This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release.

    #38931

    Frank
    Participant

    My Php-version is 7.1.29. KR Frank

    #38932

    Frank
    Participant

    Hi Michael,

    good idea – however

    (although my PHP-version is not related to the ref above, I tried to set the default_authentication_plugin=mysql_native_password in my.cnf),

    but it still does NOT work (same error).

    Regrads
    Frank

    #38933

    Frank
    Participant

    After:
    $ php ./bin/console oro:install –env=prod –timeout=900

    Full console-error-message is:

    In OroEntityExtendBundle.php line 130:

    In ConnectionFactory.php line 87:

    An exception occured while establishing a connection to figure out your platform version.

    You can circumvent this by setting a ‘server_version’ configuration value

    For further information have a look at:
    https://github.com/doctrine/DoctrineBundle/issues/673

    In AractMySQLDriver.php line 125:
    An Exception occurred in driver: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

    In PDOConnection.php line 50:
    SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

    In PDOConnection.php line 46:
    SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client

    In PDOConnection.php line 46:
    PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]


    Last row is very similiar to the mysql-setting
    ‘default_authentication_plugin=mysql_native_password’
    in my.cnf.

    However, as mentioned before: This was set, and the error is still there!!!

    Thank you for any help.
    Frank

    #38934
    Andrey Yatsenko
    Andrey Yatsenko
    Moderator

    I tried to set the default_authentication_plugin=mysql_native_password in my.cnf),
    but it still does NOT work (same error).

    It is required to restart the MySQL server after changing my.cnf, and change the authentication method for already created users, like:

    #38935

    Frank
    Participant

    Hi Andrey,

    1.
    of course, I have restarted mysqld, after setting authentication in my.cnf:

    [frank@localhost ~]$ my_print_defaults mysqlcheck mysqld
    –default-authentication-plugin=mysql_native_password
    –datadir=/var/lib/mysql
    –socket=/var/lib/mysql/mysql.sock
    –log-error=/var/log/mysqld.log
    –pid-file=/var/run/mysqld/mysqld.pid
    –character-set-server=utf8mb4
    –collation-server=utf8mb4_unicode_ci

    2.
    and I changed user registration
    a) as recommended by
    msql>
    ALTER USER ‘mysqlUsername’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘mysqlUsernamePassword’;
    b) and, in parallel, by registration of a new user (after my.cnf modification/restart).

    Nevertheless, the error remains …

    Any more ideas …?

    Thank you for help & kind regards
    Frank

    #38936

    Frank
    Participant

    Hi there,

    i tried to debug parameters:

    ./bin/console debug:container –parameter database_name
    … database_user
    … database_password

    All is ok as set in oroapp/config/parameters.yml.


    If I change parameters.yml to:
    database_user: nonsens

    I would expect another exception/error message, however it remains 100% the same (see above).


    Could you recommend me some debugging approach to monitor the authentication-process in
    oroapp/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php – class?

    Thanks a lot
    Frank

    #38940

    Frank
    Participant

    (This) Problem sems to be solved:

    I deleted oroapp-directory,and run
    composer create-project/orocommerce … command again.

    I think, it was a (oro/doctrine) cache-problem.

    The conclusion is:
    ==================
    * orocommerce-installation procedure is not adapted to mysql 8.0 Server yet:

    * Before (!) running ./bin/console oro:install – command,
    be sure, you have
    a) activated ‘default-authentication-plugin=mysql_native_password’ -option in /etc/my.cnf, and
    b) reset authentication-method for already existing users (root … etc ..):
    msql>
    ALTER USER ‘mysqlUsername’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘mysqlUsernamePassword’;

    Then, authentication should work, even with mysql-Server > 8.0.

    Regards
    Frank

Viewing 11 replies - 1 through 11 (of 11 total)

You must be logged in to reply to this topic.

You will be redirected to [title]. Would you like to continue?

Yes No