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

Question

I'm trying to connect to a MySQL database from Symfony 3 application. But when trying to create MySQL schema from a Symfony console command I get this error: PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers

Both PHP and MySQL are running in Docker containers.

MySQL version: 8.0.1

PHP version: 7.1.3

Driver: pdo_mysql

charset: UTF8

dsn: "mysql:host=mysql;dbname=database;charset=UTF8;"

Any ideas?

I had a similar error and upgraded to PHP 7.4 to fix it. – jbrahy May 18 '20 at 23:49 — jbrahy, May 18 '20 at 23:49

score 112 · Accepted Answer · edited Mar 02 '20 at 19:09

112

MySQL 8 changed the default charset to utf8mb4. But some clients don't know this charset. Hence when the server reports its default charset to the client, and the client doesn't know what the server means, it throws this error.

See also https://bugs.mysql.com/bug.php?id=71606

That bug is against the MySQL Connector/C++ so it's affecting more than just PHP.

Okay—I got it to work by changing the character set to utf8, to be compatible with non-upgraded clients. I added this to /etc/my.cnf and restarted mysqld:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

I found these settings in an answer from 2010: Change MySQL default character set to UTF-8 in my.cnf?

edited Mar 02 '20 at 19:09

Dharman

21,838
18
57
107

answered Apr 16 '17 at 22:30

Bill Karwin

462,430
80
609
762

It worked in my environment. MySQL 8.0.1, PHP 7.1.4. Did you restart the mysql service? – Bill Karwin Apr 18 '17 at 20:47
1

FYI: https://bugs.php.net/bug.php?id=74461 See the comment from nikic, apparently this has been fixed in PHP source, and I assume it will be released in the near future. – Bill Karwin Apr 18 '17 at 20:47
1

I had dockerized application. putting that configuration to /etc/mysql/conf.d solved the error. thanks. – ssi-anik Sep 27 '17 at 10:40
Stop mysql server if running, do as stated in the answer and start mysql server back. This way it worked for me. – Lahar Shah Oct 25 '18 at 15:08
I also ran this in mysql before I tried this answer, use correct {dbname} in terminal first run this: [1] > mysql -u root -p [2] > ALTER DATABASE {dbname} CHARACTER SET utf8 COLLATE utf8_general_ci; – Lahar Shah Oct 25 '18 at 15:11
On windows the file mysqlrouter.conf.sample was here C:\Program Files\MySQL\MySQL Server 8.0\etc. I added the above lines and removed the .sample. I then restarted the service (Control Panel -> Administrative Tools -> Services). – Guy Lowe Jun 09 '19 at 12:09
On windows the file is here C:\ProgramData\MySQL\MySQL Server 8.0\my.ini – Guy Lowe Jun 13 '19 at 00:50
I tried just adding the part to [mysql] and it didn't work by itself. I thought this should be the part that worked, but don't know. So I added the [client] and [mysql] elements and then it worked. *shrug* Thanks! – Erick Robertson Apr 14 '20 at 07:53

skwidbreth · Answer 2 · 2019-02-25T18:31:07.090

47

The accepted answer saved me (thanks, Bill!!!), but I ran into another related issue, just wanted to provide some details on my experience -

After upgrading to MySQL 8.0.11, I experienced the same problem as the OP when using PHP's mysqli_connect() function. In my MySQL directory (in my case, usr/local/mysql), I created the my.cnf file, added the content in the accepted answer, then restarted the MySQL server. However, this produced a new error:

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

I added the line default_authentication_plugin = mysql_native_password, so my.cnf now looked like:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
default_authentication_plugin = mysql_native_password

and I was good to go!

For additional reference: https://github.com/laradock/laradock/issues/1392

edited Feb 25 '19 at 18:31

answered Jun 11 '18 at 04:13

skwidbreth

5,908
5
48
86

5

Note you should create new users specifying "IDENTIFIED WITH mysql_native_password" explicitly, and alter existing users, e.g.: `ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';` – ARA1307 Aug 29 '18 at 05:19
Of course use your password in: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourrootpassword'; And beware: the change in authentication method in my.cnf will revert to the old password hashing system and is strongly discouraged – Stefan Verhagen Oct 14 '19 at 21:42
did finally work after I issued: ALTER USER root@'%' IDENTIFIED WITH mysql_native_password BY 'secretpassword'; – Stefan Verhagen Oct 14 '19 at 21:48

score 13 · Answer 3 · answered Nov 20 '19 at 16:52

My case was that i was using RDS (mysql db verion 8) of AWS and was connecting my application through EC2 (my php code 5.6 was in EC2).

Here in this case since it is RDS there is no my.cnf the parameters are maintained by PARAMETER Group of AWS. Refer: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

so what i did was:

Created a new Parameter group and then edited them.
Searched all character-set parameters. These are blank by default. edit them individually and select utf8 from drop down list.

character_set_client, character_set_connection, character_set_database, character_set_server

SAVE

And then most important, Rebooted RDS instance.

This has solved my problem and connection from php5.6 to mysql 8.x was working great.

hope this helps.

Please view this image for better understanding. enter image description here

Thank you. I've been searching for this sort of solution for two days. — Dennis Concepción Martín, Aug 18 '20 at 08:42

score 5 · Answer 4 · edited Jan 22 '19 at 16:31

5

Works for me >

the environment:

localhost
Windows 10
PHP 5.6.31
MYSQL 8

set:

default-character-set=utf8

on:

c:\programdata\mysql\mysql server 8.0\my.ini

Not works on> C:\Program Files\MySQL\MySQL Server 5.5\my.ini

Tools that helps:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p

mysql> show variables like 'char%';

mysql> show variables like 'collation%';

\m/

edited Jan 22 '19 at 16:31

Alicia

1,142
1
25
40

answered Jan 22 '19 at 15:10

reyqueson

111
2
6

jbrahy · Answer 5 · 2020-06-01T21:23:41.970

2

I had this problem also. The issue was because I was running an old version of PHP (5.6) and once I upgraded to PHP 7.4 the errors went away.

This was on Amazon Linux 2 for me and the command to upgrade was:

amazon-linux-extras install php7.4

Other flavors of *nix might have it in their repositories otherwise if you're on a flavor of Linux then Remi might have it for you here: https://rpms.remirepo.net/

edited Jun 01 '20 at 21:23

answered May 18 '20 at 23:49

jbrahy

3,447
33
45

score 1 · Answer 6 · edited Mar 02 '20 at 19:07

1

I was facing same issue so I have reinstall MySQL 8 with different Authentication Method "Use Legacy Authentication Method (Retain MySQL 5.x compatibility)" then work properly.

Choose Second Method of Authentication while installing.

edited Mar 02 '20 at 19:07

Dharman

21,838
18
57
107

answered Aug 13 '19 at 00:03

npsingh

53
1
7

score 0 · Answer 7 · 2018-08-23T01:40:26.460

0

find section [MySQLi] in your php.ini and add line mysqli.default_charset = "UTF-8". Similar changes my require for section [Pdo_mysql] and [mysqlnd].

Issue seems to be specifically with MySQL version 8.0, and above solution found working with PHP Version 7.2, and solution didn't work with PHP 7.0

edited Aug 23 '18 at 01:40

answered Aug 22 '18 at 16:16

The mysqli.default_charset isn't documented at http://php.net/manual/en/mysqli.configuration.php – Dereckson Sep 18 '18 at 19:16

score 0 · Answer 8 · answered Feb 21 '20 at 15:30

I had a very similar issue to this when testing a restored backup of a mysql and associated php system. No matter what configuration settings I added on mysql it was not making any difference. After troubleshooting the issue for some time I noticed in the mysql logs that the mysql config files were being largely ignored by mysql. The reason was due to the file permissions being too open which was due to the fact my zip backup and restore process was losing all file permissions. I modified my backup and restore scripts to use tar instead and then everything worked as it should.

In summary, check the file permissions on your mysql config files are correct. Hope this helps someone.

score 0 · Answer 9 · answered Feb 02 '21 at 11:42

In My Aws Windows I Solved it and steps are

Open "C:\ProgramData\MySQL\MySQL Server 8.0"
Open My.ini
Find "[mysql]"
After "no-beep=" Brake Line and insert "default-character-set=utf8"
Find "[mysqld]"
Insert "collation-server = utf8_unicode_ci"
Insert "character-set-server = utf8"

example

...
# socket=MYSQL

port=3306

[mysql]
no-beep=
default-character-set=utf8
# default-character-set=

# SERVER SECTION
# ----------------------------------------------------------------------
# 
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.=
# 
# server_type=2
[mysqld]

collation-server = utf8_unicode_ci
character-set-server = utf8
# The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking=
# enable-named-pipe=
# shared-memory=
...

score -1 · Answer 10 · answered Jun 11 '19 at 06:45

-1

I solved this problem by adding following code into my /etc/my.cnf file -

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

After saving that,I went into my system settings and stopped MYSQL server and started again, and it worked.

answered Jun 11 '19 at 06:45

hardik chugh

590
8
10

this is the exact same answer as the accepted answer – Isaac Jan 18 '21 at 22:43

score -2 · Answer 11 · answered Aug 05 '18 at 06:01

-2

I had the same issue to connect to local system.

I checked in the services list (Run->Services.msc->Enter) wampmysqld64 was stopped.

Restarted it. and was able to login.

answered Aug 05 '18 at 06:01

Shashikant Pandit

2,200
15
25

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

11 Answers11

Linked

Related