MySQL 9.x: test_utf8mb4_as_ci-4841-t — SET NAMES … COLLATE utf8mb4_0900_as_ci ignored

[renecannao]

Symptom

TAP test test_utf8mb4_as_ci-4841-t (group mysql95-g1) fails on MySQL 9.5:

not ok 1 - collation_connection , Expected utf8mb4_0900_as_ci . Actual utf8mb4_general_ci

The assertion is at test/tap/tests/test_utf8mb4_as_ci-4841-t.cpp:62:

ok(
    "utf8mb4_0900_as_ci" == var_ext.val,
    "collation_connection , Expected utf8mb4_0900_as_ci . Actual %s",
    var_ext.val.c_str()
);

What the test does

From the file header:

The test performs a 'SET NAMES' query to set utf8mb4_as_ci collation, then run a query ...

diag("Testing SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_ci");
...
char * query = (char *)"SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_ci";
...
"SELECT /* hostgroup=" + _TO_S(MYSQL8_HG) + ";create_new_connection=1 */ @@collation_connection"
...
ok("utf8mb4_0900_as_ci" == var_ext.val, ...);

Client issues SET NAMES utf8mb4 COLLATE utf8mb4_0900_as_ci, then queries @@collation_connection. Expected: utf8mb4_0900_as_ci. Actual: utf8mb4_general_ci (what ProxySQL has configured as default). The COLLATE clause isn't propagated to the backend connection.

Test passes on MySQL 8.4; fails on MySQL 9.5 (not verified on 9.0 — the test might not be in mysql90-g1 yet, or it's passing there).

Companion issue

This is very likely the same root cause as charset_unsigned_int-t (separately filed): on 9.x, SET NAMES ... COLLATE <name> from the client isn't propagated through ProxySQL to the backend connection — the connection stays on whatever ProxySQL's mysql-default_collation_connection is set to.

Origin

The test name references issue #4841. That was the original regression the test guards against: "Supporting utf8mb4_0900_as_ci collation". Coverage is still valid for pre-9.x backends; 9.x breaks it again, in a way consistent with the 255-ID-collation bug in charset_unsigned_int-t.

Evidence: ci_infra_logs/mysql95-g1-myrun3/tests/proxysql-tester.py/tests/test_utf8mb4_as_ci-4841-t.log

Related

• Companion: charset_unsigned_int-t (likely same ProxySQL 9.x code path).
• Original regression: Incorrectly attempt to use a collation with id larger than 255 during backend handshake #4841.
• Infrastructure: infra: migrate mysql84-gr to dbdeployer and cap pgsql_servers max_connections #5625.

Suggested fix direction

Investigate ProxySQL's handling of SET NAMES … COLLATE <name> under MySQL 9.x protocol:

• Does the COLLATE portion reach the backend via the outbound COM_QUERY?
• Or is it intercepted/short-circuited to update only ProxySQL's own session state (which then reports the configured default)?
• Is the collation_id field being truncated or remapped when the ID > 255?

Part of MySQL 9.x test-surface triage. Infrastructure: #5625.

[coderabbitai]

🔗 Related PRs

#5488 - Protocol encoding/decoding and utility unit tests (Phase 2.5) [merged]
#5512 - Phase 3.7: MySQL error classification + unit tests [merged]
#5538 - CI test fixes: cluster sync, plan counts, test group assignments, and diagnostics [merged]
#5548 - Fix #5306 - SHOW WARNINGS with inline comments incorrectly sets warning_count [merged]
#5559 - Fix crash when COM_BINLOG_DUMP received with idle backends (#5556) [merged]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!

[renecannao]

Likely addressed by PR #5625 (commit 7c6f42b — validate_charset now compares server_version as a numeric major instead of testing only the first character, so MySQL 9.x is handled like 8.x for collation ids >= 255). utf8mb4_0900_as_ci is id 305, which is exactly the path that was broken on 9.x. Marking to verify — please re-run test_utf8mb4_as_ci-4841-t against MySQL 9.x and confirm.