MySQL Bugs: #107246: FLUSH PRIVILEGES should provide more information on correct and unneeded usage

Bug #107246	FLUSH PRIVILEGES should provide more information on correct and unneeded usage
Submitted:	9 May 2022 14:24	Modified:	28 Jun 2022 9:27
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	ALL	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	flush privileges, grants, revoke, Waiting for acl cache lock

Description:
Looking here https://dev.mysql.com/doc/refman/8.0/en/flush.html#flush-privileges there's an explanation of what FLUSH PRIVILEGES does. However, the use of this command has evolved over the lifetime of MySQL and often it was required when changing privileges.

My understanding is that now if you use the following commands there's no need to run FLUSH PRIVILEGES afterwards:

- CREATE / ALTER / DROP USER
- GRANT / REVOKE
... maybe some others I've forgotten about

The main thing here is that if you directly manipulate the mysql grant related tables then clearly this won't be "seen" by the server. Many examples on the internet often suggest you run FLUSH PRIVILEGES after change "grant information" yet that seems not to be necessary.

There may be times when this command is needed but in most cases I'd expect that is no longer necessary.

I think that describing this more clearly, rather than the specific action it achieves would be useful and help avoid incorrect advice that may be posted on the web.

One thing that has been seen is that making grant related changes takes a global lock so FLUSH PRIVILEGES will also do this. On busy servers that locking may be problematic and has been seen to do some on a number of occasions on servers running 8.0.

How to repeat:
Search on the web about privilege management in MySQL and see if FLUSH PRIVILEGES is mentioned. If you see advice to use it when it's not necessary this indicates that people reminder older versions when it was more necessary. Perhaps on 5.5 and earlier? I don't remember now.

- https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permis... does mention FLUSH PRIVILEGES but uses it anyway. No mention of global locking.
- https://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database
- https://blog.devart.com/how-to-create-a-new-user-and-grant-privileges.html
- https://mediatemple.net/community/products/dv/204404494/how-do-i-grant-privileges-in-mysql

This is a very random tiny sample taken searching in Google which just shows the point.

Suggested fix:
- Improve the documentation of FLUSH PRIVILEGES along the lines indicated. Referring to historic usage may be useful.
- Make a note that some global locks are taken when running PRIVILEGE related statements. If they behaviour is different mention which ones.

Hello Simon,

Thank you for the documentation enhancement request.

regards,
Umesh

Hi Simon,

First, some items of note:

1. We are not responsible for what others external to Oracle say about
MySQL. It is a given that what these others provide is *not* official
documentation.

2. We generally do not spend a great deal of time discussing things that
don't happen or that aren't done with MySQL. A list of such things could soon
grow to an unmanageable length.

3. We generally do not discuss behaviour of old versions; we provide
information about the behaviour that applies in the current major version.
(And see (2).) Adding back info long since removed about versions 10+ years old will not solve any of the issues raised here.

The requested information regarding grants and FLUSH PRIVILEGES is already present in the Manual, and this behaviour is the same for all supported versions of MySQL. E.g. see https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html. mysqldoc rev 73601 adds a cross-reference to this section from the description of FLUSH PRIVILEGES, in all current versions of the Manual.

Closed.