MySQL Bugs: #108209: ReplicaSet.status: Failed to execute query on Metadata Illegal mix of collations

Bug #108209	ReplicaSet.status: Failed to execute query on Metadata Illegal mix of collations
Submitted:	22 Aug 2022 0:31	Modified:	6 Dec 2022 12:18
Reporter:	Yoseph Phillips	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S2 (Serious)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	collation, Replicaset

Description:
ReplicaSet.status: Failed to execute query on Metadata Illegal mix of collations.

When we have the following lines in the my.ini (Windows), or my.cnf (Linux)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

then simply following the instructions on https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-replicaset.html
fails every time with:
ReplicaSet.status: Failed to execute query on Metadata server 127.0.0.1:3360: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (MySQL Error 1267)

whenever rs.status() is called.
This is making ReplicaSet completely unusable for us as we need to use utf8mb4_unicode_ci.

Even though the collation of the server, DB, connection are all using utf8mb4_unicode_ci, when MySQL Shell is creating mysql_innodb_cluster_metadata it is creating it with utf8mb4_0900_ai_ci, which might help to explain the issue.

We can likewise reproduce the error simply by calling:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

SELECT view_id, member_id 
FROM mysql_innodb_cluster_metadata.v2_ar_members 
WHERE cluster_id = 'dcca211c-1f54-11ed-9ca1-00ff21a11a72' AND member_role = 'PRIMARY';

How to repeat:
Add the following lines in the my.ini (Windows), or my.cnf (Linux)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
(and restart the MySQL server).

From MySQL Shell follow the instructions from https://dev.mysql.com/doc/mysql-shell/8.0/en/configuring-replicaset-instances.html:

mysql-js> \connect root@rs-1:3306
mysql-js> dba.configureReplicaSetInstance('root@rs-1:3306', {clusterAdmin: "'rsadmin'@'rs-1%'"}); 
mysql-js> var rs = dba.createReplicaSet("example")
mysql-js> rs.status()

Suggested fix:
Maybe create the mysql_innodb_cluster_metadata with the configured collation instead of utf8mb4_0900_ai_ci.

Oracle support has noted that this only causes the error when:
skip-character-set-client-handshake
is also included in the my.ini or my.cnf.

I have changed this to an S2 as removing that line from the my.ini or my.cnf can kind of be used as a workaround.

We generally need that line to remain there as without it when we connect from other clients including MySQL Workbench and MySQL Shell, the collation is wrong and we need to remember to call:
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
before we do anything using the connection.

Please fix this issue so that we don't need to remove skip-character-set-client-handshake from the my.ini or my.cnf.

Hi,

I do not agree with S2, i think this is more S3, but in any case it is verifiable bug. Thank you for the report.

kind regards

Posted by developer:
 
Added the following note to the MySQL Shell 8.0.32 release notes:

 The error "ReplicaSet.status: Failed to execute query on Metadata Illegal mix of collations" was returned if the
 server was configured with character-set-client-handshake=OFF or --skip-character-set-client-handshake, and
 the client attempted to use a different collation. The server collation was used for the client sessions, instead
 of negotiating the collation used.
 As of this release, the collation is explicitly set by AdminAPI sessions.