Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.
Mermaids have the same probability of fixing your permission problems, but people continue believing in the FLUSH PRIVILEGES myth.

I see suggesting the usage of FLUSH PRIVILEGES every time someone writes a tutorial or a solution to a problem regarding creating a new account or providing different privileges. For example, the top post on /r/mysql as of the writing of these lines, “MySQL:The user specified as a definer does not exist (error 1449)-Solutions” has multiple guilty cases of this (Update: the user has corrected those lines after I posted this article).

It is not my intention to bash that post, but I have seen committing that mistake many, many times. Even if you go to the reference manual for the GRANT command, you will see a comment at the bottom -from a third party user- using GRANT and then FLUSH PRIVILEGES.

Why should I bother? Is executing FLUSH PRIVILEGES an issue? Why is everybody doing it? The reason why that command exists is because —in order to improve performance— MySQL maintains an in-memory copy of the GRANT tables, so it does not require to read it from disk on every connection, every default database change and every query sent to the server. The mentioned command forces the reload of this cache by reading it directly from disk (or the filesystem cache) as the MySQL reference manual itself clearly indicates (having even its own section: When Privilege Changes Take Effect). However, its execution is unnecessary in most practical cases because:

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

Then only reason to perform that reload operation manually is when:

you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE

For most operations, like creating a user, changing its privileges, or changing its password, you will want to use the high-level statements. Not only they are easier to use and they are compatible with a larger number of MySQL versions, but they will also prevent you from making mistakes (of course, remember to setup the NO_AUTO_CREATE_USER sql mode). They even usually work nicely in a MyISAM-hostile environment like a Galera cluster. There are certainly reasons to edit the tables manually- as an administrator, you may want to tweak the privileges in a special way or import the mysql.* tables from elsewhere, so in those cases running FLUSH PRIVILEGES is mandatory. Please note that, as the manual page says, in most cases (e.g. global privileges) changing a user’s grants will only affect new connections, and certainly never to ongoing queries, as privileges are checked at the beginning of the query processing- read the manual page for details.

So, again, why my crusade against the overuse of FLUSH PRIVILEGES, after all, worst case scenario, the same privileges will be loaded again! It is not a question of performance issues. Although, in an extreme case it certainly can be an issue. Check for example the following script, that executes 10 000 CREATE USER statements (this can only be done in a single thread as the grant tables are still in MyISAM format, even in 5.7.6):

def execute_test(port, drop, flush, thread):
   db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
   for i in range(0, TIMES):
      cursor = db.cursor()
      if (drop):
         sql = "DROP USER 'test-" + `thread` + '-' + `i` + "'@'localhost'"
         sql = "CREATE USER 'test-" + `thread` + '-' + `i` + "'@'localhost' IDENTIFIED BY '" + DEFAULT_PASSWORD + "'"
      if (flush):
         cursor = db.cursor()
         flush_sql = "FLUSH PRIVILEGES"

The timing for both executions are:

$ time ./
Not flushing
Executing the command 10000 times

real    0m15.508s
user    0m0.827s
sys     0m0.323s

$ ./ -d
Not flushing
Dropping users
Executing the command 10000 times

$ time ./ -f
Flushing after each create
Executing the command 10000 times

real    2m7.041s
user    0m2.482s
sys     0m0.771s

$ ./ -d
Not flushing
Dropping users
Executing the command 10000 times

We can see that using FLUSH PRIVILEGES is 8x slower that not using them. Again, I want to stress that performance is not the main issue here, as most people would execute it only once at the end of each command block, so it wouldn’t be a huge overload. Even if there is some extra read IO load, we must assume that every round trip to the database, and every commit takes some server resources -so that can be extrapolated to any command. Additionally, concurrency issues is not a typical problem for MySQL account creation, as the mysql.user table it not usually (or should not be) very dynamic.

The main issue I have against the overuse of FLUSH PRIVILEGES is that people execute it without really understanding why they do it and what that command actually does. Every time a person has a problem with MySQL privilege systems, the first piece of advice that is given is to execute this command “just in case”. Check, for example, answers on dba.stackexchange like this, this and this (which I have selected among many others), and where the original user was not altering manually the mysql.* tables. The issue is that in most cases this command does nothing, and the real problem lays on the poor understanding of MySQL’s permission model. As the saying tells- when you have a hammer, every problem looks like a nail. People read that that is a proper way to solve permission-related problems, and they pass the “knowledge” on, creating basically the MySQL equivalent of an urban myth.

So, the next time you encounter a problem with a user not being able to log it, or apply privileges to a user, there are many other sources of issues such as: using old_passwords, using a different authentication method than the native passwords, not having the actual privileges or the WITH GRANT OPTION properties to apply them, your server not identifying you with the same user or host than the one you are actually in, using skip-name-resolve so dns entries are ignored, waiting for a new connection for the changes to take effect, … and many other issues that come with authorization and authentication. MySQL grant system is not precisely obvious and perfect (Hello, granting permissions from databases that do not exist?), but taking 5 minutes to read the extensive manual on privileges can avoid you many headaches in the future. TL;TR RTFM

For those people that already know when to use or not to use FLUSH PRIVILEGES, please, next time you find someone overusing it, educate the user on best practices so people no longer relay in magic and urban myths to solve problems, go to reddit/stackoverflow/your favorite social network/etc. and upvote good practices/comment on bad practices. Today it could be FLUSH PRIVILEGES, tomorrow it could be “add OPTIMIZE TABLE in a cron job every 5 minutes for your InnoDB tables” (and yes, that last one was actually found in the wild).

Tagged on:                                                         

10 thoughts on “Stop using FLUSH PRIVILEGES

  • 2015-04-02 at 19:43

    Yes, I think the suggestion to run FLUSH PRIVILEGES needlessly is made by people who can’t remember what that command does, or that GRANT/REVOKE/CREATE USER do the same thing automatically.

    It’s like people who use vim, who hit escape three or four times in a row, because they can’t remember that a single escape is enough to return you to command mode, whether you’re in insert mode or in ex mode.

    By the way, here’s a suggestion to run OPTIMIZE TABLE after every page load of a PHP application!

    • 2015-04-02 at 19:49

      Wow! When I answer a question, I try to literally answer what is requested literally first (you never know what actual reason lead to certain questions), but then I recommend a preferred way. In most cases, they stick to my recommendation. I cannot see a reason to do that, for every http request! :-O

      Your comments are always welcome, thank you Bill!

    • 2015-04-03 at 13:58

      Running OPTIMIZE TABLE after every page load would have made a good april fools recommendation 🙂

      • 2018-01-31 at 14:56

        I dont think so. Some Joker would spread it as truth as fire on a dry forest, messing up people doing real work… we already have plenty of dumbness to deal with daily…

  • 2015-04-03 at 18:18

    Hey @jynus:disqus, thanks for going through & creating a post about it. I corrected to convey the message.
    BTW you meant “10000 CREATE USER” when you write “10000 CREATE TABLE” above…

    @billkarwin:disqus yeah I hit escape more than once at times 🙂

    • 2015-04-03 at 18:55

      Thank you for the correction- I have amended it.

      Please note that, as I stated at the beginning of the post, it was not my intentions to criticize your article- which surely is very useful to many people, but it was merely a trigger for it, as I have seen it in many places.

      If you prefer it, I can delete the link if it is harmful to you, as it was not my intention to attack your content- we need more people writing guides and tutorials like you did. Regards,

      • 2015-04-03 at 21:01

        Hey @jynus:disqus… This was “multiple-esc-keystrokes” scenario for me, I better correct myself.
        So no-harm & nothing to delete! This article is linked mentioning you in the “Update” section too…
        Do comment in future if you see any issues.

        Happy G’Friday!

  • 2015-04-07 at 15:35

    I agree with you that altering the mysql tables isn’t the way you should change grants. With Galera it could even potentially lead to differences between the nodes as the insert/update/delete statements are replicated but flush privileges isn’t. You can obviously log into every node and issue the flush privileges manually, but doing it the proper way is way quicker. 😉

    • 2015-04-07 at 15:48

      Thank you Art for taking the time to read the article and commenting it. Your comments are greatly appreciated and you are absolutely right- Galera is becoming more and more popular and it is another reason to avoid those low level statements!

      Another, appointed by a friend, is that you cannot longer assume that the native passwords are the only way to authenticate a user for MySQL. That is why they are changing/disabling many related commands in 5.7.

  • 2015-07-17 at 19:52

    OMG the constant “FLUSH PRIVILEGES” advice you find on the net makes me batty. I’ll be pointing people to this article.

Comments are closed.