Binary-protocol prepared-statement parameter resolves to the legacy ..._general_ci default collation after character_set_client is restored via a standalone SET CHARACTER_SET_CLIENT = <user-variable> statement

After a session does the following - which is exactly the standard boilerplate every
`mysqldump` export begins and ends with, not a contrived sequence:

1. Captures the current charset/collation into user variables:
`SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT; SET
@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;` (mysqldump also captures
`@OLD_CHARACTER_SET_RESULTS`).
2. Runs `SET NAMES utf8mb4;` (bare, no `COLLATE`) — as mysqldump's header always does.
3. ... executes the dump body (any INSERTs etc.) ...
4. Restores the previous charset/collation via *standalone* `SET` statements —
`SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;` etc. — because `SET NAMES` does not
accept a variable as its charset argument, so this is the only syntactically valid way to
restore a dynamically-captured (not literally known at dump-creation time) charset/collation.

...any *subsequent binary-protocol prepared statement* (`COM_STMT_PREPARE` /
`COM_STMT_EXECUTE` — what every JDBC/ODBC/PDO-native/C-API client uses for server-side prepared
statements) that binds an untyped string parameter gets that parameter's collation resolved as
the legacy, pre-11.4 hardcoded default for the character set (`utf8mb4_general_ci`), *not* the
connection's actual, correctly-restored `@@collation_connection`.

The critical, hard-to-diagnose part: every session variable inspectable via `SELECT @@...`
(`@@character_set_client`, `@@character_set_connection`, `@@character_set_results`,
`@@collation_connection`, `@@character_set_collations`) reports the *correct*, fully-restored
value. Only the actual bind-parameter typing is wrong — verifiable only via `SELECT
COLLATION` with a bound parameter, or by hitting the resulting `ERROR 1267 (HY000)` when that
parameter is later compared against anything not `utf8mb4_general_ci` (the common case on any
schema created since utf8mb4's default stopped being `general_ci`).

1. 1. Why this matters in practice

Any application that:
1. Imports a `mysqldump` export over a connection, then
2. Reuses *that same connection* afterward — e.g. pulled back from a connection pool
(HikariCP, c3p0, etc.) for further prepared-statement queries with string parameters —

...is at risk of a silent, intermittent `ERROR 1267: Illegal mix of collations` that has nothing
to do with the query, the schema, or the application code, and cannot be diagnosed by inspecting
any session variable.

1. Steps to Reproduce

```sql
– On any connection, e.g. `mariadb` CLI:
SET @OLD_CHARACTER_SET_CLIENT = @@CHARACTER_SET_CLIENT;
SET @OLD_COLLATION_CONNECTION = @@COLLATION_CONNECTION;
SET NAMES utf8mb4; – mysqldump's header line
– (imagine the dump's INSERT statements here)
SET CHARACTER_SET_CLIENT = @OLD_CHARACTER_SET_CLIENT; – mysqldump's restore footer
SET COLLATION_CONNECTION = @OLD_COLLATION_CONNECTION;

– Sanity check — everything reads back correctly:
SELECT @@collation_connection, @@character_set_client;
– utf8mb4_unicode_ci | utf8mb4 (correct, matches the connection's original state)
```

Then, *on the same connection*, via any client using binary-protocol prepared statements
(shown here with MariaDB Connector/J, default `useServerPrepStmts=true`, but the driver is not
implicated — see Analysis):

```java
String url = "jdbc:mariadb://HOST/DB?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=true";
try (Connection conn = DriverManager.getConnection(url, user, pass)) {
// ... run the six SET statements above on this same connection ...
try (PreparedStatement ps = conn.prepareStatement("SELECT COLLATION"))

{ ps.setString(1, "x"); ResultSet rs = ps.executeQuery(); rs.next(); System.out.println(rs.getString(1)); // utf8mb4_general_ci <-- WRONG }

}
```

• *Expected:* `utf8mb4_unicode_ci` (matches `@@collation_connection`, printed correctly just
  above by the plain `SELECT`).
• *Actual:* `utf8mb4_general_ci`.

1. Isolation / additional findings

• Reproduces on a *stock* `mariadb:11.8` container — no `--collation-server` or
  `--character-set-collations` override required (also separately confirmed with
  `--collation-server=utf8mb4_unicode_ci --character-set-collations=utf8mb4=utf8mb4_unicode_ci`
  set, with identical results).
• `SET CHARACTER_SET_CLIENT = <anything>`, alone, as a standalone statement, is *sufficient* to
  trigger it (no need for `CHARACTER_SET_RESULTS` or `COLLATION_CONNECTION` to also be
  individually re-set).
• `SET COLLATION_CONNECTION = <anything>`, alone — whether from a user variable or a literal —
  does *not* trigger it.
• Using a *literal* value instead of a user variable in the `SET CHARACTER_SET_CLIENT =` /
  `SET COLLATION_CONNECTION =` restore statements does *not* avoid the bug either — it is
  specifically the standalone-statement form, not the use of a variable, that matters.
• Issuing one more, single, atomic `SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci` (with literal
  arguments) after the broken sequence fully repairs the connection — bound parameters resolve
  correctly again from that point on. (This is not a fix mysqldump itself can apply, since `SET
  NAMES` cannot take a variable, and the whole point of the capture/restore dance is to restore a
  charset/collation that isn't literally known when the dump was written.)
• Confirmed independent of client library / connector version: reproduces identically with
  MariaDB Connector/J 3.5.9 and 3.5.10.
• Disabling server-side prepared statements entirely (`useServerPrepStmts=false` in Connector/J,
  i.e. falling back to client-side/text-protocol prepare, which sends bound values as escaped SQL
  literals rather than typed binary-protocol parameters) avoids the affected code path entirely.

1. Suspected root cause

This looks related to MDEV-25829 (default Unicode collation changed to
`utf8mb4_uca1400_ai_ci`) and its accompanying `@@character_set_collations` machinery. Working
theory: `SET CHARACTER_SET_CLIENT = <value>`, executed as a standalone statement (as opposed to
as part of one `SET NAMES` statement), takes a different internal code path for (re-)deriving
whatever internal state is used to type an untyped binary-protocol bind parameter — one that
falls back to the character set's historical hardcoded default collation
(`information_schema.character_sets.default_collate_name`-style) rather than consulting the
session's already-correct `@@collation_connection` / `@@character_set_collations`. `SET NAMES`
apparently updates that internal state correctly; the three-statement equivalent (which the
server's own documentation and `mysqldump` both treat as an interchangeable, equivalent way to
express the same session state) does not.

Since `SET NAMES` cannot accept a variable as its charset/collation argument, the standalone-`SET`
form is not a contrived edge case — it is the only syntactically valid way to restore a
dynamically-captured (not literally known in advance) charset/collation, and it is exactly what
`mysqldump` generates, unconditionally, in the footer of every export it produces. Any
application/ORM/migration tool that imports a `mysqldump` export over a connection it later
reuses for prepared statements is exposed to this.

1. Suggested fix direction

Whatever internal state governs binary-protocol untyped-bind-parameter collation resolution
should be recomputed identically regardless of whether `character_set_client` /
`character_set_results` / `collation_connection` are set via one `SET NAMES ... COLLATE ...`
statement or via the three equivalent standalone `SET <var> = <value>` statements — these two
forms are documented as equivalent ways to reach the same session state and must not diverge.

1. Workaround

• After any mysqldump-style restore sequence (or generally after any standalone `SET
  CHARACTER_SET_CLIENT = ...`) runs on a connection that will be reused for prepared statements,
  follow up with one explicit, literal `SET NAMES <charset> COLLATE <collation>` to force
  re-derivation of the internal state, *or*
• Avoid reusing such a connection for server-side (binary-protocol) prepared statements at all
  (e.g. `useServerPrepStmts=false` in MariaDB Connector/J), *or*
• Avoid running `mysqldump`-restored scripts over a pooled/long-lived connection that will later serve prepared-statement traffic.

—

Disclaimer: AI was used to summarize and formalize this bug report.