How to solve PDO Connection Error: SQLSTATE[HY000] [2054] Server sent charset unknown to the client

This exception showed up recently when dockerizing an old PHP 5.6 application that uses MySQL as RDBMS. Initially configuring PHP to work with the proper extensions was already a headache because some extensions were old and needed to be compiled from source and so on, so when I finally solved the problem of the extensions, the SQLSTATE[HY000] [2054] error appeared in my screen.

This problem appears basically because you're using an application that uses a default charset that isn't the default one anymore in MySQL 8. In my case, my PHP 5.6 application was connecting to the database using utf8 and by default on MySQL 8, the default charset is automatically set to utf8mb4 and therefore, the exception appears because the client application doesn't understand what the server means and throws the mentioned error.

In this article, I will propose you 2 possible solutions for this problem when trying to connect to your database.

Solution #1 (Adjust default character set)

By default, in MySQL 8 the default charset is set to utf8mb4, which is usually not compatible with old clients, just like in my case with a Symfony 1.4 project. If you want to provide compatibility with MySQL 8, you will need to adjust the default character set of your MySQL instance through the configuration file. Find the configuration file of MySQL (usually located in /etc/my.cnf or /etc/mysql/conf.d/my.cnf) and add the following settings in the mentioned sections:

# /etc/mysql/conf.d/my.cnf

[mysqld]

collation-server = utf8_unicode_ci
character-set-server = utf8 

# Otherwise the following exception will be thrown: the server requested authentication method unknown to the client
default_authentication_plugin= mysql_native_password

[client] 
default-character-set=utf8 

[mysql]
default-character-set=utf8

After updating the configuration file of MySQL, be sure to update the authentication plugin used for your users. In this case, I'm using the root user to connect to the database, So i'll update specifically that user only:

--- Update the authentication plugin of the user e.g for root with the password ---
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourRootPassword';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourRootPassword';
ALTER USER 'default'@'%' IDENTIFIED WITH mysql_native_password BY 'yourRootPassword';

--- Then flush the privileges ---
FLUSH PRIVILEGES;

Finally restart the mysql service (or container in case that you're using docker):

sudo service mysql restart

And that's it! Your application should be able to connect now assuming that the old charset was UTF-8.

Solution #2 (Downgrade MySQL)

This was the solution for me since, it wasn't possible to use MySQL 8 in the old project, so I had to simply use MySQL 5.7 instead. Downgrading automatically removed every of the new exceptions that I never saw when configuring the project as usual. The original RDBMS for this project was MySQL 5.7, so that solved every incompatibility.

Happy coding ❤️!