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.

The way I use mysqldump

sudo mysqldump --master-data=1 --flush-logs --single-transaction --routines --quick --all-databases > db.sql

Simple system to track errors in code and find them easily

Hi,

Here is a trick I developed during my years as a developer / programmer and team code leader. This system not just helps you to find the location of the error or warning in the code, but also who wrote it (without the need to open Git Blame or SVN log) and when was it coded in written.

They say that grep (find in all files) is the best friend of the programmer. Hence if you see an error in text or log file, let's say "Error: can't find configuration file". So you copy the text and "grep" it. It might be you would find this string in a few places scattered around the code.

So, we have started to give some Error Codes to the error. For example "Error: can't find configuration file (1029)". This will make it much easier and this unique error code is easy to search inside all the code files.

So how do you keep all the Error Codes unique ? Maybe a new programmer will add the same code number.

Do you keep a file with all Error Codes ?

Here is my system for assigning error codes:

"Error: some error description (ErrorCode: 20200515.1271726)"

And here is the format of the Error Code

YYYYMMDD.XXXHHII

YYYY, MM, DD - is obvious the date the code was written

HH, II - is the hour and minutes

The XXX is a unique number each of the coder team member identifier.

So let's say a programmer called Dani with code 019 have to add a new log  line with some warning or error. He will add 20200515.0191727 as the Error Code.
Next time I will see this code I can easily 'grep' it and I can also know who wrote this piece of code and when.

Believe me, it helps when the team is large.

Cheers.
Cnaan