Struggling with a strange MySQL error when connecting to MySQL Version 8?
PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers
Turns out this error is related to how MySQL sets the default charset to utf8mb4, which isn't known to a lot of the worlds programming languages such as PHP < 7.3 (?), C++/MySQL connector etc. which can be a bit frustrating to fix, having to be forced to upgrade the application.++
There is, however a simple fix! If you're hosting your own MySQL database, you only need to change the MySQL Config file (/etc/my.cnf) to the following:
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8
Once that is done, restart mysqld and you should be good to go, however, if you're using AWS RDS, you do not have direct access to the MySQL config file. Fret not! There is a way to fix this too! You need to configure some DB Parameter Groups which will allow you to add the parameters we need. This can be done either from the console, AWS CLI or RDS API. Reference the AWS Documentation for more info.
Login to your AWS Console, navigate to AWS RDS and find your databases.
Start by navigating to the Parameter Groups menu on the left and click to Create parameter group.
Give the group a new name and description, and make sure that the group family is mysql8.0. Create the group, then navigate back to the group's page and click the newly created group.
Click Edit parameters in the top right to start modifying the parameters. In the filters box, search for "CHAR". You should set the following:
- Character_set_client = "utf8"
- Character_set_connection = "utf8"
- Character_set_database = "utf8"
- Character_set_server = "utf8"
Finish by saving the changes. Now navigate back to your database, and click the "Modify" button to make changes to the configuration of the RDS instance.
Scroll down to locate the Database options then locate the DB parameter group and set it to the group you just created. Scroll down to save.
Important!
When applying the changes, you WILL be asked when to apply the changes. This can either be done in the next maintenance window, which could be far in the future, or you can set it to happen immediately. If you set it to immediately the MySQL instance will become unavailable wile it applies the changes! This can take from a few minutes up to 10-15 minutes.
In addition to this you must do a restart of the RDS instance to ensure that the database has been updated with the changes. This will also make the MySQL instance unavailable while it is restarting!
However, once you have restarted the instance, you should now be able to connect to the instance using older programming languages once again!
Top comments (2)
Great write up. Just remember for the AWS configuration you also need to set
collation-server = utf8_unicode_ci as described above
I think this bug may lie somewhere else. utf8mb4 is compatible with the MySQLi implementation all the way back to the PHP 5.x days. It could be that PDO itself has bugs in it, which I wouldn't be surprised. PDO doesn't do as well with more enterprise style features in MySQL/MariaDB. For instance, there is a ton of high available features in the normal MySQLi driver missing from PDO that are exploited in PUDL for scale out solutions: github.com/darkain/pudl (and PUDL forces the connection to utf8mb4, which is why I know it works in 5.x code)