Uncommon Sense MySQL - When EXPLAIN Can Trash Your Database

When EXPLAIN Can Trash Your Database If 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:

DELIMITER $$
CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET latin1
    DETERMINISTIC
BEGIN 
delete from test.t1;
RETURN 'OK'; 
END $$

Query OK, 0 rows affected (0.01 sec)

DELIMITER ;

mysql> create table t1(i int);
mysql> insert into t1 values(1); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1; 
+------+
| i    |
+------+
|    1 |
+------+
1 row 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 |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
2 rows in set, 1 warning (0.00 sec)


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

DELIMITER $$

CREATE FUNCTION `cleanup`() RETURNS char(50) CHARSET latin1

DETERMINISTIC

BEGIN

delete from test.t1;

RETURN 'OK';

END $$

Query OK, 0 rows affected (0.01 sec)

DELIMITER ;

mysql> create table t1(i int);

mysql> insert into t1 values(1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+------+

| i |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> explain select * from (select cleanup()) as t1clean;

+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+

2 rows in set, 1 warning (0.00 sec)

mysql> select * from t1;

Empty set (0.00 sec)

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:

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

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

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.

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

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

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

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

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

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.

4 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments

Justin Swanhart

3 years ago

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.