Connecting to MySQL 8 from Old PHP version using mysqli

If you have upgraded your MySQL to MySQL 8 version and still using older version of PHP you might encounter a few problems in the connection using mysqli connector library.

1.

The first problem we are going to solve is:

php mysql 8: "Server sent charset (255) unknown to the client. Please, report to the developers"

The problem is that MySQL have changed the default character set from utf8 to utf8mb4 and it is not supported by mysqli connector (nor PDO as I have checked) in older versions of php.

"The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 ( utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0). When the 8.0 client requests a character set of utf8mb4 , what it sends to the server is the default 8.0 utf8mb4 collation; that is, the utf8mb4_0900_ai_ci."

https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

The way to solve it easily (there are other ways, such as upgrading php or connectors and more ...) is to force MySQL 8.0 to use UTF8 as default charset for connection.

Add those lines at the end of the my.cnf configuration file. In Ubunutu 20.04 it will be /etc/mysql/mysql.conf.d/mysqld.cnf

So:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And add at the end (must be at the end of the file):

[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

do not forget to restart the server after:

sudo service mysql restart

2.

Second problem is new plugin for MySQL 8 passwords. Instead of the default mysql_native_password it is now using caching_sha2_password so you will get the error:  mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

The last line we added to the my.cnf file should fix it but not entirely. When creating the users inside your database make sure to CREATE USER or ALTER USER to use the mysql_native_password like that:

CREATE USER 'username'@`location` IDENTIFIED WITH mysql_native_password BY '[somepassword]'; 

That solved me some problems.