The ProxySQL Integration MGR is an excellent high availability solution for MySQL. The structure is as follows:

MySQL 8.0.23

If you are using MGR, upgrade to MySQL 8.0.23 or later, which is very stable

In ProxySQL+MySQL 8.0MGR, there is a monitor that uses SQL as follows, which has been stable for many years, and collapsed in 8.0.23

mysql> select * from performance_schema.replication_group_members  where \performance_schema.replication_group_members.member_host=@@hostname;
Copy the code

Error:

ERROR 1267 (HY000): Illegal mix of collations (ascii_general_ci,IMPLICIT) and (utf8_general_ci,SYSCONST) for operation ‘=’

SQL > select * from character set (‘ = ‘); In previous versions, it was only a full table scan, but in 8.0.23 it is more strict to report an error directly.

Results: github.com/zhishutech/… Just use it. Keep two other conclusions in mind:

  1. MySQL > alter table set (s); MySQL > alter table set (s);

  2. – in 8.0.23, two field character sets are inconsistent.

Cause analysis:

Addition_to_sys8.sql tables are used in addition_to_sys8.sql, most of which are from performance_schema. Use SQL query to see which fields are ascii_general_CI configuration:

SELECT table_schema, table_name, column_name, character_set_name, \collation_name FROM information_schema.columns WHERE COLLATION_NAME \='ascii_general_ci' and table_schema='performance_schema' and table_name like "%group%";
Copy the code

1 row in set (0.00 sec)

You can see that our member_host is defined as an ASCII character set. Just to be sure:

CREATE TABLE `replication_group_members` (  `CHANNEL_NAME` char(64) NOT NULL,  `MEMBER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,  `MEMBER_HOST` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,  `MEMBER_PORT` int DEFAULT NULL,  `MEMBER_STATE` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,  `MEMBER_ROLE` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,  `MEMBER_VERSION` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Copy the code

Performance_schema. replication_group_members is not allowed to be changed. The collate of the table is defined as UTF8MB4_0900_ai_CI. Utf8mb4_bin, if this Internet company, will be punished with caning. However, I don’t want to say anything when I look at the other related fields in MySQL.

mysql> select charset(MEMBER_HOST), MEMBER_HOST,charset(@@hostname), \@@hostname from performance_schema.replication_group_members;
Copy the code

3 rows in set (0.00 sec)

See there are several solutions:

  1. Convert @@hostname to a character
select * from performance_schema.replication_group_members  where  \performance_schema.replication_group_members.member_id=convert(@@server_uuid using ascii);
Copy the code

However, this fix method and our own written development specification have some conflicts, as far as possible to reduce the operation in MySQL.

  1. Use utF8 fields to compare the final fix as follows:
select * from performance_schema.replication_group_members  where  \performance_schema.replication_group_members.member_id=@@server_uuid;
Copy the code

MySQL > select * from ‘ASCII’ where ‘ASCII’ = ‘ASCII’; MySQL > select * from ‘ASCII’;

SELECT table_schema, table_name, column_name, character_set_name, \collation_name FROM information_schema.columns WHERE COLLATION_NAME \='ascii_general_ci';
Copy the code

I see similar questions being submitted in the official bug community, but the official prevarication is nothing but water off a duck’s back. If you are an official enterprise edition user, you can see the use of your enterprise edition privileges to the official SR, suggest the official after MySQL8.0.25 these acSSII code fields, all changed to the same as the system. Also pushing MySQL 8.0 to convert to UTF8MB4 early.

If you have any problems or experiences using MGR, please share them in the comments.

This article uses the article synchronization assistant to synchronize