We are encountering a collation-related issue in Cloud SQL MySQL when trying to create an audit rule:
The audit rule creation fails with a collation mismatch error.
Current database charset and collation:
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
From our investigation:
The audit plugin always uses *_general_ci collations internally (utf8mb3_general_ci or utf8mb4_general_ci), regardless of the database/table collation.
Switching from a more complex collation like utf8mb4_0900_ai_ci to utf8mb4_general_ci seems to work but is not recommended as a best practice.
The routine that triggers the error appears to be cloudsql_create_canonical_rules, particularly this query:
SELECT count(*) INTO found_rows
FROM mysql.audit_log_supported_ops
WHERE op_name = opstr;
A potential workaround could be to add COLLATE utf8mb4_0900_ai_ci to the comparison:
SELECT count(*) INTO found_rows
FROM mysql.audit_log_supported_ops
WHERE op_name COLLATE utf8mb4_0900_ai_ci = opstr;
We cannot change the collation of the audit_plugin tables without a Google support ticket.
Questions:
Are there recommended ways to resolve this collation mismatch when creating audit rules in Cloud SQL MySQL?
Is there a supported workaround for the cloudsql_create_canonical_rules routine without altering system tables?
Any Cloud SQL best practices for handling audit logging with different collations?