Cannot use caching_sha2_password using XDevAPI, unless using mysqlsh first
I am writing some code that uses the XDevAPI (using Go, but that doesn't matter
really). But the authentication, though working, is giving me headaches, the
caching part of it.
I understand the caching of the password in (server) memory, and that it has
to be first done over TLS. Next time, until server restart (or FLUSH PRIVILEGES).
What I am doing:
1. Negotiate and set capability tls=1
2. Loop over methods MYSQL41 and SHA256_MEMORY
1) write AuthenticateStart
2) read AuthenticateContinue
3) Scramble password using challange got from (2)
4) write AuthenticateContinue
5) read message, should be SessionStateChanged
6) read AuthenticateOk
So it works with SHA256_MEMORY, but only when I first used `mysqlsh`
using the same user. If I do `FLUSH PRIVILEGES`, auth fails again with my code.
Giving me a "ERROR 1045 (HY000): Access denied for user",
TLS handshake worked, that's in place. Status var Mysqlx_ssl_accepts
increases. In mysqlsh, status shows the ciphers...
Looking at other Connectors (J/Python/.Net/C), I cannot figure it out
what the trick is so that the connectors first authentication is
cached. I follow the UMLs, the documentation, .. no avail.
What is the trick? What setting? What message has to be sent so that
my code tells server to "cache password!"? What am I missing??
What is the magical sauce mysqlsh uses to make it happen?