Bug #43336 ANALYZE and OPTIMIZE do not honour --read-only
Submitted: 3 Mar 2009 17:01 Modified: 19 May 2010 14:33
Reporter: Andrew Hutchings Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:5.0 / 5.1 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[3 Mar 2009 17:01] Andrew Hutchings
Description:
The documentation states:
This variable is off by default. When it is enabled, the server allows no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables, nor does it prevent the server from inserting rows into the log tables (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).

Yet you can still run ANALYZE and OPTIMIZE from any user whilst in --read-only mode.

How to repeat:
1. Run a server with --read-only
2. As a non-super: ANALYZE TABLE or OPTIMIZE TABLE

Suggested fix:
In sql_parse.cc make the cases for SQLCOM_OPTIMIZE and SQLCOM_ANALYZE use the deny_updates_if_read_only_option() to deny them in --read-only mode when not super user.
[4 Jun 2009 10:39] Konstantin Osipov
Yes you can. What exactly is the problem here?
Would you like us to document explicitly that OPTIMIZE and ANALYZE are allowed under --read-only?
[24 Jun 2009 8:38] Geert Vanderkelen
Yes, lets document this explicitly. Lets reassign?
[25 Jun 2009 17:49] Andrew Hutchings
After analysing this issue further I have determined this is a bug, not a feature request.

ANALYZE and OPTIMIZE TABLE requires INSERT and SELECT privileges, yet in read only mode you cannot insert into tables without SUPER privilege.  You can, however, run both ANALYZE and OPTIMIZE table.  So either it is a bug or the INSERT privilege requirement should be removed for these two operations.

They also modify data files although not strictly changing the table structure.  This is not the behaviour expected of a option called 'read only'.
[19 May 2010 5:46] Konstantin Osipov
In InnoDB neither ANALYZE nor OPTIMIZE change data.
MyISAM does happen to write the new table statistics on disc. But MyISAM would also happen to try to do it in case of FLUSH TABLE, which is allowed under read_only (nothing new information is written though since setting read_only has a FLUSH TABLES under the hood).
[19 May 2010 5:48] Konstantin Osipov
Actually, since OPTIMIZE in InnoDB performs re-create, it does change data.
[19 May 2010 8:02] Konstantin Osipov
09:53 < serg> what --read-only was created for ? according to the manual
09:53 < serg>    *  `--read-only'
09:53 < serg>      Cause the slave to allow no updates except from slave threads or
09:53 < serg>      from users having the `SUPER' privilege. On a slave server, this
09:53 < serg>      can be useful to ensure that the slave accepts updates only from
09:53 < serg>      its master server and not from clients. This variable does not
09:53 < serg>      apply to `TEMPORARY' tables.
09:53 < serg>  
09:53 < serg> I personally don't think that OPTIMIZE or ANALIZE break this, 
              they (sic:tables) cannot be considered "updated" in my opinion
[19 May 2010 8:03] Konstantin Osipov
Tobias notes: if we change this behaviour, running mysqlcheck -Aa on slave will require SUPER, which it currently doesn't.
[19 May 2010 8:05] Andrew Hutchings
In which case please see my comment from the 25th June 2009.
[19 May 2010 8:06] Konstantin Osipov
This should be documented. Please mention in --read-only description that OPTIMIZE and ANALYZE are allowed, and why (--read-only is an option which purpose is to protect the slave from going out of sync. We allow to check consistency of the slave with mysqlcheck -aA and OPTIMIZE/ANALYZE even when this option is ON).
[19 May 2010 8:09] Andrew Hutchings
In reply to Tobias' comment.  My suggested fix would only require SUPER privilege in read-only mode.
[19 May 2010 11:25] Konstantin Osipov
See also Bug#53798 "OPTIMIZE TABLE breaks repeatable read"
[19 May 2010 14:33] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Modified read_only description:

read_only does not apply to TEMPORARY tables, nor does it prevent the
server from inserting rows into the log tables (see ). This variable
does not prevent the use of ANALYZE TABLE or OPTIMIZE TABLE
statements because its purpose is to prevent changes to table
structure or contents. Analysis and optimization do not qualify as
such changes. This means, for example, that consistency checks on
read-only slaves can be performed with mysqlcheck --all-databases
--analyze.