wsrep_sync_wait silently ignored after COM_CHANGE_USER (causal reads skipped, stale reads)

Upstream references:

• https://github.com/mariadb-corporation/galera/issues/613 (open, reported on 10.6.5)
• https://perconadev.atlassian.net/browse/PXC-4003 (same issue, fixed in Percona XtraDB Cluster)
• https://github.com/sysown/proxysql/pull/3929 (ProxySQL workaround citing galera#613)

Summary

After a client connection executes COM_CHANGE_USER, wsrep_sync_wait no longer has any effect on that connection.
The session still reports @@session.wsrep_sync_wait=1, but reads do not perform a causal wait: wsrep_causal_reads does not increase, and reads can return data older than writes already committed on another node.
Running SET SESSION wsrep_sync_wait=1 again after COM_CHANGE_USER does not restore the behaviour.
A new connection works correctly.
Reproduced on 10.6.17-13 and on 10.6.28-24, the latest 10.6 Enterprise release, so it is not fixed in current 10.6.

Impact

Connection poolers that reset backend connections with COM_CHANGE_USER hit this on almost every request.
ProxySQL does this by default (mysql-reset_connection_algorithm=2, mysql-connpoll_reset_queue_length=50).
The same applies to other client libraries or pools that use change_user for resets.
Any application that relies on wsrep_sync_wait for read-after-write consistency across nodes silently gets stale reads.
In a real-world setup, this shows up as intermittent application errors: a row updated on one node is not found when it is read on another node through ProxySQL.
Direct connections to MariaDB, which never issue COM_CHANGE_USER, do not reproduce the problem.

How to repeat

Live 2-node cluster (attached: galera613_test.py)

Setup: 2 nodes, run on 10.6.17-13 (Galera 26.4.17) and on 10.6.28-24 (Galera 26.4.27), SET GLOBAL wsrep_sync_wait=1 on both nodes, no other traffic, no ProxySQL.

For each scenario, a writer connection on node 2 inserts a row with autocommit, and a reader connection on node 1, opened fresh for each scenario, immediately runs SELECT COUNT for that id.
This is repeated 200 times.
The script records the increase of wsrep_causal_reads on node 1, the number of reads that did not find the row (stale), and the average read latency.

python3 galera613_test.py --node1 <node1> --node2 <node2> --user <user> [--password <pw>]

Requirements: pip install mysql-connector-python (or mariadb). The user needs CREATE, INSERT, SELECT and DROP on the test database (default: test) and must be able to connect over TCP to both nodes.

Results on 10.6.17-13-MariaDB-enterprise, Galera 26.4.17:

Results on 10.6.28-24-MariaDB-enterprise, Galera 26.4.27:

On both versions, in every scenario after COM_CHANGE_USER, the connection behaves exactly like wsrep_sync_wait=0, in causal read count, stale reads and latency, although the variable reads 1.
Scenario 6 shows that re-setting the variable explicitly does not help.
This suggests the causal-wait path depends on some per-connection wsrep state that is not re-initialised by COM_CHANGE_USER, rather than on the variable value itself (not analysed in code).

Workarounds

• ProxySQL: mysql-reset_connection_algorithm=1 and mysql-connpoll_reset_queue_length=0, so connections that would need COM_CHANGE_USER are not reused (PR #3929).
• Session affinity at the application layer, so that reads go to the node that performed the write.
• Re-setting wsrep_sync_wait after COM_CHANGE_USER does not work (scenario 6).

• A draft for the galera suite is attached (galera_sync_wait_change_user.test). It has not been run.