Mysql – Character set ‘utf8mb4’ is not a compiled character set and is not specified in the ‘/usr/share/thesql/charsets/Index.xml’ file

character-setMySQLutf-8

Full error

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file
InvalidArgumentException: There was a problem connecting to the database: SQLSTATE[HY000] [2019] Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)

mysql Ver 14.14 Distrib 5.7.26

I'm trying to fix this error, but can't get it sorted on a live server. I have had to go through the procedure of adding the following to the /usr/share/mysql/charsets/Index.xml file on a local virtual machine which worked.

<charset name="utf8mb4">
  <family>Unicode</family>
  <description>UTF-8 MB4 Unicode</description>
  <collation name="utf8mb4_general_ci" id="45">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="utf8mb4_bin"     id="46">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

I've restarted mysql a couple of times since updating this to no avail. My database connection is listed like so

<?php
$db = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $db_username, $db_pass);

And all my database variable seem to check out based on other posts I've seen

enter image description hereenter image description here

my.cnf has the following set too

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Is there any kind of cache that could be failing to clear even after a service mysqld restart command? (CentOS 6)

Best Answer

I've found a fix for this, which was related to the package php71w-mysqlnd.x86_64 / php71w-mysql.x86_64

My local machine had php71w-mysqlnd.x86_64 installed whereas the live server had php71w-mysql.x86_64

After running the following two commands, it's now working

yum erase php71w-mysql.x86_64
yum install php71w-mysqlnd.x86_64

service httpd restart

Ref: https://stackoverflow.com/a/38006719/871150