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

[dotherightthing]

scripts
       wordpress-plugin
         test
           runs WordPress (PHP) unit tests:
      AssertionError: PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753
PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753: expected 'PHP Warning:  mysqli_real_connect(): …' to equal ''
      + expected - actual
      -PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753
      -PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753

      at Context.<anonymous> (file:///home/runner/work/wpdtrt-npm-scripts/wpdtrt-npm-scripts/test/tasks/scripts.spec.mjs:475:30)
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

[dotherightthing]

Maybe https://www.dpscomputing.com/blog/2022/04/19/mysqli_connect-the-server-requested-authentication-method-unknown-to-the-client/

[dotherightthing]

https://ostechnix.com/change-authentication-method-for-mysql-root-user-in-ubuntu/

The caching_sha2_password plugin performs authentication using SHA-256 password hashing. In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password. It provides more secure password encryption, faster authentication, and better performance.

Even though caching_sha2_password plugin provides enhanced secure encryption and better performance, it has some compatibility issues with existing MySQL installations and causes problems with some PHP versions. For details, refer this link.

If you encountered with any compatibility issues or if the client or connector doesn't support caching_sha2_password authentication plugin, revert back to native authentication method i.e. mysql_native_password as described in the following section.

1.2. Change authentication plugin to mysql_native_password

MySQL includes a mysql_native_password plugin that implements native pluggable authentication method. It provides authentication based on the password hashing method.

The mysql_native_password was the default authentication method in the older MySQL versions.

To change to mysql_native_password plugin, login to MySQL as root user:

$ mysql -u root -p

If you are using auth_socket plugin, you should enter the following command to login into MySQL server as root user:

$ sudo mysql

To set mysql_native_password plugin as default authentication for MySQL root user, run the following command at MySQL prompt.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';

Update the changes using command:

mysql> FLUSH PRIVILEGES;

[dotherightthing]

https://firefart.at/post/using-mysql-service-with-github-actions/

The Ubuntu image already contains a preconfigured MySQL server but if you want to use a specific or newer version or even a MariaDB server you need to use a service. A service in Github Actions is just a docker container running a specific image and exposing it’s ports to localhost. You can also install the services you need via apt-get but using Docker might be easier in this case.

[dotherightthing]

Or? https://github.com/sleeyax/mysql-native-password

[dotherightthing]

Or use the bitnami mysql image: https://stackoverflow.com/a/62651774

[dotherightthing]

Or: https://github.com/atk4/data/blob/3.0.0/.github/workflows/test-unit.yml

    services:
      mysql:
        image: mysql:8
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=5 -e MYSQL_ROOT_PASSWORD=atk4_pass -e MYSQL_USER=atk4_test -e MYSQL_PASSWORD=atk4_pass -e MYSQL_DATABASE=atk4_test__data --entrypoint sh mysql:8 -c "exec docker-entrypoint.sh mysqld --default-authentication-plugin=mysql_native_password"

[dotherightthing]

Added the bitnami image. Connection seems to work now, however still getting this error:

 1) scripts
       wordpress-plugin
         test
           runs WordPress (PHP) unit tests:

      /home/runner/work/_temp/wordpress
      + expected - actual

      -false
      +true

      at Context.<anonymous> (file:///home/runner/work/wpdtrt-npm-scripts/wpdtrt-npm-scripts/test/tasks/scripts.spec.mjs:465:30)
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

[dotherightthing]

Error was hiding missing environmental variables. After resolving these, the original error still persists:

  1) scripts
       wordpress-plugin
         test
           runs WordPress (PHP) unit tests:

      AssertionError: PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753
PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753: expected 'PHP Warning:  mysqli_real_connect(): …' to equal ''
      + expected - actual

      -PHP Warning:  mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password] in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753
      -PHP Warning:  mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client in /home/runner/work/_temp/wordpress/wp-includes/wp-db.php on line 1753

      at Context.<anonymous> (file:///home/runner/work/wpdtrt-npm-scripts/wpdtrt-npm-scripts/test/tasks/scripts.spec.mjs:486:30)
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)

[dotherightthing]

In wp-db.php, db_connect() sets the mysqli_real_connect $port argument to null. Does this mean that it isn't aware of the Github Actions mysql service which is configured to run on port 3306/tcp ?

[dotherightthing]

The standard value for the MySQL port of your database is: 3306.

The default MySQL port is TCP (Transmission Control Protocol).

https://www.forge12.com/en/blog/default-mysql-port/

[dotherightthing]

load.php:

function require_wp_db() {
    global $wpdb;

    require_once ABSPATH . WPINC . '/wp-db.php';
    if ( file_exists( WP_CONTENT_DIR . '/db.php' ) ) {
        require_once WP_CONTENT_DIR . '/db.php';
    }

    if ( isset( $wpdb ) ) {
        return;
    }

    $dbuser     = defined( 'DB_USER' ) ? DB_USER : '';
    $dbpassword = defined( 'DB_PASSWORD' ) ? DB_PASSWORD : '';
    $dbname     = defined( 'DB_NAME' ) ? DB_NAME : '';
    $dbhost     = defined( 'DB_HOST' ) ? DB_HOST : '';

    $wpdb = new wpdb( $dbuser, $dbpassword, $dbname, $dbhost );
}