Bug #84081 super_read_only and read_only not allowing OPTIMIZE TABLE on 5.7
Submitted: 6 Dec 2016 18:38 Modified: 9 Dec 2016 7:04
Reporter: Gillian Gunson (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.7.14, 5.7.16 OS:Ubuntu
Assigned to: CPU Architecture:Any
Tags: error, Optimize, read_only, super_read_only

[6 Dec 2016 18:38] Gillian Gunson
Description:
The official documentation for super_read_only suggests that it's like read_only, except that the restrictions apply to SUPER users: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_super_read_only

The docs for read_only say that OPTIMIZE TABLE is still allowed (http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only), and it is for 5.6; but in my testing of 5.7 both read_only and super_read_only restrict it. This is the case with InnoDB and MyISAM.

If read_only is ON but super_read_only is OFF, the super user can optimize a table but a regular user can't.

If super_read_only is ON (and therefore read_only=ON too), neither user can optimize a table. 

How to repeat:
####### as SUPER user 

mysql> show global 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)

mysql> optimize table default_table;
+---------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                           | Op       | Msg_type | Msg_text                                                          |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
| schema_production.default_table | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| schema_production.default_table | optimize | status   | OK                                                                |
+---------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.23 sec)

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

mysql> show global variables like '%read_only';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.00 sec)

mysql> optimize table default_table;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

###### with non-SUPER user

mysql> show global variables like '%read_only';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+
4 rows in set (0.00 sec)

mysql> optimize table default_table;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

mysql> show global 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.01 sec)

mysql>  optimize table default_table;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement

Suggested fix:
Update the 5.7 docs to specify the change in behaviour?
[9 Dec 2016 7:04] MySQL Verification Team
Hello Gillian,

Thank you for the report and feedback.
Verified as described.

Thanks,
Umesh
[1 Feb 2017 23:17] Ceri Williams
Related "Unable to analyze table whilst super_read_only is enabled" 
https://bugs.mysql.com/bug.php?id=81442
[9 Nov 2021 15:27] Anthony Maidment
This issue persists in version 8.0.27

The docs (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only) state:
When read_only is enabled and when super_read_only is enabled, the server still permits these operations:
- Use of ANALYZE TABLE or OPTIMIZE TABLE statements. The purpose of read-only mode is to prevent changes to table structure or contents. Analysis and optimization do not qualify as such changes.

Replicating the above, running as root, with ALL privileges:

mysql> show global variables like '%read_only';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_read_only | OFF   |
| read_only        | ON    |
| super_read_only  | ON    |
| tx_read_only     | OFF   |
+------------------+-------+

Query: optimize table `table_name`

Error Code: 1290
The MySQL server is running with the --super-read-only option so it cannot execute this statement