Why the explain command may modify MySQL data

If someone asked you whether running EXPLAIN on a query would change your database, you would probably say no; that's usually the view. EXPLAIN is supposed to show us how a query is executed, not execute it, so it cannot change any data.

Unfortunately, common sense doesn't apply in this case with MySQL (at the time of writing this post, MySQL 8.0.21 and prior) - there are cases where explain can change your database, as this bug shows:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.31 |
+-----------+
1 row in set (0.01 sec)

 mysql> DELIMITER $$
mysql> CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET utf8mb4
    -> DETERMINISTIC
    -> BEGIN
    -> delete from test.t1;
    -> RETURN 'OK';
    -> END $$
Query OK, 0 rows affected (0.00 sec)

 mysql>

mysql> select * from t1$$
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
+------+------+
2 rows in set (0.00 sec)

 mysql> explain select * from (select cleanup()) as t1clean$$
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+------+----------------+
| 1 | PRIMARY | <derived2> | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+------+----------------+
2 rows in set, 1 warning (0.01 sec)

 mysql> select * from t1$$
Empty set (0.00 sec)

 mysql>

The problem here is that explain executes the stored function cleanup(), which can modify data.

This differs from the more sane PostgreSQL behavior, which does not execute stored functions when running EXPLAIN (it does if you run EXPLAIN ANALYZE ).

In MySQL, this decision comes from trying to do the right thing and provide the most reliable explanation (the query execution plan may well depend on what the stored function returns), but this safety tradeoff does not seem to be considered.

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

mysql> explain select * from (select sleep(5000) as a) b;

This will run for over an hour.

While this behavior is unfortunate, it only occurs when you have unrestricted permissions. If you have a more complex setup, the behavior might be different.

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

mysql> explain select * from (select cleanup()) as t1clean;
ERROR 1370 (42000): execute command denied to user 'abce'@'localhost' for routine 'test.cleanup'

This will also fail if the user has EXECUTE privileges, but the user executing the stored function does not have DELETE privileges:

mysql> explain select * from (select cleanup()) as t1clean;
ERROR 1142 (42000): DELETE command denied to user 'abce'@'localhost' for table 't1'

So what if one wants to make EXPLAIN safer, for example if one is developing a tool like Percona Monitoring and Management which, among other things, allows users to run EXPLAIN on their queries?

It is recommended that users set permissions for proper monitoring. This should be the first line of defense for this (and many other) problems, however, it is difficult to rely on. Many users will choose the easy way and will use the "root" user with full privileges for monitoring.

Wrap the EXPLAIN statement in BEGIN ... ROLLBACK, which will undo any damage that EXPLAIN might have done. The downside is of course the "work" of deleting the data, and when you undo the work you will have done it. (Note: of course, this only applies to transactional tables. If you're still running MyISAM, there are more serious issues to worry about in that case)

Use "set transaction read-only" to indicate that you do not want any write operations. In this case, an EXPLAIN that attempts to write the data will fail and do no work.

While these workarounds make it safer for tools to run EXPLAIN, it does not help users running EXPLAIN directly, and I really hope that this problem will be solved by redesigning EXPLAIN so that it does not try to run stored functions, as PostgreSQL does. For those who want to know how exactly a query is executed, there is now EXPLAIN ANALYZE.

The above is the details of why the explain command may modify MySQL data. For more information about the explain command to modify MySQL data, please pay attention to other related articles on 123WORDPRESS.COM!