MySQL Bugs: #74543: read_only=1 prevents EXPLAIN UPDATE/DELETE/INSERT

Bug #74543	read_only=1 prevents EXPLAIN UPDATE/DELETE/INSERT
Submitted:	24 Oct 2014 10:11	Modified:	24 Oct 2014 11:48
Reporter:	Alexey Kopytov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6/5.7, 5.7.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Setting read_only to 1 makes EXPLAIN UPDATE/DELETE/INSERT fail with an error, even though they do no modify data.

Assuming connection mysql2 is root, and mysql1 is a non-root user:

mysql1> create table t (a int);
Query OK, 0 rows affected (0.02 sec)

mysql1> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

mysql2> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql1> explain insert into t values(2);
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql1> explain update t set a=2;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql1> explain delete from t;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

How to repeat:
See above.

Thank you for the bug report.

What are the plans to fix this bug?

The documentation states that this feature should be working as of version 5.6.3.

As of MySQL 5.6.3, permitted explainable statements for EXPLAIN are SELECT, DELETE, INSERT, REPLACE, and UPDATE. Before MySQL 5.6.3, SELECT is the only explainable statement.

Bug #86860 marked as duplicate of this one