use caching_sha2_password for root

[komorny]

Hello. I have a root user with authentication via caching_sha2_password and I cannot find a way to manage other users through this user, because the mysql_user module cannot log in to the server under the root account. Also in the documentation it is stated "Currently, there is only support for the mysql_native_password encrypted password hash module.", which is unacceptable at a time when the use of mysql_native_password is obsolete and will be deprecated in the next versions of mysql. Is there any way to manage from ansible mysql users via root account that uses caching_sha2_password authentication? If not, when will it be? Well thank you.

[Andersson007]

@komorny hello, thanks for opening the discussion!
I can say nothing about the plugins, maybe our other maintainers can help?
cc @laurent-indermuehle @Jorge-Rodriguez @rsicart @betanummeric @markuman

[laurent-indermuehle]

In my last contribution, I noticed that the new MySQL password plugins can be difficult to use correctly. I apologize, but I do not have time to work on this. However, as I recently explored this topic and am familiar with our integration tests, I would be happy to provide guidance to anyone willing to submit a pull request to address this.