MySQL 8.0.4 can't connect

[bscheshirwork]

I try to use fresh mysql. I run tests. I have errors:

Next PDOException: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client in /var/www/html/framework/db/Connection.php:686
Stack trace:
#0 /var/www/html/framework/db/Connection.php(686): PDO->__construct('mysql:host=mysq...', 'travis', 'travis', NULL)
#1 /var/www/html/framework/db/Connection.php(612): yii\db\Connection->createPdoInstance()
#2 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(137): yii\db\Connection->open()
#3 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(50): yiiunit\framework\rbac\DbManagerTestCase::createConnection()
#4 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(82): yiiunit\framework\rbac\DbManagerTestCase::runConsoleAction('migrate/up', Array)
#5 /repo/vendor/phpunit/phpunit/src/Framework/TestSuite.php(702): yiiunit\framework\rbac\DbManagerTestCase::setUpBeforeClass()
#6 /repo/vendor/phpunit/phpunit/src/TextUI/TestRunner.php(546): PHPUnit\Framework\TestSuite->run(Object(PHPUnit\Framework\TestResult))
#7 /repo/vendor/phpunit/phpunit/src/TextUI/Command.php(195): PHPUnit\TextUI\TestRunner->doRun(Object(PHPUnit\Framework\TestSuite), Array, true)
#8 /repo/vendor/phpunit/phpunit/src/TextUI/Command.php(148): PHPUnit\TextUI\Command->run(Array, true)
#9 /repo/vendor/phpunit/phpunit/phpunit(53): PHPUnit\TextUI\Command::main()
#10 {main}
Caused by
PDOException: PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in /var/www/html/framework/db/Connection.php:686
Stack trace:
#0 /var/www/html/framework/db/Connection.php(686): PDO->__construct('mysql:host=mysq...', 'travis', 'travis', NULL)
#1 /var/www/html/framework/db/Connection.php(612): yii\db\Connection->createPdoInstance()
#2 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(137): yii\db\Connection->open()
#3 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(50): yiiunit\framework\rbac\DbManagerTestCase::createConnection()
#4 /var/www/html/tests/framework/rbac/DbManagerTestCase.php(82): yiiunit\framework\rbac\DbManagerTestCase::runConsoleAction('migrate/up', Array)
#5 /repo/vendor/phpunit/phpunit/src/Framework/TestSuite.php(702): yiiunit\framework\rbac\DbManagerTestCase::setUpBeforeClass()
#6 /repo/vendor/phpunit/phpunit/src/TextUI/TestRunner.php(546): PHPUnit\Framework\TestSuite->run(Object(PHPUnit\Framework\TestResult))
#7 /repo/vendor/phpunit/phpunit/src/TextUI/Command.php(195): PHPUnit\TextUI\TestRunner->doRun(Object(PHPUnit\Framework\TestSuite), Array, true)
#8 /repo/vendor/phpunit/phpunit/src/TextUI/Command.php(148): PHPUnit\TextUI\Command->run(Array, true)
#9 /repo/vendor/phpunit/phpunit/phpunit(53): PHPUnit\TextUI\Command::main()
#10 {main}

# Local run unit tests
# for docker-compose CLI like this manual https://blog.jetbrains.com/phpstorm/2016/11/docker-remote-interpreters/
#
# Important: run `composer install` before first time use
# docker-compose -f ~/projects/yii2-phpunit-docker-composition.yml run --rm --entrypoint composer php update --no-dev -vvv
#
# run directly (can't terminate)
# docker-compose -f ~/projects/yii2-phpunit-docker-composition.yml run --rm --entrypoint /repo/vendor/bin/phpunit php
#
# use shell
# docker-compose -f ~/projects/yii2-phpunit-docker-composition.yml run --rm --entrypoint bash php
# /repo/vendor/bin/phpunit

version: '2'
services:
  php:
    image: bscheshir/codeception:php7.2.2-fpm-alpine-yii2 #contain phpunit
    volumes:
      - ./yii2/tests/data/config-docker.php:/var/www/html/tests/data/config.php # use named connection for db
      - ./yii2:/var/www/html #src and tests shared to container
      - ~/.composer/cache:/root/.composer/cache
    environment:
      TZ: Europe/Moscow
      XDEBUG_CONFIG: "remote_host=192.168.0.83 remote_port=9002 remote_enable=On"
      PHP_IDE_CONFIG: "serverName=codeception"
    depends_on:
      - pgsql
      - mysql
# example for pgsql
# also add ./tests/data/config.local.php
# <?php
# $config['databases']['pgsql']['dsn'] = 'pgsql:host=pgsql;port=5432;dbname=yii2advanced';
# $config['databases']['pgsql']['username'] = 'yii2advanced';
# $config['databases']['pgsql']['password'] = 'yii2advanced';
  pgsql:
    image: postgres:10.2
    environment:
      TZ: Europe/Moscow
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: yiitest
      POSTGRES_USER: postgres
    networks:
      default:
        aliases:
         - postgres
  mysql:
    image: mysql:8.0.4
    environment:
      TZ: Europe/Moscow
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: yiitest
      MYSQL_USER: travis
      MYSQL_PASSWORD: travis

bash-4.4# /repo/vendor/bin/phpunit tests/framework/rbac/MySQLManagerCacheTest.php

Additional info

Q	A
Yii version	2.0.15-dev
PHP version	7.2.2
Operating system

[samdark]

Likely not Yii problem: http://www.yiiframework.com/forum/index.php/topic/50805-cdbconnection-failed-to-open-the-db-connection-sqlstatehy000-2054-the-server-requested-authentication-method-unknown-to-the-client/

[bscheshirwork]

Connection to yiitest@0.0.0.0 failed. Unable to load authentication plugin 'caching_sha2_password'.

[bscheshirwork]

https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

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

[samdark]

As I said, it can't be fixed at framework level.

[bscheshirwork]

Yep. But this notes is helpful (for example: build a official Docker images)

Further, it is always possible to change ––default-authentication-plugin to a non-default value of your choice (At present, options other than caching_sha2_password are: mysql_native_password and sha256_password). For example, if ––default-authentication-plugin is set to mysql_native_password, the behavior of the MySQL 8.0.4 server is similar to MySQL 8.0.3 and prior. Correspondingly, libmysqlclient supports the MYSQL_DEFAULT_AUTH options for mysql_options() C API function. (For libmysqlclient based client tools available in MySQL packages, the ––default-auth command-line options achieves the same purpose.) The client-side option is a performance tool and saves an extra round-trip when used in tandem with ––default-authentication-plugin option on server side.

We also recommend updating libmysqlclient to MySQL 8.0.4 or later so that new default authentication plugin is supported by clients that use it.

[bscheshirwork]

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

I try to use old plugin

  mysql:
    image: mysql:8.0.4
    ports:
      - "3308:3306" #for external connection
    entrypoint: ['/entrypoint.sh', '--default-authentication-plugin=mysql_native_password'] # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
    # docker exec -ti projects_mysql_1 bash
    environment:
      TZ: Europe/Moscow
      MYSQL_ROOT_PASSWORD: secret
      MYSQL_DATABASE: yiitest
      MYSQL_USER: travis
      MYSQL_PASSWORD: travis

but users has no rights

docker exec -ti projects_mysql_1 bash

root@c8c97496e726:/# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
root@c8c97496e726:/# mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@c8c97496e726:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show create user travis
    -> ;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for travis@%                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'travis'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*7A0EBF5CB2B2C3DDE9D8F186A8F502941FE1A2BA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> show tables from yiitest;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
mysql> show create user root;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@%                                                                                                                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[bscheshirwork]

https://github.com/docker-library/mysql/issues/380