WordPress Error establishing a MySQL database connection IIS10 [duplicate]

Question

I'm trying to setup WordPress with MySQL on my local Windows 10 machine. I'm getting this error:

Error establishing a database connection This either means that the username and password information in your wp-config.php file is incorrect or we can't contact the database server at localhost:3307. This could mean your host's database server is down.

I already checked here: Error establishing a database connection on wordpress

1. Are you sure you have the correct username and password?
   Yes, I can login to SQL Server Workbench with the root credentials, so that is working
2. Are you sure that you have typed the correct hostname?
   That's what I'm trying below
3. Are you sure that the database server is running?
   Yes, checked in MySQL workbench and it's running

I ran this query select @@hostname which gives me: DESKTOP-CFT2ESY

I tried adding to wp-config.php (not all at the same time):

define('DB_HOST', 'localhost:3307');
define('DB_HOST', 'localhost:8899');
define('DB_HOST', 'localhost');
define('DB_HOST', 'DESKTOP-CFT2ESY');
define('DB_HOST', '127.0.0.1');

None of these work, it just changes the hostname string in the above error message.

I then added:
define('WP_ALLOW_REPAIR', true);
define('DB_CHARSET', 'utf8');

But that does not change the error message at all.

UPDATE 1

I ran SHOW GLOBAL VARIABLES LIKE 'PORT'; and my server runs on port 3306. In WorkBench I see my database is up and running. I also added all privileges for user root on my database schema. My user's Limit to hosts matching is set to localhost.

UPDATE 2

I then ran this PHP code which I found here (I just changed the root user's password to my root user's password):

<?php

$host="localhost"; 

$root="root"; 
$root_password="rootpass"; 

$user='newuser';
$pass='newpass';
$db="newdb"; 

    try {
        $dbh = new PDO("mysql:host=$host", $root, $root_password);

        $dbh->exec("CREATE DATABASE `$db`;
                CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
                GRANT ALL ON `$db`.* TO '$user'@'localhost';
                FLUSH PRIVILEGES;") 
        or die(print_r($dbh->errorInfo(), true));

    } catch (PDOException $e) {
        die("DB ERROR: ". $e->getMessage());
    }
?>

But this trows the error:

DB ERROR: SQLSTATE[HY000] [2054] Server sent charset unknown to the client. Please, report to the developers

UPDATE 3

I then ran query: SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "myDB"; which returns utf8, so the same character set as defined in my wp-config.php.

UPDATE 4
I checked here. I just had a mysqlrouter.conf.sample in my C:\Program Files\MySQL\MySQL Server 8.0\etc folder, so I added a my.cnf file with:

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

I restarted the MySQL windows service but the error remains the same.

What else can I try?

Answer 1

As commented by @treyBake please see here.

Basically MySQL 8 changed the default charset to utfmb4 and there are now errors with some clients. I downgraded to MySQL Server 5.6 and the problem is gone.

Answer 2

Look here for the potential solution:

PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers

In essence, you have to make sure that both the DB and the script uses the same charset, preferably utf-8

UPDATE: See also this:http://php.net/manual/en/mysqli.set-charset.php