0

I have a database installation where the collations look as so:

enter image description here

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.

sample of collections

How can I remove all traces of utf8mb3 from the information schema?

Thank you all in advance

4
  • Perhaps you meant 8.0.28?
    – Rick James
    Jun 3, 2022 at 20:55
  • "utf8mb3" is a synonym for "utf8". In the future, MySQL intends to make "utf8" == "utf8mb4".
    – Rick James
    Jun 3, 2022 at 20:57
  • Can you provide the particular context for the problem, and any specific error message?
    – Rick James
    Jun 3, 2022 at 20:58
  • The error message I am getting is from PRTG, which is the software suite that my company uses for monitoring. It has an inbuilt sensor to monitor the 'seconds behind master' value in a database. This sensor is failing with an 'utf8mb3 is not supported by .NET' error. That is the error I am seeing.
    – M.Barbara
    Jun 3, 2022 at 22:21

1 Answer 1

1

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 MySQL release notes:

  • 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.

4
  • Thank Bill. That was very helpful. I will see if downgrading mysql version is feasible. I am somewhat confused as to why though the mb3 is showing up AT ALL. I have a single database on the host, a fairly simple one at that. I didnt specify the collation on database creation, and the default should be UTF8mb4. I installed clean from 8.0.28 which should have mb4 as default. I am confused as to why it would have set that value.
    – M.Barbara
    Jun 3, 2022 at 22:26
  • As an additional question, Were I to back up the database, drop and re-create, and then re-ingest all of the data, would I be able to force that collation? The environment is not yet in production, so while time consuming, it is a feasible option. Thank you.
    – M.Barbara
    Jun 3, 2022 at 22:28
  • The default character set of the INFORMATION_SCHEMA database seems to be utf8mb3. This is fixed in the server and not configurable or alterable. Also I notice that the value of the configuration variable character_set_system reports as utf8mb3. This is also hard-coded in the server and not changeable. It's a configuration variable, but it cannot be changed except in the source code. Jun 3, 2022 at 23:53
  • I don't think you can change this in the current version of MySQL. Jun 3, 2022 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.