Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database

When EXPLAIN Can Trash Your DatabaseIf I ask you if running EXPLAIN on the query can change your database, you will probably tell me NO; it is common sense. EXPLAIN should show us how the query is executed, not execute the query, hence it can’t change any data.

Unfortunately, this is the case where common sense does not apply to MySQL (at the time of this writing MySQL 8.0.21 and previous versions) – there are edge cases where EXPLAIN can actually change your database as this Bug illustrates:

The problem is EXPLAIN executes the cleanup() stored function… which is permitted to modify data. This is different from the more sane PostgreSQL behavior which will NOT execute stored functions while running EXPLAIN (it will if you run EXPLAIN ANALYZE).

This decision in the MySQL case comes from trying to do the right stuff and provide the most reliable explain (query execution plan may well depend on what stored function returns) but it looks like this security tradeoff was not considered.

While this consequence of the current MySQL EXPLAIN design is one of the most severe, you also have the problem that EXPLAIN – which a rational user would expect to be a fast way to check the performance of a query – can take unbound time to complete, for example:

This will run for more than an hour, creating an additional accidental (or not) Denial of Service attack vector.

Going Deeper Down the Rabbit Hole

While this behavior is unfortunate, it will happen only if you have unrestricted privileges.  If you have a more complicated setup, the behavior may vary.

If the user lacks EXECUTE privilege, the EXPLAIN statement will fail.

If the user has EXECUTE privilege but the user executing the stored function lacks DELETE privilege, it will fail too:

Note: I’m saying user executing stored function, rather than the current user, as depending on the SECURITY clause in Stored Function definition it may be run either as definer or as invoker.

So what can you do if you want to improve EXPLAIN safety, for example, if you’re developing a tool like Percona Monitoring and Management which, among other features, allows users to run EXPLAIN on their queries?

  • Advise users to set up privileges for monitoring correctly.  It should be the first line of defense from this (and many other) issues, however, it is hard to rely on.  Many users will choose the path of simplicity and will use “root” user with full privileges for monitoring.
  • Wrap your EXPLAIN statement in BEGIN … ROLLBACK which will undo any damage EXPLAIN may have caused. The downside of course is the “work” of deleting the data and when undoing the work will be done. (Note: Of course this only works for Transactional tables, if you still run MyISAM…. Well in this case you have worse problems to worry about.)
  • Use “ set transaction read-only”  to signal you’re not expecting any writes…   EXPLAIN which tries to write data will fail in this case without doing any work.

While these workarounds can have tools running EXPLAIN safer, it does not help users running EXPLAIN directly, and I really hope this issue will be fixed by redesigning EXPLAIN in a way it is not trying to run stored functions, as PostgreSQL already does.

For those who want to know how the query is executed EXACTLY, there is now EXPLAIN ANALYZE.

Share this post

Comments (4)

  • Justin Swanhart Reply

    It is a bad idea to create functions that change data, in a best practices sense. Stored routines should modify data, and stored functions should just display it. Obviously, the SQL standard and MySQL allow stored functions to modify data, but because this side effect is well known, that is not a best practice, and it should be avoided.

    November 23, 2020 at 7:12 pm
    • peterzaitsev Reply

      I agree. In practice through people do not always follow best practices and we need to care about behavior for such people too.

      November 24, 2020 at 12:03 pm
  • Gytis Repečka Reply

    Did you have a chance to test if it affects MariaDB too?

    November 24, 2020 at 2:07 am
    • peterzaitsev Reply

      Nope. But there is a test case right in the blog post so you can test it and let us know!

      November 24, 2020 at 11:52 am

Leave a Reply