skip to Main Content

I am using PHP 8.1 and MySQL 8.0. All databases, tables and individual fields are all set to a collation of utf8mb4_0900_ai_ci – except for databases: information_schema and performance_schema, which are utf8mb3_general_ci (system default?).

Here are the global variables MySQL configs for collation and charset:

//performance_schema.global_variables

character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8mb3

collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb4_0900_ai_ci
collation_server utf8mb4_0900_ai_ci
default_collation_for_utf8mb4 utf8mb4_0900_ai_ci

As you can see, I am trying to set my server up to have a charset of utf8mb4 and a collation of utf8mb4_0900_ai_ci (which is what MySQL 8.0 recommends)

performance_schema.session_variables has a slight different set of variables, I’m not sure if this makes a difference or not. I’ve tried rebooting MySQL, but the session variables do not update to what the global variables are set to above. I’ve tried manually changing these, but they won’t change, even if I’m using the MySQL admin user:

//performance_schema.session_variables

character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb3
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8mb3

collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb3_general_ci
collation_server utf8mb4_0900_ai_ci
default_collation_for_utf8mb4 utf8mb4_0900_ai_ci

Here is my script to connect to MySQL and print the charset data:

$conn = mysqli_connect($hostname, $username, $password, $database);
mysqli_set_charset($conn,"utf8mb4");
mysqli_query($conn, "SET collation_connection = 'utf8mb4_0900_ai_ci'");
mysqli_query($conn, "SET SESSION collation_connection = 'utf8mb4_0900_ai_ci'");
var_dump(mysqli_get_charset($conn));

//
object(stdClass)#2 (8) { 
  ["charset"]=> string(7) "utf8mb4" 
  ["collation"]=> string(18) "utf8mb4_general_ci" 
  ["dir"]=> string(0) "" 
  ["min_length"]=> int(1) 
  ["max_length"]=> int(4) 
  ["number"]=> int(45) 
  ["state"]=> int(1) 
  ["comment"]=> string(13) "UTF-8 Unicode" 
}

The problem is that "collation" is showing utf8mb4_general_ci, but it’s supposed to be utf8mb4_0900_ai_ci. I’ve read through various guides here and I am unable to fix this. Anyone know how to do this properly?

2

Answers


  1. You have set the collation correctly. The information that you see in mysqli_get_charset is irrelevant.

    Try:

    var_dump($conn->query("SELECT @@collation_connection")->fetch_column());
    

    and you will see the right collation.

    I am not sure, but I think mysqlnd has no way of figuring out which collation is actually in use. You can only set charset, but you cannot set the collation, so it will always report the default collation. But it doesn’t matter much for mysqlnd as collation is not used by PHP. You can safely set it like you are doing it now.

    Login or Signup to reply.
  2. Collation belongs to the database.
    ALTER DATABASE database /*!40100 DEFAULT COLLATE latin2_hungarian_ci */;
    works for me.
    you can find collations here:
    https://mariadb.com/kb/en/setting-character-sets-and-collations/

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search