mysql.addSecondaryIdxOnReplica / mysql.dropSecondaryIdxOnReplica fail with ERROR 1267 Illegal mix of collations on MySQL 8 Cloud SQL read replica

Google Cloud Database
1

When running on MySQL 8: mysql.addSecondaryIdxOnReplica / mysql.dropSecondaryIdxOnReplica I get the error:
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’

In delete: After Step 1 (Delete record from mysql.cloudsql_replica_index)
In add: After Step 2 (Insert record into mysql.cloudsql_replica_index)

I’ve also tried creating a new Replica, but it gets the same table structure.

Here are some details regarding this Error:

  1. SHOW CREATE PROCEDURE mysql.dropSecondaryIdxOnReplica ⇒ Database Collation: utf8mb4_0900_ai_ci

  2. SHOW CREATE TABLE mysql.cloudsql_replica_index ⇒ columns db_name/table_name/index_name are utf8mb4_general_ci.

  3. CALL mysql.dropSecondaryIdxOnReplica('…', 'schema.table', '') prints Step 1 then fails with ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='.

    Expected: Procedures succeed.
    Request: Align collations (alter table to utf8mb4_0900_ai_ci or add explicit COLLATE in procedures).

1 Like
2

Hi @guynir,

Your best option here is to file a bug report or a feature request with Google Cloud support. Explain the situation exactly as you’ve detailed it—that the collations are mismatched (utf8mb4_general_ci vs. utf8mb4_0900_ai_ci) and it’s preventing the official procedures from running.

1 Like
3

This error happens because MySQL 8 is comparing strings that use different collations: the stored procedures run with utf8mb4_0900_ai_ci, while the mysql.cloudsql_replica_index table columns are still utf8mb4_general_ci. MySQL 8 is stricter about this and fails when it sees a comparison using = between the two. The proper fix is to align the table collation with the MySQL 8 default by converting mysql.cloudsql_replica_index to utf8mb4_0900_ai_ci, after which the procedures should run normally. Creating a new replica does not help because the same table structure is copied. If the table cannot be altered or shows corruption, you can repair it first using third party tools like Stellar Repair for MySQL and then apply the collation change.