MYSQL 8.0.2.8 Information_schema character set is incorrect, and I cannot find where to change it
-
I have a database installation where the collations look as so:
As you can see, all the databases are utf8mb4, while the information schema is showing up as utf8mb3. This is causing me issues as I need to monitor some replication on this database using PRTG and PRTG is complaining that utf8mb3 is not accepted by .NET.
Now, I know that information_schema is a view and not a table, therefore I cannot just alter table it. I am not sure where it is getting the utfmb3
When I run :
SELECT TABLE_NAME, TABLE_COLLATION, TABLE_SCHEMA FROM information_schema.tables; almost everything comes up utf8mb4_0900_ai_ci, with the expected exception of some NULLs, and some utf8mb4_bin spread out here and there.
How can I remove all traces of utf8mb3 from the information schema?
Thank you all in advance
-
No user can alter the information_schema. The schema itself is like a view. So fixing this must be done in the MySQL source code and included in a subsequent release.
mysql> alter schema information_schema default character set utf8mb4 ; ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'The issue of .NET getting errors related to utf8mb3 has been reported here: https://bugs.mysql.com/bug.php?id=107259
I suggest you should make an account on the MySQL bugs site and click the "Affects Me" button for the above bug. The more people who do that, the more they prioritize working on fixing it.
I haven't tried the following solution, because I'm not a .NET user, but based on the release notes, you might be able to fix this in the short term by downgrading back to MySQL 8.0.23 or earlier.
This is apparently the result of MySQL gradually migrating to utf8mb4 in stages. First they will change all references to "utf8" to "utf8mb3". Then they will make the old name "utf8" refer to the better implementation utf8mb4.
I saw the following statements in the https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ :
8.0.11: The utf8mb3 character set will be replaced by utf8mb4 in a future MySQL version. The utf8 character set is currently an alias for utf8mb3, but will at that point become a reference to utf8mb4. To avoid ambiguity about the meaning of utf8, consider specifying utf8mb4 explicitly for character set references instead of utf8.
8.0.13: The utf8mb3 character set is deprecated and will be removed in a future MySQL version. Please use utf8mb4 instead.
8.0.24: Client applications and test suite plugins now report utf8mb3 rather than utf8 when writing character set names.
8.0.26: These statements now report utf8mb3 rather than utf8 when writing character set names: EXPLAIN, SHOW CREATE PROCEDURE, SHOW CREATE EVENT.
Stored program definitions retrieved from the data dictionary now report utf8mb3 rather than utf8 in character set references. This affects any output produced from those definitions, such as SHOW CREATE statements.
This error message now reports utf8mb3 rather than utf8 when writing character set names: ER_INVALID_CHARACTER_STRING.
8.0.27: String conversion warnings that previously referred to utf8 now reference utf8mb3 instead.
8.0.29: The server now uses utf8mb3 rather than utf8 in the following cases:
In the output of SHOW SQL statements (SHOW CREATE TABLE, SHOW CREATE VIEW, SHOW CREATE DATABASE)
When reporting invalid strings.
The server now uses utf8mb3 in place of the alias utf8 for character set names when populating data dictionary tables from built-in character sets. This affects the display of character set and related information in the MySQL Information Schema tables listed here: CHARACTER_SETS, COLLATIONS, COLUMNS, COLLATION_CHARACTER_SET_APPLICABILITY, PARAMETERS, ROUTINES, SCHEMATA
This change also affects the output of the SQL SHOW CHARACTER SET, SHOW COLLATION, SHOW CREATE DATABASE, and SHOW CREATE TABLE statements.
I think the best solution would be for MySQL to finish their migration of all system tables and schemas and variables to utf8mb4.
