Why is MySQL Workbench showing wrong collation in table editor?

Question

I'm currently using MySQL Workbench 8.0.21. If I press the wrench icon for the table editor window I always get the same (wrong) collation utf8mb4_0900_ai_ci. But this is not the real collation for the table!

I believe that this must be a bug with the Workbench, because I tried different things:

• Setting the collation_connection for the client manually but without effect

  SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
SET collation_connection = utf8mb4_general_ci;

• Checking the table collation

  SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'parts'; which correctly prints utf8mb8_general_ci

• Tried to edit mysql.cnf

but again without effect:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server=utf8mb4_general_ci
#init-connect='SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;'
init-connect='SET collation_connection = utf8mb4_general_ci;'
character-set-server=utf8mb4

There seems to be no solution for changing the default character set/collation when connecting with MySQL Workbench.

The setting default_collation_for_utf8mb4 seems to be only valid for replication purposes. Would be turning off character-set-client-handshake an alternative solution for my problem?

Have you also experienced this weird behavior in the Workbench?

Answer 1

CHARACTER SET refers to the bits representing the characters. It is important for sending (INSERT, SELECT) data between client (Workbench, in your case) and the server (mysqld). It is also specified for each column of each table.

COLLATION is important only when comparing (WHERE) and sorting (ORDER BY). Collation rarely has impact anywhere else.

The default collation for utf8mb4 is utf8mb4_0900_ai_ci for MySQL 8.0.

You need to be concerned about the CHARACTER SET, otherwise characters sent between the client and server will likely be mangled.

You usually don't need to worry about the COLLATION.

SET NAMES and character_set_connection control the Insert/Select transliteration by specifying the encoding in the client. (It sounds like you are working entirely with UTF-8, so no conversion is needed.)

Is the "wrong collation" causing trouble somewhere? If so, please specify.

You mentioned "replication"; is the Replica having some kind of trouble?

More

In the underlying MySQL, when ALTERing a column, you must re-specify everything ([NOT] NULL, charset, collation, etc). Apparently, Workbench fails to do that for you.