-1
MySQL 8.0
phpMyAdmin

I have the following Session variables in MySQL (found in peformance_schema.session_variables):

collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb3_general_ci
collation_server utf8mb4_0900_ai_ci

These are different than my Global variables (performance_schema.global_variables), which I have no problem editing.

When I try to update the collation_database variable, I get an error:

UPDATE session_variables set collation_database = 'utf8mb4_0900_ai_ci'

//#1142 - UPDATE command denied to user 'root'@'localhost' for table 'session_variables'

I'm surprised that even the root user does not have the privilege to update this. I tried rebooting MySQL, thinking that the session gets cleared and then simply copies the global variables for the next session, but that did not work either. How does one update these session variables?

Maybe this is a phpMyAdmin-specific problem? I don't know. My goal is to set everything in MySQL to a character set of utf8mb4 and a collation of utf8mb4_0900_ai_ci, as MySQL 8.0 recommends.

I also tried the following, but it doesn't do anything:

SET SESSION collation_database = 'utf8mb4_0900_ai_ci'

The query gets executed, but nothing happens and the variable is still utf8mb3_general_ci. It's as if MySQL is ignoring the query.

Shadow
  • 33,525
  • 10
  • 51
  • 64
peppy
  • 173
  • 2
  • 17
  • Read in the [documentation](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html): _"Assigning a value to the session character_set_database and collation_database system variables is deprecated and assignments produce a warning. Expect the session variables to become read-only (and assignments to produce an error) in a future version of MySQL in which it remains possible to access the session variables to determine the database character set and collation for the default database."_ – Bill Karwin May 20 '23 at 01:11
  • @BillKarwin How are these session variables even being assigned to begin with? MySQL 8.0 recommends `'utf8mb4_0900_ai_ci'` and is the default, so why is it setting something weird like `utf8mb3_general_ci`? There has to be a way to change this, because it's a serious problem in production if utf-8 is being encoded incorrectly. – peppy May 20 '23 at 01:16
  • Did you read the documentation for the `character_set_database` variable? _"The server sets this variable whenever the default database changes."_ – Bill Karwin May 20 '23 at 06:26
  • @BillKarwin - I think I understand. Because I'm using phpmyadmin to view performance_schema.session_variables, that session table is always showing me the collation for performance_schema only, because `USE performance_schema` always sets that as the default database whenever I am viewing the session_variable table within it? I am seeing that databases `information_schema`, and `performance_schema` have a collation of `utf8mb3_general_ci` and I can't change it. Does that look normal for MySQL 8.0? – peppy May 20 '23 at 23:06
  • 1
    Yes, unfortunately this is a bug. The charset/collation of those virtual schemas like `information_schema` and `performance_schema` can't be changed. They are hardcoded to the values of `character_set_system` and the default collation for that charset, and these builtin system variables are still hardcoded to `utf8mb3` and `utf8mb3_general_ci`. – Bill Karwin May 20 '23 at 23:42

1 Answers1

0

set session collation_database = 'utf8mb4_0900_ai_ci';Please note that some parameters cannot be changed at the session level. Use set session/global or modify the parameter file.

dogs Cute
  • 564
  • 3
  • 9
  • I already tried this. The query gets executed, but there is no change in the session variable. It's as if MySQL is ignoring it. – peppy May 20 '23 at 01:03