[mysql] FLUSH PRIVILEGES is only required after making manual edits to tables such as mysql.user, not after CREATE, ALTER or DROP USER

[alereca]

What problem are you facing?

Why every time a CREATE, ALTER or DROP USER statement is executed, FLUSH PRIVILEGES is called after? It's also present after GRANT and REVOKE statements.

According to mysql documentation executing FLUSH PRIVILEGES is only required If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. (https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html). Other references:

• https://stackoverflow.com/questions/36463966/mysql-when-is-flush-privileges-in-mysql-really-needed
• https://docs.pingcap.com/tidb/dev/sql-statement-flush-privileges

How could Crossplane help solve your problem?

Remove FLUSH PRIVILEGES statements from user and grant controllers, for example:

if err := c.db.Exec(ctx, xsql.Query{
		String: "FLUSH PRIVILEGES",
	}); err != nil {
		return managed.ExternalCreation{}, errors.Wrap(err, errFlushPriv)
	}

https://github.com/crossplane-contrib/provider-sql/blob/master/pkg/controller/mysql/user/reconciler.go#L271

I would like to work in a pr if this is considered as desirable