Bug #107538 mysql sys database can't select data when use utf8mb4_general_ci
Submitted: 10 Jun 2022 7:57 Modified: 10 Jun 2022 13:37
Reporter: Austin Liu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.027 OS:CentOS (7.4)
Assigned to: CPU Architecture:x86 (inter)

[10 Jun 2022 7:57] Austin Liu
Description:
The database of sys cannot be queried anything on table because default_COLLATION_FOR_UTf8MB4 is set to UTF8MB4_general_ci.

How to repeat:
please put the Variable_name of default_collation_for_utf8mb4 to utf8mb4_general_ci and try to query anything on tables from database of sys.

Suggested fix:
please fix this bug or Disable parameter modification
[10 Jun 2022 13:37] MySQL Verification Team
Hi Mr. Liu,

Thank you for your bug report.

However, it is not a bug.

You are not supposed to query that schema, but only to use views and stored programs to make useful queries that use Information Schema and Performance schema. Due to that reason, the collation for that schema is fixed and can not be changed.

Not a bug.
[9 Nov 2023 3:10] chong zhang
mysql sys database can't select data when use utf8mb4_general_ci,I found that changing the collation rules of sys can solve the problem.

Can I rebuild the collation rules of the sys database?  What would be the impact if I did this?

eg:
1、
$ mysqldump  --user=root  --default-character-set=utf8    --password='xxx' --socket=/opt/mysql3303/mysql.sock  --single-transaction --hex-blob  --set-gtid-purged=off   --skip-tz-utc  --routines --triggers --events --add-drop-database --databases sys > sys_dump.sql

2、
$ sed -i "s#utf8mb4_0900_ai_ci#utf8mb4_general_ci#g" sys_dump.sql

3、
mysql> set session default_collation_for_utf8mb4=utf8mb4_0900_ai_ci;
mysql> source /xxx/sys_dump.sql
[9 Nov 2023 11:04] MySQL Verification Team
Sorry,

We are afraid that collation can not be changed for that schema.

Please, do note that this schema does not have any tables, but only views and stored procedures.

Hence, you would not gain anything by changing schema.
[10 Nov 2023 5:58] chong zhang
when  default_COLLATION_FOR_UTf8MB4 is set to UTF8MB4_general_ci  ,  mysql sys database can't select data ,then I changing the collation rules of sys can solve the problem .
In default_COLLATION_FOR_UTf8MB4 is set to UTF8MB4_general_ci, The benefit of ci is that I can select sys database . 
But I don't know what unexpected risks there are when changing the sys sorting rule,Can I do this?
[10 Nov 2023 6:21] chong zhang
My other repair method is to add collate utf8mb4_0900_ai_ci .
The script is based on MySQL 8.0.25,Script pair function sys.sys_get_config (1 location)

view sys.schema_table_statistics(2 locations), view sys.schema_table_statistics_with_buffer(2 locations),

view x$schema_table_statistics(2 locations), viewx$schema_table_statistics_with_buffer(2 locations),a total of 9 associated fields add collate utf8mb4_0900_ai_ci. Is this repair plan feasible? can i do it?

There are a total of 9 error fields for association conditions, and add collate utf8mb4_ 0900_ AI_ Ci.
[10 Nov 2023 12:35] MySQL Verification Team
Hi Mr. Liu,

We must admit that we have never ever encountered any report that has problems with SYS schema collation.

You have several different routes in solving your problem.

First of all dump that schema for the safety.

Second, look at the script that creates schema. It's name is ALL_SYS_SCHEMA.sql.

Collation is everywhere defined as utf8mb3_general_ci or utf8mb4_0900_as_ci.

That script is run when you initialised your data directory.

If you look at the script, there are explicit COLLATE statements, so altering views would not help.

Hence, the easiest workaround is that you change your queries by using COLLATE yourself.

Another workaround is that you use collation introducers. You can read about those in our Manual.

There are  other options that  require lot's of work and are risky, hence we can NOT recommend them.