WP-CLI in Github Actions - "Error establishing a database connection."

Question

I learn gh actions and try install wordpress via wp-cli. This is my workflow yml file:

name: WordPress install

on: [workflow_dispatch]

jobs:
  run:    
    runs-on: ${{ matrix.operating-system }}
    strategy:      
      matrix:
        operating-system: [ubuntu-latest]
        php-versions: ['7.2']
    name: PHP ${{ matrix.php-versions }} Test on ${{ matrix.operating-system }}
    steps:
    - name: Checkout
      uses: actions/checkout@v1

    - name: Setup PHP
      uses: shivammathur/setup-php@v1
      with:
        php-version: ${{ matrix.php-versions }}
        extension-csv: mbstring, intl #optional, setup extensions
        ini-values-csv: post_max_size=256M, short_open_tag=On #optional, setup php.ini configuration
        coverage: xdebug #optional, setup coverage driver
        pecl: false #optional, setup PECL
        
    -   name: Set up MySQL
        run: |
            sudo /etc/init.d/mysql start
            
    -   name: Install wp
        run: |
            curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
            php wp-cli.phar core download
            echo 1
            php wp-cli.phar config create --dbname=wordpress_test --dbuser=root --dbpass=root --dbhost=127.0.0.1 --dbprefix=wptests_
            echo 2
            php wp-cli.phar db create
            php wp-cli.phar db check
            echo 30
            mysql --user="root" --password="root" --execute='show databases;'
            echo 31
            php wp-cli.phar db query 'SHOW TABLES' --allow-root
            echo 32
            php wp-cli.phar db tables
            echo 3
            php wp-cli.phar core install --url=wpclidemo.dev --title="WP-CLI" --admin_user=wpcli --admin_password=wpcli [email protected]
            echo 4
            php wp-cli.phar option get siteurl
            echo 5

But i'm getting this result in console

Success: WordPress downloaded.
1
Success: Generated 'wp-config.php' file.
2
Success: Database created.
Success: Database checked.
30
Warning: arning] Using a password on the command line interface can be insecure.
Database
information_schema
mysql
performance_schema
sys
wordpress_test
31
32
Error: Error establishing a database connection.
Error: Process completed with exit code 1.

So direct query via mysql works but not via wp-cli. And it's working fine on my local PC

What could be the reason for the error?

Demo in github actions here

Answer 1

I enabled debug mode in wp-config.php and got errors

PHP Warning:  mysqli_real_connect(): (HY000/2054): 
The server requested authentication method unknown to the client in /home/runner/work/gh-actions-wp-demo/gh-actions-wp-demo/wp-includes/class-wpdb.php on line 2019
Error: `The server requested authentication method unknown to the client`

Fixed with this line in workwlow.yml

mysql --user="root" --password="root" --execute="ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';"