read_only mode prevents non-SELECT EXPLAIN statements

[monty solomon]

Setting super_read_only prevents EXPLAIN DELETE, INSERT, REPLACE, or UPDATE statements from executing.

mysql> SET GLOBAL super_read_only=1;

mysql> EXPLAIN DELETE FROM users WHERE id=1\G
ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

mysql> EXPLAIN INSERT INTO users SET id=1\G
ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

mysql> EXPLAIN REPLACE INTO users SET id=1\G
ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

mysql> EXPLAIN UPDATE users SET id=2 WHERE id=1\G
ERROR 1290 (HY000): The MySQL server is running with the --read-only (super) option so it cannot execute this statement

Server version: 5.6.25-73.1-log Percona Server (GPL), Release 73.1, Revision 07b797f

[Przemek (pmalkowski)]

It is not "super_read_only" specific, it happens also for normal read_only:

mysql [localhost] {msandbox_ro} (test) > select @@version,@@version_comment,@@read_only;
+-----------+------------------------------+-------------+
| @@version | @@version_comment | @@read_only |
+-----------+------------------------------+-------------+
| 5.6.26 | MySQL Community Server (GPL) | 1 |
+-----------+------------------------------+-------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox_ro} (test) > explain delete from foo;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

slave1 [localhost] {msandbox_ro} (test) > select @@version,@@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment |
+-----------+------------------------------+
| 5.7.9-log | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.01 sec)

slave1 [localhost] {msandbox_ro} (test) > show variables like '%read_only';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+
4 rows in set (0.00 sec)

slave1 [localhost] {msandbox_ro} (test) > explain update foo set bar=1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

[monty solomon (monty+launchpad)]

The non-SELECT EXPLAIN statements work for me when using read_only mode. They only fail when using super_read_only mode.

mysql Ver 14.14 Distrib 5.6.27, for osx10.8 (x86_64) using EditLine wrapper

mysql> explain delete from foo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | 3 | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

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

mysql> explain delete from foo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | 3 | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

[monty solomon (monty+launchpad)]

mysql Ver 14.14 Distrib 5.6.25-73.1, for osx10.8 (x86_64) using EditLine wrapper

mysql> use test;
Database changed
mysql> explain delete from foo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | 1 | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.01 sec)

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

mysql> explain delete from foo;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | 1 | Deleting all rows |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

[Przemek (pmalkowski)]

Monty, but only if your user has SUPER privilege, right? In that case, 'read_only' simply does not apply.

mysql [localhost] {msandbox_ro} (db1) > select @@version,@@version_comment;
+-------------+------------------------------------------------------+
| @@version | @@version_comment |
+-------------+------------------------------------------------------+
| 5.6.26-74.0 | Percona Server (GPL), Release 74.0, Revision 32f8dfd |
+-------------+------------------------------------------------------+
1 row in set (0.01 sec)

mysql [localhost] {msandbox_ro} (db1) > show variables like '%read_only';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+
4 rows in set (0.06 sec)

mysql [localhost] {msandbox_ro} (db1) > show grants;
+-------------------------------------------------------------------------------------------+
| Grants for msandbox_ro@localhost |
+-------------------------------------------------------------------------------------------+
| GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'localhost' IDENTIFIED BY PASSWORD <secret> |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox_ro} (db1) > explain delete from foo;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

[monty solomon (monty+launchpad)]

Thanks, I forgot the user I was testing had SUPER privileges.

Should this bug be marked that it affects 5.7 too?

[Laurynas Biveinis (laurynas-biveinis)]

We will mark bugs as affecting 5.7 once we have the 5.7 tree

[monty solomon (monty+launchpad)]

I asked because I saw that there are other bugs marked for 5.7

https://bugs.launchpad.net/percona-server/5.7

[Laurynas Biveinis (laurynas-biveinis)]

These are either bugs directly applying to the 5.7 port in progress, upstream bugs known to apply for 5.7 or to be fixed there, or simply marked by mistake.

[monty solomon (monty+launchpad)]

The upstream bug reports that it applies to 5.7.

[Laurynas Biveinis (laurynas-biveinis)]

Ah, OK

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1661