Bug #93907 explain update is prevented when read_only is on
Submitted: 14 Jan 2019 6:02 Modified: 15 Jan 2019 9:20
Reporter: zhai weixiang (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0, 8.0.13, 5.7.24 OS:Any
Assigned to: CPU Architecture:Any

[14 Jan 2019 6:02] zhai weixiang 
Description:
When read_only is on, EXPLAIN dml will reports error ER_OPTION_PREVENTS_STATEMENT, But as far as I know, explain didn't change any data, and should be allowed to execute when read_only is on

How to repeat:
--connection default
SELECT @@VERSION;

CREATE TABLE t1(a INT) ENGINE=INNODB;
INSERT INTO t1 VALUES(1),(2),(3);

CREATE USER wx;
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'wx'@'%';
SET GLOBAL READ_ONLY = 1;

connect (con1,127.0.0.1,'wx',,test,$MASTER_MYPORT,);

--connection con1
--error 1290
EXPLAIN UPDATE t1 SET a = a + 1;

--connection default
disconnect con1;
SET GLOBAL READ_ONLY = 0;
DROP TABLE t1;

Suggested fix:
allow to explain DML when read_only is on
[14 Jan 2019 6:30] MySQL Verification Team 
Hello Zhai,

Thank you for the report and test case.

regards,
Umesh
[14 Jan 2019 11:25] Alexey Kopytov 
I guess it's another duplicate of bug #74543. See also bug #74542.
[15 Jan 2019 9:20] Norvald Ryeng 
Marked as duplicate of bug#74543.