WordPress Error establishing a MySQL database connection IIS10

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

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
Are you sure that you have typed the correct hostname?
That's what I'm trying below
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?

Your host does not seem to be the problem ... leave it to localhost. Make sure DB_NAME, DB_USER, DB_PASSWORD is well defined. Maby a type in one of them? — Patrick Simard, Dec 30 '18 at 16:41
You keep overwriting those, so only the last one takes effect. — Funk Forty Niner, Dec 30 '18 at 16:47
@PatrickSimard: I was indeed listing what I tried :-), I also rechecked name/user/password and literally copied them in workbench to test and it works there, so no typos. What else could it be? I'm running MySQL as a Windows service if that matters. — Adam, Dec 31 '18 at 15:26
Have you checked if your DB is up and running, listens to the socket, and the user permissions permit the access from any host? — Eriks Klotins, Jan 02 '19 at 13:09
@Flo Remove the port in the `DB_HOST`. Its unnecessary. Aswell, try to create a simple PHP mysql connection via pdo or mysqli. — Xatenev, Jan 02 '19 at 13:36
Set allowed hosts to % (everyone) and see if it makes a difference. Also, create a simple standalone php file to check and troubleshoot your db connection — Eriks Klotins, Jan 02 '19 at 13:38
@Xatenev: I'm currently already testing with just `localhost`. Do you have a sample PHP file you recommend for me? I don't work with MySQL a lot :-). @Eriks: I tried changing it to `%`, but it does not change the error. — Adam, Jan 02 '19 at 13:49
@Flo https://stackoverflow.com/a/6549440/3223157 use the `new PDO(...)` line. Example: https://bpaste.net/show/4cecbad900d3 — Xatenev, Jan 02 '19 at 13:50
Have you tried creating a vanilla PHP project and connecting to the DB using PDO? Just to see if it's something WP end or if it's something between PHP + MySQL? — treyBake, Jan 02 '19 at 14:24
@treyBake: that's what I tried under update 2 right? If not: do you have an example I can use? — Adam, Jan 02 '19 at 14:32
@Flo kinda - but you're trying to create a database through it, connect to a database and try select test data from a table — treyBake, Jan 02 '19 at 14:32
Tried `$dbh->exec("SELECT * FROM myDB.wp_terms;") or die(print_r($dbh->errorInfo(), true));`, but the error remains `Server sent charset unknown to the client.` — Adam, Jan 02 '19 at 14:36
this help: https://stackoverflow.com/questions/43437490/pdo-construct-server-sent-charset-255-unknown-to-the-client-please-rep ? — treyBake, Jan 02 '19 at 14:43
You can still change the default charset doing so `ALTER DATABASE MyDb CHARACTER SET utf8;` — Ulrich Dohou, Mar 23 '19 at 23:02

score 4 · Accepted Answer · answered Jan 03 '19 at 14:34

4

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.

answered Jan 03 '19 at 14:34

Adam

6,041
36
120
208

Eriks Klotins · Answer 2 · 2019-01-02T14:40:18.367

1

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

edited Jan 02 '19 at 14:40

answered Jan 02 '19 at 14:16

Eriks Klotins

4,042
1
12
26

I suggest to leave wp alone and try to troubleshoot the problem with the standalone script. Have you checked you MySQL connection charset? – Eriks Klotins Jan 02 '19 at 14:40
That is what I tried under update 3 right? It says utf8 already. – Adam Jan 02 '19 at 14:41
I am not sure how exactly the wp-config handles charsets. That is why I suggested working with the standalone script where you have full control. Importantly, the script charset, the connection charset, and the DB charset have to be the same for things to work. – Eriks Klotins Jan 02 '19 at 14:44

WordPress Error establishing a MySQL database connection IIS10

2 Answers2