CloudSqlProxyRunner cannot authenticate against Cloud SQL for MySQL 8.4 (caching_sha2_password full auth fails on Auth Proxy v1)

### Under which category would you file this issue?

Providers

### Apache Airflow version

3.3.1

### What happened and how to reproduce it?

Reproduced both inside Cloud Composer and standalone with the provider's own `CloudSqlProxyRunner`. The auth cache was cleared before **every** trial, since a single clear only makes the first attempt cold.

Reporting here rather than through the managed-service channel because the defect is in the provider code, not in Composer — Composer only installs the package, and the same failure reproduces on a plain virtualenv install.

### What happens

`CloudSqlProxyRunner` always launches **Cloud SQL Auth Proxy v1**, and v1 cannot complete `caching_sha2_password` **full authentication**. Any task connecting to a Cloud SQL for MySQL 8.4 instance therefore fails with `1045 Access denied` on the first connection after the server's auth cache is emptied — which happens on every restart, failover or maintenance event.

This is easy to miss, because `caching_sha2_password` has two paths. **Fast auth** is served from the server's in-memory cache and works fine over v1. **Full auth** is only taken when that cache is cold. The cache never expires on its own, so once anything primes it the deployment looks healthy indefinitely — until the next restart, at which point tasks start failing for no visible reason.

The runner is hard-wired to v1 and there is no way to opt out:

- `CLOUD_SQL_PROXY_DOWNLOAD_URL` / `CLOUD_SQL_PROXY_VERSION_DOWNLOAD_URL` point at the v1 buckets
- `_build_command_line_parameters()` emits `-dir` and `-instances`, both removed in v2 (`cloud_sql.py:595-596`, unchanged in 22.4.0)
- `sql_proxy_version` only selects among **v1** releases
- `sql_proxy_binary_path` does not help either — a v2 binary rejects those flags

### Why this matters now

Cloud SQL for MySQL 8.4 makes `caching_sha2_password` the default for all new users, and **an account cannot be moved back**: `ALTER USER ... IDENTIFIED WITH mysql_native_password` returns `ERROR 4052`, and Cloud SQL exposes no flag for `authentication_policy` or `mysql_native_password`. So an affected deployment has no client-side workaround short of re-priming the auth cache by hand after every restart.

Cloud SQL Auth Proxy v1 is also no longer receiving development, and its own startup banner recommends migrating to v2.

### How to reproduce

Against a Cloud SQL for MySQL 8.4 instance with a user on `caching_sha2_password`:

1. Empty the auth cache with `FLUSH PRIVILEGES` — this is what a restart does. (`ALTER USER USER() IDENTIFIED BY ''` clears a single account's entry and needs no privileges.)
2. Immediately connect through proxy **v1** → `1045 Access denied`.
3. Repeat step 1, then connect through proxy **v2** → success.

Step 1 must be repeated before every trial. A single clear only makes the first attempt cold, because a successful connection re-primes the cache — this is why the problem is often misattributed to the client library.

Result, with the cache flushed before each row:

| client | proxy | cold cache |
|---|---|---|
| mysqlclient 2.2.8 | v1 | **FAIL 1045** |
| mysqlclient 2.2.8 | v2 | OK |
| mysql-connector-python 26.7 (`use_pure=True`) | v1 | **FAIL 1045** |
| mysql-connector-python 26.7 (`use_pure=True`) | v2 | OK |

The client library is not the variable — both send the password in cleartext over the unix socket on the full-auth path. Only the proxy version matters.

Confirmed inside Composer with a DAG that flushes the cache before each probe, connecting via `MySqlHook` through the provider's own `CloudSqlProxyRunner` (v1) and through a v2 runner:

```
proxy v1 FAIL cache=cold OperationalError: (1045, "Access denied for user ''@'cloudsqlproxy~' (using password: YES)")
proxy v2 OK cache=cold SELECT 1 returned ((1,),)
```

Failing task traceback (v1):

```
File ".../airflow/providers/mysql/hooks/mysql.py", line ..., in get_conn
...
MySQLdb.OperationalError: (1045, "Access denied for user ''@'cloudsqlproxy~' (using password: YES)")
```

### What you think should happen instead?

`CloudSqlProxyRunner` should be able to run Auth Proxy v2, ideally by default.

A drop-in replacement is straightforward: fetch the binary from
`https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy//cloud-sql-proxy..`,
invoke it as `cloud-sql-proxy --unix-socket `, and detect readiness from the socket
appearing rather than by parsing the startup banner.

**The socket layout is identical to v1's** (`/`), so existing
connection extras keep working and this can be a behaviour-compatible swap. Credentials come from
Application Default Credentials in both versions.

Suggested shape, in rough order of preference:

1. Default to v2, with an escape hatch to force v1 for anyone depending on its behaviour.
2. Or accept a `v2.x.x` value in `sql_proxy_version` (or add `sql_proxy_major_version`) and branch the
flag construction accordingly.

We have implemented (1) as a separate runner class and it works in Cloud Composer against MySQL 8.4
with a cold auth cache, using the unchanged `MySqlHook`/mysqlclient client. Roughly 100 lines,
including startup-timeout handling and surfacing the proxy's own stderr on early exit — the latter
matters because a misconfigured quota project makes v2 fail with a 403 that is otherwise invisible.

Two details worth carrying into any implementation:

- v2 does **not** create the socket directory, where v1 did. It needs an explicit `mkdir`.
- v2 attributes its Cloud SQL Admin API call to the credentials' quota project. Ambient ADC is correct
on a managed worker, but a developer's local ADC often carries an unrelated quota project, which
produces a confusing `403 SERVICE_DISABLED` naming a project unrelated to the instance. Worth an
optional `--quota-project` passthrough.

### Operating System

Linux — Cloud Composer 3 worker image (composer-3-airflow-3.3.1-build.0), Python 3.11.8

### Deployment

Google Managed Service for Apache Airflow

### Apache Airflow Provider(s)

google

### Versions of Apache Airflow Providers

apache-airflow-providers-google==22.3.0

### Official Helm Chart version

Not Applicable

### Kubernetes Version

_No response_

### Helm Chart configuration

_No response_

### Docker Image customizations

_No response_

### Anything else?

This occurs on **every** connection attempt made after the instance's auth cache is emptied, so in
practice it means every restart, failover or maintenance event breaks Cloud SQL MySQL 8.4 tasks until
something re-primes the cache out of band.

### Prior art

- #44564 added v2 support alongside service-account impersonation, but was **closed as stale** in
March 2025 after a requested rebase onto the new provider structure — it was never merged. This
report is narrower: v2 support as a correctness fix, independent of impersonation.
- #39546 (open) covers service-account impersonation, which is related but distinct.

### Note on diagnosis

If anyone investigating this tries to confirm it with the `mysql` CLI: `--get-server-public-key`
supplies an RSA key-exchange capability the affected clients do not have, **and** primes the server's
auth cache. It will report success against an instance that is genuinely broken, and mask the problem
for every subsequent connection. The MySQL error log
(`textPayload:"Access denied"` on `resource.type="cloudsql_database"`) is a more reliable signal — it
records user, source IP and timestamp, and cannot be fooled by a warm cache.

### Are you willing to submit PR?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)

Start with CloudSqlProxyRunner and _build_command_line_parameters() in cloud_sql.py, especially the v1 download settings and the -dir/-instances flags around lines 595-596. Compare the reported separate v2 runner and verify startup, socket readiness, timeout, and stderr handling against a cold MySQL 8.4 auth cache. Done means CloudSqlProxyRunner can use v2 while existing connection extras continue to work.

Written by the indexing model from the issue text.